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
