Обсуждение: repmgr setup and one other question

Поиск
Список
Период
Сортировка

repmgr setup and one other question

От
sbob
Дата:
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