Skip to content

Instantly share code, notes, and snippets.

@avshabanov

avshabanov/Dockerized Postgres 12.2 Replication Demo.md

Created March 2, 2020 03:55
Show Gist options
  • Save avshabanov/eb8e03a050c79f8e77420b06f9b4abe5 to your computer and use it in GitHub Desktop.
Save avshabanov/eb8e03a050c79f8e77420b06f9b4abe5 to your computer and use it in GitHub Desktop.
Download ZIP
Postgres 12 Replication Demo
Raw
Dockerized Postgres 12.2 Replication Demo.md

Postgres - Simple Master-Slave Replication

This is insecure, fast-to-setup setup of simple master-slave replication with dockerized PostgreSQL installation. Note, that below works with version postgres version 12.2: postgres (PostgreSQL) 12.2 (Debian 12.2-1.pgdg100+1).

Preparation:

# This is where your primary database files will be stored
mkdir -p ~/opt/postgres/simple-replica/master/data
# This is where archive will be stored
mkdir -p ~/opt/postgres/simple-replica/master/archive

# This is where your database will store bootstrap data. This is needed to only at startup time
mkdir -p ~/opt/postgres/simple-replica/slave/bootstrap-data
# This is where you'll have slave replica data stored
mkdir -p ~/opt/postgres/simple-replica/slave/data

# This is a configuration of your postgresql primary node
cat << EOF > ~/opt/postgres/simple-replica/master/postgresql.conf
listen_addresses = '*'
port = 5432
max_connections = 50
ssl = off
shared_buffers = 32MB

# Replication Settings - Master
wal_level = hot_standby
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql-master/archive/%f && cp %p /var/lib/postgresql-master/archive/%f'
max_wal_senders = 3
EOF

# This is a configuration of your postgresql primary node's connectivity
cat << EOF > ~/opt/postgres/simple-replica/master/pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust
host    all             all             172.0.0.0/8             trust

local   replication     all                                     trust
host    replication     all             0.0.0.0/0               trust
host    replication     all             ::1/0                   trust
EOF

# This is a bootstrap configuration of your postgresql slave node
cat << EOF > ~/opt/postgres/simple-replica/slave/postgresql.conf
listen_addresses = '*'
port = 5432
max_connections = 50
ssl = off
shared_buffers = 32MB

# Replication Settings - Slave
hot_standby = on
EOF

Preparing Master DB

Create DB Schema

Start docker w/master configuration:

docker run --rm --name pg-simple-replica-master \
  -e POSTGRES_PASSWORD=docker \
  -v ${HOME}/opt/postgres/simple-replica/master/postgresql.conf:/etc/postgresql/postgresql.conf \
  -v ${HOME}/opt/postgres/simple-replica/master/pg_hba.conf:/var/lib/postgresql/data/pg_hba.conf \
  -v ${HOME}/opt/postgres/simple-replica/master/data:/var/lib/postgresql/data \
  -v ${HOME}/opt/postgres/simple-replica/master/archive:/var/lib/postgresql-master/archive \
  -p 10432:5432 \
  postgres \
  -c 'config_file=/etc/postgresql/postgresql.conf'

Login to docker with master DB:

docker exec -it pg-simple-replica-master psql -h localhost -U postgres -d postgres

Create test DB and test user:

CREATE USER tester NOSUPERUSER;
ALTER USER tester WITH PASSWORD 'test';
CREATE DATABASE test_db WITH OWNER tester;

-- Replication user
CREATE USER repuser NOSUPERUSER;
ALTER USER repuser WITH REPLICATION;
ALTER USER repuser WITH PASSWORD 'test';
ALTER USER repuser WITH CONNECTION LIMIT 5;

\q

Login as user tester and create DB schema, see books.sql:

docker exec -it pg-simple-replica-master psql -h 127.0.0.1 -U tester test_db

Setting up Slave

Find IP address of pg-simple-replica-master e.g. via docker network inspect bridge. Let's say it is 172.17.0.2

Start docker w/slave configuration to bootstrap slave node:

docker run --rm --name pg-simple-replica-slave \
  -e POSTGRES_PASSWORD=docker \
  -v ${HOME}/opt/postgres/simple-replica/slave/postgresql.conf:/etc/postgresql/postgresql.conf \
  -v ${HOME}/opt/postgres/simple-replica/slave/bootstrap-data:/var/lib/postgresql/data \
  -v ${HOME}/opt/postgres/simple-replica/slave/data:/var/lib/postgresql-slave/data \
  -p 20432:5432 \
  postgres \
  -c 'config_file=/etc/postgresql/postgresql.conf'

Run backup:

docker exec -it pg-simple-replica-slave /bin/bash

su postgres
pg_basebackup -h 172.17.0.2 -D /var/lib/postgresql-slave/data -U repuser -v -P --wal-method=stream

Sample error on master if misconfigured:

2020-03-01 06:27:15.183 GMT [127] FATAL:  no pg_hba.conf entry for replication connection from host "172.17.0.3", user "repuser", SSL off

If everything goes well you'll see:

pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/5000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_60"
32554/32554 kB (100%), 1/1 tablespace                                         
pg_basebackup: write-ahead log end point: 0/5000138
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: base backup completed

Once slave backup is done, stop slave node.

Then make new configuration file for slave node (again, note that primary external IP is 172.17.0.2 in this example):

# This is a working configuration of your postgresql slave node
cat << EOF > ~/opt/postgres/simple-replica/slave/postgresql.conf
listen_addresses = '*'
port = 5432
max_connections = 50
ssl = off
shared_buffers = 32MB

# Replication Settings - Slave
hot_standby = on
primary_conninfo = 'host=172.17.0.2 port=5432 user=repuser password=test'
EOF

# THIS IS IMPORTANT: in essence this is telling postgres that it will start as a standby
# Prior to that there used to be a `recovery.conf` file that had similar setting 
touch ~/opt/postgres/simple-replica/slave/data/standby.signal

Start slave node with new configuration and updated data location:

docker run --rm --name pg-simple-replica-slave \
  -e POSTGRES_PASSWORD=docker \
  -v ${HOME}/opt/postgres/simple-replica/slave/postgresql.conf:/etc/postgresql/postgresql.conf \
  -v ${HOME}/opt/postgres/simple-replica/slave/data:/var/lib/postgresql/data \
  -p 20432:5432 \
  postgres \
  -c 'config_file=/etc/postgresql/postgresql.conf'

Try connecting:

# Slave:
docker exec -it pg-simple-replica-slave psql -h 127.0.0.1 -U tester test_db

# Master:
docker exec -it pg-simple-replica-master psql -h 127.0.0.1 -U tester test_db

Run selects to see that database has up-to-date records.

Check Replication State

When connected as admin docker exec -it pg-simple-replica-master psql -h localhost -U postgres -d postgres:

select * from pg_stat_activity  where usename = 'repuser';

Start Slave with Updated pg_hba

Prepare pg_hba.conf:

cat << EOF > ~/opt/postgres/simple-replica/slave/pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust
host    all             all             172.0.0.0/8             trust

local   replication     all                                     trust
host    replication     all             0.0.0.0/0               trust
host    replication     all             ::1/0                   trust
EOF

Start docker container as follows:

docker run --rm --name pg-simple-replica-slave \
  -e POSTGRES_PASSWORD=docker \
  -v ${HOME}/opt/postgres/simple-replica/slave/postgresql.conf:/etc/postgresql/postgresql.conf \
  -v ${HOME}/opt/postgres/simple-replica/slave/data:/var/lib/postgresql/data \
  -v ${HOME}/opt/postgres/simple-replica/slave/pg_hba.conf:/var/lib/postgresql/data/pg_hba.conf \
  -p 20432:5432 \
  postgres \
  -c 'config_file=/etc/postgresql/postgresql.conf'

Links

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment