How to upgrade postgresql database from 10 to 12 without losing data for openproject

Question

My OpenProject management software is installed with default postgresql 10. Currently the postgresql DB is 12, It is having lot of new features.

I want to upgrade my Postgres DB without losing the data in the DB. My system is ubuntu 18.04 and hosted openproject.

I searched the internet and could not find a step by step to upgrade postgresql.

Can you please guide me to install new DB and all data should be in the new DB. thanks for your help.

Answer 1

A) First create a backup of all the databases for that (You can continue from B if you dont need a backup)

1. Log in as postgres user

sudo su postgres 

2. Create a backup .sql file for all the data you have in all the databases

pg_dumpall > backup.sql 

B) Upgrade to PostgreSQL12

1. update packages and install postgres 12

sudo apt-get update sudo apt-get install postgresql-12 postgresql-server-dev-12 

2. Stop the postgresql service

sudo systemctl stop postgresql.service 

3. migrate the data

/usr/lib/postgresql/12/bin/pg_upgrade \ --old-datadir=/var/lib/postgresql/10/main \ --new-datadir=/var/lib/postgresql/12/main \ --old-bindir=/usr/lib/postgresql/10/bin \ --new-bindir=/usr/lib/postgresql/12/bin \ --old-options '-c config_file=/etc/postgresql/10/main/postgresql.conf' \ --new-options '-c config_file=/etc/postgresql/12/main/postgresql.conf' 

4. Switch to regular user

exit 

5. Swap the ports the old and new postgres versions.

#change port to 5432 sudo vim /etc/postgresql/12/main/postgresql.conf #change port to 5433 sudo vim /etc/postgresql/10/main/postgresql.conf 

6. Start the postgresql service

sudo systemctl start postgresql.service 

7. Log in as postgres user

sudo su postgres 

8. Check your new postgres version

psql -c "SELECT version();" 

8. Run the generated new cluster script

./analyze_new_cluster.sh 

9. Return as a normal(default user) user and cleanup up the old version's mess

#uninstalls postgres packages      sudo apt-get remove postgresql-10 postgresql-server-dev-10 #removes the old postgresql directory sudo rm -rf /etc/postgresql/10/ #login as postgres user sudo su postgres #delete the old cluster data ./delete_old_cluster.sh 

10. Congrads! Your postgresql version is now upgraded, If everything works well in B, we dont have to apply the backup as we have already migrated the data from the older version to the newer version, the backup is just in case if anything goes wrong.

NOTE: Change the postgresql.conf and pg_hba.conf as per your requirement

PS: Feel free to comment your issues, suggestions or anyother modifications you would like to suggest