Postgresql has a feature that lets you setup streaming replication. There are lots of places to get information by they’re all over the place

Basically the goal here is to get another server which is automatically updated to the latest state of the master server.

We want to do this by using streaming replication to a hot standby server which in the event of a major issue could be used instead of the master server

This means we do not care about anything to do with file based replication for this (however useful that is or can be).

The reason for this is to allow creating a server which can be used for analytics without affecting production. Possibly on a cheaper VM or provider which is fine for analytics but wouldn’t be good enough for production.

At it’s core this is very simple to setup. In practice this has been more complicated, but mostly not because of postgresql itself.

All you need is:

A master server with postgresql
A standby server to replicate the master server on
A user with replication permissions on the master server
Update the config on the master server to allow network access from the standby server
Update the config on the master server to allow replication and using a hot_standby
Update the config on the standby server to allow talking to the master server (??)
Update the config on the standby server to become a standby server
A way to transfer a base backup to the standby server

Install PostgreSQL on both servers

$ sudo apt-get update
$ sudo apt-get install postgresql postgresql-contrib postgresql-client

If you’re using Ubuntu 16.04 then you need to do a bit more

$ # as root
$ echo "deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main" > /etc/apt/sources.list.d/pgdg.list

Setup an SSH connection between the servers

We need this to transfer the base backup needed to setup the standby server

$ sudo su
$ su postgres
$ ssh-keygen
$ cat ~/.ssh/id_rsa.pub #or whatever

Copy that public key to ~/.ssh/authorized_keys on the standby server.

Create replication user on master

$ psql -c "CREATE USER rep REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD 'yourpassword';"

Obviously change the password to something better

Configure the master server

$ sudo su
$ cd /etc/postgres/9.4/main
$ $EDITOR pg_hba.conf

Insert this anywhere not at the bottom of the pg_hba.conf of the master

host    replication     rep     IP_address_of_slave/32   md5

Then the main postgresql config file

$ sudo su
$ cd /etc/postgres/9.4/main
$ $EDITOR postgres.conf

listen_addresses = 'localhost,IP_address_of_THIS_host'
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 1
hot_standby = on

What you need here is to make sure it is listening on an IP address the standby can see. For my server I used '*' which has it listen on all IPs for the server.

You also need to set the other settings as above to get the master server properly setup for replication

Finally, after these changes (specifically archive_mode = on) you need to restart postgresql to apply the changes:

$ service postgresql restart

Configure the slave server

First stop the server

$ service postgresql stop

Configure the standby server to allow access from the master just in case we have to turn this server in the master at some point in the future.

$ sudo su
$ cd /etc/postgresql/9.4/main
$ $EDITOR pg_hba.conf

Like before, add this, but not at the end of the file.

host    replication     rep     IP_address_of_master/32  md5

Now for the main config on the standby server

$ sudo su
$ cd /etc/postgresql/9.4/main
$ $EDITOR postgres.conf

listen_addresses = 'localhost,IP_address_of_THIS_host'
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 1
hot_standby = on

Replicating the initial database

psql -c "select pg_start_backup('initial_backup');"
rsync -cva --inplace --exclude=*pg_xlog* /var/lib/postgresql/9.1/main/ slave_IP_address:/var/lib/postgresql/9.1/main/
psql -c "select pg_stop_backup();"