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


  • We want to set up master node on, and standby node(slave) on
  • PostgreSQL data folder is at /var/lib/postgresql/9.1/main/

Master Server

In pg_hba.conf, add this line

host    replication postgres 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= 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/ --exclude

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.


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.