Creating a hot standby for Postgresql

Postgresql tips

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 5.6.7.8 is the ip address of the slave:


host replication     replicator     5.6.7.8/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 1.2.3.4 is the ip of the Master:


pg_basebackup -h 1.2.3.4 -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 1.2.3.4 is the ip of the Master:


standby_mode = 'on'primary_conninfo = 'host=1.2.3.4 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.


Steve Taylor

CITA OpenMake Software

0 thoughts on “Standby for Postgresql Windows”

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Posts

DevOps

Polarion SVN Importer

Using the Polarion SVN Importer tool Lessons on Polarion SVN Importer On and off over the last few years or so I have been working with the open-source Polarion SVN Importer tool to help customers migrate to Subversion Read more...

DevOps

Incremental builds – critical to Continuous Build

Incremental Builds are critical for fast continuous build Making Incremental Builds real Incremental builds are important for managing continuous build for continuous delivery. Handing over a release from dev to prod must be easy and Read more...

DevOps

Build Environment Variables

Build Environment Variables and how to manage the details Build Environment Variables are part of the build process Build Environment Variables are critical, detailed pieces of the over build process. If you are using OpenMake Read more...