Tuesday, September 7, 2021

MongoDB Enterprise vs Community vs Percona

 

Introduction

The document tells about the difference between the MongoDB Enterprise, Community, and Percona MongoDB database technology. The document focus on the features.

MongoDB Enterprise and Percona MongoDB, both are based on the MongoDB community edition and with their extensions. The key differences between MongoDB Enterprise and Percona MongoDB against Community open source are as follows. 

  • Technical Support
  • Operations
  • High Availability
  • Backup, Restore and Disaster Recovery tools
  • Reliability 
  • Security

Features

MongoDB Enterprise

OPs Manager - is designed and powerful to assist database administrators, system architects, and performance analysts when administering, monitoring, and tuning MongoDB Server and databases. It effectively assists DBAs on MongoDB

UI based application and can reduce the user errors 

Installation and database upgrade

Server configuration and tuning

Scalability

Database replica sets and high availability/DR

Collection sharding

Server monitoring

Database performance

Query tuning

Backup agent and jobs, etc. 

Percona MongoDB

Percona Monitoring and Management is a best-of-breed open source database monitoring solution. It helps DBAs to reduce some management complexity, help to

Helps to optimize performance

Helps improve the security of business-critical database environments

Community Edition

There are tools available to support the MongoDb community eition, however none of the tool helps to administarte the cluster rather it can monitor the environment. Here are the few tools which mongoDB support to monitor the system

Ref: https://docs.mongodb.com/database-tools/

Technical Support

MongoDB Enterprise subscription provides all kind of MongoDB Technical support, from application coding issues to cluster performance tuning. 

Percona Management and Monitoring for MongoDB (PMM for MongoDB or PSMDB) subscription provides support for Server managements and monitoring, and MongoDB technical support. 

Community edition provides the flexibility and extensibility to the MongoDB feature available in community, and the full access of the MongoDB program and code. 

Operations

MongoDB Enterprise include OPs Manager provide the facilities and GUI tools for all kind of MongoDB administration tasks, such as installation, upgrade, configuration, sharding, tuning, indexing, server node failover and promotion, backup and point-in-timer restore, etc. 

Percona Management and Monitoring for MongoDB (PMM for MongoDB or PSMDB) provides monitoring facilities and some management tools have GUI interface, and for complex functions have to use console scripts. 

Community edition provides console commands for all administration, configuration, monitoring and backup tasks.  

High Availability

All three MongoDB provisions, MongoDB Enterprise and Percona Management and Monitoring for MongoDB and Community edition provide the same level high availability at same configurations, the differences are their management effort, response speed, and operational efficiency. 

Backup, Restore and Disaster Recovery 

MongoDB Enterprise include OPs Manager provide the management for High Availability and data replication, and provides a complete HA and DR solution including backup, replication and disaster recovery. 

RPO - 

RTO -

Reliability

MongoDB Enterprise include Ops Manager provide the MongoDB upgrade and patches with quality tests and also provide warnings for the bugs they found in the MongoDB Community releases. It maintains the enterprise class product. 

Percona Management and Monitoring for MongoDB (PMM for MongoDB or PSMDB) also provide their tested and modified MongoDB upgrade and patches in MongoDB Community releases. 

Security

MongoDB Enterprise include OPs Manager provide all the current encryption, authentication and authorisation capabilities. 

Percona Management and Monitoring for MongoDB (PMM for MongoDB or PSMDB) also provide the key authentication and authorisation capabilities, which meet current VCS MongoDB security requirements. 

PCI and audit compliance

Both MongoDB Enterprise include OPs Manager and Percona Management and Monitoring for MongoDB (PMM for MongoDB or PSMDB) can meet the current PCI-DSS standards and Audit requirements.  

Monitoring

Ops Manger has got rich performance visibility through dozens of optimized charts that highlight the metrics that matter. Easily tailor clients' own monitoring dashboards, integrate with existing APM tools, and send customized alerts to a wide range of endpoints to stay ahead of potential issues.

Percona Management and Monitoring for MongoDB provides the competitive monitoring capabilities to Ops Manager. 

Community edition provides the users total freedom on the implementation of monitoring metrics and schedules, and displays. 

Feature Comparison 

Feature CategoryFeaturesCommunityMongoDB EnterprisePercona Server for MongoDB
EnginesStorageWiredTigerWiredTiger (default)WiredTiger (default)

In-MemoryNoIn-Memory (Enterprise only)Percona Memory Engine





SecurityEncryptionNoKey server = KMIPKey server = Hashicorp Vault

LDAP AuthenticationNoYesSimple LDAP Auth, (legacy) External SASL Authentication

LDAP AuthorizationNoYesYes

Kerberos AuthenticationNoYesYes

Audit Logging NoYesYes

Log RedactionNoYesYes

X509 AuthenticationYesYesYes





Backup and RestoreBackup mongodumpYes (Snapshot, Oplog)Yes (replicaset) - Hot-backup

Continuously BackupNoYes (OpLog)Yes (OpLog)

Point in Time RecoveryNoYes (Snapshot and OpLog of replicaset) Yes (Snapshot and OpLog of replicaset) 





Performance and TuningProfilingNoYes (0, 1, 2)Yes (rateLimit)

Text Search Standard MongoDB text searchStandard MongoDB text search by localeNGRAM and Standard MongoDB text search





InstallationPackagesSource or Binary packagesmongodb-enterprise packagePercona Distribution for MongoDB (psmdb and pbmdb package)





Management ToolsGUI ToolNoOpsManagerPercona Tookit - MongoDB Query Digest, Server Summary; 

ClusterYes (Console Commands)YesYes (Console Commands)

ReplicaSetYes (Console Commands)YesYes (Console Commands)

ShardingYes (Console Commands)YesYes (Console Commands)

MonitoringNoYes (OpsManager)PMM

SNMP MonitoringNoNoYes





Third Party ToolsCompassYes - CommunityYesYes - Community

Studio 3TYesYesYes





Coste.g. for 50 ServersFreeLicense: > $500KSupport: > $225K for 1 Year (7.5K per server in year 2019)


Features in Common

Feature CategoryFeaturesCommunityMongoDB EnterprisePercona Server for MongoDB

Sharding ZonesYesYesYes

Faster Initial SyncYesYesYes

Auto-BalancingYesYesYes

Tuneable ConsistencyYesYesYes

ViewsYesYesYes

mongoreplay ToolYesYesYes

CollationYesYesYes

Faceted SearchYesYesYes

Decimal TypeYesYesYes

Balancer on Config PrimaryYesYesYes

Parallel BalancingYesYesYes

Writes Consistent by DefaultYesYesYes

Linear Read ConcernYesYesYes

Graph DB FunctionsYesYesYes

Faceted SearchYesYesYes

Bucketed AggregationYesYesYes

Sort by Bucket CountYesYesYes

Counts in AggregationYesYesYes

Improved Aggregation ArraysYesYesYes

Improved Aggregation StringsYesYesYes

Add Aggregation Flow controlsYesYesYes

Improved Aggregation DatesYesYesYes

CollStats in AggregationYesYesYes

Add Aggregation Type operatorYesYesYes

Improved profiler and CurrentOpYesYesYes

Aggregation Operators for Type ConversionYesYesYes

Aggregation String OperatorsYesYesYes

Enhancements to Change StreamsYesYesYes


Monday, September 6, 2021

MongoDB Hardware Upgrade

Following are the steps will help us at the time of  hardware enhancement of the MongoDB Nodes.  


Replication Cluster:

Step1 :Login to any server and check for ps -aef | grep mongo , login to node , check for rs.status() and then check for rs.printslaveReplicationInfo() , confirm there should not be any lag between the nodes.

Step2 :login to the secondary server and check for ps -aef | grep mongo , login to secondary  node  and run the below command

   use admin , db.shutdownServer() and handover the server to IT team for uplift

Step3: After reboot of server check for the mongo processes (ps -ef|grep mongo) and login to node and check for replication health status [rs.status() ,rs.printslaveReplicationInfo() etc. ]

Step4 :Repeat step 1 to 3 if any other secondary nodes are present in cluster.

step5: Login to primary server and check for ps -aef | grep mongo , login to node , check for rs.status() and then check for rs.printslaveReplicationInfo() , confirm there should not be any lag between the nodes.

Step6 :Run the below query from the admin db 

 use admin , rs.stepDown()  wait until the preference changes to secondary [confirm Primary node has been elected successfully in the node by using command rs.status()]

           db.shutdownServer() and handover the server to IT team for uplift

Step7 :After reboot of server check for the mongo processes (ps -ef|grep mongo) and login to node and check for replication health status [rs.status() ,rs.printslaveReplicationInfo() etc. ]

Step 8 :If any Arbiter node is present , hardware uplift is not mandatory for this as this node doesn't contain any data.

Sharded Cluster :

Data node : shard_0 , shard_1 , shard_2 etc.

Config server : node_0 , node_1, node_2

Query Router : Router_1 , Router_2 , Router_3 etc.

Data node : Follow the same steps as for replication steps for each server in the shard node.

Config server : Follow the same steps as for replication steps for each server in the config server.

Query Router :

Step 1 :Login to router server and check for ps - aef | grep mongo , and stop the mongo service as [service mongod stop

Note : perform the operation router by router 

step:2 : Handover the server to IT team for hardware uplift , after reboot check for the mongo services [ps - aef| grep mongo]

Monday, May 23, 2016

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

Sunday, May 15, 2016

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

Friday, May 13, 2016

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)