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:
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:
- Stop the currently running instance.
sudo service postgresql stop
- Rename the existing v11 cluster.
sudo pg_renamecluster 11 main main_unused_11
11 main_unused_11 5433 down postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main_unused_11.log
- 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`
- Perform the migration of the older version.
sudo pg_upgradecluster 9.6 main
- Restart the service (which should be now v11).
sudo service postgresql restart
- 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
- Then remove old installation packages.
sudo apt --purge remove postgresql-9.6 postgresql-client-9.6 postgresql-contrib-9.6
- Grande finale.
That would be all.