Instantly share code, notes, and snippets.

Embed
What would you like to do?
An example of how to setup streaming replication for PostgreSQL with Docker.

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!

example_db example_user abc123
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
#!/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}
#!/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}
# 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
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 abc123
@volkanunsal

This comment has been minimized.

volkanunsal commented Apr 7, 2015

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?

@volkanunsal

This comment has been minimized.

volkanunsal commented Apr 7, 2015

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

@volkanunsal

This comment has been minimized.

volkanunsal commented Apr 9, 2015

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.

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