Unlike Mysql, Postgres doesn’t have a native replication process. Nevertheless it’s possible to built a warm stand-by replicate using asynchronous log transfer.
Prerequisites
- SSH access between your master and slave nodes
- rsync
- pg_standby
Enable archive log and log shipping
First create a directory for shipped log on the slave node :
mkdir /var/lib/postgresql/alog/Then enable archive log and log shipping into the master setting file :
master:~# vi /etc/postgresql/8.3/main/postgresql.conf
archive_mode = on
archive_command = 'rsync -a %p slave:/var/lib/postgresql/alog/%f'
archive_timeout = 60
master:~# /etc/init.d/postgresql-8.3 restart
The archive_command will be executed each time the archive_timeout is reached or after the log file grow by 16Mo. After setting these parameters and restarting the postgresql service you can watch the archive directory and you should start seeing WAL files being transmitted on your slave node.
Enable replication
Now you need to configure your stand-by server for “continous recovery mode” using archive log has data source. The ‘pg_standby’ tool will be usefull for that.
slave:~$ vi /var/lib/postgresql/8.3/main/recovery.conf
restore_command = '/usr/lib/postgresql/8.3/bin/pg_standby -d /var/lib/postgresql/alog/%f %p %r >> /var/log/postgresql/pg_standby.log 2>&1'
slave:~$ /etc/init.d/postgresql-8.3 start
Ta-dah your warm stand-by replicate should be ready. You can check the /var/log/postgresql/pg_standby.log for more info. Note that you can’t connect to the slave postgres anymore, this behaviour is perfectly normal.
Further Reading and sources