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

clusteringdebianfailoverpostgresqlrepmgr

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

Related Question