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.

`pg_upgrade` or use dump & restore: https://www.postgresql.org/docs/current/upgrading.html — , Feb 26 '20 at 08:33

score 121 · Accepted Answer · edited Dec 17 '21 at 00:52

121

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

Log in as postgres user

sudo su postgres

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

pg_dumpall > backup.sql

B) Upgrade to PostgreSQL12

update packages and install postgres 12

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

Stop the postgresql service

sudo systemctl stop postgresql.service

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'

Switch to regular user

exit

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

Start the postgresql service

sudo systemctl start postgresql.service

Log in as postgres user

sudo su postgres

Check your new postgres version

psql -c "SELECT version();"

Run the generated new cluster script

./analyze_new_cluster.sh

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

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

edited Dec 17 '21 at 00:52

Adrian Gonzalez

737
1
7
20

answered Jun 04 '20 at 15:55

Pavan Varyani

1,454
1
8
15

how do you restore the backup.sql file. will that restore all the DB's in postgres? – Tanzil Khan Jul 07 '20 at 07:13
@TanzilKhan restore the backup file if something has gone wrong in steps of (B), use this command psql -U username -f backup.sql to back up, it will restore all the DB's. – Pavan Varyani Jul 07 '20 at 12:18
8

Just a note for anyone doing this on WSL, type `cd ~` before running `pg_upgrade`. Otherwise you'll run into permission issues because its trying to do Linux permission stuff in the Windows file system and fails – twig Sep 29 '20 at 10:49
3

This is great, it even works with postgresql 13 o//. – Nek Nov 22 '20 at 19:29
7

I had to be in a directory writeable by user `postgres` to use this on my Ubuntu Linux system. I switched to `cd /tmp` before I `sudo su postgres` and then it ran fine. Otherwise, I was getting a `could not open log file "pg_upgrade_internal.log"` error. – Todd Nov 30 '20 at 23:10
NOTE: mac homebrew users need to pass in `-U $USER` (with your login userid) to the pg_upgrade script – kbrock Jul 02 '21 at 13:58
Simpler and more automated version: https://stackoverflow.com/a/61066445/87520 – SamGoody Jul 13 '21 at 18:17
1

should use: su - postgres – VnDevil Oct 13 '21 at 07:16
1

I'd suggest to backup files **/etc/postgresql/10/main/pg_hba.conf** and **/etc/postgresql/10/main/postgresql.conf** before running step 9. I just lost all the settings. – Wojciech Jakubas Jan 08 '22 at 22:15
Where can I get the ./analyze_new_cluster.sh file? We need to upgrade from 10 to 12 in RDS Postgresql and after upgrading the DB is really slow because is not using indexes. – Antonio Gamiz Delgado Mar 16 '23 at 19:08

score 27 · Answer 2 · edited Jul 04 '20 at 15:29

27

Backup the database
```
psql --version sudo -u postgres psql pg_dumpall > alldbs.sql
```
(this command will backup all databases from the postgresql db)

Then exit from postgres user, and:

Inside a terminal run these commands:

sudo systemctl stop postgres
sudo apt-get install -y postgresql-12 postgresql-server-dev-12 postgresql-contrib-12 libpq-dev postgresql-12-hypopg
sudo pg_dropcluster 12 main --stop
sudo pg_upgradecluster 10 main
sudo pg_dropcluster 10 main --stop

restart the postgresql service:
```
sudo systemctl restart postgresql
```
login to the postgres:
```
su - postgres
```
to check the version:
```
psql --version
```

I have done using the above steps and I could update the DBand restore all data.

edited Jul 04 '20 at 15:29

vvvvv

25,404
19
49
81

answered Apr 14 '20 at 13:11

vivek rajagopalan

843
3
13
22

to reload the data you do psql -f db.out postgres – Rahul Jan 07 '21 at 10:18
1

@Rahul when you run `pg_upgradecluster` it migrates all databases, schemas, extensions and custom functions. – Evgen Bodunov Aug 31 '22 at 22:11
I've found this approach caused me unexpected trouble by automatically setting start.conf to manual. As a result, the postgres service claimed to be healthy but was in fact not running. There were other problems too, but this was the most difficult one to diagnose. – Roland Heath Nov 16 '22 at 07:00
In step 1, this is showing multiple commands on one line. There should be line-breaks between them. – fooquency Feb 19 '23 at 17:08

ajesh · Answer 3 · 2020-10-21T12:38:09.310

Postgresql Upgrade using pg_upgrade

Step - 1 (Find the locale language and encoding of source Postgresql server)

Log in to Postgresql database as postgres user
Execute the query SHOW LC_COLLATE;

Step - 2 ( Stop the existing/source Postgresql server)

Find the running Postgresql service using $ sudo systemctl list-units | grep postgres
Stop the service $ sudo service postgresql-<source-version>.service stop

Step - 3 (Install target Postgresql server)

Configure repos and install (lot of tutorials available in google)

Step - 4 (Update the locale language and encoding)

$ /usr/pgsql-<target-version>/bin/initdb -D /var/lib/pgsql/<target-version>/data --locale=<add-your-encoding>

Step - 5 (Check the source to target upgrade has any potential issues)

Run the command as postgres user, $sudo su postgres

  $  /usr/pgsql-<target-version>/bin/pg_upgrade --old-bindir /usr/pgsql-<source-version>/bin --new-bindir /usr/pgsql-<target-version>/bin --old-datadir /var/lib/pgsql/<source-version>/data --new-datadir /var/lib/pgsql/12/data --link --jobs=2 --check

If it's ok we can move to the next step, If not fix the issues before proceeding to the next step Expected output: Clusters are compatible

Step - 6 (Upgrade the source to target Postgresql version using the link option)

link option is pretty faster than the migration
Run the command as postgres user, $sudo su postgres

    $ /usr/pgsql-<target-version>/bin/pg_upgrade --old-bindir /usr/pgsql-<source-version>/bin --new-bindir /usr/pgsql-<target-version>/bin --old-datadir /var/lib/pgsql/<source-version>/data --new-datadir /var/lib/pgsql/<target-version>/data --link

Expected output: Upgrade Complete

Thanks. ENJOY !!!

score 0 · Answer 4 · answered May 08 '23 at 09:26

There is a problem when migrating from e.g. 10 to 14, because then the encoding of the passwords is changed from md5 to SCRAM-SHA-256 but of course no conversion is performed. There are then two possible solutions, set a new password for all users on the postgresql console, or set the procedure back to md5 in the postgresql.conf. But this is not enough, the pg_hda.conf must also be adapted.