Creating a hot standby for Postgresql
How to setup a fully redundant standby for Postgresql
To get started you need two machines that are the same architecture, ie 64 bit, with the same version of Postgres installed, including same Postgres architecture.
1. On Master – In pgAdmin run:
CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'mypassword';
2. On Master – In pgAdmin, edit the postgresql.conf and set the following:
wal_level = hot_standby max_wal_senders = 3 checkpoint_segments = 8 wal_keep_segments = 8
3. On Master – In pgAdmin, edit the pg_hba.conf and set the following where 18.104.22.168 is the ip address of the slave:
host replication replicator 22.214.171.124/32 trust
4. On Master – restart the postgres windows service
5. On Slave – stop the postgres windows service
6. On Slave – open a command prompt and cd to your Postgres directory (c:\program files\Postgresql\9.4) and run:
rd /s “c:\program files\Postgresql\9.4\data”
7. On Slave – open a command prompt and cd to your Postgres directory (c:\program files\Postgresql\9.4) and run where 126.96.36.199 is the ip of the Master:
pg_basebackup -h 188.8.131.52 -D “c:\program files\Postgresql\9.4\data” -U replicator -v –P –X stream
8. On slave – In pgAdmin, edit the postgresql.conf and set the following:
hot_standby = on
9. On slave – create a text file (c:\program files\Postgresql\9.4\data\recovery.conf) with the following contents where 184.108.40.206 is the ip of the Master:
standby_mode = 'on'primary_conninfo = 'host=220.127.116.11 port=5432 user=replicator password=mypassword'trigger_file = 'c:\temp\postgresql.trigger'
10. On Slave – start the postgres windows service
Master and Slave should now be in sync. Update Master DB and confirm that the Slave is updated as well.