1

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.

Rajat Jain
  • 201
  • 3
  • 12

1 Answers1

2

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

Umar Hayat
  • 116
  • 4
  • That is great! But how will I get to know that due to replication lag , queries are not going to slave server? Will pgpool will raise any alarm? – Rajat Jain May 05 '20 at 06:54
  • If replication delay exceeds `delay_threshold`, so SELECT query will be sent to slave. You can enable logging using `log_standby_delay` with values (none, always or if_over_threshold) – Umar Hayat May 05 '20 at 07:40