0

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?

4
  • did you read the documentation from here postgresql.org/docs/current/warm-standby-failover.html – zsheep Feb 7 '20 at 18:51
  • yes I read, but the error: "could not connect to server: Connection refused" appears becasue database is stopped. If database are down is it possible to run the command "repmgr node rejoin" ? – Guif If Feb 8 '20 at 20:58
  • to my understanding no the databases have to be up and running to change there roles. there may be away around this, but i do not know it, given no other comments have been posted, My suggestion is post to postgresql general email list to try and get help there – zsheep Feb 9 '20 at 0:11
  • If a node that was previously primary at startup is still, can I repmgr change its state? Maybe do aun update on repmgr database? – Guif If Feb 9 '20 at 9:09
0

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
2
  • Welcome to DBA.SE. Could you possibly edit your answer and explain what would happen if your solution is applied? Are there any risks? Could something break? Would you try something that isn't explained? Consider having a peek at How to Answer for some ideas. Help us increase the quality of DBA.SE together. Thanks. – John K. N. Apr 24 '20 at 11:08
  • Please also consider reading the Markdown help for hints on how to format questions and answers. – John K. N. Apr 24 '20 at 11:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.