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


Hope this helps

How to create symlink for pg_xlog when all ready in production.

1. Need create direcotry where you want to link directory:

Example:

mkdir /pg_xlog/pg_xlog92

chown -R postgres:postggre /pg_xlog/pg_xlog92

2. By using rsync command sync data from pg_xlog to /pg_xlog/pg_xlog92

Example:

rsync -r /pg_xlog/  /pg_xlog/pg_xlog92

3. Shutdown the cluster and move the pg_xlog as pg_xlog_old.

Example:

mv pg_xlog pg_xlog_old

4. Move pg_xlog92 from pg_xlog_old

Example:

cd pg_xlog

mv pg_xlog ../

5. Start your cluster.

Slony Replication Steps to configure A to B and A to C servers

1. Steps for server A to B:

a. Created below scripts for configure slony A to B.

i)initalize_IGRTEST.slonik

## Script for Initialization.

cluster name = igrtest;
node 1 admin conninfo = 'dbname=registrationlive host=192.168.137.158 user=postgres port=5432 password=adminedb';
node 2 admin conninfo = 'dbname=registrationlive host=192.168.137.163 user=postgres port=5432 password=adminedb';

init cluster (id = 1, comment = 'Primary node for slave');
#### Setting Store Nodes ###
store node (id = 2, event node = 1, comment = 'Slave Node For The Primary Node 1');
#### Storing all nodes in the Slony-I catalogs in schema _igrtest
store path(server = 1, client = 2, conninfo = 'dbname=registrationlive host=192.168.137.158 user=postgres port=5432 password=adminedb');
store path(server = 2, client = 1, conninfo = 'dbname=registrationlive host=192.168.137.163 user=postgres port=5432 password=adminedb');
[postgres@localhost slon_test]$


ii)create_set_SLONTEST.slonik

## Script for Create_set

cluster name = igrtest;
node 1 admin conninfo = 'dbname=registrationlive host=192.168.137.158 user=postgres port=5432 password=adminedb';
node 2 admin conninfo = 'dbname=registrationlive host=192.168.137.163 user=postgres port=5432 password=adminedb';

#-----------------------------
# Creating sets for Tables   
#-----------------------------
#---- Set 1 --------#

try { create set (id = 1, origin = 1, comment = 'Set 1 for shadow'); } on error { echo 'Failed...set 1'; exit 1;}
echo 'Set 1 ...created';

set add table (set id = 1, origin = 1, id = 1, full qualified name = 'igr.igr_test', comment = 'Table igr.igr_test with primary key');
echo 'PKey table *** igr.igr_test *** added.';

#---- Set 2 --------#

try { create set (id = 2, origin = 1, comment = 'Set 2 for shadow'); } on error { echo 'Failed...set 2'; exit 1;}
echo 'Set 2 ...created';

set add table (set id = 2, origin = 1, id = 2, full qualified name = 'igr.igr_slon', comment = 'Table igr.igr_slon with primary key');
echo 'PKey table *** igr.igr_slon *** added.';

iii)Start primary and slave slony demons by using below script's.

Primary_start.sh
================

#!/bin/bash

DATE=$(date +%G%m%d)
SLONLOG="/tmp/slon_test/primary_$DATE.log"

/opt/PostgreSQL/9.2/bin/slon -s 1000 -d0 slontest 'host=192.168.137.158 dbname=slontest user=postgres password=adminedb  port=5432' > $SLONLOG 2>&1 &

exit

Slave_start.sh
===============

#!/bin/bash
#
#
DATE=$(date +%G%m%d)
SLONLOG="/tmp/slon_test/slave_$DATE.log"

/opt/PostgreSQL/9.2/bin/slon -s 1000 -d0 slontest 'host=192.168.137.163 dbname=slontest user=postgres password=adminedb port=5432' > $SLONLOG 2>&1 &

exit


iv)Subscribe_set_IGRTEST.slonik

## Script for subscribe_set

cluster name = igrtest;
node 1 admin conninfo = 'dbname=registrationlive host=192.168.137.158 user=postgres port=5432 password=adminedb';
node 2 admin conninfo = 'dbname=registrationlive host=192.168.137.163 user=postgres port=5432 password=adminedb';

try { subscribe set (id = 1, provider = 1 , receiver = 2, forward = yes, omit copy = false); } on error { exit 1;}
echo 'Set 1 subscribed to nodes 1';
try { subscribe set (id = 2, provider = 1 , receiver = 2, forward = yes, omit copy = false); } on error { exit 1;}
echo 'Set 2 subscribed to nodes 1';


2. Steps for server A to C:

a) Created below scripts for configure slony A to C.

i) initalize_NEWBUILD.slonik

## Script for Initialization.

cluster name = newbuild;
node 3 admin conninfo = 'dbname=registrationlive host=192.168.137.158 user=postgres port=5432 password=adminedb';
node 4 admin conninfo = 'dbname=registrationlive host=192.168.137.164 user=postgres port=5432 password=adminedb';

init cluster (id = 3, comment = 'Primary node for slave');
#### Setting Store Nodes ###
store node (id = 4, event node = 3, comment = 'Slave Node For The Primary Node 3');
#### Storing all nodes in the Slony-I catalogs in schema _newbuild
store path(server = 3, client = 4, conninfo = 'dbname=registrationlive host=192.168.137.158 user=postgres port=5432 password=adminedb');
store path(server = 4, client = 3, conninfo = 'dbname=registrationlive host=192.168.137.164 user=postgres port=5432 password=adminedb');

ii) create_set_NEWBUILD.slonik

## Script for Create_set

cluster name = newbuild;
node 3 admin conninfo = 'dbname=registrationlive host=192.168.137.158 user=postgres port=5432 password=adminedb';
node 4 admin conninfo = 'dbname=registrationlive host=192.168.137.164 user=postgres port=5432 password=adminedb';

#-----------------------------
# Creating sets for Tables   
#-----------------------------
#---- Set 3 --------#

try { create set (id = 3, origin = 3, comment = 'Set 3 for shadow'); } on error { echo 'Failed...set 3'; exit 1;}
echo 'Set 3 ...created';

set add table (set id = 3, origin = 3, id = 3, full qualified name = 'igr.igr_test', comment = 'Table igr.igr_test with primary key');
echo 'PKey table *** igr.igr_test *** added.';

#---- Set 3 --------#

try { create set (id = 4, origin = 3, comment = 'Set 4 for shadow'); } on error { echo 'Failed...set 4'; exit 1;}
echo 'Set 4 ...created';

set add table (set id = 4, origin = 3, id = 4, full qualified name = 'igr.igr_slon', comment = 'Table igr.igr_slon with primary key');
echo 'PKey table *** igr.igr_slon *** added.';


iii)Start primary and slave slony demons by using below script's.

Primary_start.sh
================

#!/bin/bash

DATE=$(date +%G%m%d)
SLONLOG="/tmp/slon_test/primary_$DATE.log"

/opt/PostgreSQL/9.2/bin/slon -s 1000 -d0 newbuild 'host=192.168.137.158 dbname=slontest user=postgres password=adminedb  port=5432' > $SLONLOG 2>&1 &

exit

Slave_start.sh
===============

#!/bin/bash
#
#
DATE=$(date +%G%m%d)
SLONLOG="/tmp/slon_test/slave_$DATE.log"

/opt/PostgreSQL/9.2/bin/slon -s 1000 -d0 slontest 'host=192.168.137.164 dbname=slontest user=postgres password=adminedb port=5432' > $SLONLOG 2>&1 &

exit

iv) subscribe_set_NEWBUILD.slonik

## Script for subscribe_set

cluster name = newbuild;
node 3 admin conninfo = 'dbname=registrationlive host=192.168.137.158 user=postgres port=5433 password=adminedb';
node 4 admin conninfo = 'dbname=registrationlive host=192.168.137.164 user=postgres port=5432 password=adminedb';

try { subscribe set (id = 3, provider = 3 , receiver = 4, forward = yes, omit copy = false); } on error { exit 1;}
echo 'Set 3 subscribed to nodes 3';
try { subscribe set (id = 3, provider = 3 , receiver = 4, forward = yes, omit copy = false); } on error { exit 1;}
echo 'Set 4 subscribed to nodes 3';

How to configure cascading replication ?

Cascading Replication Steps

Streaming slave – Slave1

Cascading slave – Slave2

Streaming Replication from primary to slave1 [Steps 1- 11]

Cascading Replication from slave1 to slave2 [Steps 12- 18]

1. Modify the below parameters on primary server postgresql.conf:

archive_command= 'cp %p /home/postgres/arch/%f' [Example]

wal_level=hot_standby

max_wal_senders = 5

wal_keep_segments = 32

2. Take an online backup of primary server

select pg_start_backup('rep');

3. Create a new directory on slave1[Eg: /test/streaming] and copy

the data directory of primary to slave1 server.

Eg: cp /opt/PostgreSQL/9.2/data slave1:/test/streaming

4. After the copy is complete, stop the online backup on primary.

select pg_stop_backup();

5. Copy the required wal's from primary to slave1.

Eg: cp /home/postgres/arch/[required wals]* slave1:/home/

6. Create a recovery.conf file on slave1 server data directory:

standby_mode = 'on'

primary_conninfo = 'host=[IP address of primary] port=5432

user=postgres'

trigger_file = '/tmp/standby.trg'

restore_command = 'cp /home/postgres/arch/%f %p'

7. Modify the below parameters on slave1 postgresql.conf:

8. Add the primary server entry in pg_hba.conf of slave1:

For Eg: host replication all [IP address of primary]/32 trust

9. Write a command so that wals are being copied continuously from

Eg: rsync –a -z primary:/home/postgres/arch slave1:/home/

10. Remove the postmaster.pid on slave1 data directory.

Eg: In location /test/streaming

rm –rf postmaster.pid

11.Now, start the slave1 server and verify whether it is up and

running and verify the logs

/opt/PostgreSQL/9.2/bin/pg_ctl -D /test/streaming [data

12. Cascading replication from slave1 to slave2

Create a new directory on slave1[Eg: /test/cascading] and copy the

data directory of slave1 to slave2 and the required wals.

Eg: cp /test/streaming slave2:/test/cascading [data directory copy]

cp /home/postgres/arch/[required wals]* slave1:/home/postgres/arch [wals copy]

13. Modify the max_wal_senders = 3 in slave1 postgresql.conf for cascading replication.

- max_wal_senders value in slave1[streaming slave] should be lesser than the value of the one in primary.

14. On slave2 server add a recovery.conf file in data directory:

standby_mode = 'on'

primary_conninfo = 'host=[IP address of slave1] port=5432

user=postgres'

trigger_file = '/tmp/standby.trg'

restore_command = 'cp /home/postgres/arch/%f %p'

15. Add the slave1 server entry in pg_hba.conf of slave2:

For Eg: host replication all [IP address of slave1]/32 trust

16. Make sure that wals are being copied continuously from slave1 to

Eg: rsync -a –z slave1:/home/postgres/arch slave2:/home/

17. Remove the postmaster.pid on slave2 data directory.

Eg: In location /test/cascading

rm –rf postmaster.pid

18. Now, start the slave2 cluster.

/opt/PostgreSQL/9.2/bin /pg_ctl -D /test/cascading[data directory of slave2]

Note: - Make sure that there is a passwordless authentication from primary to slave1 and from slave1 to slave2 for the wal's to be copied. [or else save the wals in a common mount point where primary, slave1 and slave2 can also access].