In a previous post Part 1 I went over how to install Postgres using the Debian/Ubuntu packages. It would be a good idea to go over that post before reading this one.
This post will cover the usage of the package specific commands that are installed.
First we will look at how the postgresql-client-common package wraps the Postgres native commands to work with multiple instances of Postgres installed on the same machine. Native commands being for instance psql, pg_dump, pg_restore, etc.
To make this system work wrapper code is present that runs the Postgres binaries
on a per cluster basis. As was alluded to in the installation section a cluster is identified as the combination of Postgres version and cluster name, where cluster name has to be unique for a given Postgres version. The wrapper that handles this is in the postgresql-client-common package that was installed as part of sudo apt-get -y install postgresql. It is a PERL program that can be found at /usr/share/postgresql-common/pg_wrapper for those that want to see what is pulling the levers behind the curtain. The connection to the Postgres commands e.g. psql, pg_dump, pg_restore, etc happens in /usr/bin where these commands are symlinked to pg_wrapper.
Using man pg_wrapper will provide information on how it works in conjunction with the other commands. FYI, man postgresql-common will lead to the same information. The man entry details the order of precedence for how pg_wrapper selects the binary appropriate for the cluster Postgres version. A quick simple overview of common cases, the binaries used will be those belonging to the cluster that match the below in descending order of priority:
The cluster specified using --cluster version/cluster name.
From PGHOST environment variable.
From PGCLUSTER environment variable.
Port specified as -p or PGPORT environment variable.
With multiple clusters the one that is listening on port 5432.
Again look at the man file for in depth information on the interaction of above. There are some commands that are not changed by the above as they always default to the newest installed version. These commands are psql, pg_archivecleanup, and pg_isready.
An example of how this works:
aklaver@arkansas:~$ pg_dump --version
pg_dump (PostgreSQL) 14.8 (Ubuntu 14.8-1.pgdg22.04+1)
--The above returns 14.8 as that is the server version listening on port 5432.
--If the ports where swapped and Postgres 15 was listening on 5432 then the
--version returned would have been 15.3.
Below the cluster is specified by --cluster so the binary will the one associated with
it.
aklaver@arkansas:~$pg_dump --cluster 14/main --version
pg_dump (PostgreSQL) 14.8 (Ubuntu 14.8-1.pgdg22.04+1)
aklaver@arkansas:~$ pg_dump --cluster 15/main --version
pg_dump (PostgreSQL) 15.3 (Ubuntu 15.3-1.pgdg22.04+1)
Just to prove the psql version is not affected by the cluster version:
aklaver@arkansas:~$ psql --cluster 14/main --version
psql (PostgreSQL) 15.3 (Ubuntu 15.3-1.pgdg22.04+1)
aklaver@arkansas:~$ psql --cluster 15/main --version
psql (PostgreSQL) 15.3 (Ubuntu 15.3-1.pgdg22.04+1)
Next we move on to the additional commands installed by the postgresql-common package.
Commands that are unique to the packaging. These commands work at the cluster level and are used to manage clusters through their life cycle.
pg_backupcluster
pg_buildext
pg_createcluster
pg_ctlcluster
pg_dropcluster
pg_lsclusters
pg_renamecluster
pg_restorecluster
pg_upgradecluster
pg_virtualenv
pg_config * sort of unique
pg_config
It shares a name with the Postgres native pg_config command, but is actually a shell script that runs the latest version of the native pg_config. There is no man file for this command.
For the rest of the commands listed above detailed information can be found on each using man <command name>. What follows is a simple explanation of the commands for purposes of deciding what to use for a given situation. They are presented in an presumed order of importance/usage. For these commands the cluster is specified using general form:
command [options] cluster-version cluster-name.
pg_lsclusters
Shows the status of installed clusters along with some basic configuration information.
The clusters can be filtered using a version and further with a cluster name.
aklaver@arkansas:~$ pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
14 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
15 main 5433 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log
aklaver@arkansas:~$ pg_lsclusters 14 main
Ver Cluster Port Status Owner Data directory Log file
14 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
pg_ctlcluster
Wraps the Postgres pg_ctl command. Is used to control the actions (start, stop, restart, reload, status, promote) of the Postgres service. When run as root it redirects the actions to systemd, exceptions being when options are passed directly to the underlying postgres and pg_ctl commands.
aklaver@arkansas:~$ sudo pg_ctlcluster 15 main start
aklaver@arkansas:~$ pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
14 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
15 main 5433 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log
NOTE: The action(stop/start/restart,etc) can be before or after the cluster designation.
pg_createcluster
Create a new cluster. The version name combination need to be unique across clusters.
aklaver@arkansas:~$ sudo pg_createcluster 14 new_instance
Creating new PostgreSQL cluster 14/new_instance ...
/usr/lib/postgresql/14/bin/initdb -D /var/lib/postgresql/14/new_instance --auth-local peer --auth-host scram-sha-256 --no-instructions
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 "en_US.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/14/new_instance ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... America/Los_Angeles
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
Ver Cluster Port Status Owner Data directory Log file
14 new_instance 5434 down postgres /var/lib/postgresql/14/new_instance /var/log/postgresql/postgresql-14-new_instance.log
pg_dropcluster
Completely removes a cluster and associated files(conf, log, etc). If the cluster is running the –stop will need to be used to first shutdown the cluster.
aklaver@arkansas:~$ sudo pg_dropcluster --stop 15 main
pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
14 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
pg_backupcluster
Backup up cluster using either Postgres binaries pg_basebackup or pg_dump. Manage backups. This includes expiring backups and WAL management. See man file for all the options.
aklaver@arkansas:~$ sudo pg_backupcluster 14 main dump
Creating dump in /var/backups/postgresql/14-main/2023-06-27T181915Z.dump
Dumping postgres to /var/backups/postgresql/14-main/2023-06-27T181915Z.dump/postgres.dump …
Dumping redmine to /var/backups/postgresql/14-main/2023-06-27T181915Z.dump/redmine.dump …
Dumping template1 to /var/backups/postgresql/14-main/2023-06-27T181915Z.dump/template1.dump …
Dumping test to /var/backups/postgresql/14-main/2023-06-27T181915Z.dump/test.dump …
Besides individual database dumps which are in the pg_dump custom format, the global values e.g. roles, tablespaces are dumped. The cluster configuration(postgresql.conf, pg_hba.conf, etc) is stored in the config.tar.gz file.
aklaver@arkansas:~$ sudo ls -al /var/backups/postgresql/14-main/2023-06-27T181915Z.dump/
total 828
drwxr-x--- 2 postgres postgres 4096 Jun 27 11:21 .
drwxr-xr-x 3 postgres postgres 4096 Jun 27 11:24 ..
-rw-r----- 1 postgres postgres 11656 Jun 27 11:19 config.tar.gz
-rw-r----- 1 postgres postgres 65 Jun 27 11:19 createcluster.opts
-rw-r----- 1 postgres postgres 253 Jun 27 11:19 databases.sql
-rw-r----- 1 postgres postgres 1124 Jun 27 11:19 globals.sql
-rw-r----- 1 postgres postgres 1072 Jun 27 11:19 postgres.dump
-rw-r----- 1 postgres postgres 798415 Jun 27 11:19 redmine.dump
-rw-r----- 1 postgres postgres 143 Jun 27 11:19 status
-rw-r----- 1 postgres postgres 1569 Jun 27 11:19 template1.dump
-rw-r----- 1 postgres postgres 1270 Jun 27 11:19 test.dump
Do a basebackup:
aklaver@arkansas:~$ sudo pg_backupcluster 14 main basebackup
Creating backup in /var/backups/postgresql/14-main/2023-06-27T183046Z.backup
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 5/2E000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_3671245"
52498/52498 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 5/2E000100
pg_basebackup: waiting for background process to finish streaming …
pg_basebackup: syncing data to disk …
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
The commands above where repeated to get multiple backups for the below.
aklaver@arkansas:~$ sudo pg_backupcluster 14 main list
Cluster 14 main backups in /var/backups/postgresql/14-main:
Dumps:
/var/backups/postgresql/14-main/2023-06-27T181915Z.dump: 815567 Bytes
/var/backups/postgresql/14-main/2023-06-27T183329Z.dump: 815567 Bytes
Basebackups:
/var/backups/postgresql/14-main/2023-06-27T183046Z.backup: 7544428 Bytes
/var/backups/postgresql/14-main/2023-06-27T183343Z.backup: 7544478 Bytes
Total: 16720040 Bytes
aklaver@arkansas:~$ sudo pg_backupcluster 14 main expiredumps 1
Removing /var/backups/postgresql/14-main/2023-06-27T181915Z.dump ..
aklaver@arkansas:~$ sudo pg_backupcluster 14 main expirebasebackups 1
Removing /var/backups/postgresql/14-main/2023-06-27T183046Z.backup …
aklaver@arkansas:~$ sudo pg_backupcluster 14 main list
Cluster 14 main backups in /var/backups/postgresql/14-main:
Dumps:
/var/backups/postgresql/14-main/2023-06-27T183329Z.dump: 815567 Bytes
Basebackups:
/var/backups/postgresql/14-main/2023-06-27T183343Z.backup: 7544478 Bytes
Total: 8360045 Bytes
pg_restorecluster
Restore a dump or basebackup from pg_backupcluster to a new cluster. Using new PostgreSQL cluster created in pg_createcluster section.
aklaver@arkansas~$ sudo pg_restorecluster 14 new_instance
/usr/lib/postgresql/14/bin/initdb -D /var/lib/postgresql/14/new_instance --auth-local peer --auth-host scram-sha-256 --no-instructions --encoding UTF8 --lc-collate en_US.UTF-8 --lc-ctype en_US.UTF-8
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 "en_US.UTF-8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /var/lib/postgresql/14/new_instance … ok
creating subdirectories … ok
selecting dynamic shared memory implementation … posix
selecting default max_connections … 100
selecting default shared_buffers … 128MB
selecting default time zone … America/Los_Angeles
creating configuration files … ok
running bootstrap script … ok
performing post-bootstrap initialization … ok
syncing data to disk … ok
Ver Cluster Port Status Owner Data directory Log file
14 new_instance 5434 down postgres /var/lib/postgresql/14/new_instance /var/log/postgresql/postgresql-14-new_instance.log
Restoring /var/backups/postgresql/14-main/2023-06-27T183329Z.dump/config.tar.gz to /etc/postgresql/14/new_instance …
Setting cluster_name = 14/new_instance
Setting data_directory = /var/lib/postgresql/14/new_instance
Setting external_pid_file = /var/run/postgresql/14-new_instance.pid
Setting hba_file = /etc/postgresql/14/new_instance/pg_hba.conf
Setting ident_file = /etc/postgresql/14/new_instance/pg_ident.conf
Setting port = 5434
Setting stats_temp_directory = /var/run/postgresql/14-new_instance.pg_stat_tmp
Starting cluster 14 new_instance …
Restoring /var/backups/postgresql/14-main/2023-06-27T183329Z.dump/globals.sql …
Creating databases from /var/backups/postgresql/14-main/2023-06-27T183329Z.dump/databases.sql …
Restoring /var/backups/postgresql/14-main/2023-06-27T183329Z.dump/postgres.dump to database postgres …
Restoring /var/backups/postgresql/14-main/2023-06-27T183329Z.dump/redmine.dump to database redmine …
Restoring /var/backups/postgresql/14-main/2023-06-27T183329Z.dump/template1.dump to database template1 …
Restoring /var/backups/postgresql/14-main/2023-06-27T183329Z.dump/test.dump to database test …
vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "redmine": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "template1": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "test": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "postgres": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "redmine": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "template1": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "test": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "postgres": Generating default (full) optimizer statistics
vacuumdb: processing database "redmine": Generating default (full) optimizer statistics
vacuumdb: processing database "template1": Generating default (full) optimizer statistics
vacuumdb: processing database "test": Generating default (full) optimizer statistics
Ver Cluster Port Status Owner Data directory Log file
14 new_instance 5434 online postgres /var/lib/postgresql/14/new_instance /var/log/postgresql/postgresql-14-new_instance.log
pg_renamecluster
Rename a cluster.
aklaver@arkansas:~$ sudo pg_renamecluster 14 new_instance test
Stopping cluster 14 new_instance …
Starting cluster 14 test …
aklaver@arkansas:~$ pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
14 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
14 test 5434 online postgres /var/lib/postgresql/14/test /var/log/postgresql/postgresql-14-test.log
15 main 5433 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log
pg_upgradecluster
Uses pg_upgrade to move from one major version to another. By default the –method used is dump which uses pg_dump to dump the old version and pg_restore to restore to new version. Using –method=upgrade will use pg_upgrade. See man file for variations of upgrade e.g. link and clone.
In below pg_dump/pg_restore are used and the new version(-v 15) is explicitly selected.
Without -v the newest version of Postgres installed will be used as the upgrade target.
aklaver@arkansas:~$ sudo pg_upgradecluster -v 15 14 test
Stopping old cluster…
Restarting old cluster with restricted connections…
Notice: extra pg_ctl/postgres options given, bypassing systemctl for start operation
Creating new PostgreSQL cluster 15/test …
/usr/lib/postgresql/15/bin/initdb -D /var/lib/postgresql/15/test --auth-local peer --auth-host scram-sha-256 --no-instructions --encoding UTF8 --lc-collate en_US.UTF-8 --lc-ctype en_US.UTF-8
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 "en_US.UTF-8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /var/lib/postgresql/15/test … ok
creating subdirectories … ok
selecting dynamic shared memory implementation … posix
selecting default max_connections … 100
selecting default shared_buffers … 128MB
selecting default time zone … America/Los_Angeles
creating configuration files … ok
running bootstrap script … ok
performing post-bootstrap initialization … ok
syncing data to disk … ok
Copying old configuration files…
Copying old start.conf…
Copying old pg_ctl.conf…
Starting new cluster…
Notice: extra pg_ctl/postgres options given, bypassing systemctl for start operation
Roles, databases, schemas, ACLs…
set_config
(1 row)
set_config
(1 row)
set_config
(1 row)
set_config
(1 row)
set_config
(1 row)
set_config
(1 row)
Fixing hardcoded library paths for stored procedures…
Upgrading database postgres…
Analyzing database postgres…
Fixing hardcoded library paths for stored procedures…
Upgrading database redmine…
Analyzing database redmine…
Fixing hardcoded library paths for stored procedures…
Upgrading database test…
Analyzing database test…
Fixing hardcoded library paths for stored procedures…
Upgrading database template1…
Analyzing database template1…
Stopping target cluster…
Stopping old cluster…
Disabling automatic startup of old cluster…
Starting upgraded cluster on port 5434…
Success. Please check that the upgraded cluster works. If it does,
you can remove the old cluster with
pg_dropcluster 14 test
Ver Cluster Port Status Owner Data directory Log file
14 test 5435 down postgres /var/lib/postgresql/14/test /var/log/postgresql/postgresql-14-test.log
Ver Cluster Port Status Owner Data directory Log file
15 test 5434 online postgres /var/lib/postgresql/15/test /var/log/postgresql/postgresql-15-test.log
aklaver@arkansas:~$ pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
14 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
14 test 5435 down postgres /var/lib/postgresql/14/test /var/log/postgresql/postgresql-14-test.log
15 main 5433 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log
15 test 5434 online postgres /var/lib/postgresql/15/test /var/log/postgresql/postgresql-15-test.log
Set up for using upgrade method by dropping the 15 test cluster created using the dump/restore method above.
aklaver@localhost:~$ sudo pg_dropcluster --stop 15 test
aklaver@arkansas:~$ pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
14 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
14 test 5435 down postgres /var/lib/postgresql/14/test /var/log/postgresql/postgresql-14-test.log
15 main 5433 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log
Using the pg_upgrade method.
aklaver@arkansas:~$ sudo pg_upgradecluster -v 15 -m upgrade 14 test
Restarting old cluster with restricted connections…
Notice: extra pg_ctl/postgres options given, bypassing systemctl for start operation
Stopping old cluster…
Creating new PostgreSQL cluster 15/test …
/usr/lib/postgresql/15/bin/initdb -D /var/lib/postgresql/15/test --auth-local peer --auth-host scram-sha-256 --no-instructions --encoding UTF8 --lc-collate en_US.UTF-8 --lc-ctype en_US.UTF-8
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 "en_US.UTF-8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /var/lib/postgresql/15/test … ok
creating subdirectories … ok
selecting dynamic shared memory implementation … posix
selecting default max_connections … 100
selecting default shared_buffers … 128MB
selecting default time zone … America/Los_Angeles
creating configuration files … ok
running bootstrap script … ok
performing post-bootstrap initialization … ok
syncing data to disk … ok
Copying old configuration files…
Copying old start.conf…
Copying old pg_ctl.conf…
/usr/lib/postgresql/15/bin/pg_upgrade -b /usr/lib/postgresql/14/bin -B /usr/lib/postgresql/15/bin -p 5435 -P 5434 -d /etc/postgresql/14/test -D /etc/postgresql/15/test
Finding the real data directory for the source cluster ok
Finding the real data directory for the target cluster ok
Performing Consistency Checks
Checking cluster versions ok
Checking database user is the install user ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for system-defined composite types in user tables ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Creating dump of global objects ok
Creating dump of database schemas
ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok
Checking for new cluster tablespace directories ok
If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.
Performing Upgrade
Analyzing all rows in the new cluster ok
Freezing all rows in the new cluster ok
Deleting files from new pg_xact ok
Copying old pg_xact to new server ok
Setting oldest XID for new cluster ok
Setting next transaction ID and epoch for new cluster ok
Deleting files from new pg_multixact/offsets ok
Copying old pg_multixact/offsets to new server ok
Deleting files from new pg_multixact/members ok
Copying old pg_multixact/members to new server ok
Setting next multixact ID and offset for new cluster ok
Resetting WAL archives ok
Setting frozenxid and minmxid counters in new cluster ok
Restoring global objects in the new cluster ok
Restoring database schemas in the new cluster
ok
Copying user relation files
ok
Setting next OID for new cluster ok
Sync data directory to disk ok
Creating script to delete old cluster ok
Checking for extension updates ok
Upgrade Complete
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
/usr/lib/postgresql/15/bin/vacuumdb --all --analyze-in-stages
Running this script will delete the old cluster's data files:
./delete_old_cluster.sh
pg_upgrade output scripts are in /var/log/postgresql/pg_upgradecluster-14-15-test.mKua
Disabling automatic startup of old cluster…
Success. Please check that the upgraded cluster works. If it does,
you can remove the old cluster with
pg_dropcluster 14 test
Ver Cluster Port Status Owner Data directory Log file
14 test 5434 down postgres /var/lib/postgresql/14/test /var/log/postgresql/postgresql-14-test.log
Ver Cluster Port Status Owner Data directory Log file
15 test 5435 down postgres /var/lib/postgresql/15/test /var/log/postgresql/postgresql-15-test.log
aklaver@arkansas:~$ pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
14 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
14 test 5434 down postgres /var/lib/postgresql/14/test /var/log/postgresql/postgresql-14-test.log
15 main 5433 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log
15 test 5435 down postgres /var/lib/postgresql/15/test /var/log/postgresql/postgresql-15-test.log
NOTE: Be sure and look at the information under Upgrade Complete section above before starting the new cluster and/or dropping the old cluster.
pg_buildext
Build and install Postgres extension. The man page indicates this is more a packaging
command and not really for end users.
pg_virtualenv
Create a temporary Postgres instance for running tests on.