Monday, 7 October 2019

What do you need to do before upgrading Postgres Docker image

If you just use the new Postgres version, it is very likely you'll get a message like this:

FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version X, which is not compatible with this version Y

In my case, I had Postgres and pgAdmin running as services in docker-compose:

$ docker-compose pull && docker-compose up
Pulling pg_db    ... done
Pulling pg_admin ... done
Recreating postgres-demo-pg ... done
Recreating postgres-demo-pgadmin ... done
Attaching to postgres-demo-pg, postgres-demo-pgadmin
postgres-demo-pg | 2019-10-07 09:46:59.270 UTC [1] FATAL:  database files are incompatible with server
postgres-demo-pg | 2019-10-07 09:46:59.270 UTC [1] DETAIL:  The data directory was initialized by PostgreSQL version 11, which is not compatible with this version 12.0 (Debian 12.0-1.pgdg100+1).
postgres-demo-pg exited with code 1

To prevent this situation do the following:

Revert postgres version to the previous one (that matches data version, 11 in my case):

docker-compose.yml:

services:
   pg_db:
      image: posgres:11

Run postgres docker container so db loads data as usual:

$ docker-compose up

Use docker exec to dump data into file:

$ docker exec postgres-demo-pg pg_dumpall -U postgres > dump.sql

Stop db container:

$ docker-compose down

Delete old mount dir:

$ sudo rm -rf database_data/

Recreate it:

$ mkdir database_data

Copy dump file to local dir that will be mounted as Postgres data dir:

$ sudo cp dump.sql database_data/

Set postgres version to the latest one:

docker-compose.yml:

services:
   pg_db:
      image: postgres


Run docker container based on the image with the latest Postgres version:

$ docker-compose pull && docker-compose up

Load dump file into db:

$ docker exec -it postgres-demo-pg bash
root@2873483c35ac:/# cd /var/lib/postgresql/data/
root@2873483c35ac:/var/lib/postgresql/data# psql -U postgres < dump.sql
SET
...
SET
root@2873483c35ac:/var/lib/postgresql/data# exit
exit

Verify db version - that it is indeed the latest:

$ docker exec -it postgres-demo-pg bashroot@2873483c35ac:/# postgres --version
postgres (PostgreSQL) 12.0 (Debian 12.0-1.pgdg100+1)
root@2873483c35ac:/# exit
exit

No comments: