Postgresql update is not a trivial task.
According to PostgreSQL Versioning policy:
A major release is numbered by increasing either the first or second part of the version number, e.g. 9.1 to 9.2. Minor releases are numbered by increasing the third part of the version number, e.g. 9.2.3 to 9.2.4
Upgrading to a minor version is done through the upgrade process of Debian.
For major versions, the process is much more complex. This post describes the upgrading from 9.4 to 9.5 in a Debian (Jessie) server.
First things first
First think carefully if you really need to upgrade. Large databases could be very slow and big downtime will take place.
Actually, you need to upgrade
- when significant security issues are fixed with this version
- when this major version includes new features, which are necessary for your application
If you DO NOT NEED to upgrade (for a period of time, e.g. 1 year or more)
hold Postgresql updates using
apt-mark hold postgresql
REMARK: remove the hold with
apt-mark unhold postgresql
If you really NEED to upgrade
- Take a full backup (using pg_dumpall or pg_dump)
- Create a detailed upgrade plan and execute it in TEST server
- Finally, upgrade production server
The Classical Approach
The user manual covers this topic in depth. See here.
There are two options available. You can upgrade:
pg_dump -Fc -U [ROLE] [myDB1] -f /path/to/myDB1.dump
pg_restore -C -d myDB1 /path/to/myDB1.dump
- using pg_upgrade in-place
If you are in doubt, select the first method. This will work in all cases. Additionally, it is the only method which works in case new Postgresql version includes changes in system tables.
The Debian Way
STEP 1 – Inform Users for DOWNTIME
Your application must be unavailable during update. So, inform users accordingly.
STEP 2 – Update Debian as usual
apt-get update && apt-get -V upgrade
More details here.
Sometimes, you may get a message like:
The following packages have been kept back: postgresql (9.4+170.pgdg80+1 => 9.5+172.pgdg80+1) postgresql-contrib (9.4+170.pgdg80+1 => 9.5+172.pgdg80+1)
In this case (or similar), just use:
apt-get install postgresql-9.5
STEP 3 – Remove new cluster
Remove the new version cluster, which created during common Debian update (in STEP 2):
pg_dropcluster --stop 9.5 main
STEP 4 – UPGRADE with pg_upgradecluster
In Debian there is a tool available to perform the upgrade. This is a Perl script, named pg_upgradecluster. It will upgrade an existing PostgreSQL cluster to a new major version. Data and configuration files of the old version are copied to the new cluster. This utility also reconfigures the old database cluster to use a different network port.
Use this command:
pg_upgradecluster -v 9.5 9.4 main
you will get something like:
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 ... config /etc/postgresql/9.5/main data /var/lib/postgresql/9.5/main locale en_US.UTF-8 socket /var/run/postgresql port 5433 Disabling connections to the new cluster during upgrade... Redirecting start request to systemctl Roles, databases, schemas, ACLs... Fixing hardcoded library paths for stored procedures... Upgrading database myDB1... Analyzing database myDB1... Fixing hardcoded library paths for stored procedures... Upgrading database myDB2... Analyzing database myDB2... Fixing hardcoded library paths for stored procedures... Upgrading database postgres... Analyzing database postgres... Fixing hardcoded library paths for stored procedures... Upgrading database template1... Analyzing database template1... Fixing hardcoded library paths for stored procedures... Upgrading database myDB3... Analyzing database myDB3... Re-enabling connections to the old cluster... Re-enabling connections to the new cluster... Copying old configuration files... Copying old start.conf... Copying old pg_ctl.conf... Stopping target cluster... Redirecting stop request to systemctl Stopping old cluster... Redirecting stop request to systemctl Disabling automatic startup of old cluster... Configuring old cluster to use a different port (5433)... Starting target cluster on the original port... Redirecting start request to systemctl Success. Please check that the upgraded cluster works. If it does, you can remove the old cluster with pg_dropcluster 9.4 main
STEP 5 – remove old cluster
When you are sure that new cluster works normally, use this command to remove old cluster:
pg_dropcluster --stop 9.4 main
this will remove
/etv/postgresql/9.4 /var/lib/postgresql/9.4 /var/run/postgresql/9.4-main.pid
You may also remove the binaries.