Repmgr: Error to promote Standby Cluster to Master Cluster in failover

Question

I maked the configuration of the failover system based in Repmgr, but i don't haved success to promote the Standby Cluster to Master Cluster in case of failover.

Following my configurations, this process shoulded happens automatically, but does not works.

Additional Information:

• Debian 9.2, PostgresSQL 9.6, Repmgr 3.3

• IP Master (host1): 10.0.0.1, IP Standby (host2): 10.0.0.2

• Contents included in /etc/postgresql/9.6/main/postgresql.conf, the same for all

  wal_keep_segments = 5000
  hot_standby = on
  archive_mode = on
  listen_addresses = '*'
  max_wal_senders = 18
  wal_level = hot_standby
  shared_preload_libraries = 'repmgr_funcs'
  archive_command = 'cd .'
  

• Contents of /etc/postgresql/9.6/main/pg_hba.conf

Master

    local   all             postgres                                peer
    local   all             all                                     peer
    host    all             all             127.0.0.1/32            trust
    host    all             all             ::1/128                 md5
    host    repmgr          repmgr          10.0.0.2/32             trust
    host    replication     repmgr          10.0.0.2/32             trust

Standby

    local   all             postgres                                peer
    local   all             all                                     peer
    host    all             all             127.0.0.1/32            trust
    host    all             all             ::1/128                 md5
    host    repmgr          repmgr          10.0.0.1/32             trust
    host    replication     repmgr          10.0.0.1/32             trust

• Contents of /etc/repmgr.conf

Master

    cluster=cluster
    node=1
    node_name=host1
    conninfo='host=host1 user=repmgr dbname=repmgr connect_timeout=2'
    failover=automatic
    promote_command='repmgr standby promote -f /etc/repmgr.conf --log-to-file'
    follow_command='repmgr standby follow -f /etc/repmgr.conf --log-to-file'
    logfile='/var/log/postgresql/repmgr.log'
    loglevel=NOTICE
    reconnect_attempts=4
    reconnect_interval=5

Standby

    cluster=cluster
    node=2
    node_name=host2
    conninfo='host=host2 user=repmgr dbname=repmgr connect_timeout=2'
    failover=automatic
    promote_command='repmgr standby promote -f /etc/repmgr.conf --log-to-file'
    follow_command='repmgr standby follow -f /etc/repmgr.conf --log-to-file'
    logfile='/var/log/postgresql/repmgr.log'
    loglevel=NOTICE
    reconnect_attempts=4
    reconnect_interval=5

• Contents included in /etc/default/repmgrd, the same for all

  REPMGRD_ENABLED=yes
  REPMGRD_CONF="/etc/repmgr.conf"
  

• Result of repmgr cluster show command

  postgres@host1:~$ repmgr cluster show
  Role      | Name   | Upstream | Connection String
  ----------+--------|----------|--------------------------------------------------------
  * master  | host1  |          | host=host1 user=repmgr dbname=repmgr connect_timeout=2
    standby | host2  | host1    | host=host2 user=repmgr dbname=repmgr connect_timeout=2
  

• Error found in log file (/var/log/postgresql/repmgr.log) when executing command "repmgrd"

  [ERROR] unable to retrieve record for node 1: ERROR: relation "repmgr_cluster.repl_nodes" does not exist LINE 1: ...onninfo, slot_name, priority, active FROM "repmgr_cl...
  

Complete Tutorial: https://medium.com/@victor.boissiere/how-to-setup-postgresql-cluster-with-repmgr-febc2f10c243

Answer 1

check out this useful link :

• http://raghavt.blogspot.in/2015/01/configuring-automatic-failover-using.html