Dockerized Postgresql : Delete PGDATA content

Question

I am trying to configure replication between two Postgresql docker containers. All tutorials out there - which are based on regular VM, not containers - states that a backup from the Master has to be done in the Replica / StandBy, through a command like this:

pg_basebackup -h 192.168.0.108 -U replicator -p 5432 -D $PGDATA -Fp -Xs -P -R

Unfortunately, this throws an error:

pg_basebackup: error: directory "/var/lib/postgresql/data" exists but is not empty

I cannot delete the content of this folder because the Postgresql service will crash and I will be kicked out of the container.

So, how can I do this given that I cannot stop the Postgresql service because of the containerized application?

You would probably have to start a container that is not running postgresql by default. The model would be something like (a) start primary (b) start container for pg_basebackup that populates the postgres data volume then finally (c) start the replica postgres container. — larsks
– larsks, Commented Jan 12, 2022 at 0:20
Thanks for the idea :). Please add a response so I can upvote you. — George
– George, Commented Jan 12, 2022 at 10:20

larsks · Accepted Answer · 2022-01-13 00:22:00Z

1

These instructions following the percona docs for configuration postgres replication.

Create a network for the postgres servers:

docker network create pgnet

Start the primary database server:

docker run -d \
  --network pgnet \
  -v primary_data:/var/lib/postgresql/data \
  --name pg_primary \
  -e POSTGRES_PASSWORD=secret \
  -e POSTGRES_DB=exampledb \
  docker.io/postgres:14

Create a replication user and configure pg_hba.conf to allow replication access:

docker exec pg_primary \
  psql -U postgres -c \
  "CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'secret'"

docker exec pg_primary \
  sh -c 'echo host replication replicator 0.0.0.0/0 md5 >> $PGDATA/pg_hba.conf'

docker exec pg_primary \
  psql -U postgres -c 'select pg_reload_conf()'

Prepare the pgdata directory for the replica server. We do this by running the pg_basebackup command in an ephemeral container:

docker run -it --rm \
  --network pgnet \
  -v replica_data:/var/lib/postgresql/data \
  --name pg_replica_init \
  docker.io/postgres:14 \
  sh -c 'pg_basebackup -h pg_primary -U replicator -p 5432 -D $PGDATA -Fp -Xs -P -R -W'

(The -W in the above command forces pg_basebackup to prompt for a password. There are other ways you could provide the password; the postgres docs have details.)

Start the replica:

docker run -d \
  --network pgnet \
  -v replica_data:/var/lib/postgresql/data \
  --name pg_replica \
  docker.io/postgres:14

Verify that replication is working. Create a table in the primary:

docker exec pg_primary \
  psql -U postgres exampledb -c 'create table example_table (id int, name text)'

See that it shows up in the replica:

docker exec pg_replica \
  psql -U postgres exampledb -c '\d'

You should see as output:

             List of relations
 Schema |     Name      | Type  |  Owner
--------+---------------+-------+----------
 public | example_table | table | postgres
(1 row)

edited Jan 13, 2022 at 0:22

answered Jan 12, 2022 at 13:14

larsks

318k50 gold badges474 silver badges482 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

George Over a year ago

Thanks, this works like a charm. There's a tiny problem though with the "exampledb" which is not created at all.

larsks Over a year ago

That's my bad; I dropped an environment variable from the invocation of the primary. I've fixed that in the answer.

Collectives™ on Stack Overflow

Dockerized Postgresql : Delete PGDATA content

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related