How can I upgrade PostgreSQL from version 11 to version 13?

user_1324356 0
Viewed 930

I'm trying to upgrade PostgreSQL from 11 to 13 on a Debian system, but it fails. I have a single cluster that needs to be upgraded:

$ sudo -u postgres pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
11  main    5432 online postgres /var/lib/postgresql/11/main  /var/log/postgresql/postgresql-11-main.log

Here's what I've tried to upgrade it:

$ sudo -u postgres pg_upgradecluster 11 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@11-main
Restarting old cluster with restricted connections...
Notice: extra pg_ctl/postgres options given, bypassing systemctl for start operation
Error: cluster configuration already exists
Error: Could not create target cluster

After this, the system is left in an unusable state:

$ sudo systemctl status postgresql@11-main.service 
ā— postgresql@11-main.service - PostgreSQL Cluster 11-main
     Loaded: loaded (/lib/systemd/system/postgresql@.service; enabled-runtime; vendor preset: enabled)
     Active: failed (Result: exit-code) since Tue 2022-06-14 06:48:20 CEST; 19s ago
    Process: 597 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 11-main start (code=exited, status=0/SUCCE>
    Process: 4508 ExecStop=/usr/bin/pg_ctlcluster --skip-systemctl-redirect -m fast 11-main stop (code=exited, status=>
   Main PID: 684 (code=exited, status=0/SUCCESS)
        CPU: 1.862s

Jun 14 06:47:23 argos systemd[1]: Starting PostgreSQL Cluster 11-main...
Jun 14 06:47:27 argos systemd[1]: Started PostgreSQL Cluster 11-main.
Jun 14 06:48:20 argos postgresql@11-main[4508]: Cluster is not running.
Jun 14 06:48:20 argos systemd[1]: postgresql@11-main.service: Control process exited, code=exited, status=2/INVALIDARG>
Jun 14 06:48:20 argos systemd[1]: postgresql@11-main.service: Failed with result 'exit-code'.
Jun 14 06:48:20 argos systemd[1]: postgresql@11-main.service: Consumed 1.862s CPU time.

$ sudo systemctl start postgresql@11-main.service 
Job for postgresql@11-main.service failed because the service did not take the steps required by its unit configuration.
See "systemctl status postgresql@11-main.service" and "journalctl -xe" for details.

Luckily, rebooting the system brought the old cluster back online, but nothing has been upgraded. Why does the upgrade fail? What are "the steps required by its unit configuration"? How can I upgrade PostgreSQL with minimal downtime?

debian postgresql
2 Answers

I found the source of my problem: a configuration file owned by the wrong user (root instead of postgres) that could not be removed by the pg_dropcluster command because I ran it as the user postgres.

For future reference, here are the correct steps to upgrade a PostgreSQL cluster from 11 to 13:

  • Verify the current cluster is the still the old version:

    $ pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
11  main    5432 online postgres /var/lib/postgresql/11/main  /var/log/postgresql/postgresql-11-main.log
13  main    5434 down   postgres /var/lib/postgresql/13/main  /var/log/postgresql/postgresql-13-main.log

  • Run pg_dropcluster 13 main as user postgres:

    $ sudo -u postgres pg_dropcluster 13 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

  • Run the pg_upgradecluster command as user postgres:

    $ sudo -u postgres pg_upgradecluster 11 main

  • Verify that everything works, and that the only online cluster is now 13:

    $ pg_lsclusters 
Ver Cluster Port Status Owner    Data directory               Log file
11  main    5434 down   postgres /var/lib/postgresql/11/main  /var/log/postgresql/postgresql-11-main.log
13  main    5432 online postgres /var/lib/postgresql/13/main  /var/log/postgresql/postgresql-13-main.log

  • Drop the old cluster:

    $ sudo -u postgres pg_dropcluster 11 main

  • Uninstall the previous version of PostgreSQL:

    $ sudo apt remove 'postgresql*11'
user_13243560

The Debian packages create a cluster automatically when you install the server package, so get rid of that:

pg_dropcluster 13 main

Then stop the v11 server and try again.

user_64643080
Related