I configured a master-slave replication server with postgres 9.6 following this doc and all worked fine. When I make changes on master server immediately are replicated to slave server and I cannot write on slave server as spected.
After that I attempted to do the same with pgpoolII + watchdog for load balancing but I don't know why the roles of each servers are switched (watchdog information).
PostgreSQL replication checks
psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"
From the slave server this command has no results. Also
psql -x -c "select * from pg_stat_replication;" gives me information about the connected slave server.
But pgpool tells me different
pcp_recovery_node -h VIRTUALIPCLUSTER -p 9898 -U postgres -n 1
DETAIL: primary server cannot be recovered by online recovery. # The answer is the same in both servers as they both were master
this command tells me that both servers role is standby and the postgres on the one that should be the master is down (but on master server the postgres server is running). Also load_balance_node is set to true on slave server and false for master(different from the docs consulted)
And this is the watchdog info
10.1.157.213:9999 Linux pgslave 10.1.157.213 9999 9000 4 MASTER
10.1.157.212:9999 Linux pgmaster 10.1.157.212 9999 9000 7 STANDBY
As you can see the information is inconsistent. It is supposed the master server is always the master primary and the slave is the standby.
Can anyone guide me on here? I'm receiving very inconsistent information from postgres and pgpool services. How can I fix this configuration and make my servers consistent as spected
Each server has its own IP and a virtual IP addr (the same for both)
How can I achieve this infrastructure?