Transitioning Synchronous Standby to Asynchronous in EDB Postgres



In the world of solution architecture, where we as solution architects are developing configurations to meet specific customer needs, we often consider a great deal of interesting scenarios. One of such scenario my team at EnterpriseDB (EDB) encountered recently bears exploring further with this blog

For a business critical application, transaction durability is crucial. Therefore, we helped one customer recently to design an architecture for near zero data loss.

One of our customers wanted to use two synchronous replication mechanisms within the same data center with a high-speed network between the master and standby for data transfer. Also, the database administrator  wanted to have read consistency from the replicas.

In Postgres 9.6, we have synchronous_commit = remote_apply, which helps in getting read-balancing consistency.

In addition to these requirements, the customer wanted to layer another capability into architecture; where if one of his synchronous standby replicas is down, then write transactions should not be stopped/hung. Transactions on the master should go into a “waiting state” if all the synchronous replication standbys are down.

EDB Postgres Advanced Server 9.6 comes with all building blocks out of the box to include this kind of logic to handle the scenario.

To achieve this capability in EDB Postgres, the following steps can be used:

1  Create the pg_background extension. The following is a link which you can use to compile pg_background module:
Below is a snapshot:

[root@ca420ebfb299 pg_background]# make install
/bin/mkdir -p '/usr/edb/as9.6/lib'
/bin/mkdir -p '/usr/edb/as9.6/share/extension'
/bin/mkdir -p '/usr/edb/as9.6/share/extension'
/bin/install -c -m 755 '/usr/edb/as9.6/lib/'
/bin/install -c -m 644 .//pg_background.control '/usr/edb/as9.6/share/extension/'
/bin/install -c -m 644 .//pg_background--1.0.sql  '/usr/edb/as9.6/share/extension/'
[root@ca420ebfb299 pg_background]# psql
psql.bin (
Type "help" for help.

edb=# create extension pg_background;

2  Create the dbms_scheduler and pgagent extension in EDB Postgres as shown below:

edb=# CREATE EXTENSION dbms_scheduler ;
edb=# create extension pgagent;

3  Now, you can create the procedure which can do following:

  • Identify all synchronous standby and check the status of each named synchronous standby in pg_stat_replication
  • If named synchronous standby doesn’t exists in pg_stat_replication, then change the synchronous_standby_names parameter in such a way that it doesn’t lose the name of synchronous standbys, however can demote the named synchronous to asynchronous standby. For that, it’s recommended to use following string for synchronous_standby_names parameter:
2(standby1, standby2…)
  • After demoting the synchronous standby to asynchronous, send an e-mail to your DBAs group to notify them about demotion and your DBAs can take necessary steps. In EDB Postgres, we have a package UTL_SMTP, which can be used for sending e-mails. The following is an example of such a procedure:
    p_sender        VARCHAR2,
    p_recipient     VARCHAR2,
    p_subj          VARCHAR2,
    p_msg           VARCHAR2,
    p_mailhost      VARCHAR2
    v_conn          UTL_SMTP.CONNECTION;
    v_crlf          CONSTANT VARCHAR2(2) := CHR(13) || CHR(10);
    v_port          CONSTANT PLS_INTEGER := 25;
    v_conn := UTL_SMTP.OPEN_CONNECTION(p_mailhost,v_port);
        'Date: ' || TO_CHAR(SYSDATE,
        'Dy, DD Mon YYYY HH24:MI:SS') || v_crlf
        || 'From: ' || p_sender || v_crlf
        || 'To: ' || p_recipient || v_crlf
        || 'Subject: ' || p_subj || v_crlf
        || p_msg
        , 1, 32767));

For more information on the UTL_SMTP package in EDB Postgres, click here.

  • If none of the standbys are available, then maintain the setting of synchronous_standby_names as given below:

synchronous_standby_names = 1(standby1, standby2,) The above setting will cover the scenario, where write should be stopped or should be in hanging state in case all standbys are down.

  • If replication slots are getting used, then check the lag for replication slots and reset the replication slots, so that we are not overloading pg_xlog.

Note: If you are using synchronous standby with replication slots, it’s recommended to have the same name for synchronous standbys and replication slots.

4  The following is a link procedure which covers all points mentioned in step 3.

5  After creating the procedure, the user can schedule a job as given below:

EXEC sys.DBMS_SCHEDULER.create_program (
     'Job which can transition synchronous to asynchronous');  

    program_name         => 'synchronous_to_asynchrnous',
    argument_position    => 1,
    argument_name        => 'allowed_slots_lag',
    argument_type        => 'NUMERIC',
    default_value        => '1073741824');
    program_name         => 'synchronous_to_asynchrnous',
    argument_position    => 2,
    argument_name        => 'p_sender',
    argument_type        => 'TEXT',
    default_value        => '');

    program_name         => 'synchronous_to_asynchrnous',
    argument_position    => 3,
    argument_name        => 'p_recipient',
    argument_type        => 'TEXT',
    default_value        => '');

    schedule_name    => 'every_minute',  
    start_date       => now, 
    repeat_interval  => 'FREQ=MINUTELY;',  
    comments         => 'This schedule executes every minute');  

   DBMS_SCHEDULER.create_job (
   job_name       => 'job_synchronous_to_asynchrnous',
   program_name    => 'synchronous_to_asynchrnous',
   schedule_name   => 'every_minute',
   comments        => 'Job which can transition synchronous to asynchronous');

Following is a snapshot of manual execution of job:

edb=# exec DBMS_SCHEDULER.RUN_JOB('job_synchronous_to_asynchrnous', TRUE);
INFO: synchronos_standby_names => 2(standby1,standby2)
INFO: standby_name => {2,standby1,standby2}
INFO: old standby count => 2
INFO: synchronous_standby_count => 2

EDB-SPL Procedure successfully completed

For more information on the DBMS_SCHEDULER package in EDB Postgres, click here.

pg_xlog_location_diff function for PostgreSQL/PPAS

In PostgreSQL 9.2, community has added a function pg_xlog_location_diff(), which is very useful for finding the difference between two xlog location in bytes and also useful for monitoring replication. Detail of this function is given in following link:

However this function is not available for users of PostgreSQL/PPAS 9.0/9.1 users. So, I thought to write same function plpgsql so, that users can take benefit of same in 9.0/9.1.

Before using formula and developing function, lets understand what is xlog and offset.
Let’s consider user has used function pg_current_xlog_location() function and he gets following information:

worktest=# select pg_current_xlog_location();
(1 row)

In above, first field before forward slash is the hexadecimal value of logical xlog file and second field i.e. D1012B80 is hexadecimal offset inside the logical xlogfile.

So, whenerver user sees information for xlog location, he gets xlog information in following format:

(hexadecimal) xlog/ (hexadecimal) offset

To calculate the difference between two xlog location, user can use following formula, which gives difference in hexadecimal:

(FF000000 * xlog + offset) - (FF000000 * xlog + offset)

where FF000000 is max value of offset i.e In PostgreSQL/PPAS offset value can go from 00000000 to FF000000 and if we convert that in decimal, it gives maximum 4278190080 bytes (4080MB)

Based on above formula, following is plpgsql function which can be use to get the difference:

CREATE OR REPLACE FUNCTION pg_xlog_location_diff_sql( text, text)
 RETURNS numeric
 LANGUAGE plpgsql
       offset1 text;
       offset2 text;
       xlog1 text;
       xlog2 text;
       SQL text;
       diff text;
       /* Extract the Offset and xlog from input in
          offset and xlog variables */
       /* Prepare SQL query for calculation based on following formula
         (FF000000 * xlog + offset) - (FF000000 * xlog + offset)
         which gives value in hexadecimal. Since, hexadecimal calculation is cumbersome
         so convert into decimal and then calculate the difference */
       SQL='SELECT (x'''||'FF000000'||'''::bigint * x'''||xlog1||'''::bigint 
                                +  x'''||offset1||'''::bigint)'||' 
                   (x'''||'FF000000'||'''::bigint * x'''||xlog2||'''::bigint 
                                +  x'''||offset2||'''::bigint)';
       EXECUTE SQL into diff;
       /* Return the value in numeric by explicit casting  */
       RETURN diff::numeric;

Usage example is given below:

worktest=# select pg_xlog_location_diff_sql(pg_current_xlog_location(),'1/D009F578');
(1 row)

I hope this will help PostgreSQL/PPAS 9.0/9.1 users.

Asynchronous/Synchronous Streaming Replication in PostgreSQL 9.1

Since, PostgreSQL 9.1 has already been released and there are lot of new features added in it, so, I thought to blog about each features.

Today, I am discussing about Synchronous and Asynchronous Replication supported in PostgreSQL 9.1.

For Asynchronous Replication, user can use following method:
1. Change Following Parameters in postgresql.conf file of Primary Database:

archive_command = cp -i %p /Users/postgres/archive/%f
archive_mode = on
max_wal_senders = 3 # Maximum 'wal_senders', processes responsible for managing a connection with a standby server
wal_keep_segments = # How many WAL segments (=files) should be kept on the primary, whatever may happen (you won't have to copy them manually on the standby if the standby gets too far behind)
wal_level = hot_standby

2. Make Following changes in pg_hba.conf

host    replication     postgres        [Ipv4 address of Standby Server]/32  trust
host    replication     postgres        [Ipv4 address of Master Server]/32   trust

3. Restart the PostgreSQL Cluster using pg_ctl as given below:

pg_ctl -D [data directory path] restart -m fast

4. Take base Backup of PostgreSQL(Primary) using pg_basebackup command on Standby(This is a new command which has been introduced in PostgreSQL 9.1)

pg_basebackup -D /Users/vibhor/testbackup -v -Fp -l Testbackup -h -U postgres 

Sytax of pg_basebackup is given below:

pg_basebackup -D <local directory> -v -Fp -l [backup label] -h [PG server hostname/ip] -U superuser

For more options, user can use following command:

pg_basebackup --help

5. Create recovery.conf file and include following parameters:

restore_command = 'cp -i /Users/postgres/archive/%f %p'		# e.g. 'cp /mnt/server/archivedir/%f %p'
standby_mode = on
primary_conninfo = 'host=localhost port=5432'         	# e.g. 'host=localhost port=5432'
trigger_file = '/tmp/makeprimary.trigger'

6. Change following parameters in Postgresql.conf file of Standby:

hot_standby=on/off # If you want to use Hot Standby at the same time.

7. Then Start the Standby using following command:

pg_ctl -D [standby directory] start.

To verify the about asynchronous replication, use can use following command on primary:

postgres=# select procpid,usesysid, usename, application_name, client_addr, state, sent_location,write_location,sync_state from pg_stat_replication;
 procpid | usesysid | usename  | application_name | client_addr |   state   | sent_location | write_location | sync_state 
    3661 |       10 | postgres | walreceiver      | ::1         | streaming | 0/D0001D0     | 0/D0001D0      | async
(1 row)

To switch Asynchronous replication to Synchronous Replication,use following steps:

1. Change following parameter in postgresql.conf on Primary Server:

 synchronous_standby_names = 'sync_replication'

2. Reload the above changes on primary using following command:

pg_ctl -D [primary data directory] reload

3. Change following parameter in recovery.conf file on Standby:

primary_conninfo = 'host=localhost port=5432 application_name=sync_replication'
4. Restart the standby using following command:
pg_ctl -D [standby directory] restart -m fast

To verify the switch from Asynchronous to Synchronous, user can use following command on primary:

postgres=# select procpid,usesysid, usename, application_name, client_addr, state, sent_location,write_location,sync_state from pg_stat_replication;
 procpid | usesysid | usename  | application_name | client_addr |   state   | sent_location | write_location | sync_state 
    3830 |       10 | postgres | sync_replication | ::1         | streaming | 0/E000078     | 0/E000078      | sync
(1 row)

PostgreSQL 9.1 also, gives flexibility of controlling Synchronous Replication session wise. So suppose if you want one transaction/session to be replicated as asynchronous, then user can set following parameter in his session on primary:

In Transaction:
  set synchronous_commit=false;

In Session:

set synchronous_commit=false;

Physical Standby Vs Hot Standby

Some thoughts always come in mind about standby terminologies. Once, Someone has asked question about Physical Standby of Oracle10g.
Is Oracle10g Physical Standby a Hot Standby Or WarmStandby?
Till Oracle 10g, Physical Standby of Oracle is a standby which has two mode: 1. Managed Recovery Mode 2. Read Only Mode. It cannot be in both mode at same time.
If the standby is in recovery mode then, it’s a Warm Standby and when its read only mode then, then it’s lagging from Primary and would be able to response SELECT queries.
Either way, till Oracle 10g physical standby was not meeting the requirement of Hot Standby. It was playing the role of Warm Standby.
However, from Oracle 11g, Physical Standby can be Recovery mode and read only mode, both at the same time, which now fulfill the definition of Hot Standby. With the complexity of managing the Standby, licensing thing comes in Picture.
PG9.0 onwards, PostgreSQL now has Cold Standby, Warm Standby and Hot Standby, with zero cost. Community is making the PostgreSQL Technology more advance with new features.

Hot Standby in PostgreSQL 9.0:

As per definition of Hot Standby, its a method of redundancy in which primay and secondary (Backup Server) runs simultaneously. The data is mirrored to secondary so that both should contain identical data in real time. With this user, would be able to execute the Query against Database while secondary is in archive recovery mode.

This is what introduce in PG9.0. In-built Hot Standby.

PostgreSQL Community always try to make the things simpler as much as possible, same they have proven in Hot Standby Implementation.

Following are the steps of configuring Hot Standby:

1. Make sure following parameters are set in Configuration file, postgresql.conf of Primary:
wal_level = ‘hot_standby’archive_mode = onarchive_command = ‘cp %p /Library/PostgreSQL/9.0/data/archivelog/%f’

2. After setting the above parameters, Now take the hot backup of PG9.0 Instance. Steps of Hot Backup is simple:
a) Execute following command in Primary:

select pg_start_backup(‘Hot Standby Backup’);

b) Take the file system backup of PG 9.0 Data directory, as given below:

cp -r $PRIMARY/* $HTStandby/

c). Execute following command to stop the Hot Backup:

select pg_stop_backup();

3. After taking the Hot Backup and restoring it desired location, set following parameter in postgresql.conf file of Hot Standby:

hot_standby = on

4. Create a recovery.conf file in Hot Standby restore location and set the following parameters:

standby_mode = 'on'
restore_command = 'cp /Library/PostgreSQL/9.0/data/archivelog/%f %p'

5. Clean the pg_xlog and pg_xlog/archive_status directories of Hot Standby Data directory.
6. Remove the file from Hot Standby Data Directory.
7. Now start the Instance using following command:

pg_ctl -D $HTStandby start

Now, hot standby is up and running.

Following are few snapshots (Primary Port is 5432 and Hot Standby Port 5433:

1. Here I have inserted one value in table “test” and created a new table test2 with some records:

2. Now on Standby Following is a snapshot: