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