repmgr setup and one other question

From: sbob <sbob(at)quadratum-braccas(dot)com>
To: Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: repmgr setup and one other question
Date: 2023-08-01 23:02:44
Message-ID: 955a4ddf-d828-3a36-89e9-188aa3f8086b@quadratum-braccas.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Browse pgsql-admin by date

  From Date Subject
Next Message Phani Prathyush Somayajula 2023-08-02 13:51:17 Assign User Defined DataType To Columns
Previous Message richard coleman 2023-08-01 19:27:40 Re: analyze foreign tables