PostgreSQL 9.2 Master – Slave Monitoring

Nagios plugin script written in Bash to check the master-slave replication in PostgreSQL (tested on PostgreSQL 9.2.4) (executed on the slave).
The script will report how many bytes the slave server is behind, and how many seconds ago the last replay of data occurred.

The script must be executed as ‘postgres’ user.

Possible outputs:


Comments

PostgreSQL 9.2 Master – Slave Monitoring — 14 Comments

  1. Pingback: PostgreSQL Replication | Uptime Through Simplicity

  2. This script expects that you will alwasy logon as the postgres user itself, while this is not alweays the case.

    I added an extra option for the user, and edited the psql commands so it will use this user instead of the static postgres user.

    export p_user=$6

    master_lag=$($psql -U $p_user -h$p_host -p$p_port -A -t -c “SELECT pg_xlog_location_diff(pg_current_xlog_location(), ‘0/0’) AS offset” $p_db)
    slave_lag=$($psql -U $p_user -h$s_host -p$s_port -A -t -c “SELECT pg_xlog_location_diff(pg_last_xlog_receive_location(), ‘0/0’) AS receive” $p_db)
    replay_lag=$($psql -U $p_user -h$s_host -p$s_port -A -t -c “SELECT pg_xlog_location_diff(pg_last_xlog_replay_location(), ‘0/0’) AS replay” $p_db)
    replay_timediff=$($psql -U $p_user -h$s_host -p$s_port -A -t -c “SELECT NOW() – pg_last_xact_replay_timestamp() AS replication_delay” $p_db)

  3. Hi, when I run it, I got this error. Please help to solve it. Thanks!

    : No such file or directoryne 18: /usr/bin/psql
    : No such file or directoryne 19: /usr/bin/psql
    : No such file or directoryne 20: /usr/bin/psql
    : No such file or directoryne 21: /usr/bin/psql
    monitor_master_slave.sh: line 58: syntax error: unexpected end of file

    • Hi

      Where do you have ‘psql’ installed? Execute ‘which psql’ to find its location, or execute ‘locate bin/psql’ 😉

  4. Does this work for streaming replication?.. I receive an error when trying to run. Streaming Replication has the secondary always in recovery mode.

    ERROR: recovery is in progress
    HINT: WAL control functions cannot be executed during recovery.
    CRITICAL: Stream has no value to compare (is replication configured or connectivity problem?)

  5. Hi, when I run it, I got this error. Please help to solve it. Thanks!

    My operative system is debian

    /opt/postgresql/bin/psql: No such file or directory
    /opt/postgresql/bin/psql: No such file or directory
    /opt/postgresql/bin/psql: No such file or directory
    /opt/postgresql/bin/psql: No such file or directory

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.