Postgresql – Is it possible that the old primary follow the new secondary

postgresqlrepmgr

I need to change the roles between two nodes.

Circumstance:

2servers:

primary: psql01 and
standby: psql02

After psql01 stops, psql02 becomes the new primary. After a while, start psql01

On psql01:

[-bash-4.2$ repmgr -f /var/lib/pgsql/repmgr/repmgr.conf cluster show
 ID | Name         | Role    | Status               | Upstream | Location | Priority | Timeline | Connection string
----+--------------+---------+----------------------+----------+----------+----------+----------+---------------------------------------------
 1  | psql01       | primary | * running            |          | default  | 100      | 7        | host=psql01 user=repmgr dbname=repmgr
 2  | psql02       | standby | ! running as primary |          | default  | 100      | 8        | host=psql02 user=repmgr dbname=repmgr

WARNING: following issues were detected
  - node "postgresql02" (ID: 2) is registered as standby but running as primary](url)

On psql02:

repmgr -f /var/lib/pgsql/repmgr/repmgr.conf cluster show

     ID | Name         | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string
    ----+--------------+---------+-----------+----------+----------+----------+----------+---------------------------------------------
     1  | psql01       | primary | ! running |          | default  | 100      | 7        | host=psql01 user=repmgr dbname=repmgr
     2  | psql02       | primary | * running |          | default  | 100      | 8        | host=psql02 user=repmgr dbname=repmgr

    WARNING: following issues were detected
      - node "psql01" (ID: 1) is running but the repmgr node record is inactive

I try to put my old primary to standby with the following commands:

-bash-4.2$ repmgr -f /var/lib/pgsql/repmgr/repmgr.conf node service --action=stop --checkpoint
NOTICE: issuing CHECKPOINT
DETAIL: executing server command "sudo systemctl stop postgresql-11"

-bash-4.2$ repmgr -f /var/lib/pgsql/repmgr/repmgr.conf -d 'host=psql01 user=repmgr dbname=repmgr' node rejoin
ERROR: connection to database failed
DETAIL:
could not connect to server: Connection refused
        Is the server running on host "psql01" (172.16.11.171) and accepting
        TCP/IP connections on port 5432?

What's exaclty happen? If psql01 goes down how can I change the role to secondary?

Best Answer

On postgresql12:

systemctl stop postgresql-12.service

/usr/pgsql-12/bin/repmgr -f /etc/repmgr/12/repmgr.conf node rejoin --force-rewind --verbose -d 'host= psql02 user=repmgr dbname=repmgr connect_timeout=2' --dry-run
Related Question