Coding with Titans

so breaking things happens constantly, but never on purpose

HowTo: migrate PostgreSQL 9.6 to 11.5

in  , , ,

Recently I have created a post on how to upgrade OS on Raspberry Pi. It worked pretty well until I figured out there is something wired with my PostgreSQL installation. And it turned out I ended up with two instances installed one next to the other. So I had the 9.6 version running with my existing databases and as well a cluster for version 11.5.

Let me try then to rephrase the other guide, I found here, explaining, how to upgrade PostgreSQL to latest and migrate the databases.

Let’s first verify, what packages have been already available on the machine (so what versions will be discussed). If you don’t have the v11 installed, do it now, otherwise there are no tools to continue with the migration and procedure below will immediately fail.

dpkg --get-selections | grep postgres

That resulted with:

postgresql                      install
postgresql-11                   install
postgresql-9.6                  install
postgresql-client               install
postgresql-client-11            install
postgresql-client-9.6           install
postgresql-client-common        install
postgresql-common               install
postgresql-contrib-9.6          install

Or verify by listing existing clusters:

pg_lsclusters

9.6 main    5432 down   postgres  /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log
11  main    5433 down   <unknown> /var/lib/postgresql/11/main  /var/log/postgresql/postgresql-11-main.log

Now the real work:

  1. Stop the currently running instance.
sudo service postgresql stop
  1. Rename the existing v11 cluster.
sudo pg_renamecluster 11 main main_unused_11

Resulting with:

11  main_unused_11 5433 down   postgres /var/lib/postgresql/11/main  /var/log/postgresql/postgresql-11-main_unused_11.log
  1. Extra step - copy configuration files, otherwise migration will stop with an error.
cp /etc/postgresql/11/main_unused_11/pg_hba.conf /var/lib/postgresql/11/main/pg_hba.conf
cp /etc/postgresql/11/main_unused_11/postgresql.conf /var/lib/postgresql/11/main/postgresql.conf
cp /etc/postgresql/11/main_unused_11/pg_ident.conf /var/lib/postgresql/11/main/pg_ident.conf`
  1. Perform the migration of the older version.
sudo pg_upgradecluster 9.6 main
  1. Restart the service (which should be now v11).
sudo service postgresql restart
  1. Drop old and unnecessary clusters (11 main - should be created automatically during migration).
sudo pg_dropcluster 9.6 main
sudo pg_dropcluster 11 main_unused_11
  1. Then remove old installation packages.
sudo apt --purge remove postgresql-9.6 postgresql-client-9.6 postgresql-contrib-9.6
  1. Grande finale.

That would be all.