4

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?

  • 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 Oct 9 '17 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 Oct 9 '17 at 9:53
3

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. This 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.

  • 3
    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. – a_horse_with_no_name Oct 9 '17 at 9:27
  • You can also have 2 or more clusters of the same version. – ypercubeᵀᴹ Oct 9 '17 at 9:31
  • 2
    @a_horse_with_no_name Incorrect. Section 4.13 of SQL-92 is titled "Clusters of catalogs". – Basil Bourque Oct 9 '17 at 9:36
  • 2
    Hmm, that chapter is not present in e.g. SQL:2008 – a_horse_with_no_name Oct 9 '17 at 9:39
  • @BasilBourque: Thank you, in particular your linked Stackoverflow answer was very informative. – Anders Rabo Thorbeck Oct 9 '17 at 10:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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