PostgreSQL 9 Streaming Replication Tutorial

1 min 26 sec read
In Code

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

My environment

  • Ubuntu 10.04 or later
  • PostgreSQL 9.1

Both servers should have same users, password, and common config for replication

Suppose:

  • 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/

Master Server

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 '

Standy Server

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
  1. Change postgres user on DB
$ sudo su postgres
$ psql
> \password
  1. Start master DB
  2. On master, login as postgres user
  3. 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()";
  1. Start standby(slave) DB
  2. Try to write some data on master DB, and check changes on slave

That’s all.

Note that: Data should be only written on Master and replicated to Slave. Slave is not expected data from anywhere but Master.

Conclusion

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.