While there’s absolutely nothing new in this blog"post"that isn’t covered by the wonderful docs I’ve been asked multiple times now by customers if we had some kind of ‘crib notes’ format for how to get replication up and running. And since I just had to set this up and document it for a customer, I figured I might as well"post"it so that I can simply point people to it in the future. So here we are.

Now, let’s get started. I assume you already have two PostgreSQL servers up with the binaries installed. For simplicity’s sake, we will call these machines ‘master’ and ‘standby’. Note too that I’m using replication slots which needs PostgreSQL 9.4.0 or later; if you’re using something earlier, simply ignore the slot stuff.

Let’s get started!

On the master, do the following:

$ cat << EOF >> postgresql.conf
    wal_level = hot_standby
    full_page_writes = on
    wal_log_hints = on
    max_wal_senders = 6
    max_replication_slots = 6
    hot_standby = on
    hot_standby_feedback = on
EOF

On the master, add the external IP addresses of the servers to pg_hba.conf:

$ cat << EOF >> pg_hba.conf
    host replication repl_user IP_of_master/32 md5
    host replication repl_user IP_of_standby/32 md5
EOF

Restart PostgreSQL on the master for the changes to take affect

On the master, create the replication user:

$ psql \
  -d postgres \
  -U postgres \
  -c "CREATE ROLE repl_user LOGIN REPLICATION ENCRYPTED PASSWORD 'secretpasswordhere';"

On the master, create the replication slot for the standby:

$ psql \
  -d postgres \
  -U postgres \
  -c "SELECT * FROM pg_create_physical_replication_slot('standby1', true);"

On the standby, wipe the existing cluster:

$ cd /var/lib/pgsql/9.4/data
$ pg_ctl -D $PWD -mf stop
$ cd ..
$ rm -rfv data

On the standby, use the pg_basebackup command to clone the master (enter the repl_user’s password from above when prompted):

$ pg_basebackup \
  -D data \
  -Fp \
  -R \
  -Xs \
  -c fast \
  -l 'initial clone' \
  -P \
  -v \
  -h IP_of_master \
  -U repl_user

On the standby, tweak the recovery.conf that was created for you and add the replication slot name:

$ cd data
cat << EOF >> recovery.conf
primary_slot_name = 'standby1'
EOF

Start the standby up

And that’s it. You should be all done. Easy, right?