PostgreSQL – Difference Between Clusters and Servers

postgresqlpostgresql-9.6terminology

From the official documentation (emphasis mine):

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?

Best Answer

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.

Related Solutions

Postgresql – Slow enumeration of schemas and tables in a largish database

This can also be a shortcoming of pgAdmin. There have been a number of improvements for recent versions. Which one do you use? 1.14.2 is the current version (as of 2012-05-21).

Are queries from the command line interface psql slow, too?

Apart from that, you need to keep vacuuming all your tables in the database, not just pg_tables. I would strongly advice to enable the auto vacuuming daemon.

Also, pg_tables is not relevant at all. It is a system view to implement the SQL standard information schema. The relevant system tables would be pg_namespace, pg_class, pg_attribute and others. But it is pointless to target just one of them. For your kind of setup you need to be vacuuming the whole catalog. And it is very advisable to vacuum all of your database on a regular basis anyway. So, let me repeat: use autovacuum.

For starters, you can run in a Debian shell:

vacuumdb -afz
reindexdb -a
reindexdb -s

The last line targets system tables for possible bloat of indexes in the catalog in particular. See man reindexdb or the fine manual.

Finally, another option would be to upgrade to PostgreSQL 9.1. There have been a number of improvements for VACUUM and ANALYZE (among many others). In particular VACUUM FULL has been rewritten for 9.0 and does a much better job now.

Postgresql – How to create a new instance of PostgresSQL on OS-X 10.8 Server

"all others databases and roles created by my own are lost!"

I can't speak to the best practices for creating a new database, but as part of the Mountain Lion Server upgrade all of the "old" Postgres data should have been backed up to a subdirectory under /Library(/Server?) Unfortunately as soon as I discovered this I moved-and-renamed the directory for safekeeping so I don't have the exact name!

I have a completely separate install of Postgres that I built myself for webapp purposes, but I certainly wouldn't call what my approach a "best practice". Largely what I did was build a "stock" install using a very similar set of configure parameters as Apple did (discovered by running pg_config) but with a different install location and default port setting. Following that I coptied-and-tweaked /Applications/Server.app/Contents/ServerRoot/System/Library/LaunchDaemons/org.postgresql.postgres.plist and that's worked fine for me so far.