Label posts in list when click Blogger Menu link (menu–>label like "home")

step 1:-
Hello friends, this awesome trick of Blogger where you can show your posts of specific label as list form. To do this just copy this code and paste on page, post or sidebar of your Blog.


var numposts = 100;
var standardstyling = true;

function showrecentposts(json) {
  for (var i = 0; i < numposts; i++) {
    var entry = json.feed.entry[i];
    var posttitle = entry.title.$t;
    var posturl;
    if (i == json.feed.entry.length) break;
    for (var k = 0; k < entry.link.length; k++) {
      if (entry.link[k].rel == ‘alternate’) {
        posturl = entry.link[k].href;
        break;
      }}
    posttitle = posttitle.link(posturl);
    if (standardstyling) document.write(‘

  • ‘);
        document.write(posttitle);}
        if (standardstyling) document.write(‘
  • ‘);
    }




    marked   color first is the “website URL” second one is the” label name”
    step 2:-copy the code above code  to the new page like below screen shot and publish it.

    Image





    step 3:-after publish the page copy the link like below screen shot
    Image


    step 4:- go the the “settings->search preference” add “new direct”  and enter below this as per ur blog
                                                                             From:http://www.oracledba786.tk
                                                                                  To:http://www.oracledba786.tk





    Image





    step 5:- if u click “postgresdba”menu it will show like Label posts in list 

    Image



    High Availability/Scalability Using Haproxy And Pgbouncer In Postgresql

    I have multiple PostgreSQL servers for a web application. Typically one master and multiple slaves in hot standby mode (asynchronous streaming replication).
    I use PGBouncer for connection pooling: one instance installed on each PG server (port 6432) connecting to database on localhost. I use transaction pool mode.
    In order to load-balance my read-only connections on slaves, I use HAProxy (v1.5) with a conf more or less like this:
          listen pgsql_pool 0.0.0.0:10001
          mode tcp
          option pgsql-check user ha
          balance roundrobin
          server master 10.0.0.1:6432 check backup
          server slave1 10.0.0.2:6432 check
          server slave2 10.0.0.3:6432 check
          server slave3 10.0.0.4:6432 check
    So, my web application connects to haproxy (port 10001), that load-balance connections on multiple pgbouncer configured on each PG slave.
    Here is a representation graph of my current architecture:
    Image pgbouncer > postgresql” height=”567″ src=”https://lh4.googleusercontent.com/uFI6eBjkUnQiRyaen5e55VxV_KAwp_RG24X5ZBUGIERu8CkV6-e7bJInijZBRGqrE_RkKXvT6ekFUaysIcLX4_kuAlM6QZ2niPz_rfSICv9wHiJGcDnYO7k0RNq1iaX5nPw20kw&#8221; style=”-webkit-transform: rotate(0.00rad); border: none; transform: rotate(0.00rad);” width=”602″ />
    This works quite well like this, but I realize that some implements this quite differently: web application connects to a single PGBouncer instance that connects to HAproxy which load-balance over multiple PG servers:
    Here is the reason: HAProxy redirects connection to different servers. this results in MAC address change in the database connection. So if PGBouncer is above HAProxy, each time the connections in the pool gets invalidated because of MAC address change.
    but
    Image haproxy > postgresql” height=”567″ src=”https://lh6.googleusercontent.com/UXp7Ti_yzlKwrAsW9yqaFfVlZ3J-Cq5e4DD9EnOESf2vVxDPBKw1NW-MaFwkM34s832FQbBGhQ4jKDJNCmqO443ya-QP8Pv3GqLMhklAcivElzUS08tKmCpB4vW8s1uSj5pOsyQ&#8221; style=”-webkit-transform: rotate(0.00rad); border: none; transform: rotate(0.00rad);” width=”602″ />
    Clients making a large number of DB requests will have to setup a connection with a remote PGBouncer for each request. This is more expensive, than running PgBouncer locally (so the application connects to pgbouncer locally) and pgBouncer maintains a pool of connections with the remote PG server.
    So, IMO, PGBouncer -> HAProxy -> PGServer seems to be better than, HAProxy -> PGBouncer -> PGServer, especially when the PGBouncer is local to the client application.

    9.4 Replication with RepMgr and PgBouncer in Postgresql

    repmgr 3.0:-
                       repmgr is an open-source tool suite to  monitor replication, and perform administrative tasks such as failover or manual switchover operations.
    PgBouncer:-
                      PgBouncer is connection pooler for PostgreSQL databases and acts as gateway for clients to connect to the databases.pgBouncer allows for backend database configuration changes by just editing its configuration file and reloading the process.
    Related image
    Setup:-
    dbhost1: first database host running on 192.168.0.100
    dbhost2: second database host running on 192.168.0.101
    pgbouncer-host: host running pgbouncer on 192.168.0.102
    Instructions:-
    1.Install dependencies.
    2.Configure ssh access between db nodes.
    3.Configure databases.
    4.Configure PostgreSQL replication.
    5.Configure replication manager.
    6.Clone slave.
    7.Configure pgBouncer.
    8.Failover.
    1) Install Dependences (dbhost1, dbhost2, pgbouncer-host)
    The default ubuntu 14.04 installation does not include PostgreSQL 9.4 so we will need to add the repository:
    Create the file /etc/apt/sources.list.d/pgdg.list, and add a line for the repository
    Import the repository signing key, and update the package lists
    wget –quiet -O – https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add –  
    sudo apt-get update  
    Install the dependencies for database hosts (dbhost1,dbhost2)
    sudo apt-get install postgresql-9.4  
    sudo apt-get install postgresql-9.4-repmgr  
    sudo apt-get install postgresql-client-9.4  
    Install the dependencies for pgBouncer host (pgbouncer-host)
    sudo apt-get install postgresql-client-9.4  
    sudo apt-get install pgbouncer  
    2) Configure password-less ssh between dbhosts (dbhost1,dbhost2)
    by default postgreSQL creates a postgres local linux user which is used to do all database management operations. To create slaves or recreate a master after failure repmgr requires that the user running the commands have password-less ssh access to the source host. Since the postgres user is the user which has permissions on the postrgres directories, we will configure that user for ssh.
    switch to the user
    $ sudo su postgres
    create ssh key pair
    $ ssh-keygen
    copy the publich key id_rsa.pub contents and add it to the authorized_keys file in the other host’s postgres user ssh directory. For more details see https://github.com/2ndQuadrant/repmgr/blob/master/SSH-RSYNC.md
    test that you can ssh between servers before going to the next step.
    3) Configure Databases and users (dbhost1)
    We will need two databases one will be used by repmgr and the other will be our application database. All commands are to be executed using the postgres user.
    for repmgr database
    $ createuser -s repmgr
    $ createdb repmgr -O repmgr
    for our application database
    $ createuser test_user
    $ createdb test_db -O test_user
    since this user will be used by the applications (clients) we will set a password for it. Run the following command from inside a psql shell with the default postgres user.
    postgres=# ALTER USER test_user WITH PASSWORD ”;
    4) Configure PostgreSQL replication (dbhost1)
    We will now configure the master db host (dbhost1) for replication. This is acheived by editing the postgresql.conf file and updating the follwing items
    listen_addresses=’*’  
    wal_level = ‘hot_standby’  
    archive_mode = on  
    archive_command = ‘cd .’  
    max_wal_senders = 10  
    max_replication_slots = 1  
    hot_standby = on  
    shared_preload_libraries = ‘repmgr_funcs’  
    We also need to configure the authentication configuration (pg_hba.conf) to allow repmgr to access replication database and also allow password based access for our application database (test_db). We will add the following lines:
    host    repmgr          repmgr     192.168.0.0/0     trust  
    host    replication     repmgr     192.168.0.0/0     trust  
    host    test_db         test_user  192.168.0.0/0     md5  
    restart the postgres service after the changes are done
    $ sudo service postgresql restart
    5) Configure Replication Manager (dbhost1,dbhost2)
    We will need to create a repmgr.conf on each database host. The file could be stored anywhere but we will use /etc/repmgr/repmgr.conf.
    The file contents for the dbhost1 should be
    cluster=test  
    node=1  
    node_name=node1  
    use_replication_slots=1  
    conninfo=’host=dbhost1 user=repmgr dbname=repmgr’  
    pg_bindir=/usr/lib/postgresql/9.4/bin  
    We are now ready to join our master to the replication topology. Using the postgres user run the command
    $ repmgr -f /etc/repmgr/repmgr.conf master register
    and similarly for dbhost2 we need to create the file /etc/repmgr/repmgr.conf with the contents
    cluster=test  
    node=2  
    node_name=node2  
    use_replication_slots=1  
    conninfo=’host=dbhost2 user=repmgr dbname=repmgr’  
    pg_bindir=/usr/lib/postgresql/9.4/bin  
    6) Clone Slave (dbhost2)
    ssh into our slave server dbhost
    stop the postgresql service
    $ sudo service postgresql stop
    run the following command using the postgres user to clone the master
    $ repmgr -f /etc/repmgr/repmgr.conf –force –rsync-only -h dbhost1 -d repmgr -U repmgr –verbose
    standby clone
    start the postgresql server
    $ sudo service postgresql start
    register the slave as a standby
    $ repmgr -f /etc/repmgr/repmgr.conf –force standby register
    if all the previous steps ran successfully we should be able to see the state of our replication cluster using the command
    $ repmgr -f /etc/repmgr/repmgr.conf cluster show
    and it should return an output like this
     Role      | Connection String  
    * master  | host=dbhost1 user=repmgr dbname=repmgr
     standby | host=dbhost2 user=repmgr dbname=repmgr
    7) Configure pgBouncer (pgbouncer-host)
    All pgBouncer needs is a configuration file pgbouncer.ini which can be stored anywhere in the system. The contents of the file should be
    [databases]
    test_db = host=dbhost1 port=5432 dbname=test_db
    [pgbouncer]
    listen_port = 6543  
    listen_addr = 127.0.0.1  
    auth_type = md5  
    auth_file = users.txt  
    logfile = pgbouncer.log  
    pidfile = pgbouncer.pid  
    admin_users = test_user  
    The configuration above instructs pgBouncer to listen for connections on port 6543 and relay connections to the database test_db to our current master dbhost1 on port 5432.
    Because pgBouncer uses md5 based authentication with postgreSQL 8.0 style files we need to create a users.txt file which will have the user credentials for the database users. A sample of this file is below
    “test_user” “somepassword”
    To run pgBouncer we run the command
    $ pgbouncer -d pgbouncer.ini -R
    Congratulations!! you now have a running master-slave replicated postgreSQL setup.
    8) Failover (dbhost2,pgbouncer-host)
    In case of failover due to the master node failure. All that is needed to do is first make sure the master is truely down. Preferably, stopping the postgresql service if it is running. Then we just need to promote our slave to become the new master using the command:
    $ repmgr -f /etc/repmgr/repmgr.conf standby promote
    and then change the pgBouncer configuration to point to dbhost2 and re run the command
    $ pgbouncer -d pgbouncer.ini -R
    furher know about pgbouncer

    Setup of Hot Standby Replication, Failover, Rebuilding the master node,Monitor the standby,Check the delay manually and Check the progress of streaming replication,Recoverability of PostgreSQL,

    Prerequisites:
    1. Same version of the PostgreSQL Database must be installed on both servers.
    2. Configure the Password-less ssh authentication to use the “postgres” user.
    3. Production server must operate in WAL archiving enabled mode by setting archive_mode and archive_command in postgresql.conf file.
    4. Production and stand by servers always should have connectivity between them to transfer the archived WAL files from production.
    5. Set up your standby host’s environment and directory structure exactly the same as your primary.
    Step 1: Installation
       The first step would be to install PPAS (same version) on two different servers (running same OS/OS version). For putting up this blog, I tried these steps on 2 servers, each running Postgres Plus Advanced Server 9.2.1 on RHEL 6.1. For the sake of simplicity, instead of overwriting the default DB cluster/instance on secondary db server, I created a different db instance
    Primary DB Server:
    Name:dbserver1
    IP Address: 192.168.160.147
    Data directory: /opt/PostgresPlus/9.2AS/data
    Port: 5444
    Stand by DB Server
    Name:dbserver2
    IP Address: 192.168.160.150
    Data Directory: /opt/PostgresPlus/9.2AS/data2
    Port: 5222
    Step 2: Parameter Setup
    Change the below parameter:
     wal_level = hot_standby
     max_wal_senders = 3
     wal_keep_segments = 128 (optional/depending on load)
     replication_timeout = 5 sec (optional)
     hot_standby = on (required/effective only for hot stand by server)
    To ease the pain of back-up-restore, failover-failback I created two postgresql.conf backups on primary server (under data directory)
    1.   postgresql.conf.dbserver1
    2.    postgresql.conf.dbserver2
    Both the files are same, with only difference in the value for port (dbserver1=5444 and dbserver2=5222).
    The value for replication related parameters and hot_standby same in both the files. As the replication parameters are not going to cause any difference on secondary server unless you use cascaded replication and hot_standby value is ignored on Primary Server.
    Add a new value in pg_hba.conf
    host  replication   all   192.168.160.0/24      trust
    Step 3: Create recovery.conf
    Create two dummy recovery.conf files namely recovery.conf.dbserver1_is_master and recovery.conf.dbserver2_is_master.
    recovery.conf.dbserver1_is_master
    standby_mode = ‘on’
    primary_conninfo = ‘host=192.168.160.147 port=5444 user=enterprisedb password=ashnik’
    restore_command = ‘scp [email protected]:/mnt/arch/%f %p’
                # optional
               # needs archiving command to be enabled on primary
    recovery_target_timeline = ‘latest’      #optional
    trigger_file = ‘/opt/PostgresPlus/9.2AS/data/recover.trigger’
    recovery.conf.dbserver2_is_master
    standby_mode = ‘on’
    primary_conninfo = ‘host=192.168.160.150 port=5222 user=enterprisedb password=ashnik’
    restore_command = ‘scp [email protected]:/mnt/arch/%f %p’
            # optional
           # needs archiving command to be enabled on primary
    recovery_target_timeline = ‘latest’      #optional
    trigger_file = ‘/opt/PostgresPlus/9.2AS/data/recover.trigger’
    For the ease of management, you should keep the passwords same on both the servers. Here the password is used in plain text, but one can always use password files or md5 password.
    Step 4: Backup
    You need to take a cold backup of your primary server and restore it on secondary server. With rest of the files in the backup you will also get below files:
      postgresql.conf.dbserver1
      postgresql.conf.dbserver2
      recovery.conf.dbserver1_is_master
      recovery.conf.dbserver2_is_master
    Step 5: Setup of Standby Server
    Now copy the postgresql.conf.dbserver2 as postgresql.conf on dbserver2.
    Then copy the recover.conf.dbserver1_is_master as recovery.conf
              Now start the db cluster on secondary server first to make sure it is not going to lose any transaction. You will see an error in log complaining about non-availability of primary server, which can be ignored at this point. Confirm that secondary database instance is up and running and you can connect and fire read queries on the database.
    Step 6: Start-up the Primary Server
    Once sure, start the primary server.
    Check the primary and secondary server. Connect to each of them to confirm the connectivity, role and read/write accessibility.
    You can use “pg_ctl status” OS command and “pg_is_in_recovery()” SQL function to confirm the status of each database.
    Now let’s try to do a failover.
    Step 7: Create a Failure
    So for that, first there has to be a failure. On the primary site, fire a command
           kill -9
    Now connect to the secondary database, check if you can do write operation or simply check if it’s still in recovery state by pg_is_in_recovery() function. So do you still get “t” as the output of the above command? Are you still not able to write operations on the secondary database?
    Well don’t be surprised, actually PostgreSQL does not do failover on its own. There have always been two school of thoughts about any failover mechanism, one which says “auto failover” and other which says “manual failover”. If given a choice, I always prefer a manual failover for DR site. This ensures that my DR site does not mistakenly assume a network failover as a disaster or a failure. Moreover, the DR site is not just database, one also needs to make sure the application and client connectivity is modified accordingly. Hence it’s best to keep it manual. Auto-failover is useful if you want to do hot-streaming replication for HA (which I would discuss in a later post in this series).
    Step 8: Switch Secondary/slave database to become read/write
    On the secondary server create the trigger file (as specified in recovery.conf)
    touch opt/PostgresPlus/9.2AS/data2/recovery.trigger
    Now connect again and check if your current live sessions/new sessions to the secondary database are able to do write operations to the database. If yes, then great! You just completed a successful failover.
    Step 9: Rebuilding the Master Database
    Now, we need to re-build the master (assuming that the master database server is up).
    First of all clean up the database cluster on old Primary server:
               rm -Rf /opt/PostgresPlus/9.2AS/data
    Now, take a backup from new primary (dbserver2) to rebuild the master:
    pg_basebackup -D /opt/PostgresPlus/9.2AS/data -h 192.168.160.150 -W -p 5222 -Fp –xlog-method=stream
    Once the backup is complete, copy the postgresql.conf.dbserver1 as postgresql.conf and then remove recovery.done and copy the recover.conf.dbserver2_is_master as recovery.conf.
    Step 10: Start the Primary DB as new slave
    Now start the db cluster on master node. Once the start-up is successful, make sure everything is alright by connecting to the database and firing the below command:
    SELECT pg_is_in_recovery(); #expected output is “t”
    Then fire the below command on dbserver1 and dbserver2 and both should be same:
    SELECT txid_current_snapshot();
    Carefully inspect the log files on secondary node (dbserver1) to confirm the recovery is in progress and there is no issues in the replication.
    Step 11:Monitoring
    $ psql ­c “SELECT pg_current_xlog_location()” ­h192.168.160.147
    (primary host)
    pg_current_xlog_location
    0/2000000
    (1 row)
    $ psql ­c “select pg_last_xlog_receive_location()” ­h192.168.160.150
    (standby host)
    pg_last_xlog_receive_location
    0/2000000
    (1 row)
    $ psql ­c “select pg_last_xlog_replay_location()” ­h192.168.160.150
    (standby host)
    pg_last_xlog_replay_location
    0/2000000
    (1 row)
    Step 12: Other ways to check streaming replication: the easiest way is “select now()-pg_last_xact_replay_timestamp();” at slave side. pg_last_xact_replay_timestamp() function gives time stamp of last transaction replayed during recovery, time at which the commit or abort WAL record for that transaction was generated on the primary. If no transactions have been replayed during recovery, this function returns NULL. Otherwise, if recovery is still in progress this will increase monotonically. If recovery has completed then this value will remain static at the value of the last transaction applied during that recovery. When the server has been started normally without recovery the function returns NULL. You can try with some operation on Master and then check the fuction output.
    If you want to check the delay manually, then go for below steps:
    Step 1:Need to create table on Primary using below command.
    create table stream_delay (tstamp timestamp without time zone );
    insert into stream_delay select now();
    Step 2: schedule the below command on primary to execute every minute on cronjob.
    update stream_delay set tstamp=’now()’;
    step 3: verify the delay on slave by selecting the “stream_delay” table.
                  It should show the last time that was updated in primary. Difference between this timestamp and current timestamp of slave server shows the time delay between Primary and slave.
    You can also check the progress of streaming replication by using ps command.#The displayed LSNs indicate the byte position that the standby server has written up to in the xlogs.
    [primary] $ ps ­ef | grep sender
    postgres  6879  6831  0 10:31 ?        00:00:00 postgres: wal sender
    process postgres 127.0.0.1(44663) streaming 0/2000000
    [standby] $ ps ­ef | grep receiver
    postgres  6878  6872  1 10:31 ? receiver process   streaming 0/2000000

    Postgresql Hot Standby Installation And Test The Monitor Of Standby

    1- Keygen Generate And Copy
    [postgres@pg01 ~] ssh-keygen -t rsa
    [postgres@pg02 ~] ssh-keygen -t rsa
    [postgres@pg01 ~] ssh-copy-id -i .ssh/id_rsa.pub postgres@pg02
    [postgres@pg02 ~] ssh-copy-id -i .ssh/id_rsa.pub postgres@pg01
    2- Create Streaming Replication User on Primary DB
    psql# createuser -U postgres -p 5432 repuser -P -c 10 –replication
    3- Primary DB pg_hba.conf configuration
    [root@pg01 ~] vi /pgdata/data/postgresql.conf
    Add below line
    host replication repuser 192.168.10.12/32 md5
    [root@pg01 ~] systemctl reload postgresql.service
    4- Create Archive Directory on Hot Standby Server
    [root@pg02 ~] mkdir -p /pgdata/ARCHIVELOG
    5- Postgresql.conf configuration on Primary DB
    [root@pg01 ~] vi /pgdata/data/postgresql.conf
    Parameters change like below
    listen_addresses = ‘192.168.10.10’
    wal_level = hot_standby # minimal, archive, hot_standby, or logical
    archive_mode = on # enables archiving; off, on, or always
    archive_command = ‘rsync -a %p [email protected]:/pgdata/ARCHIVELOG/%f’ # command to use to archive a logfile segment
    max_wal_senders = 3 # max number of walsender processes
    wal_keep_segments = 1000 # in logfile segments, 16MB each; 0 disables
    max_replication_slots = 4 # max number of replication slots(change requires restart)
    [root@pg01 ~] systemctl restart postgresql.service
    6- Base Backup
    You can use pg_basebackup when primary db is start or stop. Stop status is more confident.
    [root@pg01 ~] systemctl stop postgresql.service
    /pgdata/data directory has to be empty on hot standby server. pg_basebackup command will fill up that directory.
    [root@pg02 ~] /usr/pgsql-9.5/bin/pg_basebackup -h 192.168.10.10 -D /pgdata/data -U repuser -p 5432 -v -P –xlog-method=stream
    7- Postgresql.conf configuration on Hot Standby Server
    Primary DB configuration has to be disabled with # mark.
    #wal_level = hot_standby # minimal, archive, hot_standby, or logical
    #archive_mode = on # enables archiving; off, on, or always
    #archive_command = ‘rsync -a %p [email protected]:/pgdata/ARCHIVELOG/%f’ # command to use to archive a logfile segment
    #max_wal_senders = 3 # max number of walsender processes
    #wal_keep_segments = 1000 # in logfile segments, 16MB each; 0 disables
    #max_replication_slots = 4
    listen_addresses = ‘*’
    hot_standby = on
    8- Create Recovery.conf file on Hot Standby Server
    [root@pg02 ~] vi /pgdata/data/recovery.conf
    restore_command = ‘cp /pgdata/ARCHIVELOG/%f %p’ # e.g. ‘cp /mnt/server/archivedir/%f %p’
    archive_cleanup_command = ‘/usr/pgsql-9.5/bin/pg_archivecleanup /pgdata/ARCHIVELOG/%r’
    standby_mode = on
    primary_conninfo = ‘host=192.168.10.10 port=5432 user=repuser password=repuserpass’
    trigger_file = ‘/pgdata/data/failover.uygula’
    If you want to delay to apply committed values. You can use recovery_min_apply_delay parameter in recovery.conf like below.
    recovery_min_apply_delay = 5min
    This parameter provide 5 minutes delay. When you commit a transaction at primary side, hot standby will apply this transaction 5 minutes later.
    9- Start Hot Standby PostgreSQL Service
    [root@pg02 ~] systemctl start postgresql.service
    10- Test
    Primary DB
    Replications list
    psql# select * from pg_stat_replication ;
    Sender process check
    ps -ef | grep sender
    postgres 18388 18298 0 17:04 ? 00:00:00 postgres: wal sender process repuser 192.168.10.12(33700) streaming 0/9000348
    psql# create table test (name text);
    psql# insert into test values(‘nijam’);
    Hot Standby DB
    Recovery mode check
    psql# select pg_is_in_recovery();
    Receiver process check
    ps -ef | grep receiver
    postgres 20936 20919 0 17:04 ? 00:00:00 postgres: wal receiver process streaming 0/9000268
    psql# select * from test;
    name
    ———–
    nijam

    Determining replication monitoring in postgreSql

    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.

    Check The Replication Delay -4 In Postgresql

    You can get the delay in bytes from the master side quite easily using pg_xlog_location_diff to compare the master’s pg_current_xlog_insert_location with the replay_location for that backend’s pg_stat_replication entry.
    postgres=# SELECT
     pg_last_xlog_receive_location() receive,
     pg_last_xlog_replay_location() replay,
     (
      extract(epoch FROM now()) –
      extract(epoch FROM pg_last_xact_replay_timestamp())
     )::int lag;
     receive   |   replay   |  lag
    ————+————+——-
    1/AB861728 | 1/AB861728 | 2027
    the lag is only only important when receive is different than replay. execute the query on the slave

    Time-Based Replication Monitoring In The Hot_Standby_Delay

    This was something that had been a long-standing item on my personal TODO list, and happened to scratch the itch of a couple of clients at the time.
    Previously it would only take an integer representing how many bytes of WAL data the master could be ahead of a replica before the threshold is crossed:
    check_hot_standby_delay –dbhost=master,replica1 –critical=16777594
    This is certainly useful for, say, keeping an eye on whether you’re getting close to running over your wal_keep_segments value. Of course it can also be used to indicate whether the replica is still processing WAL, or has become stuck for some reason. But for the (arguably more common) problem of determining whether a replica is falling too far behind determining what byte thresholds to use, beyond simply guessing, isn’t easy to figure out.
    Postgres 9.1 introduced a handy function to help solve this problem: pg_last_xact_replay_timestamp(). It measures a slightly different thing than the pg_last_xlog_* functions the action previously used. And it’s for that reason that the action now has a more complex format for its thresholds:
    check_hot_standby_delay –dbhost=master,replica1 –critical=”16777594 and 5 min”
    For backward compatibility, of course, it’ll still take an integer and work the same as it did before. Or alternatively if you only want to watch the chronological lag, you could even give it just a time interval, ‘5 min’, and the threshold only takes the transaction replay timestamp into account. But if you specify both, as above, then both conditions must be met before the threshold activates.

    Monitoring Streaming Replication with script also in postgreSql

    One of the easiest ways to monitor slave lag when using streaming replication is to turn hot standby on your slave and use pg_last_xact_replay_timestamp() and/or the other recovery information functions. Here’s an example query to run on the slave systems to get the number of seconds behind it is:
    SELECT extract(epoch from now() – pg_last_xact_replay_timestamp()) AS slave_lag
    The issue with this query is that while your slave(s) may be 100% caught up, the time interval being returned is always increasing until new write activity occurs on the master that the slave can replay. This can cause your monitoring to give false positives that your slave is falling behind if you have things set up to ensure your slaves are no more than a few minutes behind. A side affect of this monitoring query can also give you an indication that writes to your master have stopped for some reason.
    One of our clients has a smaller sized database that doesn’t get quite as much write traffic as our typical clients do. But it still has failover slaves and still needs to be monitored just like our other larger clients to ensure it doesn’t fall too far behind. So, my coworker introduced me to the pg_stat_replication view that was added in PostgreSQL 9.1. Querying this from the master returns information about streaming replication slaves connected to it.
    postgres=# select * from pg_stat_replication;
    -[ RECORD 1 ]—-+——————————
    pid              | 16649
    usesysid         | 16388
    usename          | replication
    application_name | walreceiver
    client_addr      | xxx.xxx.xxx.xxx
    client_hostname  | db1-prod-ca
    client_port      | 58085
    backend_start    | 2013-10-29 19:57:51.48142+00
    state            | streaming
    sent_location    | 147/11000000
    write_location   | 147/11000000
    flush_location   | 147/11000000
    replay_location  | 147/11000000
    sync_priority    | 0
    sync_state       | async
    -[ RECORD 2 ]—-+——————————
    pid              | 7999
    usesysid         | 16388
    usename          | replication
    application_name | walreceiver
    client_addr      | yyy.yyy.yyy.yyy
    client_hostname  | db2-prod
    client_port      | 54932
    backend_start    | 2013-10-29 15:32:47.256794+00
    state            | streaming
    sent_location    | 147/11000000
    write_location   | 147/11000000
    flush_location   | 147/11000000
    replay_location  | 147/11000000
    sync_priority    | 0
    sync_state       | async
    He also provided a handy query to get back a simple, easy to understand numeric value to indicate slave lag. The issue I ran into using the query is that this view uses pg_stat_activity as one of its sources. If you’re not a superuser, you’re not going to get any statistics on sessions that aren’t your own (and hopefully you’re not using a superuser role as the role for your monitoring solution). So, instead I made a function with SECURITY DEFINER set, made a superuser role the owner, and gave my monitoring role EXECUTE privileges on the function.
    CREATE OR REPLACE FUNCTION streaming_slave_check() RETURNS TABLE (client_hostname text, client_addr inet, byte_lag float)
    LANGUAGE SQL SECURITY DEFINER
    AS $$
      SELECT
          client_hostname,
          client_addr,
          sent_offset – (replay_offset – (sent_xlog – replay_xlog) * 255 * 16 ^ 6 ) AS byte_lag
      FROM (
          SELECT
              client_hostname,
              client_addr,
              (‘x’ || lpad(split_part(sent_location::text,   ‘/’, 1), 8, ‘0’))::bit(32)::bigint AS sent_xlog,
              (‘x’ || lpad(split_part(replay_location::text, ‘/’, 1), 8, ‘0’))::bit(32)::bigint AS replay_xlog,
              (‘x’ || lpad(split_part(sent_location::text,   ‘/’, 2), 8, ‘0’))::bit(32)::bigint AS sent_offset,
              (‘x’ || lpad(split_part(replay_location::text, ‘/’, 2), 8, ‘0’))::bit(32)::bigint AS replay_offset
          FROM pg_stat_replication
      ) AS s;
    $$;
    Running this query gives back a few handy columns that should be good enough for most monitoring tools. You can easily add more columns from pg_stat_replication or any other tables you need to join against for more info.
    postgres=# select * from streaming_slave_check();
    client_hostname |   client_addr   | byte_lag
    —————–+—————–+———-
    db1-prod-ca     | xxx.xxx.xxx.xxx |      160
    db2-prod        | yyy.yyy.yyy.yyy |      160
    UPDATE: If you’re running PostgreSQL 9.2+, there is a new, built-in function that avoids needing the above function all together and can just query pg_stat_replication directly.
    postgres=# SELECT client_hostname
      , client_addr
      , pg_xlog_location_diff(pg_stat_replication.sent_location, pg_stat_replication.replay_location) AS byte_lag
    FROM pg_stat_replication;
    client_hostname |  client_addr  | byte_lag
    —————–+—————+———-
    db1-prod-ca     | xxx.xxx.xx.xx |        0
    db2-prod        | yy.yyy.yyy.yy |        0
    Unfortunately, this function still requires superuser privileges to obtain all relevant data and most monitoring tools do not use a superuser role (I hope). So, in that case you do still need a SECURITY DEFINER function, but it can be a much much simpler one
    CREATE OR REPLACE FUNCTION streaming_slave_check() RETURNS TABLE (client_hostname text, client_addr inet, byte_lag numeric)
    LANGUAGE SQL SECURITY DEFINER
    AS $$
      SELECT client_hostname
      , client_addr
      , pg_xlog_location_diff(pg_stat_replication.sent_location, pg_stat_replication.replay_location) AS byte_lag
      FROM pg_stat_replication;
    $$;
    This can also be useful to monitor slave lag when you don’t have hot standby turned on for your slaves to allow read-only queries.
    Combining both of the replication monitoring solutions mentioned in this post should give you a much better overall picture of the status of your master/slave systems.

    monitor replication delay -four solution in postgreSql

    Looking at the documentation and all the blog posts about how to monitor
    replication delay I don’t think there is one good and most importantly safe
    solution which works all the time.
    Solution 1:
    I used to check replication delay/lag by running the following query on the
    slave:
    SELECT EXTRACT(EPOCH FROM (now() – pg_last_xact_replay_timestamp()))::INT;
    This query works great and it is a very good query to give you the lag in
    seconds. The problem is if the master is not active, it doesn’t mean a
    thing. So you need to first check if two servers are in sync and if they
    are, return 0.
    Solution 2:
    This can be achieved by comparing pg_last_xlog_receive_location()  and
    pg_last_xlog_replay_location() on the slave, and if they are the same it
    returns 0, otherwise it runs the above query again:
    SELECT
    CASE
    WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location() THEN 0
    ELSE EXTRACT (EPOCH FROM now() – pg_last_xact_replay_timestamp())::INTEGER
    END
    AS replication_lag;
    This query is all good, but the problem is that it is not safe. If for some
    reason the master stops sending transaction logs, this query will continue
    to return 0 and you will think the replication is working, when it is not.
    Solution 3:
    Master:
    SELECT pg_current_xlog_location();
    Slave:
    SELECT pg_last_xlog_receive_location();
    and by comparing these two values you could see if the servers are in sync.
    The problem yet again is that if streaming replication fails, both of these
    functions will continue to return same values and you could still end up
    thinking the replication is working. But also you need to query both the
    master and slave to be able to monitor this, which is not that easy on
    monitoring systems, and you still don’t have the information about the
    actual lag in seconds, so you would still need to run the first query.
    Solution 4:
    You could query pg_stat_replication on the master, compare sent_location
    and replay_location, and if they are the same, the replication is in sync.
    One more good thing about pg_stat_replication is that if streaming
    replication fails it will return an empty result, so you will know it
    failed. But the biggest problem with this system view is that only the postgres
    user can read it, so it’s not that monitoring friendly since you don’t want
    to give your monitoring system super user privileges, and you still don’t
    have the delay in seconds.
    I think the best one would be 2 combined
    with a check if the wal receiver process is running before running that
    query with something like:
    $ ps aux | egrep ‘wal\sreceiver’
    postgres  3858  0.0  0.0 2100112 3312 ?        Ss   19:35   0:01 postgres:
    wal receiver process   streaming 36/900A738
    This solution would only be run on the slave and it is pretty easy to setup.