1

I am in the process of building a resilient database cluster and currently have 3 database servers running postgres 10 and repmgr. repmgr is taking care of the streaming replication including automatic failover if there is a problem with the master. So far this is working fine, if I turn off the master, repmgr detects this and makes one of the standbys into a master.

The next step is for the application servers to read /write to the database. For this I have installed pgpool on the application servers. The application connects to the db on localhost port 9999 (pgpool). Pgpool will detect which database is the master and write to that, it will load balance the reads.

The problem I am having is during failover... initially pgpool is reporting correctly which databases are masters and which are slaves but after a failover it picks up the new master and shows all slaves as down even though one of them is actually still up.

after a failover repmgr looks correct:

usr/pgsql-10/bin/repmgr -f /etc/repmgr/10/repmgr.conf cluster show ID | Name | Role | Status | Upstream | Location | Connection string
----+------------+---------+-----------+------------+----------+------------------------------------------------------- 1 | icinga-db0 | primary | * running | | default | host=icinga-db0 user=repmgr dbname=repmgr connect_timeout=2 2 | icinga-db1 | primary | - failed | | default | host=icinga-db1 user=repmgr dbname=repmgr connect_timeout=2 3 | icinga-db2 | standby | running | icinga-db0 | default | host=icinga-db2 user=repmgr dbname=repmgr connect_timeout=2

the primary failed but a secondary took over, the other secondary upstream is now the new primary

but pgpool reports this: psql -U repmgr -p 9999 -c "show pool_nodes" node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay ---------+------------+------+--------+-----------+---------+------------+-------------------+------------------- 0 | icinga-db0 | 5432 | up | 0.333333 | primary | 99 | true | 0 1 | icinga-db1 | 5432 | down | 0.333333 | standby | 5929 | false | 0 2 | icinga-db2 | 5432 | down | 0.333333 | standby | 12 | false | 13136

this doesn't match with what repmgr says as db2 should have a status of up.

Does anyone know what the cause of this might be? Is there a better way to achieve the same outcome?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.