Question: How can you list all clusters in a PostgreSQL installation?

Answer

In PostgreSQL, the term 'cluster' refers to a collection of databases that are managed by a single instance of a PostgreSQL server. A cluster includes the shared configuration and data storage for those databases. On most operating systems, PostgreSQL service or daemon manages only one cluster, but it's possible to configure multiple clusters on a single machine, each running under separate server processes with their own settings and data storage directories.

To manage and view clusters in PostgreSQL, especially when managing multiple clusters on a single system, you typically use the pg_lsclusters command, which is part of the postgresql-common package available in Debian-based systems (like Ubuntu). This command provides detailed information about each cluster, including its version, name, port, status, owner, and data directory.

Here’s how you can use pg_lsclusters:

pg_lsclusters

This will output something like the following:

Ver Cluster Port Status Owner    Data directory               Log file
12  main    5432 online postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log

For systems not using postgresql-common (such as non-Debian-based systems), listing clusters isn't standardized by a specific tool. In such cases, managing multiple clusters usually involves manually configuring different instances of the PostgreSQL service, each listening on different ports and/or using different data directories. You can check these configurations by inspecting the respective postgresql.conf file for each service.

If you have administrative access, you can also use SQL queries within a PostgreSQL session to explore details about the current database cluster:

SELECT current_setting('data_directory');

This SQL command shows the data directory of the cluster connected to your current session.

Note: The ability to manage multiple clusters easily with tools like pg_lsclusters is one of the reasons why PostgreSQL is popular for more complex setups requiring separation of database environments on a single server.