Postgresql replication

gistfile1.mediawiki

Table of Contents Configuration Slave Master Slave

Configuration

Slave

• Install WAL-E (Note: You will have to run sudo python setup.py install MULTIPLE times, probably like 10):

sudo apt-get install daemontools pv lzop

git clone https://github.com/wal-e/wal-e.git && cd wal-e && sudo python setup.py install

• Configure the environment for WAL-E (as root):

umask u=rwx,g=rx,o=
mkdir -p /etc/wal-e.d/env
echo "THEACCESSKEY" > /etc/wal-e.d/env/AWS_SECRET_ACCESS_KEY
echo "THEKEYID" > /etc/wal-e.d/env/AWS_ACCESS_KEY_ID
echo 's3://ahalogy-postgresql-wal' > \
  /etc/wal-e.d/env/WALE_S3_PREFIX
chown -R root:postgres /etc/wal-e.d

• sudo -u postgres mkdir -m 770 /var/lib/postgresql/9.2/archive
• Get replicator password from MASTER: Located in /etc/postgresql/9.2/main/recovery.conf
• sudo -u postgres vim /etc/postgresql/9.2/main/recovery.conf :

# Connect to the master postgres server using the replicator user we created.
primary_conninfo = 'host=<master_hostname> port=5432 user=replicator password=<replicator_user_password>'

# Specifies a trigger file whose presence should cause streaming replication to
# end (i.e., failover).
trigger_file = '/tmp/pg_failover_trigger'

# Shell command to execute an archived segment of WAL file series. 
# Required for archive recovery if streaming replication falls behind too far.
restore_command = 'envdir /etc/wal-e.d/env /usr/local/bin/wal-e wal-fetch "%f" "%p"'
archive_cleanup_command = '/usr/lib/postgresql/9.2/bin/pg_archivecleanup /var/lib/postgresql/9.2/archive/ %r'

• sudo vim /etc/postgresql/9.2/main/postgresql.conf:

wal_level = hot_standby 
max_wal_senders = 5
wal_keep_segments = 512
hot_standby = on

• sudo service postgresql stop

Master

• ONLY ON A NEW PRODUCTION MASTER! Otherwise NOTHING below is needed
• sudo vim /etc/postgresql/9.2/main/postgresql.conf , append the following (the first section should already be there) (place the slave hostname in there):

archive_mode = on
archive_command = '/etc/postgresql/9.2/main/local_backup_script.sh "%p" "%f"'
archive_timeout = 3600

• sudo service postgresql restart

Slave

MAKE DAMNED SURE YOU ARE ON THE SLAVE. THEN:

• sudo service postgresql stop (just to be sure)

We use 'sudo -s' for the following because these steps could take 2+ hours and otherwise you would be prompted for your PW again toward the end of the process. Taking too long on the last step will cause replication to fail.

• sudo -s
  • sudo -u postgres rm -rf /var/lib/postgresql/9.2/main
  • sudo -u postgres pg_basebackup -h <master_hostname> -D /var/lib/postgresql/9.2/main -U replicator -v -P && sudo -u postgres cp /etc/postgresql/9.2/main/recovery.conf /var/lib/postgresql/9.2/main/ && sudo service postgresql start
  • Enter the replicator user password (the plain 'Password:' prompt). (See the recovery.conf file to get the password.)