11

From the official documentation (emphasis mine):

Chapter 22. Managing Databases

Every instance of a running PostgreSQL server manages one or more databases. Databases are therefore the topmost hierarchical level for organizing SQL objects ("database objects").

Chapter 22. Managing Databases -> 22.1 Overview

More accurately, a database is a collection of schemas and the schemas contain the tables, functions, etc. So the full hierarchy is: server, database, schema, table (or some other kind of object, such as a function).

Chapter 18. Server Setup and Operation -> 18.2. Creating a Database Cluster

A database cluster is a collection of databases that is managed by a single instance of a running database server.

I don't quite understand the difference between the PostgreSQL concepts of a cluster and a server, but it seems to me that they are the same thing?

Improve this question
2
  • Take a look here. A "database" can be the machine with its disks, a running instance of PosgreSQL (or Oracle - concepts similar if you exclude the 12c specific stuff), or a schema. Within a schema transactions are guaranteed to be ACID. I think the hierarchy is best visualised as instance -> database -> schema. An instance can be the machine + disks and also the database software as running. A database is an instance with one or more running schemas (excluding templates in the case of PostgreSQL).
    – Vérace
    Commented Oct 9, 2017 at 9:53
  • A schema is what Joe Sixpack would understand as a database - a bunch of related information stored together - i.e. the "HR database", the "Sales database" &c...
    – Vérace
    Commented Oct 9, 2017 at 9:53

1 Answer 1

Reset to default
10

tl;dr

Yes.

Confusion arises from the official terms used in the SQL standard versus more commonly used terminology.

  • Cluster = database server
  • Catalog = a database
  • Schema = a namespace, a grouping of tables

Computer > Cluster(s) > Database(s)/Catalog(s) > Schema(s) > Table(s)

See my Answer to a similar question on Stack Overflow for more info.

Briefly here…

An installation of Postgres on a computer is called a cluster, per Section 4.13 of SQL-92 titled "Clusters of catalogs".

That cluster has one or more databases, each database called a catalog per the SQL standard. Each database contains one or more schemas, each schema being a namespace and security boundary for a collection of tables.

You can have more than one cluster on a computer. Each cluster (database server) listens on a separate port.

Multiple clusters is typically done for different versions of Postgres. For example, you could have Postgres versions 9.5, 9.6, and 10 all installed, each listening on a different port for incoming connections. That would be three clusters in that computer, each containing catalogs that contain schema that contain tables that contain your rows of data defined by columns.

Improve this answer
5
  • 4
    The term "cluster" as it is used in Postgres is not "per SQL standard". In fact the SQL standard doesn't talk about "clusters" at all.
    – user1822
    Commented Oct 9, 2017 at 9:27
  • You can also have 2 or more clusters of the same version.
    – ypercubeᵀᴹ
    Commented Oct 9, 2017 at 9:31
  • 3
    @a_horse_with_no_name Incorrect. Section 4.13 of SQL-92 is titled "Clusters of catalogs".
    – Basil Bourque
    Commented Oct 9, 2017 at 9:36
  • 2
    Hmm, that chapter is not present in e.g. SQL:2008
    – user1822
    Commented Oct 9, 2017 at 9:39
  • 2
    @BasilBourque: Thank you, in particular your linked Stackoverflow answer was very informative.
    – Anders Rabo Thorbeck
    Commented Oct 9, 2017 at 10:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.