Containerized Postgres version upgrade¶

How to upgrade postgres version which runs in docker container

Introduction¶

Main idea of this post is to demonstrate postgres major version upgrade process for the case when postgres runs within docker container.

Demonstrated approach uses pg_dumpall utility. In any case it does not pretend to be the "best" approach. There are other options availabe such as pg_upgrade or setting up a replication server. Moreover, for big databases pg_dumpall might be non optimal or even not feasible solution.

But as said at the beginning, idea is to demonstrate possible upgrade process for containerized postgres installation.

Base postgres version¶

Let's create instance running 13^th postgres version and map its data directory to the ~/tmp/pg folder.

$ podman run -d --name pg13 -v ~/tmp/pg:/var/lib/postgresql/data -e POSTGRES_PASSWORD=postgres -p 5432:5432 postgres:13-alpine
9dd62cf2814b4f1850b5277d00b71565ae2ea51ad6234212475e78cb4bbdfd35

$ podman ps
CONTAINER ID  IMAGE                                 COMMAND     CREATED         STATUS             PORTS                   NAMES
9dd62cf2814b  docker.io/library/postgres:13-alpine  postgres    27 seconds ago  Up 27 seconds ago  0.0.0.0:5432->5432/tcp  pg13

$ podman exec pg13 psql -c "select version()" postgres postgres
                                                   version
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 13.5 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.3.1_git20211027) 10.3.1 20211027, 64-bit
(1 row)

Having instance up and running we could create some basic objects in it

$ podman exec -it pg13 psql postgres postgres
psql (13.5)
Type "help" for help.

postgres=#

postgres=> create table t as select 1 id;
SELECT 1
postgres=> create view v as select * from t;
CREATE VIEW
postgres=> select * from v;
 id
----
  1
(1 row)

Container upgrade¶

If now we will stop 13^th version container and try to launch 14^th postgres over the same ~/tmp/pg data folder it will fail

$ podman stop pg13
pg13
$ podman run -d --name pg14 -v ~/tmp/pg:/var/lib/postgresql/data -e POSTGRES_PASSWORD=postgres -p 5432:5432 postgres:alpine
72cdeefe2b01a3dad273442b666b7d596bc1bfce6abd64ff05a50a7bc7aaf36f
$ podman ps
CONTAINER ID  IMAGE       COMMAND     CREATED     STATUS      PORTS       NAMES
$ podman logs pg14

PostgreSQL Database directory appears to contain a database; Skipping initialization

2021-12-21 21:52:37.949 UTC [1] FATAL:  database files are incompatible with server
2021-12-21 21:52:37.949 UTC [1] DETAIL:  The data directory was initialized by PostgreSQL version 13, which is not compatible with this version 14.1.

$ podman rm pg14
72cdeefe2b01a3dad273442b666b7d596bc1bfce6abd64ff05a50a7bc7aaf36f

pg_dumpall¶

In official docs there are two approaches are specified when using pg_dumpall utility: - moving data folder - create new server in parallel

Which way to go is a matter of preferences or constraints. We will use first approach. Let's start 13^th version instance back again and dump it into the ~/tmp/pg13.sql file

$ podman start pg13
pg13
$ podman exec pg13 pg_dumpall -U postgres > ~/tmp/pg13.sql

We have to stop the database, move the data folder and create an empty one. Using sudo for the directory moving command is required because uid:gid of a files inside the directory was set from within the container. And in general case might be different from the current host os username.

$ podman stop pg13
pg13
$ sudo mv ~/tmp/pg ~/tmp/pg13
$ mkdir ~/tmp/pg

New postgres version¶

Now we could init 14^th postgres version inside the same ~/tmp/pg folder. We also have to mount created ~/tmp/pg13.sql backup file to the new container to some temporary folder. This is in order to be able to import backed up data later on.

$ podman run -d --name pg14 \
  -v ~/tmp/pg13.sql:/tmp/pg13.sql \
  -v ~/tmp/pg:/var/lib/postgresql/data \
  -e POSTGRES_PASSWORD=postgres -p 5432:5432 postgres:alpine
aadec2666dbfde5bd564aef091d0e90cc278d2ea3491bc62fd0fbd856fc8c104

Final step is to trigger backup file import from within the new container.

$ podman exec pg14 psql -f /tmp/pg13.sql postgres postgres

SET
SET
SET
psql:/tmp/pg13.sql:14: ERROR:  role "postgres" already exists
ALTER ROLE
You are now connected to database "template1" as user "postgres".
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
You are now connected to database "postgres" as user "postgres".
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE VIEW
ALTER TABLE
COPY 1

As you can see there is one error shown in a log related to already existent postgres role. It doesn't block anything in our case. But in official docs it's said that it's better to backup data with a newer version of a pg_dumpall utility. Because created dump will be more compatible with a target postgres version. In order to use this approach you have to have newer version of a server to be created in parallel to the existing one. This corresponds to the second dump approach listed above.

After the import is done we could connect to the new database to make some checks.

$ podman exec -it pg14 psql postgres postgres
psql (14.1)
Type "help" for help.

postgres=#

postgres=> select version();
                                                   version
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 14.1 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.3.1_git20211027) 10.3.1 20211027, 64-bit
(1 row)

postgres=> select * from v;
 id
----
  1
(1 row)

postgres=> \d t
                 Table "public.t"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           |          |

postgres=> \sv v
CREATE OR REPLACE VIEW public.v AS
 SELECT t.id
   FROM t

As we can see, server version was upgraded to the 14^th version. Table and view were successfully migrated as well.