Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

PostgreSql Replication Monitoring

In this tutorial explained about how to monitoring Postgresql server
Checking Log Position
replica1=# select pg_last_xlog_receive_location();
       pg_last_xlog_receive_location
      -------------------------------
       41A/10808DE8

replica2=# select pg_last_xlog_receive_location();
       pg_last_xlog_receive_location
      -------------------------------
       41A/FFD1560
Timestamp for the replica replay:
replica1=# select pg_last_xact_replay_timestamp();
pg_last_xact_replay_timestamp
-------------------------------
2012-10-05 10:35:47.527-07
compare two xlog locations to see which one is higher:
replica2=# select pg_xlog_location_diff('1/10808DE8','1/FFD1560');
pg_xlog_location_diff
-----------------------
              8616072
The way you would use them is as follows:
1.The master goes down.
2.Check xlog_location_numeric() on each backend.
3.Pick the backend with the highest (or tied for highest) numeric position, and check how far behind it is in replay using replay_lag_mb(), but see below.
4.If the highest replica isn't too far behind on replay, promote it.
5.If the highest replica is too far behind, drop to the next-highest and check replay lag.

easily done using pg_xlog_location_diff:
replica2=# SELECT pg_xlog_location_diff(pg_xlog_last_receive_location(), pg_xlog_last_replay_location());
pg_xlog_location_diff
----------------------------
      16192
replay lag in megabytes, regardless of PostgreSQL version.
bench=# select replay_lag_mb();
replay_lag_mb
---------------
         93.7

  • all_replayed() and replay_lag_mb() are designed to be run only on replicas.  They will return NULL on standalone or master servers.these functions will return the same results regardless of which database they're installed in.  However, they can only be called from the database in which they are installed.   So you might want to install them in the "postgres" scratch database.
  • xlog_location_numeric() returns a numeric value which can exceed a 64-bit integer in size.  So make sure your calling and sorting code is prepared to handle a larger-than-eight-byte number.





Comments

Popular posts from this blog

Oracle DBMS SCHEDULER Examples

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

How to Enable/Disable autovacuum on PostgreSQL