Question: What is the difference between a PostgreSQL cluster and a database?

Answer

In PostgreSQL, the terms 'cluster' and 'database' are often used but they refer to different aspects of the database architecture:

PostgreSQL Cluster

A PostgreSQL cluster refers to a collection of databases that are managed by a single instance of a PostgreSQL server process. The term 'cluster' in this context does not imply multiple servers or high availability (as it might in general IT usage), but rather a group of databases that share a common set of system resources such as memory, background processes, configurations, and catalog information like users and permissions.

When you install PostgreSQL and initialize its environment using the initdb command, you are actually creating a new cluster. All databases created under this setup will share the same PostgreSQL instance.

# Initialize a new PostgreSQL cluster
initdb -D /path/to/data/directory

PostgreSQL Database

Within a PostgreSQL cluster, a database is a separate namespace for organizing data. It contains one or more schemas, which in turn include objects like tables, views, functions, etc. Each database is isolated from others; you cannot directly query or join tables across databases within the same cluster.

You can create a new database using the SQL CREATE DATABASE command:

-- Create a new database in a PostgreSQL cluster
CREATE DATABASE mydatabase;

Differences Summarized

• Cluster: Refers to the entire PostgreSQL installation including all databases managed by a specific server process.
• Database: A specific, isolated collection of data within the cluster, containing tables, views, and other database objects.

Understanding this distinction is important for managing PostgreSQL effectively, especially when configuring backups, replication, or planning system resources.