xDB Replication Server CLI Commands for setup MMR.

Please follow the below steps will help you to configure MMR in command line.

1. Created mmr database and mtest table, inserted values on primary server.

edb=# create database mmr;
CREATE DATABASE
edb=# \c mmr
You are now connected to database "mmr" as user "enterprisedb".
mmr=# create table mtest(id int primary key);
CREATE TABLE
mmr=# insert into mtest values(generate_series(1,10));
INSERT 0 10
mmr=#
mmr=# select count(*) from mtest;
 count
-------
    10
(1 row)

edb=# create database mnode;
CREATE DATABASE

The below version command provides the xDB Replication Server CLI’s version number.

/usr/bin/java -jar /opt/PostgresPlus/EnterpriseDB-xDBReplicationServer/bin/edb-repcli.jar -version
Version: 6.0.0-beta2

2.By using below command encrypted the password.  We need to give the password into the infile.txt.

[enterprisedb@localhost ~]$/usr/bin/java -jar /opt/PostgresPlus/EnterpriseDB-xDBReplicationServer/bin/edb-repcli.jar -encrypt -input /opt/PostgresPlus/9.5AS/bin/infile.txt -output /opt/PostgresPlus/9.5AS/bin/pwdfile.txt


3.The following example adds a publication database definition for a master definition node in a multi-master replication system.

[enterprisedb@localhost ~]$/usr/bin/java -jar /opt/PostgresPlus/EnterpriseDB-xDBReplicationServer/bin/edb-repcli.jar -addpubdb -repsvrfile /home/enterprisedb/pubrepsvrfile -dbtype enterprisedb -dbhost localhost -dbport 5444 -dbuser enterprisedb -dbpassword SJ70z8Gk0zY=  -database mmr -repgrouptype m -nodepriority 1
Adding publication database...
Publication database added successfully. Publication database id:1


[enterprisedb@localhost ~]$/usr/bin/java -jar /opt/PostgresPlus/EnterpriseDB-xDBReplicationServer/bin/edb-repcli.jar -printpubdbids -repsvrfile /home/enterprisedb/pubrepsvrfile
Printing publication database ids...
1

[enterprisedb@localhost ~]$ /usr/bin/java -jar /opt/PostgresPlus/EnterpriseDB-xDBReplicationServer/bin/edb-repcli.jar -printpubdbidsdetails -repsvrfile /home/enterprisedb/pubrepsvrfile
Printing publication database ids with details...
id:host:port:database|sid:user
1:localhost:5444:mmr:enterprisedb



[enterprisedb@localhost ~]$ /usr/bin/java -jar /opt/PostgresPlus/EnterpriseDB-xDBReplicationServer/bin/edb-repcli.jar -updatepubdb -repsvrfile /home/enterprisedb/pubrepsvrfile -pubdbid 1 -dbhost localhost -dbport 5444 -dbuser enterprisedb -dbpassword SJ70z8Gk0zY= -database mmr -nodepriority 1
Updating publication database ...
Publication database with ID 1 is updated successfully.


[enterprisedb@localhost ~]$ /usr/bin/java -jar /opt/PostgresPlus/EnterpriseDB-xDBReplicationServer/bin/edb-repcli.jar -createpub pub95 -repsvrfile /home/enterprisedb/pubrepsvrfile -pubdbid 1 -reptype T -tables public.mtest -repgrouptype m
Creating publication...
Tables:[[public.mtest, TABLE]]
Filter clause:[]
Conflict Resolution Option:[ Earliest Timestamp ]
Standby Conflict Resolution Option:[ Manual ]
Publication created.


[enterprisedb@localhost ~]$ /usr/bin/java -jar /opt/PostgresPlus/EnterpriseDB-xDBReplicationServer/bin/edb-repcli.jar -addpubdb -repsvrfile /home/enterprisedb/pubrepsvrfile -dbtype enterprisedb -dbhost localhost -dbport 5444 -dbuser enterprisedb -dbpassword SJ70z8Gk0zY=  -database mnode -repgrouptype m -nodepriority 2
Adding publication database...
Replicating publication schema...
Publication database added successfully. Publication database id:5

[enterprisedb@localhost ~]$ /usr/bin/java -jar /opt/PostgresPlus/EnterpriseDB-xDBReplicationServer/bin/edb-repcli.jar -validatepubs -repsvrfile /home/enterprisedb/pubrepsvrfile -pubdbid 1 -repgrouptype m
Validating all available publications ...
The schema definitions for all the non snapshot-only publications tables are in sync
with the source.

[enterprisedb@localhost ~]$ /usr/bin/java -jar /opt/PostgresPlus/EnterpriseDB-xDBReplicationServer/bin/edb-repcli.jar -dosynchronize pub95 -repsvrfile /home/enterprisedb/pubrepsvrfile -repgrouptype m
Performing synchronize...
Publication synchronized successfully.

mnode=# \dt
           List of relations
 Schema | Name  | Type  |    Owner   
--------+-------+-------+--------------
 public | mtest | table | enterprisedb
(1 row)

mnode=# select count(*) from mtest;
 count
-------
    10
(1 row)

mnode=# insert into mtest values(generate_series(11,100));
INSERT 0 90


[enterprisedb@localhost ~]$ /usr/bin/java -jar /opt/PostgresPlus/EnterpriseDB-xDBReplicationServer/bin/edb-repcli.jar -dosynchronize pub95 -repsvrfile /home/enterprisedb/pubrepsvrfile -repgrouptype m
Performing synchronize...
Publication synchronized successfully.

edb=# \c mmr
You are now connected to database "mmr" as user "enterprisedb".
mmr=# select count(*) from mtest;
 count
-------
    10
(1 row)


mmr=# select count(*) from mtest;
 count
-------
   100
(1 row)


mmr=# create table mtest1(id int primary key);
CREATE TABLE
mmr=# insert into mtest1 values(generate_series(1,100));
INSERT 0 100
mmr=# create table mtest2(id int primary key);        
CREATE TABLE
mmr=# insert into mtest2 values(generate_series(1,100));
INSERT 0 100


/usr/bin/java -jar /opt/PostgresPlus/EnterpriseDB-xDBReplicationServer/bin/edb-repcli.jar -addtablesintopub pub95 -repsvrfile /home/enterprisedb/pubrepsvrfile -tables public.mtest1 public.mtest2 -repgrouptype M
Adding tables to publication pub95 ...

Tables:[[public.mtest1, TABLE], [public.mtest2, TABLE]]
Filter clause:[]
Conflict Resolution Option:[ Earliest Timestamp, Earliest Timestamp ]
Standby Conflict Resolution Option:[ Manual, Manual ]
Replicating published table(s) to other Master Nodes...
Publication updated successfully


[enterprisedb@localhost ~]$ /usr/bin/java -jar /opt/PostgresPlus/EnterpriseDB-xDBReplicationServer/bin/edb-repcli.jar -dommrsnapshot pub95 -pubhostdbid 5 -repsvrfile /home/enterprisedb/pubrepsvrfile
Performing snapshot...
Setting any pending transactions status to cancelled...
Running EnterpriseDB Migration Toolkit (Build 49.0.3) ...
Source database connectivity info...
conn =jdbc:edb://localhost:5444/mmr
user =enterprisedb
password=******
Target database connectivity info...
conn =jdbc:edb://localhost:5444/mnode
user =enterprisedb
password=******
Connecting with source EnterpriseDB database server...
Connected to EnterpriseDB, version '9.5.0.5'
Connecting with target EnterpriseDB database server...
Connected to EnterpriseDB, version '9.5.0.5'
Importing enterprisedb schema public...
Table List: 'mtest','mtest1','mtest2'
Loading Table Data in 8 MB batches...
Disabling FK constraints & triggers on public.mtest before truncate...
Truncating table mtest before data load...
Disabling indexes on public.mtest before data load...
Loading Table: mtest ...
[mtest] Migrated 100 rows.
[mtest] Table Data Load Summary: Total Time(s): 0.212 Total Rows: 100
Disabling FK constraints & triggers on public.mtest1 before truncate...
Truncating table mtest1 before data load...
Disabling indexes on public.mtest1 before data load...
Loading Table: mtest1 ...
[mtest1] Migrated 100 rows.
[mtest1] Table Data Load Summary: Total Time(s): 0.014 Total Rows: 100
Disabling FK constraints & triggers on public.mtest2 before truncate...
Truncating table mtest2 before data load...
Disabling indexes on public.mtest2 before data load...
Loading Table: mtest2 ...
[mtest2] Migrated 100 rows.
[mtest2] Table Data Load Summary: Total Time(s): 0.622 Total Rows: 100
Enabling FK constraints & triggers on public.mtest...
Enabling indexes on public.mtest after data load...
Enabling FK constraints & triggers on public.mtest1...
Enabling indexes on public.mtest1 after data load...
Enabling FK constraints & triggers on public.mtest2...
Enabling indexes on public.mtest2 after data load...
Performing ANALYZE on EnterpriseDB database...
Data Load Summary: Total Time (sec): 0.959 Total Rows: 300 Total Size(MB): 0.0

Schema public imported successfully.


Migration process completed successfully.

Migration logs have been saved to /var/log/xdb-6.0

******************** Migration Summary ********************
Tables: 3 out of 3

Total objects: 3
Successful count: 3
Failed count: 0
Invalid count: 0

*************************************************************
Snapshot taken successfully.


mnode=# select count(*) from mtest1;
 count
-------
   100
(1 row)

mnode=# select count(*) from mtest2;
 count
-------
   100
(1 row)

[enterprisedb@localhost ~]$ /usr/bin/java -jar /opt/PostgresPlus/EnterpriseDB-xDBReplicationServer/bin/edb-repcli.jar -printpublishedtables pub95 -repsvrfile /home/enterprisedb/pubrepsvrfile
Printing tables under publication: pub95

public.mtest
public.mtest1
public.mtest2

[root@localhost tmp]# more col.txt
alter table public.mtest add column name varchar(10);

[enterprisedb@localhost ~]$ /usr/bin/java -jar /opt/PostgresPlus/EnterpriseDB-xDBReplicationServer/bin/edb-repcli.jar -replicateddl pub95 -table public.mtest -repsvrfile /home/enterprisedb/pubrepsvrfile  -ddlscriptfile /tmp/col.txt
DDL changes successfully replicated to all database nodes.

[root@localhost tmp]# more col.txt
alter table public.mtest1 add column name varchar(10);

[enterprisedb@localhost ~]$ /usr/bin/java -jar /opt/PostgresPlus/EnterpriseDB-xDBReplicationServer/bin/edb-repcli.jar -replicateddl pub95 -table public.mtest1  -repsvrfile /home/enterprisedb/pubrepsvrfile  -ddlscriptfile /tmp/col.txt
DDL changes successfully replicated to all database nodes.


mnode=# \d+ mtest
                                Table "public.mtest"
 Column |         Type          | Modifiers | Storage  | Stats target | Description
--------+-----------------------+-----------+----------+--------------+-------------
 id     | integer               | not null  | plain    |              |
 name   | character varying(10) |           | extended |              |
Indexes:
    "mtest_pkey" PRIMARY KEY, btree (id)
Triggers:
    rrpd_public_mtest AFTER DELETE ON mtest FOR EACH ROW EXECUTE PROCEDURE rrpd_public_mtest_tgfunc()
    rrpi_public_mtest AFTER INSERT ON mtest FOR EACH ROW EXECUTE PROCEDURE rrpi_public_mtest_tgfunc()
    rrpu_public_mtest AFTER UPDATE ON mtest FOR EACH ROW WHEN (old.* IS DISTINCT FROM new.*) EXECUTE PROCEDURE rrpu_public_mtest_tgfunc()

mnode=# \d+ mtest1
                               Table "public.mtest1"
 Column |         Type          | Modifiers | Storage  | Stats target | Description
--------+-----------------------+-----------+----------+--------------+-------------
 id     | integer               | not null  | plain    |              |
 name   | character varying(10) |           | extended |              |
Indexes:
    "mtest1_pkey" PRIMARY KEY, btree (id)
Triggers:
    rrpd_public_mtest1 AFTER DELETE ON mtest1 FOR EACH ROW EXECUTE PROCEDURE rrpd_public_mtest1_tgfunc()
    rrpi_public_mtest1 AFTER INSERT ON mtest1 FOR EACH ROW EXECUTE PROCEDURE rrpi_public_mtest1_tgfunc()
    rrpu_public_mtest1 AFTER UPDATE ON mtest1 FOR EACH ROW WHEN (old.* IS DISTINCT FROM new.*) EXECUTE PROCEDURE rrpu_public_mtest1_tgfunc()


mmr=# insert into mtest1 values(generate_series(101,150),'venkat');
INSERT 0 50
mmr=# insert into mtest values(generate_series(101,150),'venkat');
INSERT 0 50


[enterprisedb@localhost ~]$ /usr/bin/java -jar /opt/PostgresPlus/EnterpriseDB-xDBReplicationServer/bin/edb-repcli.jar -confschedulemmr 1 -pubname pub95 -repsvrfile /home/enterprisedb/pubrepsvrfile -realtime 10
Configuring scheduler ...
Job is successfully scheduled.


mnode=# select count(*) from mtest;
 count
-------
   150
(1 row)

mnode=# select * from mtest where id >100 limit 10;
 id  |  name
-----+--------
 101 | venkat
 102 | venkat
 103 | venkat
 104 | venkat
 105 | venkat
 106 | venkat
 107 | venkat
 108 | venkat
 109 | venkat
 110 | venkat
(10 rows)


mnode=# select count(*) from mtest1;
 count
-------
   150
(1 row)

mnode=# select * from mtest1 where id >100 limit 10;
 id  |  name
-----+--------
 101 | venkat
 102 | venkat
 103 | venkat
 104 | venkat
 105 | venkat
 106 | venkat
 107 | venkat
 108 | venkat
 109 | venkat
 110 | venkat
(10 rows)


[enterprisedb@localhost ~]$ /usr/bin/java -jar /opt/PostgresPlus/EnterpriseDB-xDBReplicationServer/bin/edb-repcli.jar  -printconfresolutionstrategy pub95 -repsvrfile /home/enterprisedb/pubrepsvrfile -table public.mtest
Primary/Standby Conflict Resolution Strategy...
Conflict Resolution Option:[ Earliest Timestamp ]
Standby Conflict Resolution Option:[ Manual ]


[enterprisedb@localhost ~]$ /usr/bin/java -jar /opt/PostgresPlus/EnterpriseDB-xDBReplicationServer/bin/edb-repcli.jar -cleanshadowhistforpub pub95 -repsvrfile /home/enterprisedb/pubrepsvrfile -mmrdbid 1
Removing shadow table's transaction history ...

Shadow tables transaction history removed successfully for DB ID 1
Shadow tables transaction history removed successfully.


[enterprisedb@localhost ~]$ /usr/bin/java -jar /opt/PostgresPlus/EnterpriseDB-xDBReplicationServer/bin/edb-repcli.jar -printschedule pub95 -repsvrfile  /home/enterprisedb/pubrepsvrfile -repgrouptype m
Printing publication schedule...

Job type        Synchronize

Scheduled time        2016-02-13 10:15:04

Previous fire time    2016-02-15 02:13:14

Next fire time        2016-02-15 02:13:24

Simple xDB Replication Server CLI Steps.

Please find below simple xDB Replication Server CLI Steps.


The following on the command line or add it to your profile:


export PATH=/opt/PostgresPlus/9.2AS/jre/bin:$PATH


The following is an example of repsvrfile for a pubrepsvrfile server:


host=localhost

port=9051

user=enterprisedb

# Password is in encrypted form.

password=ygJ9AxoJEX854elcVIJPTw==

The following is an example of repsvrfile for a subrepsvrfile server:


host=localhost

port=9052

user=enterprisedb

# Password is in encrypted form.

password=ygJ9AxoJEX854elcVIJPTw==


These files can be located in any directory as long as they can be

read by the operating system user running the xDB Replication Server

CLI.


In your file, be sure to replace the values of the following fields

with the values for your publication server or subscription server:



-encrypt -input <file> -output <file> Encrypts input file to output file


Example:


/opt/PostgresPlus/9.2AS/jre/bin/java -jar

/opt/PostgresPlus/9.2AS/bin/edb-repcli.jar -encrypt -input

/opt/PostgresPlus/9.2AS/jre/bin/infile -output

/opt/PostgresPlus/9.2AS/bin/pwdfile


Publication:

============

Adds publication database


-addpubdb -repsvrfile <file> -dbtype {oracle | enterprisedb |

postgresql | sqlserver} -dbhost <host> -dbport <port> -dbuser <user>

{-dbpassword <encpassword> | dbpassfile <file>} -database {<database>

| <service>} [-oraconnectiontype {sid | servicename}] [-repgrouptype

{m | s}] [-initialsnapshot] [-nodepriority {1 to 10}]

[-replicatepubschema {true|false}] Adds publication database


Example:

/opt/PostgresPlus/9.2AS/jre/bin/java -jar /opt/PostgresPlus/9.2AS/bin/edb-repcli.jar -addpubdb -repsvrfile

/opt/PostgresPlus/9.2AS/bin/pubrepsvrfile -dbtype enterprisedb -dbhost

localhost -dbport 5444 -dbuser enterprisedb -dbpassword

N7Ryv4TGFInSPnvctbilyg== -database x -repgrouptype S


-printpubdbids -repsvrfile <file>


Example:

/opt/PostgresPlus/9.2AS/jre/bin/java -jar

/opt/PostgresPlus/9.2AS/bin/edb-repcli.jar  -printpubdbids -repsvrfile

/opt/PostgresPlus/9.2AS/bin/pubrepsvrfile


-printpubdbidsdetails -repsvrfile <file>


Example:

/opt/PostgresPlus/9.2AS/jre/bin/java -jar

/opt/PostgresPlus/9.2AS/bin/edb-repcli.jar  -printpubdbidsdetails

-repsvrfile /opt/PostgresPlus/9.2AS/bin/pubrepsvrfile


-removepubdb -repsvrfile <file> -pubdbid <id>


Example:

/opt/PostgresPlus/9.2AS/jre/bin/java -jar

/opt/PostgresPlus/9.2AS/bin/edb-repcli.jar -removepubdb -repsvrfile

/opt/PostgresPlus/9.2AS/bin/pubrepsvrfile -pubdbid 36


-createpub <pubName> -repsvrfile <file> -pubdbid <id> -reptype {T|S}

-tables <schema1>.<table1> [<schema1>.<table2>...] [-views

<schema1>.<view1> [<schema1>.<view2>...]] [-tablesfilterclause

<index1>:<clause> [<index1>:<clause>...]] [-viewsfilterclause

<index1>:<clause> [<index2>:<clause>...]][-conflictresolution

<index1>:<{E|L|N|M}> [<index2>:<{E|L|N|M}>...]]

[-standbyconflictresolution <index1>:<{E|L|N|M}>

[<index2>:<{E|L|N|M}>...]] [-repgrouptype {M|S}]


Example:

/opt/PostgresPlus/9.2AS/jre/bin/java -jar

/opt/PostgresPlus/9.2AS/bin/edb-repcli.jar -createpub pub93

-repsvrfile /opt/PostgresPlus/9.2AS/bin/pubrepsvrfile -pubdbid 36

-reptype T -tables public.x -repgrouptype S


-validatepubs -repsvrfile <file> -pubdbid <id> -repgrouptype {m|s}


Example:

/opt/PostgresPlus/9.2AS/jre/bin/java -jar

/opt/PostgresPlus/9.2AS/bin/edb-repcli.jar -validatepubs -repsvrfile

/opt/PostgresPlus/9.2AS/bin/pubrepsvrfile -pubdbid 36 -repgrouptype s


Subscription:

=============


Adds subscription database


-addsubdb -repsvrfile <file> -dbtype {oracle | enterprisedb |

postgresql | sqlserver} -dbhost <host> -dbport <port> -dbuser <user>

{-dbpassword <encpassword> | -dbpassfile <file>} -database {<database>

| <service>}  [-oraconnectiontype {sid | servicename}]


Example:

/opt/PostgresPlus/9.2AS/jre/bin/java -jar

/opt/PostgresPlus/9.2AS/bin/edb-repcli.jar -addsubdb -repsvrfile

/opt/PostgresPlus/9.2AS/bin/subrepsvrfile -dbtype enterprisedb -dbhost

localhost -dbport 5445 -dbuser enterprisedb -dbpassword

N7Ryv4TGFInSPnvctbilyg== -database y


-printsubdbids -repsvrfile <file>


Example:

/opt/PostgresPlus/9.2AS/jre/bin/java -jar

/opt/PostgresPlus/9.2AS/bin/edb-repcli.jar -printsubdbids -repsvrfile

/opt/PostgresPlus/9.2AS/bin/subrepsvrfile


-printsubdbidsdetails -repsvrfile <file>


Example: Prints subscriptions list

/opt/PostgresPlus/9.2AS/jre/bin/java -jar

/opt/PostgresPlus/9.2AS/bin/edb-repcli.jar -printsubdbidsdetails

-repsvrfile /opt/PostgresPlus/9.2AS/bin/subrepsvrfile


-printsublist -repsvrfile <file> -subdbid <id>


Example:

/opt/PostgresPlus/9.2AS/jre/bin/java -jar

/opt/PostgresPlus/9.2AS/bin/edb-repcli.jar -printsublist -repsvrfile

/opt/PostgresPlus/9.2AS/bin/subrepsvrfile -subdbid 51


-createsub <subname> -subdbid <id> -subsvrfile <file> -pubsvrfile

<file> -pubname <pubName>


Example:

/opt/PostgresPlus/9.2AS/jre/bin/java -jar

/opt/PostgresPlus/9.2AS/bin/edb-repcli.jar -createsub sub93 -subdbid

151 -subsvrfile /opt/PostgresPlus/9.2AS/bin/subrepsvrfile -pubsvrfile

/opt/PostgresPlus/9.2AS/bin/pubrepsvrfile -pubname pub93


-dosnapshot <subname> -repsvrfile <file>


Example:

/opt/PostgresPlus/9.2AS/jre/bin/java -jar

/opt/PostgresPlus/9.2AS/bin/edb-repcli.jar -dosnapshot sub93

-repsvrfile /opt/PostgresPlus/9.2AS/bin/subrepsvrfile


-dosynchronize {<subname> | <pubname>} -repsvrfile {<subsvrfile> |

<pubsvrfile>} [-repgrouptype {s|m}]


Example:

/opt/PostgresPlus/9.2AS/jre/bin/java -jar

/opt/PostgresPlus/9.2AS/bin/edb-repcli.jar -dosynchronize sub93

-repsvrfile /opt/PostgresPlus/9.2AS/bin/subrepsvrfile -repgrouptype s


-removesub <subname> -repsvrfile <file>


Example:

/opt/PostgresPlus/9.2AS/jre/bin/java -jar

/opt/PostgresPlus/9.2AS/bin/edb-repcli.jar -removesub sub93

/opt/PostgresPlus/9.2AS/bin/subrepsvrfile

How to launch the StackBuilder UI in putty?

Please find below steps to launch the StackBuilder UI.

Step-1
======
Xming is a PC X Window Server. This enables programs being run remotely to be displayed on your desktop. Download and run the installation program from: http://sourceforge.net/projects/xming/

Navigate to the Files section and download:

Xming setup from the Xming folder the fonts package installer from the Xming-fonts folder

By default both programs will be installed into the same location, so don't the worry about over writing files. We cannot work without both packages.
Once installed, running All Programs > Xming > XLaunch is a good idea to see what the configuration looks like. In most cases, the default options should be just fine.
Finally run All Programs > Xming > Xming to start the PC X Server. The “X” icon should be visible on the Windows Taskbar, as in the image below. The X Server must be started
before setting up a SSH connection to a campus machine.

Step-2
======

In putty window you need to change like below:

X display location = localhost:0 to X display location = 127.0.0.1:0


Step-3
======
You can lunch the StackBuilder UI as below:
Try xclock

[root@localhost ~]# xclock
[root@localhost ~]# cd /opt/PostgresPlus/9.1AS/stackbuilderplus/scripts/
[root@localhost scripts]# ./runStackBuilderPlus.sh

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)

Setup a time-lagging Dr PostgreSQL - 9.4

Step-1

[enterprisedb@localhost ~]$ psql -p 5444

psql.bin (9.5.0.5)

Type "help" for help.

edb=# select pg_create_physical_replication_slot('test_95');

pg_create_physical_replication_slot

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

(test_95,)

(1 row)

Step-2

[enterprisedb@localhost ~]$ pg_basebackup -P -R -X stream -c fast -h localhost -U enterprisedb -p 5444 -D /tmp/slave

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

edb=# \! more /tmp/slave/recovery.conf

standby_mode = 'on'

primary_conninfo = 'user=enterprisedb host=localhost port=5444'

primary_slot_name = 'test_95'

recovery_min_apply_delay = 1min

[enterprisedb@localhost ~]$ pg_ctl -D /tmp/slave/ start

server starting

[enterprisedb@localhost ~]$ 2016-05-03 23:51:51 PDT LOG: redirecting log output to logging collector process

2016-05-03 23:51:51 PDT HINT: Future log output will appear in directory "pg_log".

[enterprisedb@localhost ~]$ psql -p 5447

psql.bin (9.5.0.5)

Type "help" for help.

edb=# select pg_is_in_recovery();

pg_is_in_recovery

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

t

(1 row)

Step-3

edb=# select * from pg_replication_slots;

slot_name | plugin | slot_type | datoid | database | active | active_pid | xmin | catalog_xmin | restart_lsn

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

test_95 | | physical | | | t | 11930 | | | 0/6000220

(1 row)

edb=# create table test_rep(id int primary key, name varchar(10));

CREATE TABLE

edb=# \dt

enterprisedb | test_rep | table | enterprisedb

public | dept | table | enterprisedb

public | emp | table | enterprisedb

public | jobhist | table | enterprisedb

edb=# \timing on

Timing is on.

edb=# insert into test_rep values(200000,'sample');

INSERT 0 1

Time: 3.740 ms

Step-4

edb=# \dt

public | dept | table | enterprisedb

public | emp | table | enterprisedb

public | jobhist | table | enterprisedb

edb=# \dt

public | dept | table | enterprisedb

public | emp | table | enterprisedb

public | jobhist | table | enterprisedb

edb=# \dt

enterprisedb | test_rep | table | enterprisedb

public | dept | table | enterprisedb

public | emp | table | enterprisedb

public | jobhist | table | enterprisedb

edb=# \timing on

Timing is on.

Time: 0.365 ms

edb=# select * from test_rep where id=200000;

Time: 0.365 ms

edb=# select * from test_rep where id=200000;

Time: 0.365 ms

edb=# select * from test_rep where id=200000;

Time: 0.356 ms

edb=# select * from test_rep where id=200000;

Time: 0.357 ms

edb=# select * from test_rep where id=200000;

Time: 0.366 ms

edb=# select * from test_rep where id=200000;

Time: 0.355 ms

edb=# select * from test_rep where id=200000;

Time: 0.284 ms

edb=# select * from test_rep where id=200000;

200000 | sample