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#