Reset database replication after failover

Should a database failover occur, manual intervention will be required to reconfigure the old Master to replicate once it is brought back online, or set up a new standby database.

In order to re-establish a database as the Standby (post-failover), use the server that was running as the original Master database server before failover.

  1. Remove the recovery.done file from the now-running Postgres Master database.
  2. 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
    • Verify that the two trigger files (failover.trigger and failoverLog.token) have been removed from the trigger folder found on the Server Cluster Shared directory. Clarify writes these files as a result of database failover.
    • 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: