postgresql.md

Templates

Create a template

=> CREATE DATABASE foo TEMPLATE template0;
=> \c foo
...set up template...
=> UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'foo';

Users

Create a Hashed Password

echo -n '[PASSWORD]''[USER NAME]' | openssl md5 | sed -e 's/^.* /md5/'

streaming-replication.md

What follows is a procedure for setting up postgresql streaming replication.

• M = master
• S = slave

Notes

• master and slave must have the same version of PostgreSQL,
• perform all actions as user 'postgres'.

Once-only Master preparation

M, create replication user:

CREATE USER [REPLICATION_USER] REPLICATION LOGIN CONNECTION LIMIT 1 PASSWORD '[UNENCRYPTED PASSWORD]';

M, edit pg_hba.conf:

host replication [REPLICATION_USER] [SLAVE_IP]/32 md5

M, edit postgresql.conf:

wal_level = hot_standby
wal_keep_segments = [at least 32, but better much higher]
max_wal_senders = [number of slaves]
# slave setting, ignored on master
hot_standby = on

M, restart postgresql

Prepare for copy

S, edit pg_hba.conf:

host replication [REPLICATION_USER] [MASTER_IP]/32 md5

S, prepare environment:

export REPLICATION_USER=[REPLICATION_USER]
export PG_VERSION=[PG_VERSION]
export SLAVE_IP=[SLAVE_IP]
export SLAVE_CLUSTER_NAME=[SLAVE_CLUSTER_NAME]
export SLAVE_DATA_DIRECTORY=/var/lib/postgresql/$PG_VERSION/$SLAVE_CLUSTER_NAME
export MASTER_IP=[MASTER_IP]
export MASTER_PORT=[MASTER_PORT]
export MASTER_CLUSTER_NAME=[MASTER_CLUSTER_NAME]
export MASTER_DATA_DIRECTORY=/var/lib/postgresql/$PG_VERSION/$MASTER_CLUSTER_NAME

S, stop postgresql cluster

pg_ctlcluster $PG_VERSION $SLAVE_CLUSTER_NAME stop

S, prepare data directory:

rm -rf $SLAVE_DATA_DIRECTORY
mkdir $SLAVE_DATA_DIRECTORY
chmod 0700 $SLAVE_DATA_DIRECTORY

S, create ~/recovery.conf (only for Postgresql < 9.3, see below):

This file will be copied to the database's data directory after all the data has been copied.

standby_mode = 'on'
primary_conninfo = 'host=[MASTER_IP] port=[MASTER_CLUSTER_PORT] user=[REPLICATION_USER] password=[PASSWORD]'
trigger_file = '/tmp/postgresql.trigger.[SLAVE_CLUSTER_PORT]'

Copy files

S, Using pg_basebackup

a. For postgresql < 9.3:

pg_basebackup \
  --pgdata=$SLAVE_DATA_DIRECTORY \
  --xlog-method=fetch \
  --username=$REPLICATION_USER \
  --host=$MASTER_IP \
  --port=$MASTER_PORT \
  --verbose

b. For postgresql >= 9.3:

(This creates recovery.conf)

pg_basebackup \
  --write-recovery-conf \
  --pgdata=$SLAVE_DATA_DIRECTORY \
  --xlog-method=fetch \
  --username=$REPLICATION_USER \
  --host=$MASTER_IP \
  --port=$MASTER_PORT \
  --verbose

M, Manual method

psql -c "select pg_start_backup('initial_backup');"
rsync -cva --inplace \
    --exclude='pg_xlog*' \
    --exclude='server.*' \
    --exclude='postmaster.*' \
    $MASTER_DATA_DIRECTORY/ \
    $SLAVE_IP:$SLAVE_DATA_DIRECTORY/
psql -c "select pg_stop_backup();"

Complete

S, add missing files and directories:

ln -s /etc/ssl/certs/ssl-cert-snakeoil.pem $SLAVE_DATA_DIRECTORY/server.crt
ln -s /etc/ssl/private/ssl-cert-snakeoil.key $SLAVE_DATA_DIRECTORY/server.key
cp ~/recovery.conf $SLAVE_DATA_DIRECTORY

S, start cluster:

pg_ctlcluster $PG_VERSION $SLAVE_CLUSTER_NAME start