efm_sql_command: Postgres database extension for EFM (EDB Failover Manager 2.1.x)

 

EDB Failover manager (EFM) continues to gain popularity among Postgres DBAs. EDB customers are using this tool to build a highly available EDB Postgres platform.

EFM’s primarily role is to monitor Postgres Clusters, notify the DBA of any failed clusters and automatically promote the standby cluster to function as a master.

Here are some of the high-level features of EFM:

  1. Automatic failover
  2. Switchover to a standby (s) close to master in terms of transaction xlog
  3. Set priority of standby (s)
  4. Customize notifications/alerts
  5. Transfer VIP to the new master with failover (provided, the master has been configured with VIP)

Utility “efm” is used by the DBAs to gain control of these actions. However, the need to connect to EFM cluster servers/nodes using ssh continues to not optimize the DBA experience.

Keeping this pain point in mind, we have developed “efm_sql_command” extension for the EFM 2.1.x utility command using the inherent Postgres interface. DBAs no longer need to ssh to EFM node to allow/disallow new/old nodes for EFM clusters. Additionally, through the Postgres interface itself, EFM can also be integrated with other monitoring systems like EDB Postgres Enterprise Manager, Nagios, etc.

efm_sql_command extension provides following functions:

  • efm_sql_command.efm_allow_node(‘ip address’);

This functions takes IP address as an argument and notifies EFM agents to allow the specific IP address to be part of a EFM cluster. It returns status of EFM command in 0/1. 0 means successful and 1 means failed.DBAs now can use SQL interface to notify EFM for allowing the new standby (which they are still building) without ssh to one of the EFM nodes.

  • efm_sql_command.efm_disallow_node(‘ip address’);

Similar to efm_allow_node, this function notifies to EFM agents to disallow the IP address from EFM cluster. Function report the status of the command in 0 and 1, where 0 means successfully notified and 1 means failed.

  • efm_sql_command.efm_failover();

Function notifies the EFM cluster agents to perform manual failover of master to closest standby. The function returns 0/1 status, where 0 means successfully notified EFM cluster agents and 1 means failed to notify agents.

  • efm_sql_command.efm_resume_monitoring();

If an agent is not monitoring the local EDB Postgres database, this function makes EFM agent start monitoring the local EDB Postgres database.

  • efm_sql_command.efm_set_priority(‘ip address’, ‘priority’);

Set failover priority for standby and return the status of the command in 0/1, where 0 means success and 1 means failed.

  • efm_sql_command.efm_switchover();

This function performs the switchover to closest standby of master and reconfigures the master as a new standby.

  • efm_sql_command.efm_local_properties

This a view using which DBA can view the efm properties.

  • efm_sql_command.efm_nodes_details:

This view provides the details of each node in EFM cluster. A user can use this for other the purpose. For example checking the status of standby(s), How far are the standbys from a master? etc.

  • efm_sql_command.efm_cluster_status

This function takes following arguments as text:
‘text’: To print the status of EFM cluster in TEXT
‘json’: To print the status of EFM cluster in JSON format.

This extension also gives following GUC, which DBAs can set at cluster/database/user level

ALTER SYSTEM SET efm.cluster_name TO 'clustername';
ALTER DATABASE  SET efm.cluster_name TO 'clustername';
ALTER USER  SET efm.cluster_name TO 'clustername';

Following are some snapshots of efm_sql_command’s functions

CREATE EXTENSION efm_sql_command;
edb=# select efm_extension.efm_cluster_status('text');
INFO: efm command is available
efm_cluster_status
------------------------------------------------------------------------
Cluster Status: efm
VIP:
 
Agent Type Address Agent DB Info
--------------------------------------------------------------
Idle 172.17.0.2 UP UNKNOWN
 
Allowed node host list:
172.17.0.2
 
Membership coordinator: 172.17.0.2
 
Standby priority host list:
(List is empty.)
 
Promote Status:
 
 
Idle Node Status (idle nodes ignored in XLog location comparisons):
 
Address XLog Loc Info
--------------------------------------------------------------
172.17.0.2 0/35BFC10 DB is not in recovery.
(23 rows)
edb=# select jsonb_pretty(efm_extension.efm_cluster_status('json')::jsonb);
INFO:  efm command is available 
                         jsonb_pretty                         
--------------------------------------------------------------
 {                                                           +
     "VIP": "",                                              +
     "nodes": {                                              +
         "172.17.0.2": {                                     +
             "db": "UNKNOWN",                                +
             "info": " ",                                    +
             "type": "Idle",                                 +
             "xlog": "0/35BFC10",                            +
             "agent": "UP",                                  +
             "xloginfo": "DB is not in recovery."            +
         }                                                   +
     },                                                      +
     "messages": [                                           +
         "Did not find XLog location for any non-idle nodes."+
     ],                                                      +
     "allowednodes": [                                       +
         "(List",                                            +
         "is",                                               +
         "empty.)"                                           +
     ],                                                      +
     "minimumstandbys": 0,                                   +
     "failoverpriority": [                                   +
     ],                                                      +
     "membershipcoordinator": "172.17.0.2"                   +
 }
(1 row)
edb=# select efm_extension.efm_allow_node('172.17.0.2');
INFO: efm command is available
efm_allow_node
----------------
0
(1 row)
edb=# select efm_extension.efm_disallow_node('172.17.0.2');
INFO: efm command is available
efm_disallow_node
-------------------
0
(1 row)
edb=# select * from efm_extension.efm_nodes_details ;
INFO: efm command is available
node_ip | property | value
------------+----------+--------------------------
172.17.0.2 | db | "UNKNOWN"
172.17.0.2 | info | " "
172.17.0.2 | type | "Idle"
172.17.0.2 | xlog | "0/35BC388"
172.17.0.2 | agent | "UP"
172.17.0.2 | xloginfo | "DB is not in recovery."
(6 rows)
edb=# select * from efm_extension.efm_local_properties ;
           name            |              value              
---------------------------+----------------------------------
 efm.license               |
 db.user                   | efm
 db.password.encrypted     | 074b627bf50168881d246c5dd32fd8d0
 db.port                   | 5444
 db.database               | edb
 db.service.owner          | enterprisedb
 db.service.name           | edb-as-9.6
 db.bin                    | /usr/edb/as9.6/bin
 db.recovery.conf.dir      | /pgdata
 jdbc.ssl                  | false
 jdbc.ssl.mode             | verify-ca
 user.email                | vibhor.kumar@enterprisedb.com
 script.notification       |
 bind.address              | 172.17.0.2:5430
 admin.port                | 5431
 is.witness                | false
 local.period              | 10
 local.timeout             | 60
 local.timeout.final       | 10
 remote.timeout            | 10
 node.timeout              | 50
 pingServerIp              | 8.8.8.8
 pingServerCommand         | /bin/ping -q -c3 -w5
 auto.allow.hosts          | true
 db.reuse.connection.count | 0
 auto.failover             | true
 auto.reconfigure          | true
 promotable                | true
 minimum.standbys          | 0
 recovery.check.period     | 2
 auto.resume.period        | 0
 virtualIp                 |
 virtualIp.interface       |
 virtualIp.netmask         |
 script.fence              |
 script.post.promotion     |
 script.resumed            |
 script.db.failure         |
 script.master.isolated    |
 sudo.command              | sudo
 sudo.user.command         | sudo -u %u
 jgroups.loglevel          | INFO
 efm.loglevel              | INFO
 jvm.options               | -Xmx32m
(44 rows)

I look forward to your comments on this topic. Click here to request a full demo of EFM.

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:

https://github.com/vibhorkum/pg_background
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  pg_background.so '/usr/edb/as9.6/lib/pg_background.so'
/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 (9.6.2.7)
Type "help" for help.

edb=# create extension pg_background;
CREATE EXTENSION
edb=#

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

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

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:
CREATE OR REPLACE PROCEDURE send_mail (
    p_sender        VARCHAR2,
    p_recipient     VARCHAR2,
    p_subj          VARCHAR2,
    p_msg           VARCHAR2,
    p_mailhost      VARCHAR2
)
IS
    v_conn          UTL_SMTP.CONNECTION;
    v_crlf          CONSTANT VARCHAR2(2) := CHR(13) || CHR(10);
    v_port          CONSTANT PLS_INTEGER := 25;
BEGIN
    v_conn := UTL_SMTP.OPEN_CONNECTION(p_mailhost,v_port);
    UTL_SMTP.HELO(v_conn,p_mailhost);
    UTL_SMTP.MAIL(v_conn,p_sender);
    UTL_SMTP.RCPT(v_conn,p_recipient);
    UTL_SMTP.DATA(v_conn, SUBSTR(
        '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));
    UTL_SMTP.QUIT(v_conn);
END;

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.

https://github.com/vibhorkum/procedure_transition_sync_async

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

EXEC sys.DBMS_SCHEDULER.create_program (
      'synchronous_to_asynchrnous',
      'STORED_PROCEDURE',
      '"public"."transition_sync_async"',
      3,true,  
     'Job which can transition synchronous to asynchronous');  

EXEC
  DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT(
    program_name         => 'synchronous_to_asynchrnous',
    argument_position    => 1,
    argument_name        => 'allowed_slots_lag',
    argument_type        => 'NUMERIC',
    default_value        => '1073741824');
EXEC
  DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT(
    program_name         => 'synchronous_to_asynchrnous',
    argument_position    => 2,
    argument_name        => 'p_sender',
    argument_type        => 'TEXT',
    default_value        => 'vibhor.aim@gmail.com');

EXEC
  DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT(
    program_name         => 'synchronous_to_asynchrnous',
    argument_position    => 3,
    argument_name        => 'p_recipient',
    argument_type        => 'TEXT',
    default_value        => 'vibhor.aim@gmail.com');

EXEC
  DBMS_SCHEDULER.CREATE_SCHEDULE (
    schedule_name    => 'every_minute',  
    start_date       => now, 
    repeat_interval  => 'FREQ=MINUTELY;',  
    comments         => 'This schedule executes every minute');  

EXEC    
   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.

Data Replication from Oracle logical standby to EDB Postgres

At EnterpriseDB (EDB), we are sometimes presented with individual customer use cases or requests that call for the development of innovative solutions to support their deployment of the EDB Postgres Platform. For example, one of our customers  wanted to leverage Oracle Logical standby for replication to EDB Postgres. And they wanted to know if the EDB Postgres Replication Server supported Oracle logical replication to EDB Postgres.

DBAs and database architects can use the EDB Postgres Replication Server for replicating data from Oracle Logical replication to the EDB Postgres Platform. However, the process of setting up the replication,requires extra steps. Because this was a very interesting use case, I thought a blog containing the recommended steps for using the EDB Postgres Replication Server with Oracle Logical Standby would provide some guidance for many end users. There are instances where this could support strategic replication deployments where EDB Postgres is complementing a legacy solution

For this blog, we will use following IP addresses and hostnames:

  1. EDB Postgres server: 172.17.0.4
  2. Oracle Logical Standby server: 172.17.0.3
  3. Oracle primary server: 172.17.0.2

To use EDB Postgres Replication Server 6.0, users should make sure Oracle Logical Standby is in standby guard status. The following is a list of guard status for Oracle Logical Standby:

 ALTER DATABASE GUARD mode
Mode Detail
ALL (Default) No users (except SYS) can make changes to logical standby data
STANDBY Users may modify logical standby data unless it’s being maintained by local LSPn processes (e.g. via Logical Standby)
NONE Normal security rules are applied; any user with appropriate privileges can modify logical standby data

Following a SQL command, DBAs can use the following to check the guard_status in Oracle:

SQL> SELECT guard_status FROM v$database;
GUARD_S
-------

STANDBY

After confirming the guard status in Oracle Logical Sandby, create a database user with the following privileges:

CREATE USER pubuser IDENTIFIED BY oracle;
GRANT DBA TO pubuser;
GRANT CREATE ANY TRIGGER TO pubuser;
GRANT SELECT ANY TABLE TO pubuser;
GRANT LOCK ANY TABLE TO pubuser;

Let’s create the EDB Postgres Replication Server between Oracle Logical Standby and EDB Postgres. We will be leveraging the EDB Postgres Replication Server command line interface for building publications and subscriptions. If you are not familiar with the EDB Postgres Replication Server command line interface options, please refer to the following link for more information:

https://www.enterprisedb.com/docs/en/6.0/repguide/EDB_Postgres_Replication_Server_Users_Guide.1.59.html#

  1. Add Oracle as the publication database in the EDB Postgres Replication Server. The following command can be used to add the database:
[root@epas95 /]# . /usr/ppas-xdb-6.0/etc/sysconfig/xdbReplicationServer-60.config
[root@epas95 /]# ${JAVA_EXECUTABLE_PATH} -jar /usr/ppas-xdb-6.0/bin/edb-repcli.jar \
    -addpubdb \
>   -repsvrfile ${PUBCONF} \
>   -dbtype oracle \
>   -dbhost 172.17.0.3 \
>   -dbport 1521 \
>   -dbuser ${ORAUSER} \
>   -dbpassword ${ORA_ENCRYPT_PASSWD}  \
>   -database ORCL
Adding publication database...
Publication database added successfully. Publication database id:1

Please note the publication database id: 1 and -dbhost: 172.17.0.3 which is the Logical Standby IP Address

  1. Execute the following anonymous PL/SQL block in Oracle Logical Standby as SYSDBA:
BEGIN
 FOR s IN (SELECT TRIGGER_NAME FROM ALL_TRIGGERS WHERE OWNER ='PUBUSER')
 LOOP
         IF DBMS_DDL.IS_TRIGGER_FIRE_ONCE('PUBUSER', s.TRIGGER_NAME)
         THEN
                DBMS_DDL.set_trigger_firing_property( 
                trig_owner => 'PUBUSER',  
                trig_name => s.trigger_name,   
                fire_once => FALSE);
        END IF;
 END LOOP;
END;
/

/

The DBA/User should execute the above PL/SQL block in Oracle Logical Standby to make sure triggers fire when a process changes the base table. By default, in Oracle Logical Standby, triggers never get fired properly in STANDBY guard status.

  1. Add the subscription database in EDB Postgres Replication Server. The following command can be used for adding the subscription database. In our case, we have the EDB Postgres database add:
[root@epas95 /]# ${JAVA_EXECUTABLE_PATH} -jar /usr/ppas-xdb-6.0/bin/edb-repcli.jar \
-addsubdb \
>   -repsvrfile ${SUBCONF} \
>   -dbtype enterprisedb \
>   -dbhost 172.17.0.4 \
>   -dbport 5432 \
>   -dbuser ${PGUSER} \
>   -dbpassword ${PG_ENCRYPT_PASSWD}  \
>   -database orcl
Adding Subscription Database...
Subscription database added successfully. Subscription Database id:1006

Please note the subscription database id: 1006 and -dbhost: 172.17.0.3, which is the EDB Postgres server IP address.

  1. Before creating a publication and subscription, first change the GUARD status to NONE in the Oracle Logical Standby as given below:
SQL> ALTER DATABASE GUARD NONE;

Database altered.

The above change is needed because the EDB Postgres Replication Server acquires a LOCK on tables, managed by logical standby, for creating triggers for publication.

  1. Create publication for table: REPLICATION.REPLICATION_TABLE:
[root@epas95 /]# ${JAVA_EXECUTABLE_PATH} -jar /usr/ppas-xdb-6.0/bin/edb-repcli.jar \
-createpub replication_table \
>            -repsvrfile ${PUBCONF} \
>            -pubdbid 1 \
>            -reptype t \
>            -tables REPLICATION.REPLICATION_TABLE \
>            -repgrouptype s
Creating publication...
Tables:[[REPLICATION.REPLICATION_TABLE, TABLE]]
Filter clause:[]
Publication created.
  1. After creating the publication, please use the following anonymous block to set the EDB Postgres Replication Server trigger property to fire every time there is a change to the base table.
BEGIN
 FOR s IN (SELECT TRIGGER_NAME FROM ALL_TRIGGERS WHERE OWNER ='PUBUSER')
 LOOP
         IF DBMS_DDL.IS_TRIGGER_FIRE_ONCE('PUBUSER', s.TRIGGER_NAME)
         THEN
                DBMS_DDL.set_trigger_firing_property( 
                trig_owner => 'PUBUSER',  
                trig_name => s.trigger_name,   
                fire_once => FALSE);
        END IF;
 END LOOP;
END;
/
  1. Create subscription for publication: replication_table (created in step 5) using following command:
[root@epas95 /]# ${JAVA_EXECUTABLE_PATH} -jar /usr/ppas-xdb-6.0/bin/edb-repcli.jar \
-createsub replication_table \
>   -subsvrfile ${SUBCONF} \
>   -subdbid 1006 \
>   -pubsvrfile ${PUBCONF} \
>   -pubname replication_table
Creating subscription...
Subscription created successfully
  1. After creating the subscription, re-execute the anonymous block to set EDB Postgres Replication Server trigger property:
BEGIN
 FOR s IN (SELECT TRIGGER_NAME FROM ALL_TRIGGERS WHERE OWNER ='PUBUSER')
 LOOP
         IF DBMS_DDL.IS_TRIGGER_FIRE_ONCE('PUBUSER', s.TRIGGER_NAME)
         THEN
                DBMS_DDL.set_trigger_firing_property( 
                trig_owner => 'PUBUSER',  
                trig_name => s.trigger_name,   
                fire_once => FALSE);
        END IF;
 END LOOP;
END;
/
  1. After creating a subscription, rollback the GUARD status to STANDBY in Oracle Logical Standby as shown below:
SQL> ALTER DATABASE GUARD STANDBY;

Database altered.

Now, we are ready to replicate data from Oracle Logical Standby to EDB Postgres.

Let’s first take the snapshot of data from Oracle Logical Standby to EDB Postgres using the following command:

[root@epas95 /]# ${JAVA_EXECUTABLE_PATH} -jar /usr/ppas-xdb-6.0/bin/edb-repcli.jar\
-dosnapshot replication_table \
-repsvrfile ${SUBCONF} \
-verboseSnapshotOutput true
Performing snapshot...
Running EnterpriseDB Migration Toolkit (Build 49.1.5) ...
Source database connectivity info...
conn =jdbc:oracle:thin:@172.17.0.3:1521:ORCL
user =pubuser
password=******
Target database connectivity info...
conn =jdbc:edb://172.17.0.4:5432/orcl?loginTimeout=60&connectTimeout=30
user =pubuser
password=******
Connecting with source Oracle database server...
Connected to Oracle, version 'Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options'
Connecting with target EnterpriseDB database server...
Connected to EnterpriseDB, version '9.5.5.10'
Importing redwood schema REPLICATION...
Table List: 'REPLICATION_TABLE'
Loading Table Data in 8 MB batches...
Disabling FK constraints & triggers on replication.replication_table before truncate...
Truncating table REPLICATION_TABLE before data load...
Disabling indexes on replication.replication_table before data load...
Loading Table: REPLICATION_TABLE ...
[REPLICATION_TABLE] Migrated 10 rows.
[REPLICATION_TABLE] Table Data Load Summary: Total Time(s): 0.04 Total Rows: 10
Enabling FK constraints & triggers on replication.replication_table...
Enabling indexes on replication.replication_table after data load...
Performing ANALYZE on EnterpriseDB database...
Data Load Summary: Total Time (sec): 0.191 Total Rows: 10 Total Size(MB): 0.0
Schema REPLICATION imported successfully.
Migration process completed successfully.
Migration logs have been saved to /var/log/xdb-6.0
******************** Migration Summary ********************
Tables: 1 out of 1
Total objects: 1
Successful count: 1
Failed count: 0
Invalid count: 0
*************************************************************
Snapshot taken successfully.

After taking an initial snapshot, let’s modify the replication table from Oracle primary:

18-JAN-17 REPLICATION@ ORCL PRIMARY> UPDATE replication_table SET id=11 WHERE id=10;
Elapsed: 00:00:00.01
18-JAN-17 REPLICATION@ ORCL PRIMARY> COMMIT;
Elapsed: 00:00:00.01
18-JAN-17 REPLICATION@ ORCL PRIMARY> SELECT * FROM replication_table;
1 FIRST
2 SECOND
3 THIRD
4 FOURTH
5 FIFTH
6 SIXTH
7 SEVENTH
8 EIGHTH
9 NINTH
11 TENTH

Elapsed: 00:00:00.00

After making the changes on Oracle primary, we can execute SELECT command on Oracle Logical Standby to verify if the data change was replicated by Oracle in logical standby mode.

18-JAN-17 REPLICATION@ ORCL STANDBY> SELECT * FROM replication_table;
1 FIRST
2 SECOND
3 THIRD
4 FOURTH
5 FIFTH
6 SIXTH
7 SEVENTH
8 EIGHTH
9 NINTH
11 TENTH

10 rows selected.

Elapsed: 00:00:00.00
  1. Now perform the manual sync to verify EDB Postgres Replication Server can replicate the above changes in EDB Postgres.
[root@epas95 /]# ${JAVA_EXECUTABLE_PATH} -jar /usr/ppas-xdb-6.0/bin/edb-repcli.jar  -dosynchronize  replication_table -repsvrfile ${SUBCONF} -repgrouptype s
Performing synchronize...
Synchronize done successfully.
[root@epas95 /]# psql orcl
psql.bin (9.5.5.10)
Type "help" for help.
orcl=# select * from replication.replication_table ;
id |   col
----+---------
1 | FIRST
2 | SECOND
3 | THIRD
4 | FOURTH
5 | FIFTH
6 | SIXTH
7 | SEVENTH
8 | EIGHTH
9 | NINTH
11 | TENTH

(10 rows)

The above snapshot shows that after performing -dosynchronize, the EDB Postgres Replication Server was able to replicate incremental changes on Oracle Logical Standby to EDB Postgres.

The pg_rewind tool was first introduced in PostgreSQL 9.5. This is best used in a situation when a standby becomes a master, then the old master can be reattached to the new master as a standby without restoring it from the new base backup.

The tool examines the timeline histories of the new master (the old standby) and the old master to determine the point where they diverged, and it expects to find Write-Ahead Logs (WAL) in the old master cluster’s pg_xlog directory reaching all the way back to the point of divergence.

For pg_rewind, DBAs need to have the following:

  1. wal_log_hints = on or PostgreSQL cluster with data checksum.

DBAs can also have data checksums and wal_log_hints, both enabled in their PostgreSQL cluster.

For more information on wal_log_hints and enabling data checksum, the following links are useful:

https://www.postgresql.org/docs/9.5/static/runtime-config-wal.html#GUC-WAL-LOG-HINTS

https://www.postgresql.org/docs/9.5/static/app-initdb.html#APP-INITDB-DATA-CHECKSUMS

  1. full_page_writes should be enabled, which is by default enabled in Postgres. For more information on this parameter, see the following link:

https://www.postgresql.org/docs/9.5/static/runtime-config-wal.html#GUC-FULL-PAGE-WRITES

In Postgres 9.6, Alexander Korotkov has introduced a new feature in the pg_rewind tool. Details of the commit are given below:

 commit e50cda78404d6400b1326a996a4fabb144871151
 Author: Teodor Sigaev <teodor@sigaev.ru>
 Date:   Tue Dec 1 18:56:44 2015 +0300
 
      Use pg_rewind when old master timeline was switched
 
      Allow pg_rewind to work when old master timeline was switched. Now
      user can return promoted standby to old master.
  
      old master timeline history becomes a global variable. Index
      in old master timeline history is used in function interfaces instead of
      specifying TLI directly. Thus, SimpleXLogPageRead() can easily start
      reading XLOGs from next timeline when current timeline ends.
  
      Author: Alexander Korotkov
      Review: Michael Paquier

With the new feature, a DBA/user can now reattach the standby that got promoted due to the following reasons:

  1. User error:

By mistake, the user promoted the standby using pg_ctl promote option or by creating a trigger

  1. Failover script has some logic problem, which resulted in promotion of standby to Master
  2. A user is testing failover scenario. (In failover testing, the standby needs to be promoted and needs to bring back to a state where standby can be safely attached to PostgreSQL master)
  3. Bug in failover management tool (a typical scenario is due to a network blip and the failover management tool promoted the standby)

There could be other possible scenarios where this can be useful.

Let’s try with PostgreSQL 9.5 to see the previous behavior of pg_rewind in a use case, where the DBA wants to reattach the standby to the primary server after promoting it.

For this test, we have two PostgreSQL clusters: node1 and node2. Node1 (port: 54445) is a master/primary. Node2 (port: 54446) is a standby which streams data from master node1 (port 54445)
Below is streaming replication status:

bash-postgres-9.5 $ psql -p 54445 -x -c "select * from pg_stat_replication;"
-[ RECORD 1 ]----+---------------------------------
pid              | 4482
usesysid         | 10
usename          | enterprisedb
application_name | walreceiver
client_addr      | 127.0.0.1
client_hostname  | 
client_port      | 46780
backend_start    | 21-DEC-16 18:15:08.442887 +00:00
backend_xmin     | 
state            | streaming
sent_location    | 0/8000060
write_location   | 0/8000060
flush_location   | 0/8000060
replay_location  | 0/8000060
sync_priority    | 0
sync_state       | async


bash-postgres-9.5 $  psql -p 54446 -c "SELECT pg_is_in_recovery()"
 pg_is_in_recovery 
-------------------
 t
(1 row)

As you can see the PostgreSQL instance running on port 54446 is a standby (i.e it’s in recovery mode) and it’s streaming data from node1 (port 54445).

Now, let’s promote node2 and check the status of replication on node1.

bash-postgres-9.5 $  touch /tmp/test.trigger
bash-postgres-9.5 $  psql -p 54446 -c "SELECT pg_is_in_recovery()"
 pg_is_in_recovery 
-------------------
 f
(1 row)

bash-postgres-9.5 $  psql -p 54445 -x -c "select * from pg_stat_replication;"
(0 rows)

The above snapshot shows that node2(port 54446) is now out of recovery mode. And replication status on node1(port 54445) shows that node2 is not streaming data from node1.

Now, let’s try to use pg_rewind to reattach node2 (port 54446) as standby to node1 (port 54445) to verify behavior in version 9.5.

bash-postgres-9.5 $ pg_ctl -D /var/lib/ppas/node2/ stop
bash-postgres-9.5 $  pg_rewind -D /var/lib/ppas/node2  --source-server="port=54445 host=127.0.0.1" 
could not find common ancestor of the source and target cluster's timelines
Failure, exiting

The above messages show that the user cannot reattach a promoted standby to the old master, which is a limitation of pg_rewind in 9.5, and it’s an expected behavior of pg_rewind 9.5.

We can now try the same scenario with PostgreSQL 9.6 to check the pg_rewind improvement.

bash-postgres-9.6 $ pg_ctl --version
pg_ctl (EnterpriseDB) 9.6.1.4

bash-postgres-9.6$ psql -p 54445 -x -c "select * from pg_stat_replication;"
-[ RECORD 1 ]----+---------------------------------
pid              | 4838
usesysid         | 10
usename          | enterprisedb
application_name | walreceiver
client_addr      | 127.0.0.1
client_hostname  | 
client_port      | 46790
backend_start    | 21-DEC-16 18:35:03.216873 +00:00
backend_xmin     | 
state            | streaming
sent_location    | 0/4000060
write_location   | 0/4000060
flush_location   | 0/4000060
replay_location  | 0/4000060
sync_priority    | 0
sync_state       | async

bash-postgres-9.6$ psql -p 54446 -x -c "select pg_is_in_recovery();"
-[ RECORD 1 ]-----+--
pg_is_in_recovery | t

The above snapshot shows the streaming replication between node1 (port 54445) to node2 (port 54446).

Now, let’s promote node2. Below is a snapshot of promoting node2:

bash-postgres-9.6$ touch /tmp/test
bash-postgres-9.6$ psql -p 54446 -x -c "select pg_is_in_recovery();"
-[ RECORD 1 ]-----+--
pg_is_in_recovery | f


bash-postgres-9.6$ psql -p 54446 -x -c "select * from pg_stat_replication;"
(0 rows)

The above snapshot shows that node2 (port 54446) is out of recovery mode and there is no replication between node1(port 54445) and node2(port 54446).

Let’s try to use pg_rewind to re-attach node2 as standby with node1.

bash-postgres-9.6$ pg_ctl -D /var/lib/ppas/node2/ stop
waiting for server to shut down.... done
server stopped
bash-postgres-9.6$ pg_rewind -D /var/lib/ppas/node2 --source-server="port=54445 host=127.0.0.1" 
servers diverged at WAL position 0/4000140 on timeline 1
rewinding from last common checkpoint at 0/4000098 on timeline 1
Done!

The above snapshot shows that we were able to pg_rewind node2 (port 54446) to the last common checkpoint of node1(port 54445).

We can now start node2 and check the status of replication. Below is a snapshot of that activity:

bash-postgres-9.6$ pg_ctl -D /var/lib/ppas/node2/ start
server starting
-bash-4.1$ 2016-12-21 18:55:15 UTC LOG:  redirecting log output to logging collector process
2016-12-21 18:55:15 UTC HINT:  Future log output will appear in directory "pg_log".

bash-postgres-9.6$ psql -p 54445 -x -c "select * from pg_stat_replication;"
-[ RECORD 1 ]----+---------------------------------
pid              | 5019
usesysid         | 10
usename          | enterprisedb
application_name | walreceiver
client_addr      | 127.0.0.1
client_hostname  | 
client_port      | 46794
backend_start    | 21-DEC-16 18:55:15.635726 +00:00
backend_xmin     | 
state            | streaming
sent_location    | 0/4012BA0
write_location   | 0/4012BA0
flush_location   | 0/4012BA0
replay_location  | 0/4012BA0
sync_priority    | 0
sync_state       | async

bash-postgres-9.6$ psql -p 54446 -x -c "select pg_is_in_recovery();"
-[ RECORD 1 ]-----+--
pg_is_in_recovery | t

Partition pruning in EDB Postgres 9.5

One of my colleague who was recently working with a customer has presented a customer case. According to him, customer has a partitioned table and EDB Postgres was not applying the partition pruning in his query. So, I thought to blog about partition pruning, so that EDB Postgres developers and DBAs can benefit.

EDB Postgres supports two types of partition pruning:

Constraint exclusion pruning:

It is a feature introduced in Postgresql 8.1. This type of pruning works with PostgreSQL-style of partition. With constraint exclusion enabled, the planner will examine the constraints of each partition and try to prove that the partition need not be scanned because it could not contain any rows meeting the query’s WHERE clause. When the planner can prove this, it excludes the partition from the query plan.

However, it has some limitations. Following is the limitation of constraint_exclusion:

a. Constraint exclusion only works when the query’s WHERE clause contains constants (or externally supplied parameters). For example, a comparison against a non-immutable function such as CURRENT_TIMESTAMP cannot be optimized, since the planner cannot know which partition the function value might fall into at run time.
b. Keep the partitioning constraints simple, else the planner may not be able to prove that partitions don’t need to be visited. Use simple equality conditions for list partitioning, or simple range tests for range partitioning, as illustrated in the preceding examples. A good rule of thumb is that partitioning constraints should contain only comparisons of the partitioning column(s) to constants using B-tree-indexable operators.

For verification, below shows the behavior of constraint_exclusion pruning:
1. Let’s create PostgreSQL-style partition table using table inheritance feature.

CREATE TABLE measurement (
     city_id        int not null,
     logdate        date not null,
     peaktemp        int,
     unitsales      int
 );
CREATE TABLE measurement_y2004m02 (
     CHECK ( date_part('month'::text, logdate) = 2)
 ) INHERITS (measurement);
CREATE TABLE measurement_y2004m03 (
     CHECK ( date_part('month'::text, logdate) = 3 )
 ) INHERITS (measurement);

  1. Execute simple query to verify the constraint_exclusion behavior based on above definition:
 edb=# EXPLAIN (costs off) SELECT count(*) FROM measurement WHERE date_part('month'::text, logdate) = 3;
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Aggregate
   ->  Append
         ->  Seq Scan on measurement
               Filter: (date_part('month'::text, logdate) = '3'::double precision)
         ->  Seq Scan on measurement_y2004m02
               Filter: (date_part('month'::text, logdate) = '3'::double precision)
         ->  Seq Scan on measurement_y2004m03
               Filter: (date_part('month'::text, logdate) = '3'::double precision)
(8 rows)

Above output of the query shows EDB Postgres considered all partitions of table measurements, even though we had included partition column and constant value in WHERE clause. This is due to check constraint which has date_part function. date_part function is not immutable in Postgres, therefore at planning time, EDB Postgres doesn’t know what value it will return. And, if user doesn’t include proper WHERE clause as mentioned in check constraint, pruning will not work.

In Postgres you can make a function immutable by using ALTER FUNCTION command.

In below example, we will make date_part function immutable to check if constraint_exclusion works with date_part immutable function or not:

  1. Convert date_part function to immutable :
edb=# ALTER FUNCTION date_part (text, timestamp without time zone ) immutable;
ALTER FUNCTION
  1. Perform EXPLAIN command to check the behavior of constraint_exclusion using immutable function:
edb=# EXPLAIN (costs off) SELECT count(*) FROM measurement WHERE date_part('month'::text, logdate) = 3;
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Aggregate
   ->  Append
         ->  Seq Scan on measurement
               Filter: (date_part('month'::text, logdate) = '3'::double precision)
         ->  Seq Scan on measurement_y2004m03
               Filter: (date_part('month'::text, logdate) = '3'::double precision)
(6 rows)

As you can see with immutable function EDB Postgres was able to perform constraint_exclusion pruning.

What if we change the WHERE clause little bit and include < and = operator in our SQL queries (below are examples)

edb=#  EXPLAIN (costs off) SELECT count(*) FROM measurement WHERE logdate < DATE '2004-03-01';                                      QUERY PLAN                                       -------------------------------------------------------------------------------------  Aggregate    ->  Append
         ->  Seq Scan on measurement
               Filter: (logdate < '01-MAR-04 00:00:00'::timestamp without time zone)          ->  Seq Scan on measurement_y2004m02
               Filter: (logdate < '01-MAR-04 00:00:00'::timestamp without time zone)          ->  Seq Scan on measurement_y2004m03
               Filter: (logdate < '01-MAR-04 00:00:00'::timestamp without time zone) (8 rows) edb=# EXPLAIN (costs off) SELECT count(*) FROM measurement WHERE logdate = DATE '2004-02-01';                                      QUERY PLAN                                       -------------------------------------------------------------------------------------  Aggregate    ->  Append
         ->  Seq Scan on measurement
               Filter: (logdate = '01-FEB-04 00:00:00'::timestamp without time zone)
         ->  Seq Scan on measurement_y2004m02
               Filter: (logdate = '01-FEB-04 00:00:00'::timestamp without time zone)
         ->  Seq Scan on measurement_y2004m03
               Filter: (logdate = '01-FEB-04 00:00:00'::timestamp without time zone)
(8 rows)

As you can see with a change in WHERE clause and exclusion of the way constraint defined on partition, Postgres will scan all partitions.

Based on above we can conclude that if a user is planning to use Postgres way of partition then they have to be careful about the constraint definition in order to utilize constraint_exclusion pruning.

Lets modify the definition of measurement table and verify the ,=, <= and = operator in WHERE clause.

CREATE TABLE measurement (
     city_id        int not null,
     logdate        date not null,
     peaktemp        int,
     unitsales      int
 ); 
CREATE TABLE measurement_y2004m02 (
     CHECK ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' )  ) INHERITS (measurement); CREATE TABLE measurement_y2004m03 (      CHECK ( logdate >= DATE '2004-03-01' AND logdate < DATE '2004-04-01' )
 ) INHERITS (measurement);

Below is explain plan based on above definition:

edb=# EXPLAIN (costs off) SELECT count(*) FROM measurement WHERE logdate < DATE '2004-03-01';                                      QUERY PLAN                                       -------------------------------------------------------------------------------------  Aggregate    ->  Append
         ->  Seq Scan on measurement
               Filter: (logdate < '01-MAR-04 00:00:00'::timestamp without time zone)          ->  Seq Scan on measurement_y2004m02
               Filter: (logdate < '01-MAR-04 00:00:00'::timestamp without time zone) (6 rows) edb=# EXPLAIN (costs off) SELECT count(*) FROM measurement WHERE logdate = DATE '2004-03-01';                                      QUERY PLAN                                       -------------------------------------------------------------------------------------  Aggregate    ->  Append
         ->  Seq Scan on measurement
               Filter: (logdate = '01-MAR-04 00:00:00'::timestamp without time zone)
         ->  Seq Scan on measurement_y2004m03
               Filter: (logdate = '01-MAR-04 00:00:00'::timestamp without time zone)
(6 rows)

Above clearly shows that with correct constraint definition, constriant_exclusion pruning can work for >,<,>=, <= and = operator in WHERE clause.

Fast pruning:

EDB Postgres has CREATE TABLE PARTITION SYNTAX since version 9.1. PARTITION SYNTAX in EDB Postgres uses one more pruning called fast pruning. Fast pruning uses the partition metadata and query predicates to efficiently reduce the set of partitions to scan. Fast pruning in EDB Postgres happens before query plan. Let’s verify the behavior of fast pruning.
As mentioned fast pruning works with partition which user created using EDB Postgres CREATE TABLE PARTITION Syntax. Let’s modify the above definition of measurement table to use CREATE TABLE PARTITION SYNTAX as given below:

CREATE TABLE  measurement (
     city_id        int not null,
     logdate        date not null,
     peaktemp        int,
     unitsales      int
 )
PARTITION BY RANGE(logdate)
(PARTITION y2004m01 VALUES LESS THAN ('2004-02-01'),
 PARTITION y2004m02 VALUES LESS THAN ('2004-03-01'),
 PARTITION y2004m03 VALUES LESS THAN ('2004-04-01')
);
edb=# EXPLAIN (costs off) SELECT count(*) FROM measurement WHERE logdate < DATE '2004-03-01';                                      QUERY PLAN                                       -------------------------------------------------------------------------------------  Aggregate    ->  Append
         ->  Seq Scan on measurement
               Filter: (logdate < '01-MAR-04 00:00:00'::timestamp without time zone)          ->  Seq Scan on measurement_y2004m01
               Filter: (logdate < '01-MAR-04 00:00:00'::timestamp without time zone)          ->  Seq Scan on measurement_y2004m02
               Filter: (logdate < '01-MAR-04 00:00:00'::timestamp without time zone) (8 rows) edb=# EXPLAIN (costs off) SELECT count(*) FROM measurement WHERE logdate = DATE '2004-03-01';                                      QUERY PLAN                                       -------------------------------------------------------------------------------------  Aggregate    ->  Append
         ->  Seq Scan on measurement
               Filter: (logdate = '01-MAR-04 00:00:00'::timestamp without time zone)
         ->  Seq Scan on measurement_y2004m03
               Filter: (logdate = '01-MAR-04 00:00:00'::timestamp without time zone)
(6 rows)

edb=# EXPLAIN (costs off) SELECT count(*) FROM measurement WHERE logdate > DATE '2004-03-01';
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Aggregate
   ->  Append
         ->  Seq Scan on measurement
               Filter: (logdate > '01-MAR-04 00:00:00'::timestamp without time zone)
         ->  Seq Scan on measurement_y2004m03
               Filter: (logdate > '01-MAR-04 00:00:00'::timestamp without time zone)
(6 rows)

For more information on EDB Postgres pruning please refer following link:
https://www.enterprisedb.com/docs/en/9.5/oracompat/Database_Compatibility_for_Oracle_Developers_Guide.1.327.html#

Tip:: PPAS 9.4 and Global Temporary Table

Customers who moved/migrated their database from Oracle to PPAS frequently ask for Global Temporary Table in PPAS.

Currently, PPAS doesn’t support Global Temporary tables. However, there is a way user can achieve this functionality in PPAS.

Before we continue with the implementation, lets first understand characteristics of Global Temporary Table. Following are the important characteristics of Global Temporary Table.
1. Global Temporary Table gives predefined structure for storing data.
2. It’s an unlogged table which means any activity on this table will not be logged.
3. The data in a global temporary table are private, such that data inserted by a session can only be accessed by that session.

Based on the above characteristics of Global Temporary Table AKA GTT, we can define similar kind of work by using the following method:
1. Create UNLOGGED TABLE in PPAS, which activity won’t be logged.
2. Create Row Level Security in such a way that session should be able to see their information (based on PID).
3. Create a process which can cleanup data from GTT based on pids which are not active in database.

Lets see how we can implement it in Adavanced Server.

1. Create an UNLOGGED table with all columns required and extra column of Pid.

CREATE UNLOGGED TABLE test_global_temporary_table(id numeric, col text, pid bigint default pg_backend_pid());

2. Create a function to restrict the visibility of data.

CREATE OR REPLACE FUNCTION verify_pid_context (
    p_schema       TEXT,
    p_object       TEXT
)
RETURN VARCHAR2
IS
DECLARE
   predicate TEXT;
BEGIN
    IF ( current_setting('is_superuser') = 'on')
    THEN
      predicate = 'true';
    ELSE
      predicate := format('pid = %s',pg_backend_pid());
    END IF;
    RETURN predicate;
END;

3. Apply the security policy based on above function.

DECLARE
  v_object_schema VARCHAR2(30)   := 'public';
  v_object_name VARCHAR2(30)     := 'test_global_temporary_table';
  v_policy_name VARCHAR2(30)     := 'secure_by_pid';
  v_function_schema VARCHAR2(30) := 'public';
  v_policy_function VARCHAR2(30) := 'verify_pid_context';
  v_statement_types VARCHAR2(30) := 'INSERT,UPDATE,DELETE,SELECT';
  v_update_check BOOLEAN         := TRUE;
  v_enable BOOLEAN               := TRUE;
BEGIN
  DBMS_RLS.ADD_POLICY( v_object_schema,
                       v_object_name,
                       v_policy_name,
                       v_function_schema,
                       v_policy_function,
                       v_statement_types,
                       v_update_check,
                       v_enable
                     );
END;

4. Create UPDATABLE view which can hide pid column. All sessions will be using this view as GTT.

   CREATE OR REPLACE VIEW test_global_temporary AS SELECT id, col FROM test_global_temporary_table;

5. Create a backend job, which can cleanup Table based on stale/old sessions.
For job, user/developer can do following:
a. use superuser and execute DELETE command on table:
DELETE FROM test_global_temporary WHERE pid NOT in (SELECT pid FROM pg_stat_activity);
b. To Schedule above DELETE command, user can use one of the following:
i. Crontab
ii. Or PPAS DBMS_SCHEDULE Package.

6. GRANT ALL privileges to database user who can access Global Temporary Table.

    GRANT ALL on test_global_temporary TO testdbuser;
    GRANT ALL on test_global_temporary_table To testdbuser;

Now, lets try above implementation of Global Temporary Table.

Open two sessions as a normal user (testdbuser) as given below:

[vibhorkumar@localhost ~]$ psql -U testdbuser edb
psql.bin (9.4.4.9)
Type "help" for help.

edb=> 
edb=> select pg_backend_pid();
 pg_backend_pid 
----------------
          32722
(1 row)


edb=> select pg_backend_pid();
 pg_backend_pid 
----------------
          32729
(1 row)

Now from both session insert some records:
From first session:

edb=> INSERT INTO test_global_temporary VALUES(1,'FROM pid 32722');
INSERT 0 1
edb=> INSERT INTO test_global_temporary VALUES(2,'FROM pid 32722');
INSERT 0 1
edb=> INSERT INTO test_global_temporary VALUES(3,'FROM pid 32722');
INSERT 0 1

From Second session:

edb=> INSERT INTO test_global_temporary VALUES(1,'FROM pid 32729');
INSERT 0 1
edb=> INSERT INTO test_global_temporary VALUES(2,'FROM pid 32729');
INSERT 0 1
edb=> INSERT INTO test_global_temporary VALUES(3,'FROM pid 32729');
INSERT 0 1

From First Session:

edb=> SELECT * FROM test_global_temporary;
 id |      col       
----+----------------
  1 | FROM pid 32722
  2 | FROM pid 32722
  3 | FROM pid 32722
(3 rows)

From Second Session:

edb=> SELECT * FROm test_global_temporary;
 id |      col       
----+----------------
  1 | FROM pid 32729
  2 | FROM pid 32729
  3 | FROM pid 32729
(3 rows)

which shows that unlogged table with right RLS policy and backend job, can be a potential solution for Global Temporary Tables.

Switchover/Switchback in PostgreSQL 9.3

PostgreSQL 9.3 has two key software updates making switchover/switchback easier in High Availability configurations.

First, let’s address the software patches and their descriptions:
1. First patch was committed by Fujii Masao.
Patch commit# 985bd7d49726c9f178558491d31a570d47340459

With this patch, the walsender process tries to send all outstanding WAL records to the standby in replication when the user shuts down the master.

This means:
a. All WAL records are synced between two servers after the clean shutdown of the master
b. After promoting the standby to new master, the user can restart the stopped master as new standby without a fresh backup from new master.

2. Second patch was committed by Heikki Linnakangas in PostgreSQL 9.3.
Patch commit# abfd192b1b5ba5216ac4b1f31dcd553106304b19

Before PostgreSQL version 9.3, streaming replication used to stop replicating if the timeline on the primary didn’t match the standby. This generally happens when the user promotes one of the standbys to become the new master. Promoting a standby always results in an increment of timeline ID and after that increment, other standbys will refuse to continue replicating.

With this patch in PostgreSQL 9.3, the standby asks the primary for any timeline history files that are missing from the standby when it connects – if the standby recovery.conf file has the following setting:
recovery_target_timeline=’latest’

The missing files are sent using a new replication command TIMELINE_HISTORY, and stored in the standby’s pg_xlog directory. Using the timeline history files, the standby can follow the latest timeline present in the primary, just as it can follow new timelines appearing in an archive WAL directory.

Because of above patches, if the user performs the following sequence of steps then switchover/switchback can be easily achieved:
To switchover from Master to Standby, use following steps:
On Master:
1. Use any one of following stop options for clean shutdown.

    pg_ctl stop --pgdata=data_dire --mode=fast
    or
    pg_ctl stop --pgdata=data_directory --mode=smart
    

2. Before promoting standby:

Make sure all WAL send by Master applied on Standby. Use following functions to verify it:
    * pg_last_xlog_receive_location()
    * pg_last_xlog_replay_location()

3. Have the proper archive location and archive_command setting accessible to old Master.

Following is a presentation I delivered at a recent BPUG Meet (Boston PostgreSQL User Group) to discuss switchover/switchback.