cancel
Showing results for 
Search instead for 
Did you mean: 

Moving Your Postgres Database from PPCD to Cloud Database Service Cluster Using EPRS-7

PostgreSQL is one of the most advanced Open Source database available in the market now. Let’s just think about how the integration of the Cloud with the Database makes DBA (Database Administrator) life easier and interesting.  

 

Streaming Replication, Backups, High Availability, Minor Patching, Major Upgrades, etc... are now just a click away! And even better news, there is a way to explore all of these features and many more using EDB’s Cloud Database Service (CDS). 

 

So, the first question is how do we migrate our PPCD/On-Prem Database clusters over to CDS? The simple answer is EPRS (EDB Postgres Replication Server).

 

What Is EPRS?

 

EPRS-Rep.png

 

 

  • EPRS is a replication tool offered by EnterpriseDB. It is based on the PostgreSQL logical decoding feature and as a result, every transaction is written in the WAL files, which we use for the purpose of PITR, Streaming Replication, and Disaster Recovery purposes. 
  • EPRS extracts the required changes from the WAL’s and replay it on the designated secondary database.

 

Benefits Of EPRS?

 

  1. Faster log-based replication
  2. Single Master Replication
  3. Multi Master Replication
  4. Integrate with Oracle and SQL Server

 

How It Works?

 

EPRS is based on Kafka, ZooKeeper capability, which is Open Source messaging technology for publishing and subscribing to streams of records in real-time.

 

Data replication occurs when a particular stream of messages (i.e Topics) are divided into various partitions. Each partition has its own unique sequence offset. Such topics are published by the Producers to Kafaka brokers, and the respective Consumers pull data from the Broker (based on where they are subscribed to). 

 

How to Migrate from PPCD/On-Prem to CDS?

 

Prerequisites:

 

  1. Java 1.8 and above.
  2. “Wal_level” should be set to “logical”.
  3. “track_commit_timestamp” should be on.
  4. “max_wal_sender” and “Max_replication_slots” should be set with the appropriate value
  5. Table should have Primary Key.
  6. Database Port Should be open in Security Group.

 

Installation Steps:

 

Install the edb.repo. Update edb.repo with the appropriate credentials. 

 

Let’s pull the EPRS and Install it.

 

yum install edb-rs-*

 

How to configure EPRS with Offline Snapshot to minimize the downtime?

 

Let’s follow the below steps:

 

  • Start EPRS Server.

/usr/edb/rs-7.0/server/bin/runServer.sh --host 127.0.0.1 -c /usr/edb/rs-7.0/server/etc

 

 

  • Execute “joinnetwork”.

/usr/edb/rs-7.0/client/bin/runRepCLI.sh -joinnetwork -servername localService -host 127.0.0.1 -port 8082

 

 

  • Set administrator password

/usr/edb/rs-7.0/client/bin/runRepCLI.sh -setadminpassword -savepassword

 

 

  • Encrypt Database password

/usr/edb/rs-7.0/client/bin/runRepCLI.sh -encrypt -input /tmp/input -output /tmp/output -user admin 

 

 

  • Add Source Database to the Replication.

/usr/edb/rs-7.0/client/bin/runRepCLI.sh -adddb -servername localService -dbid db1 -dbtype enterprisedb -dbhost X.X.X.X -dbport 5444 -dbuser repuser -dbpassword wjOCadkp7oMLvKejd+xm6g== -database edb -user admin

 

 

  • Create Publication.

/usr/edb/rs-7.0/client/bin/runRepCLI.sh -createpub -pubname deptpub -servername localService -dbid db1 -alltables public -nodetype RW -user admin

 

 

  • Add Target Database to the Replication.

/usr/edb/rs-7.0/client/bin/runRepCLI.sh -adddb -servername localService -dbid db2 -dbtype enterprisedb -dbhost X.X.X.X -dbport 5444 -dbuser repuser -dbpassword wjOCadkp7oMLvKejd+xm6g== -database edb -user admin

 

 

  • Join Target Database to the Publication.

/usr/edb/rs-7.0/client/bin/runRepCLI.sh -joinpub -servername localService -dbid db2 -pubname deptpub -nodetype RW -user admin

 

  • Verify the EPRS Schema is created on Publication and Subscription Cluster.

 

 

  • Start Offline Snapshot.

/usr/edb/rs-7.0/client/bin/runRepCLI.sh -startsnapshot -pubname deptpub -dbid db2 -offline -user admin

 

/usr/edb/rs-7.0/client/bin/runRepCLI.sh -checksnapshot -pubname deptpub -dbid db2 -user admin

 

 

  • Execute Pg_Basebackup on Target Cluster.

pg_basebackup -h dbserver_host -D /var/lib/edb/as11/data_db1 -c fast -X stream -U enterprisedb

 

  • Take a backup of the old cluster conf files. 
  • Replace the cluster once the backup is done. Place the backed-up conf files and start the cluster. 

 

  • Start Streaming.

/usr/edb/rs-7.0/client/bin/runRepCLI.sh -startstreaming -pubname deptpub -user admin

 





 

Version history
Revision #:
2 of 2
Last update:
3 weeks ago
Updated by:
 
Labels (1)
Contributors