Migrating the initial database

	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.

This step assumes that you have used the previous steps and configured the main database and the hot standby server prior to beginning with the following steps. Also, 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.

	Hot Standby Server Stop the Hot Standby server and do not restart it until told to do so (it should be stopped already). We don't want any data flowing from the main database to the Hot Standby while we are preparing the servers. In Terminal, as postgres user:
	pg_ctl stop -m fast
	Main Database Server You may need to use pg_ctl to stop and then start the main server to get the max_wal_server change to take effect before starting the pg_basebackup. Make sure nobody is using the database, then type in Terminal, as postgres user:
	pg_ctl stop -m fast pg_ctl start

Now that the hot standby is configured, it’s necessary to get the contents of the main database onto it, so that replication can sync up. This is done using a handy command named pg_basebackup, which essentially copies the entire data directory of your main database into a location of your choice. It is also what we know as an online backup, as the main database can stay in use while the command copies all the data.

 

On the Main Database Server

Start the pg_basebackup command and put the backup file in some place that Postgres can access. The standard backup directory /Users/Shared/Backups should be fine. The base backup should take about as long as your normal backups take and progress is shown in the Terminal window.

Make sure that Postgres has read/write access on both the local machine and the remote machine to the directory you are using.

The scp command is used to copy the base backup to the same location on the hot standby server (replace 192.168.0.10 with your backup server IP). You could use a USB key or any other mechanism to transfer the file that you want. This may also take a long time and progress is shown in the Terminal window.

pg_basebackup -U postgres -v -D - -P -Ft | bzip2 > /Users/Shared/Backups/pg_basebackup.tar.bz2 scp /Users/Shared/Backups/pg_basebackup.tar.bz2 postgres@192.168.0.10:/Users/Shared/Backups

NOTE: The bzip2 command can be quite CPU intensive and only uses a single core. If a multi-core processor is available you may wish to consider using the pigz compression program instead to get a significant speed-up to the backup. Be aware that using pigz requires separate installation and it also uses a different extension than the bzip2 method: .tar.gz vs. .tar.bz2.

pg_basebackup -U postgres -v -D - -P -Ft | pigz > /Users/Shared/Backups/pg_basebackup.tar.gz scp /Users/Shared/Backups/pg_basebackup.tar.gz postgres@192.168.0.10:/Users/Shared/Backups

 

On the Hot Standby Server

The hot standby server should already be stopped. We need to use Terminal (as the postgres user) and remove the entire data directory - if it exists.

su - postgres Enter the Postgres password pg_ctl stop -m fast (just to make sure Postgres is stopped on the backup server) cd /Library/PostgreSQL/9.6/data rm -rf * ls (There should be no files)

Then expand the copy of the base backup of the main database that we just copied to the hot standby server

tar -xjvf /Users/Shared/Backups/pg_basebackup.tar.bz2

Change the owner of the entire contents of the database directory to be postgres

chown -R postgres:daemon /Library/PostgreSQL/9.6/data

Copy the recovery config file that we made earlier

cp /Library/PostgreSQL/9.6/recovery.conf /Library/PostgreSQL/9.6/data/recovery.conf

 

Changes to postgresql.conf on the Hot Standby Server

The expansion of the base backup database from the main server will also restore its postgresql.conf and pg_hba.conf file.

We now need to adjust them to ensure that they are set to be a hot standby database. You can make these changes using PGAdmin ('Tools->Server Configuration') or by using Terminal and VI to navigate to the Data folder and edit the postgresql.conf file.

The settings below tell Postgres that it is the slave machine. You can read about these parameters in the official documentation and adjust them as needed.

hot_standby = on wal_level = hot_standby max_wal_senders = 5 wal_keep_segments = 32

Look for the 'archive' settings that were restored from the base backup and comment them out by putting a '#' in front of the parameter.

#archive_mode = on #archive_command = 'rsync -aq %p postgres@192.168.0.10:/Library/PostgreSQL/9.6/archive/%f' #archive_timeout = 60

 

Changes to pg_hba.conf on the Hot Standby Server

Edit the pg_hba.conf file and scroll down to the bottom. There should already be 3 lines that have replication in them from the main database. If they are not uncommented, you will need to do so and set permissions to 'trust' (and go back and verify the contents of the same file in the main database).

Finally, start the database on the hot standby server

pg_ctl start

 

Cleanup after Replication is Confirmed to be Working

Please confirm replication is working before doing the next step to clean up the temporary base backup files.

The base backup file was created on both the main server and migrated to the hot standby machines. If you have a large database, this file could be sizeable itself and it is best to delete it. On both machines, you can navigate to the /Users/Shared/Backups directory and remove pg_baseBackup.tar.bz2.

Alternatively: in Terminal you can:

rm -i /Users/Shared/Backups/pg_basebackup.tar.bz2 (you will be asked to confirm the deletion)