About saurabhs

saurabhs · ‎08-29-2019

Migration from PPCD/On-Prem Over CDS Using EPRS - MMR : Offline Snapshot Most of us know EnterpriseDB has a software package for cross-database Multi-Master Replication(MMR) or Single-Master Replication(SMR) servers that supports an asynchronous replication system to replicate data to EDB Postgres Advanced Server(EPAS) and PostgreSQL . The initial software package name was xDB Replication Server (5.x) and subsequently converted into EDB xDB Replication Server(6.x) and today its called as EDB Postgres Replication Server(7.beta). New EDB Postgres Replication Server 7.Beta(EPRS) , has been re-architected in the incorporation of message streaming using Apache Kafka for replication changed data between database. Apache Kafka is a distributed data streaming platform that can publish, subscribe to, store, and process streams of records in real-time. It is designed to handle data streams from multiple sources and deliver them to multiple consumers. Apache Kafka as the data streaming backbone in new EPRS to improve performance and provide enhanced failover capabilities. We can use MMR capability for bi-directional replication from PPCD cluster to Cloud Database Services(CDS) and vise versa. By using MMR we don’t have to worry about moving incremental changes from CDS to PPCD in case of backout plan. Prerequisites For Master/Source Database Servers (CentOS 7.x) EPRS-node1 EPRS-node2 Dependency softwares Java 1.8 or later Setup Bi-directional passwordless ssh for 'enterprisedb' user between 3 Nodes Open firewall ports for 2181,2881,3881,8081,8082(tcp) EDB Software Packages EDB Postgres Advanced Server 10.x EDB Postgres Replication Server 7.BETA Source DB : Version: EPAS-10 on Centos 7(On PPCD or On Premesis) Target DB: Version: EPAS-10 on Centos 7(On Cloud Database Services) “wal _level” should be logical. “track_commit_timestamp” should be on. “max_wal_sender” should be set with the appropriate value (Ex: 10). “Max_replication_slots” should be set with the appropriate value (Ex: 10). Table should have Primary Key. EPRS Port Should be open in Security Group. (i.e 2881,8082,3881,2182,3882,2181,2882,9092,8081 ) Database Port should be open. ( i.e 5432, 5444) Use only Public DNS name of Instance while adding the publication and Target clusters. Make appropriate pg_hba.conf entries. As, in case of Pgpool it might give the connections timeout error in terms of Idle connections of EPRS setup. Installation and Configuration Steps Start EPRS Server. cd /usr/edb/rs-7.0/server/bin ./runServer.sh --host ec2-34-255-48-133.eu-west-1.compute.amazonaws.com --config /usr/edb/rs-7.0/server/etc & Execute “joinnetwork”. cd /usr/edb/rs-7.0/client/bin ./runRepCLI.sh -joinnetwork -servername localService -host ec2-34-255-48-133.eu-west-1.compute.amazonaws.com -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. ./runRepCLI.sh -adddb -servername localService -dbid db1 -dbtype enterprisedb -dbhost ec2-34-254-213-11.eu-west-1.compute.amazonaws.com -dbport 5444 -dbuser repuser -dbpassword wjOCadkp7oMLvKejd+xm6g== -database migration_test -user admin Create Publication. ./runRepCLI.sh -createpub -pubname deptemppub -servername localService -dbid db1 -nodetype RW -alltables public -user admin Start EPRS Server on Node-2. cd /usr/edb/rs-7.0/server/bin ./runServer.sh --host ec2-18-200-225-14.eu-west-1.compute.amazonaws.com --config /usr/edb/rs-7.0/server/etc & Execute “joinnetwork” command to join the Node-2. Make sure to execute this command from Node-1 ./runRepCLI.sh -joinnetwork -servername remoteService -host ec2-18-200-225-14.eu-west-1.compute.amazonaws.com -port 8082 -user admin Make sure to execute this specific command on Node-1. Add Target Database to the Replication. ./runRepCLI.sh -adddb -servername remoteService -dbid db2 -dbtype enterprisedb -dbhost ec2-18-200-225-14.eu-west-1.compute.amazonaws.com -dbport 5444 -dbuser repuser -dbpassword wjOCadkp7oMLvKejd+xm6g== -database migration_test -user admin Join Target Database to the Publication. ./runRepCLI.sh -joinpub -servername remoteService -dbid db2 -pubname deptemppub -nodetype RW -user admin Start Offline Snapshot. ./runRepCLI.sh -startsnapshot -pubname deptemppub -dbid db2 -offline -user admin ./runRepCLI.sh -checksnapshot -pubname deptemppub -dbid db2 -user admin Once Offline snapshot is done, follow below process to configure Node-2. Take a backup of the old cluster conf files and start Pg_basebackup. 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 I hope this article is useful for migrating data from your existing PPCD or on-prem cluster using EPRS-7 Multi-Master Replication. If you have any questions please feel free to comment.

saurabhs · ‎08-29-2019

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 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? Faster log-based replication Single Master Replication Multi Master Replication 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: Java 1.8 and above. “Wal_level” should be set to “logical”. “track_commit_timestamp” should be on. “max_wal_sender” and “Max_replication_slots” should be set with the appropriate value Table should have Primary Key. 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

saurabhs · ‎08-07-2019

Question Can a cluster be deployed in private subnets within a VPC? Answer Yes. There are different offerings available as cloud solutions by EnterpriseDB. A Managed DBaaS deployment will allow you to use a private subnet within a VPC.

saurabhs · ‎08-07-2019

Question Is it possible to migrate data online to CDS? Answer Yes. It’s possible to migrate your data online from an on-prem or existing cloud database to CDS directly using logical replication or by using EDB Postgres Replication Server(EPRS) .

saurabhs · ‎08-07-2019

Question How do I request a new region? Answer CDS support is available for the listed region consoles in CDS. If you are looking for a new region to run your workloads please send an email to cds-help@enterprisedb.com with your use case and we will make every effort to deliver on your request.

saurabhs · ‎08-07-2019

Question How do I retain long term backups? Answer A user can retain a backup forever. Cloud Database Service (CDS) takes automatic backups of your cluster depending upon the retention period you have set for the cluster. However, you can always take a manual snapshot of the cluster and it will be retained for as long as you want. Retention policies do not apply to manually created backups and the backups will never be deleted even when a user terminates the cluster. To retain a cluster backup for long term please follow the steps below. A. Login to CDS console where the cluster is running, select the cluster name and click on ‘Backup’ icon. B. Provide a unique name as a tag and click ‘Backup’ icon. C. Once the backup is successfully completed, you will be able to see the backup under the Backups tab.

saurabhs · ‎08-07-2019

Hi seandon4, Thank you for raising the concerns with us. To proceed further could you please confirm on below points. 1. Query execution is on Partition Table or Plain Table? 2. Could you please share the Explain Plan for the reported query. (i.e with and without hardcoded values)

saurabhs · ‎07-21-2019

Hi mitchelcarlsen, Thank you for raising the concern with us. Please share the logs so we can analyze the issue further. Log Location: POSTGRES_HOME\.enterprisedb\xdb\x.x\mtk.log Note: POSTGRES_HOME is the home directory of the "Postgres" operating system account OR "enterprisedb" for EDB Advanced Server Installation.

saurabhs · ‎06-13-2019

Hi jsoler, Please find an explanation for the statement. If any transaction gets aborted, the transaction clean up won't occur properly, hence it stops the mainline code from completing the functionality. So, the fixed is provided to protect again above explained scenario in cases where it ought to return to the mainline code, but allow it where there's an interruptible process happening. The timeout can cause such scenarios (statement_timeout). Also, this might also be caused by lock_timeout. Hope this clarifies the raised concerns.

saurabhs · ‎06-04-2019

Hi jwoodsford , By looking at your reported query, there might be multiple reasons which can slow down the database performance. Also, as per mentioned by "alexc168", I/O could be one of the reasons for slowness. However, we would recommend the generic area from the troubleshooting point of view. Monitor the disk I/O as mentioned by "alexc168". Execute the statements in transaction batches. (i.e BEGIN.....COMMIT) Verify there is no blocking issue with parallel UPDATE statements. wal_buffers, checkpoint, bg_writer are tuned enough. If possible replace "INSERT" with "COPY" as it increases the performance. - Refer the following link for the usage of "COPY" statement. https://www.postgresql.org/docs/10/populate.html 6. Network latency. Hope this helps.

saurabhs · ‎05-24-2019

Hi sheila1372, There are no existing statistics table or view which maintains this information on the database. Following are the ways from which you can achieve this : Logging : Setting the logging parameters which will collect the details into the database logs : i. logging_collector -- This will enable logging ii. log_line_prefix -- What all to log iii. log_statement -- Which statements to log Note: This change will cause extra logging. Please make sure adequate disk space is available. Creating Triggers : i. Create an audit table to maintain such records. ii. Create a function which will collect the user name for the respective operation and will insert it into the audit table. iii. Create a trigger on the audit table which will invoke the function created above for DML operations. Note: Please test the trigger creation method is on test environment prior to applying on production, as it might cause the slowness for complex queries.

saurabhs · ‎11-03-2018

Hi davejung, Thank you for reporting the issue with us. We are working on it and we will udpate you soon. Meanwhile, c ould you please confirm on the pgpool version.

saurabhs · ‎08-18-2018

Hi gdedeoglu, Thank you for raising the concern. We will update you soon on the reported issue.

saurabhs · ‎07-30-2018

Hi Moin, PostgreSQL Function would be wise call for your requirement. Function can have below path to drop the child partition table with respective to the provided Master table and retention policy. And then you can call the function by setting it in the crontab. 1. Function should select the child tables with respective to the provided Master table. 2. Soon after receiving child table list. Un-bound the child table depends on the retention policy. (ex. 7 days) 3. And then drop the table after validating the data if it is old enough. Note: Also, we would suggest to test this test case in your Test enviornment, before, trying it on the Prod. Hope this helps.

saurabhs · ‎07-26-2018

Hi Tom, Thank you for your time and patience. As per documentation under supported platforms, EFM-3.1 is compatible with SLES12. However, the builds are not yet available. The documentation and the release information will be updated once the builds are ready. Please let us know in case of any issues/queries.

saurabhs · ‎07-19-2018

Hi Akash, The exception you have received is due to invalid value has been provided for the parameter "SSL Mode". The valid values for the "SSL Mode" parmeter is "Require,Prefer, Disable". Also, find the below link for the reference: http://www.npgsql.org/doc/connection-string-parameters.html Can you please try it once with the above options and let us know if you face the issue again.

saurabhs · ‎07-08-2018

Hi erik , There is no such default password for user Postgres. The password needs to be set. Please find below details: 1. Login to the machine where PostgreSQL is installed. Use below command from terminal to connect to the cluster. psql -U postgres - If the PATH is not set, above command will give an error. Below is the default PATH for PostgreSQL binaries. /Applications/Postgres.app/Contents/Versions/latest/bin/ 2. After successful login use below command to set the password: ALTER ROLE postgres PASSWORD 'postgres'; Hope this helps.

saurabhs · ‎07-06-2018

Hi l gwapnitsky, Using VIP In this case will be wise decision. The VIP remains active on Master and moves on to the Slave in case of failover. Hope this helps.

About saurabhs

Multi-Master Replication Between PPCD/On-Prem And ...

Moving Your Postgres Database from PPCD to Cloud D...

Can a cluster be deployed in private subnets withi...

Is it possible to migrate data online to CDS?

How to request a new region?

How do I retain long term backups?

Re: Simple Query with DBLink - Performance Questio...

Re: xDB Replication Server 6.2.5 - Lost entire su...

Re: Cause of error [SQL STATE: XX000]PANIC: stuck ...

Re: large postgresql database becomes sluggish aft...

Re: How can I make the modified by columns for obj...

Re: pgpool load balance issue black_function_list ...

Re: Integration with Oracle Business Intelligence ...

Re: Automating table partition

Re: Is EFM 3.1 supported in SuSe Linux 12?

Re: How to enable SSL for PostgreSQL running on Wi...

Re: Default password for postgres user?

Re: BART and failover