Hot Standby Server Configuration

Replication is a feature of postgres and is automatically set up for cloud venues. Self service venues may set this up if they wish - the support team is unable to help you.

For the purposes of the example, let's assume that:

  • the main database server is at 192.168.0.5 and
  • the hot standby server is at 192.168.0.10 as per the diagram
  • the version of postgres you are using is 10 (otherwise change 10 to xx in all commands, depending on the version of postgres you are using)

 Stop the Hot Standby server and do not restart it until told to do so.

Start Terminal, then sign on as the Postgres user via the following commands to stop the database:

su - postgres
pg_ctl stop -m fast
exit

Create recovery.conf

Create a file called 'recovery.conf' (which should not exist) in /Library/PostgreSQL/10 for safe keeping. We will be copying this file later on as Postgres expects to find it the 'data' folder in /Library/PostgreSQL/10/data. However, we cannot put it there yet as we will be deleting everything in that directory later. Replace 192.168.0.5 with the address of your primary database server.

Using Terminal as administrator of the machine, create and edit the recovery.conf since the Postgres user cannot create this file. Alternatively, copy the text and paste it into a BBedit file on the machine and use a copy command to put it into place.

As admin User
sudo vi /Library/PostgreSQL/10/recovery.conf

then enter the contents of the file by typing the yellow text below. (you can usually copy/paste into vi if in 'insert' mode)

standby_mode = on # enables stand-by readonly mode

# Connect to master postgres server using postgres user
primary_conninfo = 'host=192.168.0.5 port=5432 user=postgres'

# specify the name of a file whose presence should cause streaming replication to
# end (i.e. failover). If you create this file, then Postgres will rename 'recovery.conf'
# to 'recovery.done' and promote the hot standby to a primary server without a restart.
trigger_file = '/tmp/pg_failover_trigger'

# shell command to execute an archived segment of WAL files
# required for archive recovery if streaming repliation falls behind too far.

restore_command = 'cp /Library/PostgreSQL/10/archive/%f %p'
archive_cleanup_command = '/Library/PostgreSQL/10/bin/pg_archivecleanup /Library/PostgreSQL/10/archive/ %r'

Make sure that the archive directory exists

If you did not already do this command as part of the set up of the MASTER database, you will need to do so, so that the backup files can be received by the HOT STANDBY server.

As admin User

Make the '.ssh' folder and 'archive' folder for the postgres user in the postgres user's home directory /Library/PostgreSQL/10 and provide access to it.

DO THE SAME two commands on the both the MAIN and on the HOT STANDBY server if you have not previously done so.

sudo mkdir /Library/PostgreSQL/10/archive
sudo chown postgres:daemon /Library/PostgreSQL/10/archive

If you are re-establishing a hot-backup server, make sure to clear out all files from the 'archive' folder on the hot standby. In Terminal, type:

cd /Library/PostgreSQL/10/archive
sudo rm -rf *

You do not need to stop the main server while setting up the hot standby as we will make a base backup to 'restore to a point in time'.