Skip to content

Instantly share code, notes, and snippets.

@mattupstate
Created November 13, 2014 17:14
Show Gist options
  • Star 33 You must be signed in to star a gist
  • Fork 13 You must be signed in to fork a gist
  • Save mattupstate/c6a99f7e03eff86f170e to your computer and use it in GitHub Desktop.
Save mattupstate/c6a99f7e03eff86f170e to your computer and use it in GitHub Desktop.
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
Copy link

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

@volkanunsal
Copy link

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.

@marquicus
Copy link

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

@Umlatt
Copy link

Umlatt commented Feb 11, 2022

"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;"

@msfci
Copy link

msfci commented Sep 20, 2022

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

@DuyTC1811
Copy link

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?

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