How to Create a Standby for Postgresql Windows

Creating a hot standby for Postgresql

standby for Postgresql

Your Postgresql standby will need two machines that are the same architecture, for example 64 bit with the same version of Postgres installed, including same Postgres architecture.

1. On Master – In pgAdmin run:

[code language=”text”]

CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD ‘mypassword’;

[/code]

2. On Master – In pgAdmin, edit the postgresql.conf and set the following:

[code language=”text”]

wal_level = hot_standby

max_wal_senders = 3

checkpoint_segments = 8

wal_keep_segments = 8

[/code]

3. On Master – In pgAdmin, edit the pg_hba.conf and set the following where 5.6.7.8 is the ip address of the slave:

[code language=”text”]

host replication     replicator     5.6.7.8/32           trust

[/code]

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:

[code language=”text”]

rd /s “c:\program files\Postgresql\9.4\data”

[/code]

7. On Slave – open a command prompt and cd to your Postgres directory:

(c:\program files\Postgresql\9.4) and run where 1.2.3.4 is the ip of the Master:

[code language=”text”]

pg_basebackup -h 1.2.3.4 -D “c:\program files\Postgresql\9.4\data” -U replicator -v –P –X stream

[/code]

8. On slave – In pgAdmin, edit the postgresql.conf and set the following:

[code language=”text”]

hot_standby = on

[/code]

9. On slave – create a text file:

(c:\program files\Postgresql\9.4\data\recovery.conf) with the following contents where 1.2.3.4 is the ip of the Master:

[code language=”text”]

standby_mode = ‘on’primary_conninfo = ‘host=1.2.3.4 port=5432 user=replicator password=mypassword’trigger_file = ‘c:\temp\postgresql.trigger’

[/code]

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.

This amazing tip was provided by the mad scientist at DeployHub.

 

Key Features

Further Reading

Get Started