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:

pgbouncer > postgresql” height=”567″ src=”https://lh4.googleusercontent.com/uFI6eBjkUnQiRyaen5e55VxV_KAwp_RG24X5ZBUGIERu8CkV6-e7bJInijZBRGqrE_RkKXvT6ekFUaysIcLX4_kuAlM6QZ2niPz_rfSICv9wHiJGcDnYO7k0RNq1iaX5nPw20kw” 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

haproxy > postgresql” height=”567″ src=”https://lh6.googleusercontent.com/UXp7Ti_yzlKwrAsW9yqaFfVlZ3J-Cq5e4DD9EnOESf2vVxDPBKw1NW-MaFwkM34s832FQbBGhQ4jKDJNCmqO443ya-QP8Pv3GqLMhklAcivElzUS08tKmCpB4vW8s1uSj5pOsyQ” 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.

learing abbout postgresql database and oracle database and cricket Entertainment

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.

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

deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main  

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

https://pgbouncer.github.io/config.html

learing abbout postgresql database and oracle database and cricket Entertainment

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 enterprisedb@192.168.160.150:/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 enterprisedb@192.168.160.150:/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

learing abbout postgresql database and oracle database and cricket Entertainment

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 postgres@192.168.10.12:/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 postgres@192.168.10.12:/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

learing abbout postgresql database and oracle database and cricket Entertainment

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.

learing abbout postgresql database and oracle database and cricket Entertainment

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

learing abbout postgresql database and oracle database and cricket Entertainment

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.

learing abbout postgresql database and oracle database and cricket Entertainment

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.

learing abbout postgresql database and oracle database and cricket Entertainment

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.

learing abbout postgresql database and oracle database and cricket Entertainment

Check for PostgreSQL replication delay/lag -part3

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.

learing abbout postgresql database and oracle database and cricket Entertainment