updating postgres version

[Daniel Drucker]

I'm using xnat=docker-compose and would like to update from postgres12 to 16. But simply changing the postgres version, as noted in https://github.com/NrgXnat/xnat-docker-compose/issues/99 will presumably fail catastrophically - postgres requires manual update steps.

What should I do?

Daniel

[Rick Herrick]

The easiest way IMO is to dump the database under v12, shut down the server, restart it with v16, then import the dump. You can do all this pretty easily with a trimmed down docker-compose file that only starts the database, changing the version from the first and second runs. I've attached two separate files for this. Save these into your xnat-docker-compose folder (otherwise you'll need to change the volume mount paths and explicitly specify the path to your .env file). Then you can run:

docker compose --file docker-compose-v12.yml up --detach

docker exec --interactive --tty xnat-db pg_dump --username=xnat > export.sql

docker compose down

mv postgres-data postgres-data.v12

docker compose --file docker-compose-v16.yml up --detach

docker cp export.sql xnat-db:/tmp

docker exec --interactive --tty xnat-db psql --username=xnat --file=/tmp/export.sql

docker compose down

docker compose up --detach

The last command presumes you've changed the version of PostgreSQL in your .env file to 16.6-alpine (or whatever version of v16 you choose, but that's the default in the xnat-docker-compose project).

There is a way to migrate the actual cluster from v12 to v16, but it requires running both servers simultaneously so you have to modify the connection ports and, if I'm not mistaken, you can't even go directly from v12 to v16, but have to do it with at least one intermediate step (v12->v14 then v14->v16), but you may need to do it version by version. I honestly don't know because I haven't done this in a long time because it is such a complicated and error-prone process that everyone says just to do the dump and restore as described above!

I'm pretty certain the above is technically correct, but you absolutely 100% without fail must make a backup copy of your database before attempting this!

Let me know how it works for you.

Rick Herrick 

Senior Software Developer

ri...@xnatworks.io

https://xnatworks.io | Find us on LinkedIn! 

--
You received this message because you are subscribed to the Google Groups "xnat_discussion" group.
To unsubscribe from this group and stop receiving emails from it, send an email to xnat_discussi...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/xnat_discussion/c9e4d2f0-7c62-41db-9ac2-11bf747b7ceen%40googlegroups.com.

[Daniel Drucker]

For future reference, the above is missing a crucial step (common to any time you do a postgres dump/restore), which is to run ANALYZE afterwards.