repmgr setup and one other question
От | sbob |
---|---|
Тема | repmgr setup and one other question |
Дата | |
Msg-id | 955a4ddf-d828-3a36-89e9-188aa3f8086b@quadratum-braccas.com обсуждение исходный текст |
Список | pgsql-admin |
All; We plan to use pg pool so we can create some custom actions at failover time, via the failover script that we will pass to pg pool. We want to use repmgr ONLY for the follow command, that we will call from the pg pool failover script. Question 1 - is there a viable alternative to repmgr if we only want the follow command, seems like a lot of moving parts just for the follow command. Question 2 I have setup a PostgreSQL primary node and run the following commands against it: createuser -s repmgr createdb repmgr -O repmgr Then I created a repmgr.conf file as follows: node_id=1 node_name='node1' conninfo='host=192.168.105.139 user=repmgr dbname=repmgr connect_timeout=2' data_directory='/var/lib/postgresql/15/data' (192.168.105.139 is the primary db host) Then I ran : /usr/pgsql-15/bin/repmgr -f /var/lib/pgsql/15/repmgr.conf primary register and it worked fine Then I setup a standby using pg_basebackup, since in our target environment some of the prod systems already have hot standby's in place so using the repmgr clone is not an option After I setup the hot standby I created a repmgr.conf file like this: node_id=2 node_name='node2' conninfo='host=192.168.105.140 user=repmgr dbname=repmgr connect_timeout=2' data_directory='/var/lib/postgresql/15/data' (192.168.105.140 is the hot standby host) and I tried to register the standby like this: /usr/pgsql-15/bin/repmgr -f /var/lib/pgsql/15/repmgr.conf standby register INFO: connecting to local node "node2" (ID: 2) INFO: connecting to primary database WARNING: --upstream-node-id not supplied, assuming upstream node is primary (node ID: 1) WARNING: node "node2" not found in "pg_stat_replication" ERROR: local node not attached to primary node 1 HINT: specify the actual upstream node id with --upstream-node-id, or use -F/--force to continue anyway So I added '--upstream-node-id 1' /usr/pgsql-15/bin/repmgr -f /var/lib/pgsql/15/repmgr.conf --upstream-node-id 1 standby register INFO: connecting to local node "node2" (ID: 2) INFO: connecting to primary database WARNING: node "node2" not found in "pg_stat_replication" ERROR: this node does not appear to be attached to upstream node "node1" (ID: 1) DETAIL: no record for application name "node2" found in "pg_stat_replication" HINT: use -F/--force to continue anyway but it still failed, so I used the force flag /usr/pgsql-15/bin/repmgr -f /var/lib/pgsql/15/repmgr.conf --upstream-node-id 1 -F standby register INFO: connecting to local node "node2" (ID: 2) INFO: connecting to primary database WARNING: node "node2" not found in "pg_stat_replication" WARNING: this node does not appear to be attached to upstream node "node1" (ID: 1) INFO: standby registration complete NOTICE: standby node "node2" (ID: 2) successfully registered Which was successful, however when I run a cluster show on the primary, repmgr tells me node2 is not attached to the primary /usr/pgsql-15/bin/repmgr -f /var/lib/pgsql/15/repmgr.conf cluster show WARNING: node "node2" not found in "pg_stat_replication" ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string ----+-------+---------+-----------+----------+----------+----------+----------+------------------------------------------------------------------ 1 | node1 | primary | * running | | default | 100 | 1 | host=192.168.105.139 user=repmgr dbname=repmgr connect_timeout=2 2 | node2 | standby | running | ! node1 | default | 100 | 1 | host=192.168.105.140 user=repmgr dbname=repmgr connect_timeout=2 WARNING: following issues were detected - node "node2" (ID: 2) is not attached to its upstream node "node1" (ID: 1) If I run a select from pg_stat_replication on the primary I get this: select * from pg_stat_replication; -[ RECORD 1 ]----+------------------------------ pid | 1105 usesysid | 10 usename | postgres application_name | walreceiver client_addr | 192.168.105.140 client_hostname | client_port | 45294 backend_start | 2023-08-01 16:16:13.124477-06 backend_xmin | state | streaming sent_lsn | 0/50007E8 write_lsn | 0/50007E8 flush_lsn | 0/50007E8 replay_lsn | 0/50007E8 write_lag | flush_lag | replay_lag | sync_priority | 0 sync_state | async reply_time | 2023-08-01 16:47:45.233223-06 Do I need to do something to tell postgreSQL that the standby is 'node2'? Thanks in advance
В списке pgsql-admin по дате отправления: