{"id":525,"date":"2023-07-01T14:40:55","date_gmt":"2023-07-01T21:40:55","guid":{"rendered":"https:\/\/aklaver.org\/wordpress\/?p=525"},"modified":"2023-07-01T14:41:40","modified_gmt":"2023-07-01T21:41:40","slug":"postgres-debian-ubuntu-packagingpart-2","status":"publish","type":"post","link":"https:\/\/aklaver.org\/wordpress\/2023\/07\/01\/postgres-debian-ubuntu-packagingpart-2\/","title":{"rendered":"Postgres Debian\/Ubuntu Packaging(Part 2)"},"content":{"rendered":"\n

I<\/strong>n a previous post Part 1<\/a> 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.<\/p>\n\n\n\n

This post will cover the usage of the package specific commands that are installed.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

Using man pg_wrapper <\/em>will provide information on how it works in conjunction with the other commands. FYI, man postgresql-common <\/em>will lead to the same information. The man<\/em> 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:<\/p>\n\n\n

\nThe cluster specified using --cluster version\/cluster name.\nFrom PGHOST environment variable.\nFrom PGCLUSTER environment variable.\nPort specified as -p or PGPORT environment variable.\nWith multiple clusters the one that is listening on port 5432.\n<\/pre><\/div>\n\n\n
Again look at the man<\/em> 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.<\/p>\n\n\n\n
An example of how this works:<\/p>\n\n\n
\naklaver@arkansas:~$ pg_dump --version\npg_dump (PostgreSQL) 14.8 (Ubuntu 14.8-1.pgdg22.04+1)\n\n--The above returns 14.8 as that is the server version listening on port 5432.\n--If the ports where swapped and Postgres 15 was listening on 5432 then the \n--version returned would have been 15.3.\n\n\nBelow the cluster is specified by --cluster so the binary will the one associated with \nit.\n\naklaver@arkansas:~$pg_dump --cluster 14\/main  --version \npg_dump (PostgreSQL) 14.8 (Ubuntu 14.8-1.pgdg22.04+1)\n\naklaver@arkansas:~$ pg_dump --cluster 15\/main  --version\npg_dump (PostgreSQL) 15.3 (Ubuntu 15.3-1.pgdg22.04+1)\n<\/pre><\/div>\n\n\n
Just to prove the psql version is not affected by the cluster version:<\/p>\n\n\n
\naklaver@arkansas:~$ psql --cluster 14\/main  --version \npsql (PostgreSQL) 15.3 (Ubuntu 15.3-1.pgdg22.04+1)\n\naklaver@arkansas:~$ psql --cluster 15\/main  --version \npsql (PostgreSQL) 15.3 (Ubuntu 15.3-1.pgdg22.04+1)\n<\/pre><\/div>\n\n\n
Next we move on to the additional commands installed by the postgresql-common package.<\/p>\n\n\n\n
Commands that are unique to the packaging. These commands work at the cluster level and are used to manage clusters through their life cycle. <\/p>\n\n\n\n
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<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
For the rest of the commands listed above detailed information can be found on each using man <command name><\/em>. 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:<\/p>\n\n\n\n
command<\/em> [options<\/em>] cluster-version cluster-name<\/em>.<\/p>\n\n\n\n
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.<\/p>\n\n\n
\naklaver@arkansas:~$ pg_lsclusters\nVer Cluster Port Status Owner Data directory Log file\n14 main 5432 online postgres \/var\/lib\/postgresql\/14\/main \/var\/log\/postgresql\/postgresql-14-main.log\n15 main 5433 online postgres \/var\/lib\/postgresql\/15\/main \/var\/log\/postgresql\/postgresql-15-main.log\n\naklaver@arkansas:~$ pg_lsclusters 14 main\nVer Cluster Port Status Owner Data directory Log file\n14 main 5432 online postgres \/var\/lib\/postgresql\/14\/main \/var\/log\/postgresql\/postgresql-14-main.log\n<\/pre><\/div>\n\n\n
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.<\/p>\n\n\n
\naklaver@arkansas:~$ sudo pg_ctlcluster 15 main start\n\naklaver@arkansas:~$ pg_lsclusters\nVer Cluster Port Status Owner Data directory Log file\n14 main 5432 online postgres \/var\/lib\/postgresql\/14\/main \/var\/log\/postgresql\/postgresql-14-main.log\n15 main 5433 online postgres \/var\/lib\/postgresql\/15\/main \/var\/log\/postgresql\/postgresql-15-main.log\n<\/pre><\/div>\n\n\n
NOTE:<\/strong> The action(stop\/start\/restart,etc) can be before or after the cluster designation.<\/p>\n\n\n\n
pg_createcluster
Create a new cluster. The version name combination need to be unique across clusters.<\/p>\n\n\n
\naklaver@arkansas:~$ sudo pg_createcluster 14 new_instance\nCreating new PostgreSQL cluster 14\/new_instance ...\n\/usr\/lib\/postgresql\/14\/bin\/initdb -D \/var\/lib\/postgresql\/14\/new_instance --auth-local peer --auth-host scram-sha-256 --no-instructions\nThe files belonging to this database system will be owned by user "postgres".\nThis user must also own the server process.\n\nThe database cluster will be initialized with locale "en_US.UTF-8".\nThe default database encoding has accordingly been set to "UTF8".\nThe default text search configuration will be set to "english".\n\nData page checksums are disabled.\n\nfixing permissions on existing directory \/var\/lib\/postgresql\/14\/new_instance ... ok\ncreating subdirectories ... ok\nselecting dynamic shared memory implementation ... posix\nselecting default max_connections ... 100\nselecting default shared_buffers ... 128MB\nselecting default time zone ... America\/Los_Angeles\ncreating configuration files ... ok\nrunning bootstrap script ... ok\nperforming post-bootstrap initialization ... ok\nsyncing data to disk ... ok\nVer Cluster      Port Status Owner    Data directory                      Log file\n14  new_instance 5434 down   postgres \/var\/lib\/postgresql\/14\/new_instance \/var\/log\/postgresql\/postgresql-14-new_instance.log\n\n<\/pre><\/div>\n\n\n
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.<\/p>\n\n\n
\naklaver@arkansas:~$ sudo pg_dropcluster --stop 15 main\n\npg_lsclusters\nVer Cluster Port Status Owner Data directory Log file\n14 main 5432 online postgres \/var\/lib\/postgresql\/14\/main \/var\/log\/postgresql\/postgresql-14-main.log\n<\/pre><\/div>\n\n\n
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.<\/p>\n\n\n
\naklaver@arkansas:~$ sudo pg_backupcluster 14 main dump\nCreating dump in \/var\/backups\/postgresql\/14-main\/2023-06-27T181915Z.dump\nDumping postgres to \/var\/backups\/postgresql\/14-main\/2023-06-27T181915Z.dump\/postgres.dump \u2026\nDumping redmine to \/var\/backups\/postgresql\/14-main\/2023-06-27T181915Z.dump\/redmine.dump \u2026\nDumping template1 to \/var\/backups\/postgresql\/14-main\/2023-06-27T181915Z.dump\/template1.dump \u2026\nDumping test to \/var\/backups\/postgresql\/14-main\/2023-06-27T181915Z.dump\/test.dump \u2026\n<\/pre><\/div>\n\n\n
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.<\/p>\n\n\n
\naklaver@arkansas:~$ sudo ls -al \/var\/backups\/postgresql\/14-main\/2023-06-27T181915Z.dump\/\ntotal 828\ndrwxr-x--- 2 postgres postgres 4096 Jun 27 11:21 .\ndrwxr-xr-x 3 postgres postgres 4096 Jun 27 11:24 ..\n-rw-r----- 1 postgres postgres 11656 Jun 27 11:19 config.tar.gz\n-rw-r----- 1 postgres postgres 65 Jun 27 11:19 createcluster.opts\n-rw-r----- 1 postgres postgres 253 Jun 27 11:19 databases.sql\n-rw-r----- 1 postgres postgres 1124 Jun 27 11:19 globals.sql\n-rw-r----- 1 postgres postgres 1072 Jun 27 11:19 postgres.dump\n-rw-r----- 1 postgres postgres 798415 Jun 27 11:19 redmine.dump\n-rw-r----- 1 postgres postgres 143 Jun 27 11:19 status\n-rw-r----- 1 postgres postgres 1569 Jun 27 11:19 template1.dump\n-rw-r----- 1 postgres postgres 1270 Jun 27 11:19 test.dump\n<\/pre><\/div>\n\n\n
Do a basebackup:<\/p>\n\n\n
\naklaver@arkansas:~$ sudo pg_backupcluster 14 main basebackup\nCreating backup in \/var\/backups\/postgresql\/14-main\/2023-06-27T183046Z.backup\npg_basebackup: initiating base backup, waiting for checkpoint to complete\npg_basebackup: checkpoint completed\npg_basebackup: write-ahead log start point: 5\/2E000028 on timeline 1\npg_basebackup: starting background WAL receiver\npg_basebackup: created temporary replication slot "pg_basebackup_3671245"\n52498\/52498 kB (100%), 1\/1 tablespace\npg_basebackup: write-ahead log end point: 5\/2E000100\npg_basebackup: waiting for background process to finish streaming \u2026\npg_basebackup: syncing data to disk \u2026\npg_basebackup: renaming backup_manifest.tmp to backup_manifest\npg_basebackup: base backup completed\n\n\n<\/pre><\/div>\n\n\n
The commands above where repeated  to get multiple backups for the below.<\/p>\n\n\n
\naklaver@arkansas:~$ sudo pg_backupcluster 14 main list\nCluster 14 main backups in \/var\/backups\/postgresql\/14-main:\nDumps:\n\/var\/backups\/postgresql\/14-main\/2023-06-27T181915Z.dump: 815567 Bytes\n\/var\/backups\/postgresql\/14-main\/2023-06-27T183329Z.dump: 815567 Bytes\nBasebackups:\n\/var\/backups\/postgresql\/14-main\/2023-06-27T183046Z.backup: 7544428 Bytes\n\/var\/backups\/postgresql\/14-main\/2023-06-27T183343Z.backup: 7544478 Bytes\nTotal: 16720040 Bytes\n\naklaver@arkansas:~$ sudo pg_backupcluster 14 main expiredumps 1\nRemoving \/var\/backups\/postgresql\/14-main\/2023-06-27T181915Z.dump ..\n\naklaver@arkansas:~$ sudo pg_backupcluster 14 main expirebasebackups 1\nRemoving \/var\/backups\/postgresql\/14-main\/2023-06-27T183046Z.backup \u2026\n\naklaver@arkansas:~$ sudo pg_backupcluster 14 main list\nCluster 14 main backups in \/var\/backups\/postgresql\/14-main:\nDumps:\n\/var\/backups\/postgresql\/14-main\/2023-06-27T183329Z.dump: 815567 Bytes\nBasebackups:\n\/var\/backups\/postgresql\/14-main\/2023-06-27T183343Z.backup: 7544478 Bytes\nTotal: 8360045 Bytes\n<\/pre><\/div>\n\n\n
pg_restorecluster
Restore a dump or basebackup from pg_backupcluster to a new cluster. Using new PostgreSQL cluster created in pg_createcluster section.<\/p>\n\n\n
\naklaver@arkansas~$ sudo pg_restorecluster 14 new_instance\n\/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\nThe files belonging to this database system will be owned by user "postgres".\nThis user must also own the server process.\n\nThe database cluster will be initialized with locale "en_US.UTF-8".\nThe default text search configuration will be set to "english".\n\nData page checksums are disabled.\n\nfixing permissions on existing directory \/var\/lib\/postgresql\/14\/new_instance \u2026 ok\ncreating subdirectories \u2026 ok\nselecting dynamic shared memory implementation \u2026 posix\nselecting default max_connections \u2026 100\nselecting default shared_buffers \u2026 128MB\nselecting default time zone \u2026 America\/Los_Angeles\ncreating configuration files \u2026 ok\nrunning bootstrap script \u2026 ok\nperforming post-bootstrap initialization \u2026 ok\nsyncing data to disk \u2026 ok\nVer Cluster Port Status Owner Data directory Log file\n14 new_instance 5434 down postgres \/var\/lib\/postgresql\/14\/new_instance \/var\/log\/postgresql\/postgresql-14-new_instance.log\n\nRestoring \/var\/backups\/postgresql\/14-main\/2023-06-27T183329Z.dump\/config.tar.gz to \/etc\/postgresql\/14\/new_instance \u2026\nSetting cluster_name = 14\/new_instance\nSetting data_directory = \/var\/lib\/postgresql\/14\/new_instance\nSetting external_pid_file = \/var\/run\/postgresql\/14-new_instance.pid\nSetting hba_file = \/etc\/postgresql\/14\/new_instance\/pg_hba.conf\nSetting ident_file = \/etc\/postgresql\/14\/new_instance\/pg_ident.conf\nSetting port = 5434\nSetting stats_temp_directory = \/var\/run\/postgresql\/14-new_instance.pg_stat_tmp\nStarting cluster 14 new_instance \u2026\nRestoring \/var\/backups\/postgresql\/14-main\/2023-06-27T183329Z.dump\/globals.sql \u2026\nCreating databases from \/var\/backups\/postgresql\/14-main\/2023-06-27T183329Z.dump\/databases.sql \u2026\nRestoring \/var\/backups\/postgresql\/14-main\/2023-06-27T183329Z.dump\/postgres.dump to database postgres \u2026\nRestoring \/var\/backups\/postgresql\/14-main\/2023-06-27T183329Z.dump\/redmine.dump to database redmine \u2026\nRestoring \/var\/backups\/postgresql\/14-main\/2023-06-27T183329Z.dump\/template1.dump to database template1 \u2026\nRestoring \/var\/backups\/postgresql\/14-main\/2023-06-27T183329Z.dump\/test.dump to database test \u2026\nvacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)\nvacuumdb: processing database "redmine": Generating minimal optimizer statistics (1 target)\nvacuumdb: processing database "template1": Generating minimal optimizer statistics (1 target)\nvacuumdb: processing database "test": Generating minimal optimizer statistics (1 target)\nvacuumdb: processing database "postgres": Generating medium optimizer statistics (10 targets)\nvacuumdb: processing database "redmine": Generating medium optimizer statistics (10 targets)\nvacuumdb: processing database "template1": Generating medium optimizer statistics (10 targets)\nvacuumdb: processing database "test": Generating medium optimizer statistics (10 targets)\nvacuumdb: processing database "postgres": Generating default (full) optimizer statistics\nvacuumdb: processing database "redmine": Generating default (full) optimizer statistics\nvacuumdb: processing database "template1": Generating default (full) optimizer statistics\nvacuumdb: processing database "test": Generating default (full) optimizer statistics\n\nVer Cluster Port Status Owner Data directory Log file\n14 new_instance 5434 online postgres \/var\/lib\/postgresql\/14\/new_instance \/var\/log\/postgresql\/postgresql-14-new_instance.log\n<\/pre><\/div>\n\n\n
<\/p>\n\n\n\n
pg_renamecluster
Rename a cluster.<\/p>\n\n\n
\naklaver@arkansas:~$ sudo pg_renamecluster 14 new_instance test\nStopping cluster 14 new_instance \u2026\nStarting cluster 14 test \u2026\naklaver@arkansas:~$ pg_lsclusters\nVer Cluster Port Status Owner Data directory Log file\n14 main 5432 online postgres \/var\/lib\/postgresql\/14\/main \/var\/log\/postgresql\/postgresql-14-main.log\n14 test 5434 online postgres \/var\/lib\/postgresql\/14\/test \/var\/log\/postgresql\/postgresql-14-test.log\n15 main 5433 online postgres \/var\/lib\/postgresql\/15\/main \/var\/log\/postgresql\/postgresql-15-main.log\n<\/pre><\/div>\n\n\n
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.<\/p>\n\n\n\n
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.<\/p>\n\n\n
\naklaver@arkansas:~$ sudo pg_upgradecluster -v 15 14 test\nStopping old cluster\u2026\nRestarting old cluster with restricted connections\u2026\nNotice: extra pg_ctl\/postgres options given, bypassing systemctl for start operation\nCreating new PostgreSQL cluster 15\/test \u2026\n\/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\nThe files belonging to this database system will be owned by user "postgres".\nThis user must also own the server process.\n\nThe database cluster will be initialized with locale "en_US.UTF-8".\nThe default text search configuration will be set to "english".\n\nData page checksums are disabled.\n\nfixing permissions on existing directory \/var\/lib\/postgresql\/15\/test \u2026 ok\ncreating subdirectories \u2026 ok\nselecting dynamic shared memory implementation \u2026 posix\nselecting default max_connections \u2026 100\nselecting default shared_buffers \u2026 128MB\nselecting default time zone \u2026 America\/Los_Angeles\ncreating configuration files \u2026 ok\nrunning bootstrap script \u2026 ok\nperforming post-bootstrap initialization \u2026 ok\nsyncing data to disk \u2026 ok\n\nCopying old configuration files\u2026\nCopying old start.conf\u2026\nCopying old pg_ctl.conf\u2026\nStarting new cluster\u2026\nNotice: extra pg_ctl\/postgres options given, bypassing systemctl for start operation\nRoles, databases, schemas, ACLs\u2026\n\nset_config\n\n(1 row)\n\nset_config\n\n(1 row)\n\nset_config\n\n(1 row)\n\nset_config\n\n(1 row)\n\nset_config\n\n(1 row)\n\nset_config\n\n(1 row)\n\nFixing hardcoded library paths for stored procedures\u2026\nUpgrading database postgres\u2026\nAnalyzing database postgres\u2026\nFixing hardcoded library paths for stored procedures\u2026\nUpgrading database redmine\u2026\nAnalyzing database redmine\u2026\nFixing hardcoded library paths for stored procedures\u2026\nUpgrading database test\u2026\nAnalyzing database test\u2026\nFixing hardcoded library paths for stored procedures\u2026\nUpgrading database template1\u2026\nAnalyzing database template1\u2026\nStopping target cluster\u2026\nStopping old cluster\u2026\nDisabling automatic startup of old cluster\u2026\nStarting upgraded cluster on port 5434\u2026\n\nSuccess. Please check that the upgraded cluster works. If it does,\nyou can remove the old cluster with\npg_dropcluster 14 test\n\nVer Cluster Port Status Owner Data directory Log file\n14 test 5435 down postgres \/var\/lib\/postgresql\/14\/test \/var\/log\/postgresql\/postgresql-14-test.log\nVer Cluster Port Status Owner Data directory Log file\n15 test 5434 online postgres \/var\/lib\/postgresql\/15\/test \/var\/log\/postgresql\/postgresql-15-test.log\n\naklaver@arkansas:~$ pg_lsclusters\nVer Cluster Port Status Owner Data directory Log file\n14 main 5432 online postgres \/var\/lib\/postgresql\/14\/main \/var\/log\/postgresql\/postgresql-14-main.log\n14 test 5435 down postgres \/var\/lib\/postgresql\/14\/test \/var\/log\/postgresql\/postgresql-14-test.log\n15 main 5433 online postgres \/var\/lib\/postgresql\/15\/main \/var\/log\/postgresql\/postgresql-15-main.log\n15 test 5434 online postgres \/var\/lib\/postgresql\/15\/test \/var\/log\/postgresql\/postgresql-15-test.log\n<\/pre><\/div>\n\n\n
Set up for using upgrade method by dropping the 15 test cluster created using the dump\/restore method above.<\/p>\n\n\n
\naklaver@localhost:~$ sudo pg_dropcluster --stop 15 test\n\naklaver@arkansas:~$ pg_lsclusters\nVer Cluster Port Status Owner Data directory Log file\n14 main 5432 online postgres \/var\/lib\/postgresql\/14\/main \/var\/log\/postgresql\/postgresql-14-main.log\n14 test 5435 down postgres \/var\/lib\/postgresql\/14\/test \/var\/log\/postgresql\/postgresql-14-test.log\n15 main 5433 online postgres \/var\/lib\/postgresql\/15\/main \/var\/log\/postgresql\/postgresql-15-main.log\n<\/pre><\/div>\n\n\n
<\/p>\n\n\n\n
Using the pg_upgrade method.<\/p>\n\n\n
\naklaver@arkansas:~$ sudo pg_upgradecluster -v 15 -m upgrade 14 test\nRestarting old cluster with restricted connections\u2026\nNotice: extra pg_ctl\/postgres options given, bypassing systemctl for start operation\nStopping old cluster\u2026\nCreating new PostgreSQL cluster 15\/test \u2026\n\/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\nThe files belonging to this database system will be owned by user "postgres".\nThis user must also own the server process.\n\nThe database cluster will be initialized with locale "en_US.UTF-8".\nThe default text search configuration will be set to "english".\n\nData page checksums are disabled.\n\nfixing permissions on existing directory \/var\/lib\/postgresql\/15\/test \u2026 ok\ncreating subdirectories \u2026 ok\nselecting dynamic shared memory implementation \u2026 posix\nselecting default max_connections \u2026 100\nselecting default shared_buffers \u2026 128MB\nselecting default time zone \u2026 America\/Los_Angeles\ncreating configuration files \u2026 ok\nrunning bootstrap script \u2026 ok\nperforming post-bootstrap initialization \u2026 ok\nsyncing data to disk \u2026 ok\n\nCopying old configuration files\u2026\nCopying old start.conf\u2026\nCopying old pg_ctl.conf\u2026\n\/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\nFinding the real data directory for the source cluster ok\nFinding the real data directory for the target cluster ok\n\nPerforming Consistency Checks\n\nChecking cluster versions ok\nChecking database user is the install user ok\nChecking database connection settings ok\nChecking for prepared transactions ok\nChecking for system-defined composite types in user tables ok\nChecking for reg* data types in user tables ok\nChecking for contrib\/isn with bigint-passing mismatch ok\nCreating dump of global objects ok\nCreating dump of database schemas\nok\nChecking for presence of required libraries ok\nChecking database user is the install user ok\nChecking for prepared transactions ok\nChecking for new cluster tablespace directories ok\n\nIf pg_upgrade fails after this point, you must re-initdb the\nnew cluster before continuing.\n\nPerforming Upgrade\n\nAnalyzing all rows in the new cluster ok\nFreezing all rows in the new cluster ok\nDeleting files from new pg_xact ok\nCopying old pg_xact to new server ok\nSetting oldest XID for new cluster ok\nSetting next transaction ID and epoch for new cluster ok\nDeleting files from new pg_multixact\/offsets ok\nCopying old pg_multixact\/offsets to new server ok\nDeleting files from new pg_multixact\/members ok\nCopying old pg_multixact\/members to new server ok\nSetting next multixact ID and offset for new cluster ok\nResetting WAL archives ok\nSetting frozenxid and minmxid counters in new cluster ok\nRestoring global objects in the new cluster ok\nRestoring database schemas in the new cluster\nok\nCopying user relation files\nok\nSetting next OID for new cluster ok\nSync data directory to disk ok\nCreating script to delete old cluster ok\nChecking for extension updates ok\n\nUpgrade Complete\n\nOptimizer statistics are not transferred by pg_upgrade.\nOnce you start the new server, consider running:\n\/usr\/lib\/postgresql\/15\/bin\/vacuumdb --all --analyze-in-stages\n\nRunning this script will delete the old cluster's data files:\n.\/delete_old_cluster.sh\npg_upgrade output scripts are in \/var\/log\/postgresql\/pg_upgradecluster-14-15-test.mKua\nDisabling automatic startup of old cluster\u2026\n\nSuccess. Please check that the upgraded cluster works. If it does,\nyou can remove the old cluster with\npg_dropcluster 14 test\n\nVer Cluster Port Status Owner Data directory Log file\n14 test 5434 down postgres \/var\/lib\/postgresql\/14\/test \/var\/log\/postgresql\/postgresql-14-test.log\nVer Cluster Port Status Owner Data directory Log file\n15 test 5435 down postgres \/var\/lib\/postgresql\/15\/test \/var\/log\/postgresql\/postgresql-15-test.log\n\n\naklaver@arkansas:~$ pg_lsclusters\nVer Cluster Port Status Owner Data directory Log file\n14 main 5432 online postgres \/var\/lib\/postgresql\/14\/main \/var\/log\/postgresql\/postgresql-14-main.log\n14 test 5434 down postgres \/var\/lib\/postgresql\/14\/test \/var\/log\/postgresql\/postgresql-14-test.log\n15 main 5433 online postgres \/var\/lib\/postgresql\/15\/main \/var\/log\/postgresql\/postgresql-15-main.log\n15 test 5435 down postgres \/var\/lib\/postgresql\/15\/test \/var\/log\/postgresql\/postgresql-15-test.log\n<\/pre><\/div>\n\n\n
NOTE<\/strong>: Be sure and look at the information under Upgrade Complete section above before starting the new cluster and\/or dropping the old cluster.<\/p>\n\n\n\n
pg_buildext
Build and install Postgres extension. The man page indicates this is more a packaging
command and not really for end users.<\/p>\n\n\n\n
pg_virtualenv
Create a temporary Postgres instance for running tests on.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"footnotes":""},"categories":[5],"tags":[],"class_list":["post-525","post","type-post","status-publish","format-standard","hentry","category-postgres"],"_links":{"self":[{"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/posts\/525","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/comments?post=525"}],"version-history":[{"count":61,"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/posts\/525\/revisions"}],"predecessor-version":[{"id":587,"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/posts\/525\/revisions\/587"}],"wp:attachment":[{"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/media?parent=525"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/categories?post=525"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/tags?post=525"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}