A PostgreSQL cluster is an "instance" in Oracle parlance, (this is not the "normal" definition - see below) working on one machine.
You can have a PostgreSQL instance (cluster) with just one database, apart from the two templates (see below). To have a working system, you'll have 3 databases - two templates and one "working" database.
All of the PostgreSQL databases you create (i.e. company, organisation...) can have one or more schemas that you may also create. Any database can have multiple schemas - logical separation of functions - hr, accounting &c., i.e within your overall company/organisation.
You'll get the postgres
database and template0
and template1
by default. You should never touch template0
- it can render your system inoperable - here's blog about how to copy databases from templates. The templates are "skeletons" - from which you create databases - all of the settings (from postgresql.conf
) and system catalogs are there, but no ordinary tables! If you issue a \d
from within template1, you'll receive the message: Did not find any relations.
On the same machine, you can have many clusters (PostgreSQL definition - see discussion of clusters below) as you want (within reason) using different ports. Production machines would typically use 5432 and dev/UAT machines might have a few clusters (i.e. instances) using different ports - running small test databases isn't very resource intensive!
All of these databases can have their own (set of) schema(s) - so you could have (for example) 3 (PostgreSQL definition of) clusters running on ports 5432, 5433 and 5434, each with an hr schema, an accounting schema (as many schemas as you want - within reason).
You are not obliged to create (a) schema(s) - it can be helpful for the logical separation of large databases into their constituent sections (c.f. hr/accts...)
Re clusters!
I think I see the reason for the confusion re clusters/databases/schemas!
PostgreSQL is very old - it derives from Ingres:
Ingres began as a research project at UC Berkeley, starting in the
early 1970s and ending in 1985.
That's almost a full decade before Oracle's first release in 1979. It uses an older vocabulary than most systems' documentation.
Notice the terms that I've used:
System catalogs
(rather than the more usual system "tables")
relations
(again, rather than "tables" - PostgreSQL makes a distinction between system tables (catalogs) and ordinary tables (relations)).
PostgreSQL people are fond of using other terms i.e. tuple
which has largely been replaced by "record" and/or "row" and attribute
which has been replaced by "column" in other systems and in general usage. This change has possibly been driven by the ubiquity of spreadsheets!
These terms derive from relational calculus which derives from a paper written by Ted Codd which uses mathematical language. The originator of the Ingres system was Michael Stonebraker, an academic, hence the retention of (what might be considered) overly academic terms.
Nowadays, a "cluster
" is considered to be:
A computer cluster is a set of computers that work together so that
they can be viewed as a single system.
This is not the PostgreSQL definition - possibly stemming from an older usage - I couldn't find any links for this, so it's speculation on my part!
The best definition of a cluster for PostgreSQL is PostgreSQL's own definition:
A database cluster is a collection of databases that is managed by a
single instance of a running database server.
Note, there is nothing there about multiple machines - it is a single instance of a running database server! One can have many (PostgreSQL) clusters (i.e. instances) on a single machine - the PG definition is, in some ways, the inverse of what is more commonly accepted as the definition of a cluster (paraphrasing):
Normal definition: Many machines, one system
PostgreSQL definiton: Many systems, one machine
Re HA
Doing replication with Master/Slave will automatically have two PostgreSQL clusters located on different machines - which may involve more than one database, but as I said, in PROD, it's normally dedicated to one database (alongside your skeletal templates, which you can't delete).
You'll have to have failover provision and then you have a cluster in the modern sense - many machines, one system. A full discussion of PostgreSQL High Availability would be an answer in itself and there are many different options - I would read what PostgreSQL themselves have to say about this and also, this post by PostgresPro (big hitters in the PostgreSQL world) which provides a list of 4 systems which can do this job:
Finally, there is Percona, (and see here) and SeveralNines (a bit out of date at the time of writing - October 20th, 2022) - both big in the (Open Source) database world.
You need to read all of these posts, follow the links and ensure that you understand the pros and cons of each system and what compromises you and your stakeholders can/want to make (budget, RTO/RPO, expertise).
Last word on "clusters":
Finally, and to add a bit of complication to the mix, there are now PostgreSQL systems that are "natively" distributed. There's TimescaleDB and Citusdata - which are "distributed PostgreSQL". These work by sharding - i.e. different chunks of data on different machines, while maintaining a (user-specified, normally prime number) level of redundancy.
Their HA solutions appear to be cloud based (Citusdata is owned by Microsoft) - see here (Timescale) and here Citus. It is worth nothing that both are based on the amazing PostgreSQL system of extensions! You might want to take a look there also.
Similar systems would be CockroachDB, Yugabyte and TiDB.
Finally, from another set of heavy hitters comes this definition (top of page 2) of a cluster:
A database cluster consists of N database instances running on N
physically separate machines sharing no components and connected to
each other by a network. Each instance contains a complete copy of the
data, and is able to start and maintain arbitrary point-in-time
snapshot transactions.
So, these guys' definition is different to all of the previous ones... go figure...