PostgreSQL replication

README.md

Checklist

• Create user replication in the master

sudo -u postgres psql -c "CREATE USER rep REPLICATION \
LOGIN ENCRYPTED PASSWORD 'thepassword';"

• Modify postgresql.conf in the master

listen_address = # make sure we're listening as appropriate
wal_level = hot_standby
max_wal_senders = 3
checkpoint_segments = 8    
wal_keep_segments = 8 

• Add rule to pg_hba.conf in the master

hostssl replication     rep      1.2.3.4            md5

• Ensure you have ssl activated in the postgresql.conf master

ssl = true

• Create server.crt and server.key files in the master if they don't exist.

Check

ls -l /var/lib/postgresql/$POSTGRES_VERSION/main/ | grep server

Create

sudo -u postgres ln -s /etc/ssl/certs/ssl-cert-snakeoil.pem /var/lib/postgresql/$POSTGRES_VERSION/main/server.crt
sudo -u postgres ln -s /etc/ssl/private/ssl-cert-snakeoil.key /var/lib/postgresql/$POSTGRES_VERSION/main/server.key

• Modify postgresql.conf in the slave

wal_level = hot_standby
max_wal_senders = 3
checkpoint_segments = 8    
wal_keep_segments = 8 
hot_standby = on

• As a postgres user clone this repo in the slave

cd /var/lib/postgresql
git clone https://gist.github.com/67da7f9aa3faf45ceb3d.git replication

• Run the replication/replicate_postgres.sh script in the slave

replicate_postgres.sh

#!/bin/bash

MASTER_IP="1.2.3.4"
MASTER_PORT="5432"
REPLICATION_USER="rep"
REPLICATION_PASSWORD="thepassword"
POSTGRES_VERSION="9.1"

echo Stopping PostgreSQL
sudo service postgresql stop
 
echo Cleaning up old cluster directory
sudo -u postgres rm -rf /var/lib/postgresql/$POSTGRES_VERSION/main
 
echo Starting base backup as replicator
sudo -u postgres pg_basebackup -h $MASTER_IP -D /var/lib/postgresql/$POSTGRES_VERSION/main -U $REPLICATION_USER -v -P

echo Linking server.crt and server.key

sudo -u postgres ln -s /etc/ssl/certs/ssl-cert-snakeoil.pem /var/lib/postgresql/$POSTGRES_VERSION/main/server.crt
sudo -u postgres ln -s /etc/ssl/private/ssl-cert-snakeoil.key /var/lib/postgresql/$POSTGRES_VERSION/main/server.key
 
echo Writing recovery.conf file
sudo -u postgres bash -c "cat > /var/lib/postgresql/$POSTGRES_VERSION/main/recovery.conf <<- _EOF1_
  standby_mode = 'on'
  primary_conninfo = 'host=$MASTER_IP port=$MASTER_PORT user=$REPLICATION_USER password=$REPLICATION_PASSWORD sslmode=require'
  trigger_file = '/tmp/postgresql.trigger'
_EOF1_
"
 
echo Startging PostgreSQL
sudo service postgresql start

Add the following parameter:

--xlog-method=stream # Copy segments while cloning DB
# OR
--xlog-method=stream # Copy segments after cloning DB

If you need to backup a DB and the WAL_Segments may generate meanwhile.

Does it make sense to throw something like Wireguard into the mix when dealing with WAN to prevent your Postgres server from getting hammered due to having publicly exposed ports?