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.