0

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;"
  1. application_name-pgslave01
  2. state-streaming
  3. sync_priority-1
  4. sync_state-sync

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

show pool_nodes;

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

[Master][READ/WRITE]

name: pgmaster

postgres:9.6

pgpool:installed(active)

[Slave][READONLY]

name: pgslave

postgres:9.6

pgpool:installed(passive)

Each server has its own IP and a virtual IP addr (the same for both)

How can I achieve this infrastructure?

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.