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

2 comments:

步丈九州 said...

Thanks for your article, it helps me a lot!

micheal pan said...

BE SMART AND BECOME RICH IN LESS THAN 3DAYS....It all depends on how fast 
you can be to get the new PROGRAMMED blank ATM card that is capable of
hacking into any ATM machine,anywhere in the world. I got to know about 
this BLANK ATM CARD when I was searching for job online about a month 
ago..It has really changed my life for good and now I can say I'm rich and 
I can never be poor again. The least money I get in a day with it is about 
$50,000.(fifty thousand USD) Every now and then I keeping pumping money 
into my account. Though is illegal,there is no risk of being caught 
,because it has been programmed in such a way that it is not traceable,it 
also has a technique that makes it impossible for the CCTVs to detect 
you..For details on how to get yours today, email the hackers on : (
atmmachinehackers1@gmail.com ). Tell your 
loved once too, and start to live large. That's the simple testimony of how 
my life changed for good...Love you all ...the email address again is ;
atmmachinehackers1@gmail.com