Postgresql Master-slave replication lagged but pgpool was sending traffic to both db (master n slave)

Question

We do database load balancing using pgpool2 framwork (master + 1 slave).

Yesterday I got few database errors on test server saying - 'column X does not exist' Upon debugging we found that master-slave replication has stopped or lagged due to which django migration was done just on master server but not on slave. pgppol was still sending read queries to slave server.

How can I avoid such problems or automate such that alarm is raised or notification if anything happen.

Answer 1

If you are using streaming mode (which is default)

master_slave_mode = on
                                   # Activate master/slave mode
                                   # (change requires restart)

You need to configure following to check replication status:

# - Streaming -

sr_check_period = 10
                                   # Streaming replication check period
                                   # Disabled (0) by default
sr_check_user = 'nobody'
                                   # Streaming replication check user
                                   # This is neccessary even if you disable streaming
                                   # replication delay check by sr_check_period = 0
sr_check_password = ''
                                   # Password for streaming replication check user
                                   # Leaving it empty will make Pgpool-II to first look for the
                                   # Password in pool_passwd file before using the empty password

sr_check_database = 'postgres'
                                   # Database name for streaming replication check
delay_threshold = 10000000
                                   # Threshold before not dispatching query to standby node
                                   # Unit is in bytes
                                   # Disabled (0) by default

Here is the documentation of this feauture: https://www.pgpool.net/docs/latest/en/html/runtime-streaming-replication-check.html