Upgrade PostgreSQL with pg_upgradecluster
Another post about upgrading PostgreSQL? Really? Yes, I had to upgrade from 9.3 to 9.5 on Ubuntu 16.04 after upgrading from 14.04 today. And I couldn’t find a post that completely described my situation. So I’m writing my own.
The first thing you need to know is how to see which version of Postgres is currently running:
root@mijn-prod:~# ps aux | grep postgres | grep main postgres 831 0.0 0.5 294604 22796 ? S 21:14 0:00 /usr/lib/postgresql/9.5/bin/postgres -D /var/lib/postgresql/9.5/main -c config_file=/etc/postgresql/9.5/main/postgresql.conf
Yes, that’s 9.5 running. On Ubuntu 2 versions are installed at the same time:
root@mijn-prod:~# dpkg -l | grep postgresql ii postgresql 9.5+173ubuntu0.2 ii postgresql-9.3 9.3.24-0ubuntu0.14.04 ii postgresql-9.5 9.5.14-0ubuntu0.16.04
To start or stop a specific version you can use:
systemctl stop postgresql@9.5-main.service or systemctl start postgresql@9.5-main.service
After upgrading from Ubuntu 14.04 to 16.04 a version 9.5 was installed and a new cluster was created by the installer. You have to drop it if you want to upgrade your 9.3 cluster to 9.5. And you have to stop version 9.5 if you want to drop the cluster.
systemctl stop postgresql@9.5-main.service pg_dropcluster 9.5 main
postgres@mijn-prod:~$ pg_dropcluster 9.5 main Warning: systemd was not informed about the removed yet. Operations like "service postgresql start" might fail. To fix, run: sudo systemctl daemon-reload
I got this cryptic error message, but it worked anyway. Just run the command again if you are not sure.
root@mijn-prod:~# pg_dropcluster 9.5 main Error: specified cluster does not exist
Well with a clean 9.5 it’s time to upgrade 9.3. The upgrade will not work properly if you have people using the database and you will get something like this:
root@mijn-prod:~# pg_upgradecluster 9.3 main Stopping old cluster... Notice: extra pg_ctl/postgres options given, bypassing systemctl for stop operation pg_ctl: server does not shut down HINT: The "-m fast" option immediately disconnects sessions rather than waiting for session-initiated disconnection. Error: Could not stop old cluster
So stop the application or nginx or wherever your users come from:
systemctl stop nginx
and try again:
root@mijn-prod:~# pg_upgradecluster 9.3 main Stopping old cluster... Notice: extra pg_ctl/postgres options given, bypassing systemctl for stop operation Disabling connections to the old cluster during upgrade... Restarting old cluster with restricted connections... Redirecting start request to systemctl perl: warning: Setting locale failed. perl: warning: Please check that your locale settings: LANGUAGE = "en_US:en", LC_ALL = (unset), LC_CTYPE = "UTF-8", LANG = "en_US.UTF-8" are supported and installed on your system. perl: warning: Falling back to a fallback locale ("en_US.UTF-8"). Error: The locale requested by the environment is invalid. Error: Could not create target cluster
Ouch! More scary and cryptic error messages. The solution to the locale warnings is to set them again before running pg_upgradecluster:
root@mijn-prod:~# export LC_CTYPE=en_US.UTF-8 root@mijn-prod:~# export LC_ALL=en_US.UTF-8 root@mijn-prod:~# pg_upgradecluster 9.3 main Stopping old cluster... Notice: extra pg_ctl/postgres options given, bypassing systemctl for stop operation Disabling connections to the old cluster during upgrade... Restarting old cluster with restricted connections... Redirecting start request to systemctl Creating new cluster 9.5/main ... <cut> Success. Please check that the upgraded cluster works. If it does, you can remove the old cluster with pg_dropcluster 9.3 main
Use the version specific start/stop commands to see if both versions are working with the same set of data. If everything works well in the new version you can finally drop the 9.3 cluster.
pg_dropcluster 9.3 main
I got no output but the website no longer works with 9.3 so it’s gone. Time to remove the packages as well:
apt-get autoremove --purge postgresql-9.3
Finally don’t forget to reboot to see if everything starts after a restart.