Chapter 6. Promoting a standby server with repmgr

If a primary server fails or needs to be removed from the replication cluster, a new primary server must be designated, to ensure the cluster continues to function correctly. This can be done with repmgr standby promote, which promotes the standby on the current server to primary.

To demonstrate this, set up a replication cluster with a primary and two attached standby servers so that the cluster looks like this:

     $ repmgr -f /etc/repmgr.conf cluster show
     ID | Name  | Role    | Status    | Upstream | Location | Connection string
    ----+-------+---------+-----------+----------+----------+--------------------------------------
     1  | node1 | primary | * running |          | default  | host=node1 dbname=repmgr user=repmgr
     2  | node2 | standby |   running | node1    | default  | host=node2 dbname=repmgr user=repmgr
     3  | node3 | standby |   running | node1    | default  | host=node3 dbname=repmgr user=repmgr

Stop the current primary with e.g.:

   $ pg_ctl -D /var/lib/postgresql/data -m fast stop

At this point the replication cluster will be in a partially disabled state, with both standbys accepting read-only connections while attempting to connect to the stopped primary. Note that the repmgr metadata table will not yet have been updated; executing repmgr cluster show will note the discrepancy:

    $ repmgr -f /etc/repmgr.conf cluster show
     ID | Name  | Role    | Status        | Upstream | Location | Connection string
    ----+-------+---------+---------------+----------+----------+--------------------------------------
     1  | node1 | primary | ? unreachable |          | default  | host=node1 dbname=repmgr user=repmgr
     2  | node2 | standby |   running     | node1    | default  | host=node2 dbname=repmgr user=repmgr
     3  | node3 | standby |   running     | node1    | default  | host=node3 dbname=repmgr user=repmgr

    WARNING: following issues were detected
    node "node1" (ID: 1) is registered as an active primary but is unreachable

Now promote the first standby with:

   $ repmgr -f /etc/repmgr.conf standby promote

This will produce output similar to the following:

    INFO: connecting to standby database
    NOTICE: promoting standby
    DETAIL: promoting server using "pg_ctl -l /var/log/postgresql/startup.log -w -D '/var/lib/postgresql/data' promote"
    server promoting
    INFO: reconnecting to promoted server
    NOTICE: STANDBY PROMOTE successful
    DETAIL: node 2 was successfully promoted to primary

Executing repmgr cluster show will show the current state; as there is now an active primary, the previous warning will not be displayed:

    $ repmgr -f /etc/repmgr.conf cluster show
     ID | Name  | Role    | Status    | Upstream | Location | Connection string
    ----+-------+---------+-----------+----------+----------+--------------------------------------
     1  | node1 | primary | - failed  |          | default  | host=node1 dbname=repmgr user=repmgr
     2  | node2 | primary | * running |          | default  | host=node2 dbname=repmgr user=repmgr
     3  | node3 | standby |   running | node1    | default  | host=node3 dbname=repmgr user=repmgr

However the sole remaining standby (node3) is still trying to replicate from the failed primary; repmgr standby follow must now be executed to rectify this situation (see Chapter 7 for example).