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. | |
Hot Standby ServerStop 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 ServerYou 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.
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 |
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) |
tar -xjvf /Users/Shared/Backups/pg_basebackup.tar.bz2 |
chown -R postgres:daemon /Library/PostgreSQL/9.6/data |
cp /Library/PostgreSQL/9.6/recovery.conf /Library/PostgreSQL/9.6/data/recovery.conf |
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 |
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 |
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) |