how do you migrate from postgres12 to postgres13 for existing installs? do you have to do a pg_update manually? I have a docker install where I tried to change the version numbers and I got an error that the database was not created in the same pg version.
Just search for it on Google how to upgrade a Postgres DB in Docker. Just switching the image is not enough. You have to manually upgrade your database. Best way is to dump the database under 12 and import the dump in 13.
2 Likes
^^Please elaborate how dumping/importing is better than upgrading the cluster?
1 Like
My PostgreSQL and NC Server is not running under docker, so there might be a difference between a VM under ESXi and Docker.
In general it is a very good idea to have a backup of your db by building a dump file.
pg_dump -h localhost -p 5432 -U <MY_PG_Username> -Ft <MY_DB_Name> --file=/tmp/path_$(date +%Y%m%d_%H%M%S).dump
(more info = https://www.postgresql.org/docs/12/app-pgdump.html)
When this is prepared i did the migration as follows (but allways think about differences between VMware and Docker?!)
sudo systemctl stop postgresql@12-main (-> stop old cluster)
sudo systemctl stop postgresql@13-main (-> stop fresh installed cluster)
sudo -u PG_USERNAME pg_dropcluster 13 main (-> delete the empty new cluster, because the upgradecluster command will build a new one that is ready to use!)
sudo -u PG_USERNAME pg_upgradecluster 12 main (-> initiate the upgrade and migration of the old db -> new db)
test your new (in this case PG 13 cluster if it runs /starts properly)
sudo systemctl start postgresql@13-main
If everything is ok stop it again
sudo systemctl stop postgresql@13-main
sudo -u PG_USERNAME pg_dropcluster 12 main (-> delete your old cluster the clean your install)
sudo systemctl start postgresql@13-main (-> start new PG 13 cluster for production again)
restart nginx and your php processes again.
In case of emergency you can switch back and restore your *.dump file. So backing up Database should be step one in any case.
Hope this works …
cheers
1 Like
I see. Makes sense to have a backup before upgrading.
Re dumping older version for import into newer version of postgres. I had issues with this when going from 10 to 12. It would not properly import a binary dump. Resulting in errors such as certain tables not existing. Upgrading the source to ver 12, then dumping resulted in successful restores in another instance using postgres 12. Still doesn’t hurt to have a backup.