Your Personal Sysadmin

Upgrade Postgresql from 11 upwards

On Ubuntu 18.04

Be wary of multiple installations (11, 12, 13), as pg_upgradcluster for example will always go for the highest version.

  1. Copied configuration files for new version
cp -R  /etc/posgresql/11 /etc/posgresql/12
  1. Initialized new version db
/usr/lib/postgresql/12/bin/initdb -D /srv/postgres/12/main
  1. Stopped the current server and killed all connections
/usr/lib/postgresql/11/bin/pg_ctl -D /srv/postgres/11/main/ -mf stop
  1. Ran checked upgrade with linked files
time /usr/lib/postgresql/12/bin/pg_upgrade --old-bindir /usr/lib/postgresql/11/bin/ --new-bindir /usr/lib/postgresql/12/bin/ --old-datadir /srv/postgres/11/main/ --new-datadir /srv/postgres/12/main/ --link --check
  1. Had to fix diverse configuration file problems that are obvious when running
/usr/lib/postgresql/11/bin/pg_ctl -w \
-l pg_upgrade_server.log \
-D /srv/postgres/11/main \
-o "-p 50432 -b  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/var/lib/postgresql'" start
cat pg_upgrade_server.log

Those were mostly faulty references to configuration files, or having to explicitly state the non-standard data directory location.

Lastly, the systemd related things:

systemctl disable postgres@11-main
systemctl enable postgres@12-main

Hey! I'll happily receive your comments via email

Andreas Wagner
Freelance System Administrator from Tallinn, Estonia.