The system I work on we deploy almost solely on the Amazon AWS platform. Even I try to design the architecture in a way not to be locked-in too much into Amazon, I make use of the Amazon tools and products as much as possible (EC2, VPC, S3, SNS). PostgreSQL is our reference DB and the only DB product in production environments, still we run dedicated instances with PostgreSQL. I am quite delighted about AWS recent offering staring RDS with PostgreSQL. While is is still in BETA and I did not started yet with a conclusive test and migration plan, I need to maintain our existing instances.
There are plenty of books and tutorials about setting up PostgreSQL replication with on-board tools, without going into the details I share the express setup in this tutorial based on Streaming Replication which is part of PostgreSQL since version 9.0. I highly recommend to review the parameters and settings from the below tutorial as your project might have different requirements.
References
Tutorial
Remarks
- The tutorial is based on PostgreSQL 9.2 running on Ubuntu Server
- Paths and settings are all the PostgreSQL defaults.
- This is async setup, the master will not wait for feedback from the salve and continue to work even the slave is not available
Prerequisite
- 2 Server running the same PostgreSQL version (9.0+)
- Backup your data or use a sandbox environment.
- In the tutorial I refer to
MASTER (ip: 0.0.0.1) and
SLAVE (ip: 0.0.0.2)
Configuration
Master
- Create a replicator user
sudo -u postgres psql -c "CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'mypassword';"
- Add the slave ip to /etc/postgresql/9.2/main/pg_hba.conf
host replication all 0.0.0.2/32 trust
- Modify parameters in /etc/postgresql/9.2/main/postgresql.conf
wal_level = hot_standby
max_wal_senders = 3
checkpoint_segments = 3
wal_keep_segments = 3
Review these parameters and set them up according to your requirements
- Start the PostgreSQL instance
service postgresql start
Slave
- Modify parameters in /etc/postgresql/9.2/main/postgresql.conf
wal_level = hot_standby
max_wal_senders = 3
checkpoint_segments = 3
wal_keep_segments = 3
hot_standby = on
- Stop the PostgreSQL instance
service postgresql stop
- Clean up the old data directory
sudo -u postgres rm -rf /var/lib/postgresql/9.2/main
- Copy the database from the master with pg_basebackup
sudo -u postgres pg_basebackup -h 0.0.0.1 -D /var/lib/postgresql/9.2/main -U replicator -v -P -x
You can see the backup progress and it should result in something likes
root@:/var/lib/postgresql/9.2/main# transaction log start point: 41/7D000020
31524952/31524952 kB (100%), 2/2 tablespaces (/var/lib/postgresql/9.2/main/PG_9.)
transaction log end point: 41/7D0002A8
pg_basebackup: base backup completed
- Create a recovery configuration file /var/lib/postgresql/9.2/main/recovery.conf
standby_mode = 'on'
primary_conninfo = 'host=0.0.0.1 port=5432 user=replicator password=mypassword sslmode=require'
trigger_file = '/tmp/postgresql.trigger'
- Start the PostgreSQL instance
service postgresql start
Check the pglogs.
Test the replication
- Open any table with pgadmin on the master and apply a change, it should be reflected in the slave within short time.
- Try to change data on the slave, it will fail due to the hot-standby mode
Monitor the replication
- The master instance will not alert you when the replication is down. You can check by yourself or create a little cronjob to do it for you with this sql statement.
sudo -u postgres psql -x -c "select * from pg_stat_replication;"
You get the status back (if it is running, otherwise the statement will return ‘no rows’).
Check replication