Streaming Replication is one of the most relevant features that made the PostgreSQL team decide to jump up to 9.x instead of dealing with 8.5 version. It maybe a little tricky and confusing at the first time to make it work. Here is how I have done with it
- Ubuntu 10.04 or later
- PostgreSQL 9.1
Both servers should have same users, password, and common config for replication
- We want to set up master node on 192.168.1.14, and standby node(slave) on 192.168.1.15
- PostgreSQL data folder is at /var/lib/postgresql/9.1/main/
In pg_hba.conf, add this line
host replication postgres 192.168.1.15/30 trust
Check your postgresql.conf, and modify these things below
listen_addresses = '*' wal_level = hot_standby max_wal_senders = 5 wal_sender_delay = 1s wal_keep_segments = 32 log_connections = on log_line_prefix = '%t '
Check postgresql.conf, and modify things as below
listen_addresses = '*' hot_standby = on
Copy /usr/share/postgresql/9.1/recovery.conf.sample to */var/lib/postgresql/9.1/main/recovery.conf
standby_mode = on primary_conninfo = "host=192.168.1.14 port=5432 user=postgres password=123456"
Steps 1. Make sure postgres user has password
$ sudo passwd postgres
- Change postgres user on DB
$ sudo su postgres $ psql > \password
- Start master DB
- On master, login as postgres user
- On master, synchronize data from master to standby. Run these commands (you have to do it only once)
psql -c "SELECT pg_start_backup('label', true)"; # label is similar to backup name rsync -a /var/lib/postgresql/9.1/main/ 192.168.1.15:/var/lib/postgresql/9.1/main/ --exclude postmaster.pid psql -c "SELECT pg_stop_backup()";
- Start standby(slave) DB
- Try to write some data on master DB, and check changes on slave
Note that: Data should be only written on Master and replicated to Slave. Slave is not expected data from anywhere but Master.
Master-Slave replication model is very good for applications required high availability and read-heavy database.
For PostgreSQL Master-Master replication, bucardo maybe the solution so far.