Postgresql Upgrade – How To

Postgresql is a popular and powerful RDBMS. It is easy to use and has powerful RDBMS features.

Postgresql has good documentation which are more general and does not have step by step playbook for upgrade. Following is the step by step to upgrade. You can upgrade from lower version to higher version without issue.

Assumptions for this step by step:

  • You have postgresql 9.5 and 10 and 12 are installed.
  • When you do ps -ef |grep post -> you see 3 processes with 9, 10 and 12
  • Postgresql is installed as service

Step 1 – Stop the Postgres service

service postgresql stop

ps -ef |grep post -> [should not see any process]

Step 2 – Drop new version to ensure there is nothing new 

pg_dropcluster --stop 10 main
pg_dropcluster --stop 12 main

Step 3 – Run upgrade scripts – this will upgrade to the latest version – in this case to version 12 and will skip version 10

pg_upgradecluster -m upgrade 9.5 main

This will output upgrade logs. Read through the logs and there are commands for your references. Read through it make sure that there is nothing wrong.

Step 4 – Start up the new version and checkout your database.

pg_ctlcluster 12 main start

Check your database – login and validate your data before you drop the old database and remove everything.

Step 5 – Clean up – remove old data and old installations

pg_dropcluster 9.5 main

This will drop the 9.5 database and related data and cleans up the file system.

Step 6 – Remove installation – here the example is for Linux/Ubuntu

apt-get autoremove --purge postgresql-9.5
apt-get autoremove --purge postgresql-10

Now your system is clean and you are up and running with the latest version of postgresql.

Reference: