Streaming replication for PG 9.1

Streaming Replication

Taken from http://www.rassoc.com/gregr/weblog/2013/02/16/zero-to-postgresql-streaming-replication-in-10-mins/

# Create replicator user

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

# postgresql.conf

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

# pg_hba.conf

hostssl replication     replicator      5.6.7.8            md5

# slave's postgresql.conf

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

# replication.sh

echo Stopping PostgreSQL
sudo service postgresql stop
 
echo Cleaning up old cluster directory
sudo -u postgres rm -rf /var/lib/postgresql/9.2/main
 
echo Starting base backup as replicator
sudo -u postgres pg_basebackup -h 1.2.3.4 -D /var/lib/postgresql/9.2/main -U replicator -v -P
 
echo Writing recovery.conf file
sudo -u postgres bash -c "cat > /var/lib/postgresql/9.2/main/recovery.conf <<- _EOF1_
standby_mode = 'on'
primary_conninfo = 'host=1.2.3.4 port=5432 user=replicator password=thepassword sslmode=require'
trigger_file = '/tmp/postgresql.trigger'
_EOF1_
"
 
echo Startging PostgreSQL
sudo service postgresql start