Published 2022-05-28.
Last modified 2023-05-01.
Time to read: 3 minutes.
This article describes my recommended steps to follow when upgrading a Postgres database forced upon you when upgrading Ubuntu, and the new OS includes a new release of Postgres.
I use PostgreSQL as the database for ScalaCourses.com.
The site is served from an Ubuntu instance, and there is a backup instance.
When upgrading to Ubuntu 22.04, PostgreSQL upgraded from v13 to v14;
I used pg_upgrade, which required temporarily swapping the ports that Postgres used.
However, I used pg_upgradecluster when upgrading to Ubuntu 23.04;
and PostgreSQL upgraded from v14 to v15.
Unfortunately, the documented procedure to use pg_upgradecluster has no
consideration for systemd,
and this makes the upgrade slightly more awkward than it might otherwise be.
While both approaches have issues,
I found that it is easier and faster to upgrade a Postgres installation
using pg_upgradecluster than using pg_upgrade.
Upgrade Backup System First
One of the benefits of a backup system is that you can work through upgrade problems on the backup, before doing the same with the live instance.
This article suggests to upgrade the entire system to Ubuntu 23.04, then upgrade the active Postgres database cluster. The alternative would be to add a new PPA for Postgres, and just update that program before upgrading the entire system; this merely substitutes one set of potential issues for another. The article also demonstrates renaming the extra database cluster that the Postgres upgrade creates, but that just creates cruft since it is not required for anything.
I decided to delete the new database cluster instead, since upgrading the old database cluster is all that is required. As you will read in the Upgrade Procedure section, this is also the advice given during the upgrade procedure.
I decided to first upgrade the Ubuntu OS, then migrate the Postgres database cluster.
Upgrade Removed NVidia Support
After running do-release upgrade on the production system,
the system worked fine, except the console had no graphics.
I fixed it by automatically installing all video drivers:
$ sudo ubuntu-drivers autoinstall
Examining the Postgres Databases
pg_lsclusters displays the available PostgreSQL database clusters.
Prior to upgrading to Postgres 15,
I had never deleted the old versions.
They had quietly accumulated over the 11 years that I had been running ScalaCourses:
$ pg_lsclusters Ver Cluster Port Status Owner Data directory Log file 9.5 main 5431 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log 9.6 main 5433 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log 10 main 5434 online postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log 12 main 5432 online postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log 13 main 5435 online postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log 14 main 5436 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
Listing the databases in the active cluster showed an unnecessary database:
$ sudo -iu postgres psql -l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges --------------+----------+----------+-------------+-------------+----------------------- bix_dev | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | postgres | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | scalacourses | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | template0 | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (5 rows)
I do not care about the bix_dev database, so I decided to delete it.
The database that I care about is called scalacourses.
$ sudo -iu postgres psql -c 'drop database bix_dev;' psql (14.7 (Ubuntu 14.7-0ubuntu0.22.10.1)) Type "help" for help. DROP DATABASE
Upgrade Procedure
Ubuntu is normally upgraded by running do-release-upgrade.
Near the end of that process, the following message appeared.
At this point postgresql-15 and postgresql-client-15
had just been installed.
The message did not reflect the true state of the system,
which caused me some consternation for a moment.
The PostgreSQL version 14 is obsolete,
but the server or client packages are still installed.
Please install the latest packages
(postgresql-15 and postgresql-client-15)
and upgrade the existing clusters with pg_upgradecluster
(see manpage).
Please be aware that the installation of postgresql-15 will automatically create a default cluster 15/main.
If you want to upgrade the 14/main cluster, you need to remove the already existing 15 cluster
(pg_dropcluster --stop 15 main, see manpage
for details).
The old server and client packages are no longer supported.
After the existing clusters are upgraded, the postgresql-14 and
postgresql-client-14 packages should be removed.
Please see /usr/share/doc/postgresql-common/README.Debian.gz for details.
Following is /usr/share/doc/postgresql-common/README.Debian.gz,
which was referenced by the above message.
Most of that file is uninteresting, however two sections are important, and we will look at them.
PostgreSQL for Debian =====================
PostgreSQL is a fully featured object-relational database management system. It supports a large part of the SQL standard and is designed to be extensible by users in many aspects. Its features include ACID transactions, foreign keys, views, sequences, subqueries, triggers, outer joins, multiversion concurrency control, and user-defined types and functions.
Since the on-disk data format of all major PostgreSQL versions (like 9.6, 11, etc.) is incompatible to each other, Debian's PostgreSQL packaging architecture is designed to maintain clusters of different major versions in parallel.
This postgresql-common package provides the common infrastructure and all frontend programs that users and administrators use. The version specific server and client programs are shipped in postgresql-*-<version> packages.
For a detailed description of the architecture, please see
/usr/share/doc/postgresql-common/README.md.gz
First steps for the impatient ----------------------------- Eventually you will not get around reading at least some parts of the manual, but if you want to get straight into playing SQL, here are the steps to create a database user and a database for the Unix user 'joe':
1. Install a database server with the major version of your choice ('postgresql-XY', e. g. 'postgresql-11'). Preferably the latest version, which you can get by installing the metapackage 'postgresql'. This will automatically create a default cluster 'main' with the database superuser 'postgres'.
2. Get a shell for the database superuser 'postgres'. If your system has an active root user, use su:
# su -s /bin/bash postgres
If your system uses sudo to get administrative rights, use sudo instead:
joe$ sudo -u postgres bash
3. In this postgres shell, create a database user with the same name as your Unix login:
$ createuser -DRS joe
For details about the options, see createuser(1).
4. Create a database "joework" which is owned by "joe":
$ createdb -O joe joework
For details about the options, see createdb(1).
5. Exit the postgres shell.
6. As user joe, you should now be able to connect to your database with
$ psql joework
Cluster management ------------------ For managing clusters, the following commands are provided (each with its own manual page):
pg_createcluster - Create a new cluster or integrate an existing one into the postgresql-common architecture. pg_dropcluster - Completely remove a cluster. pg_ctlcluster - Control the server process of a cluster (start, stop, restart). pg_lsclusters - Show a list of all existing clusters and their status. pg_upgradecluster - Migrate a cluster from one major version to another one. pg_renamecluster - Rename a cluster.
Please note that you can of course also use the upstream tools for creating clusters, such as initdb(1). However, please note that in this case you cannot expect *any* of above pg_* tools to work, since they use different configuration settings (SSL, data directories, etc.) and file locations (e. g. /etc/postgresql/11/main/postgresql.conf). If in doubt, then do *not* use initdb, but only pg_createcluster. Since merely installing postgresql-NN will already set up a default cluster which is ready to work, most people do not need to bother about initdb or pg_createcluster at all.
Port assignment --------------- Please note that the pg_* tools automatically manage the server ports unless you specify them manually. The first cluster which is ever created (by any major version) will run on the default port 5432, and each new cluster will use the next higher free one.
E. g. if you first install "postgresql-11" on a clean system, the default 11/main cluster will run on port 5432. If you then create another 11 cluster, or install the "postgresql-12" package, that new one will run on 5433.
Please use "pg_lsclusters" for displaying the cluster <-> port mapping, and please have a look at the pg_createcluster manpage (the --port option) for details.
Default clusters and upgrading ------------------------------ When installing a postgresql-NN package from scratch, a default cluster 'main' will automatically be created. This operation is equivalent to doing 'pg_createcluster NN main --start'.
Due to this default cluster, an immediate attempt to upgrade an earlier 'main' cluster to a new version will fail and you need to remove the newer default cluster first. E. g., if you have postgresql-9.6 installed and want to upgrade to 11, you first install postgresql-11:
apt-get install postgresql-11
Then drop the default 11 cluster that was just created:
pg_dropcluster 11 main --stop
And then upgrade the 9.6 cluster to the latest installed version (e. g. 11):
pg_upgradecluster 9.6 main
SSL --- The PostgreSQL server packages support SSL, which provides encrypted and authenticated network communication. SSL should be used if you have an untrusted network between a database server and a client and these exchange security sensitive data like passwords or confidential database contents.
When a cluster is created with pg_createcluster, SSL support will automatically be enabled. postgresql-common makes use of the 'snakeoil' SSL certificate that is generated by the ssl-cert package, so that SSL works out of the box (ssl_cert_file, ssl_key_file). In addition, if /etc/postgresql-common/root.crt exists, it will be used as CA certificate file (ssl_ca_file).
/etc/postgresql-common/root.crt is a dummy file by default, so that client-side authentication is not performed. To enable it, you should add some root certificates to it. A reasonable choice is to just symlink the file to /etc/ssl/certs/ssl-cert-snakeoil.pem; in this case, client certificates need to be signed by the snakeoil certificate, which might be desirable in many cases. See
/usr/share/doc/postgresql-doc-11/html/ssl-tcp.html
for details (in package postgresql-doc).
Further documentation --------------------- All commands shipped by postgresql-common have detailed manpages. See postgresql-common(7) for the documentation of the database client program wrapping, and user_clusters(5) and postgresqlrc(5) for the cluster configuration.
The documentation of the database server and client functions, SQL commands, modules, etc. documented is shipped in the per-version packages postgresql-doc-<version>.
The Cluster management section in the above file provides some background information for our purposes, so I repeat it here:
For managing clusters, the following commands are provided (each with
its own manual page):
pg_createcluster - Create a new cluster or integrate an existing one
into the postgresql-common architecture.
pg_dropcluster - Completely remove a cluster.
pg_ctlcluster - Control the server process of a cluster
(start, stop, restart).
pg_lsclusters - Show a list of all existing clusters and their status.
pg_upgradecluster - Migrate a cluster from one major version to another one.
pg_renamecluster - Rename a cluster.
Please note that you can of course also use the upstream tools for
creating clusters, such as initdb(1). However, please note that in
this case you cannot expect *any* of above pg_* tools to work, since
they use different configuration settings (SSL, data directories,
etc.) and file locations (e. g.
/etc/postgresql/11/main/postgresql.conf). If in doubt, then do *not*
use initdb, but only pg_createcluster. Since merely installing
postgresql-NN will already set up a default cluster which is ready to
work, most people do not need to bother about initdb or
pg_createcluster at all.
The Default clusters and upgrading section is more important, however it contained errors. I fixed the errors in the following instructions.
When installing a postgresql-NN package from scratch, a default cluster 'main' will automatically be created. This operation is equivalent to doing 'pg_createcluster NN main --start'. Due to this default cluster, an immediate attempt to upgrade an earlier 'main' cluster to a new version will fail and you need to remove the newer default cluster first. E. g., if you have postgresql-9.6 installed and want to upgrade to 11, you first install postgresql-11: yes | sudo apt install postgresql-11 Then drop the default 11 cluster that was just created: sudo -iu postgres pg_dropcluster 11 main --stop And then upgrade the 9.6 cluster to the latest installed version (e. g. 11): sudo -iu postgres pg_upgradecluster 9.6 main
Systemd can significantly affect the upgrade process, and unfortunately it is completely ignored by the above instructions. I muddled through, as you will see.
Transcript
The following is a transcript of what I actually did:
$ sudo -iu postgres pg_dropcluster --stop 15 main Warning: stopping the cluster using pg_ctlcluster will mark the systemd unit as failed. Consider using systemctl: sudo systemctl stop postgresql@15-main Warning: systemd was not informed about the removed cluster yet. Operations like "service postgresql start" might fail. To fix, run: sudo systemctl daemon-reload $ sudo -iu postgres pg_upgradecluster 14 main Stopping old cluster... Warning: stopping the cluster using pg_ctlcluster will mark the systemd unit as failed. Consider using systemctl: sudo systemctl stop postgresql@14-main Restarting old cluster with restricted connections... Notice: extra pg_ctl/postgres options given, bypassing systemctl for start operation Creating new PostgreSQL cluster 15/main ... /usr/lib/postgresql/15/bin/initdb -D /var/lib/postgresql/15/main --auth-local peer --auth-host scram-sha-256 --no-instructions --encoding UTF8 --lc-collate en_CA.UTF-8 --lc-ctype en_CA.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_CA.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/main ... 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/Toronto creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok syncing data to disk ... ok Warning: systemd does not know about the new cluster yet. Operations like "service postgresql start" will not handle it. To fix, run: sudo systemctl daemon-reload 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) Fixing hardcoded library paths for stored procedures... Upgrading database template1... Analyzing database template1... Fixing hardcoded library paths for stored procedures... Upgrading database postgres... Analyzing database postgres... Fixing hardcoded library paths for stored procedures... Upgrading database scalacourses... Analyzing database scalacourses... Stopping target cluster... Stopping old cluster... Disabling automatic startup of old cluster... Starting upgraded cluster on port 5432... Warning: the cluster will not be running as a systemd service. Consider using systemctl: sudo systemctl start postgresql@15-main Success. Please check that the upgraded cluster works. If it does, you can remove the old cluster with pg_dropcluster 14 main Ver Cluster Port Status Owner Data directory Log file 14 main 5433 down postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log Ver Cluster Port Status Owner Data directory Log file 15 main 5432 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log
When I attempted to following the above instructions for systemd, errors appeared:
$ sudo systemctl daemon-reload $ sudo systemctl start postgresql@15-main Job for postgresql@15-main.service failed because the service did not take the steps required by its unit configuration. See "systemctl status postgresql@15-main.service" and "journalctl -xeu postgresql@15-main.service" for details.
Great, an obscure failure message. Others have fussed with this problem. I expected that rebooting would be much faster than trying to fix it, and that did prove to be the case:
$ sudo reboot
After rebooting, the upgraded Postgres cluster worked fine:
$ pg_lsclusters Ver Cluster Port Status Owner Data directory Log file 14 main 5433 down postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log 15 main 5432 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log $ sudo -iu postgres psql -l List of databases Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges --------------+--------------+----------+-------------+-------------+------------+-----------------+----------------------- postgres | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | | libc | scalacourses | scalacourses | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | | libc | template0 | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | | libc | =c/postgres + | | | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | | libc | =c/postgres + | | | | | | | postgres=CTc/postgres (4 rows)
Here is how I deleted the older database clusters:
$ for X in 9.5 9.6 10 12 13; do
sudo -iu postgres pg_dropcluster --stop $X main
end
All done!