Pg_rewind in PostgreSQL 9.5 Step by Step

 Pg_rewind makes it possible to efficiently bring an old primary in sync with a new primary without having to perform a full base backup. This works by looking in the Write Ahead Log to see which pages have been modified, and only copying across those pages.
In this example, we have a primary (running on port 5050) and a standby subscribing to it (on port 5051):

Step-1

Created new cluster as a master.

[enterprisedb@localhost bin]$ ./initdb -D /tmp/master

The files belonging to this database system will be owned by user "enterprisedb".

This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".

The default database encoding has accordingly been set to "UTF8".

The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /tmp/master ... ok

creating subdirectories ... ok

selecting default max_connections ... 100

Modified below parameters in postgresql.conf file

wal_level = hot_standby

wal_log_hints = on

max_wal_senders = 2

wal_keep_segments = 64

hot_standby = on

Modified pg_hba.conf file

[enterprisedb@localhost bin]$ vi /tmp/master/pg_hba.conf

host replication enterprisedb 127.0.0.1/32 trust

[enterprisedb@localhost bin]$ ./pg_ctl -D /tmp/master/ start

server starting

Step-2

on slave server (In my case it is local server).

Configured

[enterprisedb@localhost bin]$ ./pg_basebackup -PR -X stream -c fast -h 127.0.0.1 -U enterprisedb -p 5050 -D /tmp/slave

43452/43452 kB (100%), 1/1 tablespace

Modified port as 5051 in postgresql.conf

Modified recovery.conf file as below:

standby_mode = 'on'

primary_conninfo = 'user=enterprisedb host=127.0.0.1 port=5050 sslmode=prefer sslcompression=1'

trigger_file = '/tmp/trigger_file'

Start slave server and check the status

psql.bin (9.5.0.3)

Type "help" for help.

edb=# select pg_is_in_recovery();

pg_is_in_recovery

-------------------

t

(1 row)

On master:

pgrewind=# \x

Expanded display is on.

pgrewind=# select * from pg_stat_replication;

-[ RECORD 1 ]----+---------------------------------

pid | 71492

usesysid | 10

usename | enterprisedb

application_name | walreceiver

client_addr | 127.0.0.1

client_hostname |

client_port | 47746

backend_start | 09-JAN-16 18:15:36.454731 +05:30

backend_xmin |

state | streaming

sent_location | 0/401D360

write_location | 0/401D360

flush_location | 0/401D360

replay_location | 0/401D360

sync_priority | 0

sync_state | async

Step-3 Master:

edb=# create database pgrewind;

CREATE DATABASE

edb=# \c pgrewind

You are now connected to database "pgrewind" as user "enterprisedb".

pgrewind=# create table pgrew(id int)

pgrewind-# ;

CREATE TABLE

pgrewind=# insert into pgrew values(generate_series(1,100));

INSERT 0 100

pgrewind=# select count(*) from pgrew;

count

-------

100

(1 row)

Step-4-On slave server:

edb=# \c pgrewind

You are now connected to database "pgrewind" as user "enterprisedb".

pgrewind=# \dt

List of relations

Schema | Name | Type | Owner

--------+-------+-------+--------------

public | pgrew | table | enterprisedb

(1 row)

pgrewind=# select count(*) from pgrew;

count

-------

100

(1 row)

touch /tmp/trigger_file

stop master cluster

Step-5-On slave:

edb=# \c pgrewind

You are now connected to database "pgrewind" as user "enterprisedb".

pgrewind=# \dt

List of relations

Schema | Name | Type | Owner

--------+-------+-------+--------------

public | pgrew | table | enterprisedb

(1 row)

pgrewind=# insert into pgrew values(generate_series(101,1000));

INSERT 0 900

pgrewind=# select count(*) from pgrew;

count

-------

1000

(1 row)

Step-6-On Master

/opt/PostgresPlus/9.5AS/bin/pg_rewind -D /tmp/master/ --source-server="host=127.0.0.1 port=5051 user=enterprisedb"

servers diverged at WAL position 0/401D440 on timeline 1

rewinding from last common checkpoint at 0/401D398 on timeline 1

Done!

Modify port from 5051 to 5050

rm backup_label.old*

rm recovery.done

mv /tmp/slave/recovery.done /tmp/master/recovery.conf

[postgres@localhost ~]$ cat /tmp/master/recovery.conf
standby_mode = 'on'
primary_conninfo = 'user=postgres host=127.0.0.1 port=5437'
trigger_file = '/tmp/tigger_file'

start old master as new slave

pg_ctl -D /tmp/master start

edb=# \l+

List of databases

Name | Owner | Encoding | Collate | Ctype | ICU | Access privileges | Size | Tablespace | Description

-----------+--------------+----------+-------------+-------------+-----+-------------------------------+-------+------------+--------------------------------------------

edb | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | | 10 MB | pg_default |

pgrewind | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | | 10 MB | pg_default |

postgres | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | | 10 MB | pg_default | default administrative connection database

template0 | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | =c/enterprisedb +| 10 MB | pg_default | unmodifiable empty database

| | | | | | enterprisedb=CTc/enterprisedb | | |

template1 | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | =c/enterprisedb +| 10 MB | pg_default | default template for new databases

| | | | | | enterprisedb=CTc/enterprisedb | | |

(5 rows)

edb=# \c pgrewind

You are now connected to database "pgrewind" as user "enterprisedb".

pgrewind=# \dt

List of relations

Schema | Name | Type | Owner

--------+-------+-------+--------------

public | pgrew | table | enterprisedb

(1 row)

pgrewind=# select count(*) from pgrew;

count

-------

1000

(1 row)