Creating and Managing Cluster with pg wrappers (Ubuntu only)

There are few Ubuntu-specific wrapper binaries/commands which wrap the PostgreSQL’s inbuilt commands initdb (initialize a new cluster) and pg_ctl (control the cluster). We will go through initdb and pg_ctl in the upcoming contents but for now, let’s see these Ubuntu-specific commands in action. If you are not using Ubuntu, you can skip all of this.

Ubuntu-specific commands are:

• pg_lsclusters
• pg_createcluster
• pg_ctlcluster
• pg_dropcluster

Listing the clusters

Using pg_lsclusters command, we can list the PostgreSQL clusters created on the server. This command will list only those clusters which have been created using pg_createcluster command.

This command shows below information about the PostgreSQL clusters created on the current server:

• version of the PostgreSQL binary
• cluster name
• port
• status
• owner
• data directory
• log file

$ pg_lsclusters

The below output shows that we have 2 clusters and both are down at the moment. Status of recovery for saturn cluster indicates that this cluster would have a Standby cluster configured to recover from a Primary cluster. The data directory path shows the location of the database files of the cluster.

$ pg_lsclusters
Ver Cluster Port Status        Owner    Data directory                Log file
13  mars    5433 down          postgres /var/lib/postgresql/data/mars /var/log/postgresql/postgresql-13-mars.log
13  saturn  5432 down,recovery postgres /var/lib/postgresql/13/saturn /var/log/postgresql/postgresql-13-saturn.log
$

Creating new clusters

A new PostgreSQL cluster can be created using pg_createcluster command. Running just the command without any options will display the usage of the command.

$ pg_createcluster
Usage: /usr/bin/pg_createcluster [options] <version> <cluster name> [-- <initdb options>]

Options:
-u cluster owner and superuser (default: 'postgres')
-g group for data files (default: primary group of owner)
-d data directory (default:
/var/lib/postgresql//)
-s socket directory (default: /var/run/postgresql for clusters
owned by 'postgres', /tmp for other clusters)
-l path to desired log file (default:
/var/log/postgresql/postgresql--.log)
--locale
set cluster locale (default: inherit from environment)
--lc-collate/ctype/messages/monetary/numeric/time
like --locale, but only set for a particular category
-e Default encoding (default: derived from locale)
-p port number (default: next free port starting from 5432)
--start start the cluster after creating it
--start-conf auto|manual|disabled
Set automatic startup behaviour in start.conf (default: 'auto')
--createclusterconf=file alternative createcluster.conf to use
--environment=file alternative environment file to use
other options to pass to initdb
$

Features of pg_createcluster command

1. It internally run initdb command to create a new cluster.
2. It maintains and leverages the information of all clusters created by it.
3. By default, it automatically assigns next available port number to next cluster, starting from the default PostgreSQL port number of 5432.
4. By default, it will create the database files in the data directory path /var/lib/postgresql/<version>/<cluster_name>.
5. If it is run with -d option, then it will override the default data directory path and, either set up new data directory or reuse the existing data directory. If data directory was already having database files, it will become part of the clusters mainained by pg wrapper commands going forward.
6. If -p option is used, it will create the new cluster with given port number rather than automatically assigning the port number.
7. If --start option is used, it will start the cluster at the end of the create.

A very simple cluster can be created by passing the version of the PostgreSQL binary and cluster name to be created.

The below command will create a cluster on version 13 with the name venus. In this case, the cluster will not start automatically, rather display the start command at the end of the output. Interestingly enough, but as expected, the output of pg_createcluster the command shows the native PostgreSQL initdb command running behind the scene. The data directory path will be /var/lib/postgresql/13/venus.

$ pg_createcluster 13 venus

$ pg_createcluster 13 venus
Creating new PostgreSQL cluster 13/venus ...
/usr/lib/postgresql/13/bin/initdb -D /var/lib/postgresql/13/venus --auth-local peer --auth-host md5
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "C.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/13/venus ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Calcutta
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    pg_ctlcluster 13 venus start

Ver Cluster Port Status Owner    Data directory               Log file
13  venus   5434 down   postgres /var/lib/postgresql/13/venus /var/log/postgresql/postgresql-13-venus.log
$

Let’s create another cluster with start option which will cause the cluster to start automatically after the creation.

$ pg_createcluster 13 pluto --start

$ pg_createcluster 13 pluto --start
Creating new PostgreSQL cluster 13/pluto ...
/usr/lib/postgresql/13/bin/initdb -D /var/lib/postgresql/13/pluto --auth-local peer --auth-host md5
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "C.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/13/pluto ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Calcutta
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    pg_ctlcluster 13 pluto start

Ver Cluster Port Status Owner    Data directory               Log file
13  pluto   5435 online postgres /var/lib/postgresql/13/pluto /var/log/postgresql/postgresql-13-pluto.log
$

At this point in time, the cluster venus is stopped while pluto is running. Let’s verify the same by listing the clusters.

pg_lsclusters

$ pg_lsclusters
Ver Cluster Port Status        Owner    Data directory                Log file
13  mars    5433 down          postgres /var/lib/postgresql/data/mars /var/log/postgresql/postgresql-13-mars.log
13  pluto   5435 online        postgres /var/lib/postgresql/13/pluto  /var/log/postgresql/postgresql-13-pluto.log
13  saturn  5432 down,recovery postgres /var/lib/postgresql/13/saturn /var/log/postgresql/postgresql-13-saturn.log
13  venus   5434 down          postgres /var/lib/postgresql/13/venus  /var/log/postgresql/postgresql-13-venus.log
$

Let’s create another cluster with start option, port number and a custom data directory. The data directory path will be created automatically if it doesn’t exist.

$ pg_createcluster 13 uranus --start -p 5450 -d /var/lib/postgresql/postgres_data

$ pg_createcluster 13 uranus --start -p 5450 -d /var/lib/postgresql/postgres_data
Creating new PostgreSQL cluster 13/uranus ...
/usr/lib/postgresql/13/bin/initdb -D /var/lib/postgresql/postgres_data --auth-local peer --auth-host md5
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "C.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/postgres_data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Calcutta
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    pg_ctlcluster 13 uranus start

Ver Cluster Port Status Owner    Data directory                    Log file
13  uranus  5450 online postgres /var/lib/postgresql/postgres_data /var/log/postgresql/postgresql-13-uranus.log
$

Controlling the clusters

PostgreSQL clusters created with command pg_createcluster can be managed (start/stop/restart) by command pg_ctlcluster. Running just the command without any options will display the usage of the command.

$ pg_ctlcluster

$ pg_ctlcluster
Error: Usage: /usr/bin/pg_ctlcluster <version> <cluster> <action> [-- <pg_ctl options>]
$

The command pg_ctlcluster takes 3 main arguments – version, cluster name and action.

Stop a cluster

$ pg_ctlcluster 13 uranus stop

$ pg_ctlcluster 13 uranus stop
$

Start a cluster

$ pg_ctlcluster 13 uranus start

$ pg_ctlcluster 13 uranus start
$

Restart a cluster

$ pg_ctlcluster 13 uranus restart

$ pg_ctlcluster 13 uranus restart
$

Dropping the clusters

PostgreSQL clusters created with command pg_createcluster can be dropped by command pg_dropcluster. Running just the command without any options will display the usage of the command.

$ pg_dropcluster

$ pg_dropcluster
Usage: /usr/bin/pg_dropcluster [--stop] <version> <cluster>
$

The command pg_ctlcluster takes 2 main arguments – version and cluster name. Optionally, we can give --start option to stop the cluster, before it goes for the drop. A running cluster can not be dropped.

Let’s stop and drop all the clusters for now. In upcoming content, we will create and manage the clusters using PostgreSQL native commands, not pg wrapper commands.

$ pg_dropcluster 13 mars --stop
$ pg_dropcluster 13 pluto --stop
$ pg_dropcluster 13 saturn --stop
$ pg_dropcluster 13 venus --stop

After all the clusters are dropped, command pg_lsclusters will show that no clusters are running.

$ pg_lsclusters

$ pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
$