Standby database setup (Windows)

In order to set up the Standby database, you will re-run the Server Cluster installation executable to install the Postgres database on the Standby server.

Once complete, the data directory from the Master database server must be copied to the Standby database server. To do this, you will use a pg_basebackup tool; however first you must remove the current contents of the data directory from the Postgres Standby Database Server.

Here’s how:
  • Connect to the Standby Database server and position to the following directory:

    <Clarify Database Install Directory>/db

  • Delete the data directory.

Copy the data directory using pg_basebackup

After deleting the data directory, execute the pg_basebackup from the Standby Database server:

  1. Open a Command Window as Administrator
  2. Change your directory to the database path for pg_basebackup

    cd <Clarify Database Install Directory>/db/bin

  3. Execute the pg_basebackup command as follows:

pg_basebackup --xlog-method=stream -D <Server Database Directory>/db/data -U <Postgres User> -h <Master IP address> -v

Example: pg_basebackup --xlog-method=stream -D C:/CleoDB/Clarify_4_Database/db/data -U postgres -h 10.0.0.101

Result: Once the command finishes, you should see a new data directory in the <Clarify Database Install Directory>/db/ directory on the Standby Database server.

Modify the pg_hba.conf

Modifying the pg_hba.conf file on the Standby Database server makes the Standby Database able to take the role of Master Database Server should failover occur.

  1. On the Postgres Standby Database Server open the pg_hba.conf file and modify the IP address to that of the Postgres Master Database Server IP address:

    Example: host replication postgres 10.0.0.101/32 md

  2. Prepare the recovery configuration file on the Standby Database server to allow it to connect to the Master Database server.
    • Create a text file in the <Clarify Database Install Directory> and name it recovery.conf.
    • Add the following 3 lines of content to the file (Note: Do not enter example lines shown.) 
      standby_mode = ‘on'
      primary_conninfo = ‘host=<Master Postgres Database Server IP> port=<postgres port> user=<postgres user> password=<postgres password>’
      trigger_file ='<Clarify Share Directory>/trigger/failover.trigger'

      Example: primary_conninfo = 'host=10.0.0.101 port=5432 user=postgres password=Password1'

      Example: trigger_file = ‘C:/ClarifyShare/trigger/failover.trigger’

    • Save and close the file.
    • Copy this file to the data directory in <Clarify Database Install Directory>/db/data.
    Note: the Standby database must be configured with the same username, password, and port as the Master.
  3. Verify that all privileges have been set correctly on the PostgreSQL data directory on the Standby Database server.
    • Verify or add the postgres user to the Security on the data directory on the Postgres Standby Database Server.
      • Right-click on the <Clarify Database Install Directory>/db/data
      • Edit to change permissions
      • Click Add and add the Postgres user.
      • Select Full Control.
      • Click Apply and then OK
    • Start the postgresql-9.4 service on the Standby Database Server. When the service starts, Postgres should be running on the Standby Database server.
    • To confirm that the Standby Database server is connected and replicating, take these steps (from the Master Database server):
      • Using the PgAdminIII Postgres tool, expand the ebi4 database.
      • Run the following SQL command:

        select client_addr, state, sent_location, write_location, flush_location, replay_location from pg_stat_replication;

Result: The tool will output that data was streamed to the Standby Database server at a certain data and time.

(B) Another option is to open the following table view:

  • Expand the ebi4 database.
  • Expand the Catalogs.
  • Expand the PostgreSQL (pg_catalog).
  • Expand the Views:
    • Look for table view - pg_stat_replication and right-click and select view all records.

There should be data shown in this view – which means that replication is working. No data means replication is not working. In this case, you may need to re-check the configuration.

(C) A third option is to use the psql.exe command from a command line:

  • Open a command window as Administrator and run the following sql statement:

cd C:\<Clarify Database Install Directory>\Clarify_4_Database\db\bin

psql.exe -c "select client_addr, state, sent_location, write_location,flush_location, replay_location from pg_stat_replication” -d “ebi4” -U “postgres” -h “<host IP or Name>"

  • If prompted for a Password, enter Password1 (default password)

Output should show as follows:

  • If an entry shows as streaming, then replication has been setup and is running properly.
  • If no entry shows, then you need to go back and check your setup configuration.