Master database setup (Windows)

Setting up the Master database for replication involves configuring two files:

  • pg_hba.conf
  • postgresql.conf

Both are found in the following directory: <Clarify Database Install Directory>/db/data

Configuring pg_hba.conf to allow replication

Note: In the examples below, we use Server A (10.0.0.101) as the master database, and Server B (10.0.0.102) as the standby.

  1. Open the pg_hba.conf file.
  2. Go to the “Allow replication connection” section and copy the first host line. For example:

    #host replication postgres 127.0.0.1/32 md5

  3. Paste it after the last entry in the configuration file and:
    • Remove the #
    • Replace the ip address with the Postgres Standby database server address.
    For example: host replication postgres 10.0.0.102/32 md5
    Note: The IP address is that of the Standby database server.

  4. Save and close the file.

    Configuring postgresql.conf

    Most of the following configuration prepares the Master database if and when it becomes a standby database server in case of failure.

  5. Open the postgresql.conf file.
  6. Under the WRITE AHEAD LOG section of the file:
    • Remove the # from wal_level and set it to equal hot_standby
    • Adjust the checkpoint_segments to 256 (if not already set as the default).
  7. Under the REPLICATION section of the file:
    • Remove the # from max_wal_senders and set to equal 2 (this number must be greater than the actual number of Standby servers; this configuration assumes only 1 Standby server exists).
    • Remove the # from wal_keep_segments and set it to the same value as the checkpoint_segments (equals 256).
    • Remove the # from hot_standby and enable it (set to equal on).
    Remove the # from max_wal_senders and set to equal 2 (this number must be greater than the actual number of Standby servers; this configuration assumes only 1 Standby server exists).

  8. Restart the postgresql-9.4 service on the Master database server.