2

I wonder how I can upgrade the data from an existing PostgreSQL 14 installation to 15.

I automatically got PG 15 installed via apt, so I see

# dpkg -l | grep ii | cut -d" " -f3 | grep postgresql-1

postgresql-14
postgresql-15

But the data is still all in PG 14.

There are two services

service postgresql@14-main status

and

service postgresql@15-main status
rubo77
  • 27,777
  • 43
  • 130
  • 199

2 Answers2

2

Be sure you are still running on the old 14 cluster, Then backup your data with

pg_dumpall -F t > ~/backup_postgres_all_dbs.tar

Stop the still empty default installed postgreSQL 15 cluster and drop it.

pg_dropcluster 15 main --stop

Upgrade the 14 cluster to the latest version (which is 15 at the moment writing)

pg_upgradecluster 14 main

This can take some hours. Once it is finished, check that the upgraded cluster works:

service postgresql@14-main stop
service postgresql@15-main start

Your 14 cluster should now be “down”. you can verify it running:

pg_lsclusters

Ver Cluster Port Status Owner    Data directory              Log file
14  main    5433 down   postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
15  main    5432 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log

Check if the applications, that use postgreSQL all work (eventually adapt the port in your psql-15 config). If everything is fin then remove the 14 cluster with

# !be really sure to call this!
# !DON'T BE TOO FAST!!! # pg_dropcluster 14 main

and remove the old packages.

apt-get purge postgresql-14 postgresql-client-14

(Note: This most probably also works the same on Kali)

adapted from: https://www.paulox.net/2022/04/28/upgrading-postgresql-from-version-13-to-14-on-ubuntu-22-04-jammy-jellyfish/

rubo77
  • 27,777
  • 43
  • 130
  • 199
  • What does the `-F` parameter do in `pg_dumpall` ? It's an invalid option. `t` also doesn't work. I used this command: `sudo -u postgres pg_dumpall | gzip > /tmp/backup_postgres_all_dbs-$(date +"%Y_%m_%d").gz`, and then moved it afterwards – Typewar Jun 22 '23 at 10:00
  • I read [here](https://backup.ninja/news/postgresql-backups-what-is-pgdumpall) all the options for pg_dumpall command should be similar to pg_dump command. so it "Format tar". But this could be outdated – rubo77 Jun 30 '23 at 06:59
  • The `-m upgrade` argument to `pg_upgradecluster` will speed it up considerably. – Steve Madsen Jul 13 '23 at 20:30
  • Why is `-m upgrade` not the default then? Is it somewhat unsafe? – rubo77 Jul 18 '23 at 15:31
0

Or dump and re-import your data with

sudo -u postgres pg_dumpall --cluster 14/main > ~/backup_postgres_all_dbs.sql
sudo -u postgres psql -d postgres --cluster 15/main -f ~/backup_postgres_all_dbs.sql

see https://unix.stackexchange.com/a/707827/20661

rubo77
  • 27,777
  • 43
  • 130
  • 199
  • I'd rather avoid this method because if something specific has to be done for the upgrade it probably won't be done (eg: template change?, or hook scripts run by pg_upgradecluster?) – A.B Feb 13 '23 at 09:05
  • Only if you use vanilla postgreSQL. What application would install hook scripts? – rubo77 Feb 13 '23 at 12:38
  • Couldn't find any on Debian. But It would probably be future proof to run a dedicated upgrade script. – A.B Feb 13 '23 at 12:47