1

in our setup the show pool_nodes shows a very high replication_delay and it keeps increasing, becuase of which any new queries are not replicated in the slave

following is the output of show pool_nodes command, is there a way to reset this, data loss if fine as this is not a live/production system.

[root@DB2 ~]#  psql -h DB-HA-Hostname -U postgres -p 5432 -c 'show pool_nodes'
node_id |               hostname               | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | last_status_change
---------+--------------------------------------+------+--------+-----------+---------+------------+-------------------+-------------------+---------------------
0       | DB1-hostname | 5432 | up     | 0.500000  | primary | 0          | true              | 0                 | 2021-01-11 19:32:00
1       | DB2-hostname | 5432 | up     | 0.500000  | standby | 0          | false             | 54986528          | 2021-01-11 19:32:00
(2 rows)

I have tried, restarting nodes, restarting pgpool, restarting postgresql , deleting database etc.. but no luck. As soon as the slave gets attached the replication_delay is high again..

Harsha Aithal
  • 103
  • 1
  • 1
  • 8

2 Answers2

1

You can run this command to check status of replication:

psql -h DB-HA-Hostname -U postgres -p 5432 -c "select * from pg_stat_replication" -x

if it shows:

enter image description here

if not, the configuration has failed.

You can show your configuration ?

cursorrux
  • 1,382
  • 4
  • 9
  • 20
Viettel Solutions
  • 1,519
  • 11
  • 22
0

Check the replication is running or not if it's not running re-configure the standby then attached the nodes

select * from pg_stat_replication;

after taking basebackup start the postgresql server then pcp-attach-node on pgpool

NagabrahmamChowdary Kalluri
  • 11
  • 1
  • 6