An example of how to setup streaming replication for PostgreSQL with Docker.

README.md

PostgreSQL Streaming Replication With Docker

The *.txt files here hold user and database parameters. Specifically, replication.txt contains the user/role and password to use for replication. Whereas database.txt contains an initial database, user/role and password to create on the master.

Run the master:

$ fig run -d master

Wait for it to start up completely. Start the slave:

$ fig run slave

Wa-la!

database.txt

example_db example_user abc123

fig.yaml

master:
  image: postgres:latest
  ports:
    - '5432:5432'
  volumes:
    - ./init-master.sh:/docker-entrypoint-initdb.d/init.sh
    - .:/tmp/postgresql

slave:
  image: postgres:latest
  ports:
    - '5433:5432'
  volumes:
    - ./init-slave.sh:/docker-entrypoint-initdb.d/init.sh
    - .:/tmp/postgresql
  links:
  - master

init-master.sh

#!/bin/bash -ve
REP_USER=$(cat /tmp/postgresql/replication.txt | awk '{print $1}')
REP_PASS=$(cat /tmp/postgresql/replication.txt | awk '{print $2}')
REP_PASS_MD5=$(echo -n "${REP_PASS}${REP_USER}" | md5sum | awk '{print $1}')
DB_NAME=$(cat /tmp/postgresql/database.txt | awk '{print $1}')
DB_USER=$(cat /tmp/postgresql/database.txt | awk '{print $2}')
DB_PASS=$(cat /tmp/postgresql/database.txt | awk '{print $3}')
DB_PASS_MD5=$(echo -n "${DB_PASS}${DB_USER}" | md5sum | awk '{print $1}')

echo "Creating pg_hba.conf..."
sed -e "s/\${REP_USER}/$REP_USER/" \
    -e "s/\${DB_NAME}/$DB_NAME/" \
    -e "s/\${DB_USER}/$DB_USER/" \
    /tmp/postgresql/pg_hba.conf \
    > $PGDATA/pg_hba.conf
echo "Creating pg_hba.conf complete."

echo "Creating postgresql.conf..."
cp /tmp/postgresql/postgresql.conf $PGDATA/postgresql.conf

echo "Creating replication user..."
gosu postgres postgres --single <<-EOSQL
   CREATE ROLE ${REP_USER} PASSWORD 'md5${REP_PASS_MD5}' REPLICATION LOGIN;
EOSQL
echo "Creating replication user complete."

echo "Creating example database..."
gosu postgres postgres --single <<-EOSQL
   CREATE DATABASE ${DB_NAME};
   CREATE ROLE ${DB_USER} PASSWORD 'md5${DB_PASS_MD5}' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;
   GRANT ALL PRIVILEGES ON DATABASE ${DB_NAME} to ${DB_USER};
EOSQL
echo "Creating example database complete."

mkdir /var/lib/postgresql/archive
chown postgres:postgres /var/lib/postgresql/archive
chown -R postgres:postgres ${PGDATA}

init-slave.sh

#!/bin/bash -ve
REP_USER=$(cat /tmp/postgresql/replication.txt | awk '{print $1}')
REP_PASS=$(cat /tmp/postgresql/replication.txt | awk '{print $2}')
DB_NAME=$(cat /tmp/postgresql/database.txt | awk '{print $1}')
DB_USER=$(cat /tmp/postgresql/database.txt | awk '{print $2}')

echo "Cleaning up old cluster directory"
rm -rf ${PGDATA}/*

echo "Starting base backup as replicator"
pg_basebackup -h ${MASTER_PORT_5432_TCP_ADDR} -D ${PGDATA} -U ${REP_USER} -vP

echo "Creating pg_hba.conf..."
sed -e "s/\${REP_USER}/$REP_USER/" \
    -e "s/\${DB_NAME}/$DB_NAME/" \
    -e "s/\${DB_USER}/$DB_USER/" \
    /tmp/postgresql/pg_hba.conf \
    > $PGDATA/pg_hba.conf
echo "Creating pg_hba.conf complete."

echo "Creating postgresql.conf..."
cp /tmp/postgresql/postgresql.conf $PGDATA/postgresql.conf
echo "hot_standby = on" >> $PGDATA/postgresql.conf

echo "Writing recovery.conf file"
cat > ${PGDATA}/recovery.conf <<EOS
standby_mode = 'on'
primary_conninfo = 'host=${MASTER_PORT_5432_TCP_ADDR} port=5432 user=${REP_USER} password=${REP_PASS}'
trigger_file = '/tmp/postgresql.trigger'
EOS

mkdir /var/lib/postgresql/archive
chown postgres:postgres /var/lib/postgresql/archive
chown -R postgres:postgres ${PGDATA}

pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

# Allow anyone to connect remotely so long as they have a valid username and
# password.
host    ${REP_USER}     replication     0.0.0.0/0               md5
host    ${DB_NAME}      ${DB_USER}      0.0.0.0/0               md5

postgresql.conf

listen_addresses = '*'
wal_level = hot_standby
max_wal_senders = 5
checkpoint_segments = 32
wal_keep_segments = 32
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/archive/%f'

replication.txt

replication abc123

Very helpful! But I'm curious why you didn't add the ENV variables into the yaml file, but instead read them from plaintext files?

Also where is MASTER_PORT_5432_TCP_ADDR coming from?

Another silly question: Why did you use PASSWORD 'md5${REP_PASS_MD5}' rather than ENCRYPTED PASSWORD '${REP_PASS}'

I've been trying to implement this gist and I'm running into some issues. The streaming replication is not happening. I wonder if that has to do with the fact that the archive_command is just copying the logs to a local directory. Some other guides I've read use rsync to push them out to the slave server.

"error: failed switching to "postgres": operation not permitted" caused by gosu

"error: failed switching to "postgres": operation not permitted" caused by gosu
You can potentially replace:
gosu postgres postgres --single <<-EOSQL CREATE ROLE ${REP_USER} PASSWORD 'md5${REP_PASS_MD5}' REPLICATION LOGIN; EOSQL
With:
sudo -u postgres -H -- psql -c "CREATE ROLE ${REP_USER} PASSWORD 'md5${REP_PASS_MD5}' REPLICATION LOGIN;"

Thank you very helpful!
Used it with docker compose and with some extra commands and really saved my day

Thank you very helpful!
Used it with docker compose and with some extra commands and really saved my day

Can you share how you did it?