Solved

replicating postgresql database

Posted on 2016-10-03
4
88 Views
Last Modified: 2016-10-07
Hello,

What are the options for replicating postgresql database between two data centers for DR? (Active/Standby setup for DCs)

Thank you for your help.

J
0
Comment
Question by:onlinerack
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 41828773
I believe that depends a lot from different factors point of view like:
what version/edition of postgres are you using? - community or like EDB for instance?
for the initial replication/snapshot - how big is your database(es)
how fast/slow is the connection between Active and Passive data center location
how much data change needs to be replicated and this is a critical aspect that many don't know or measure therefore the standby lags for hours and maybe even days.

I believe the easiest would be to use "native" PITR - log shipping if you use the community version as described here https://www.postgresql.org/docs/current/static/warm-standby.html where you tar/ship the wall files and aply them on stand by as they arrive.

There are also 3rd party tools like Bucardo, and rubyrep http://www.rubyrep.org/ that you can use.
1
 
LVL 5

Author Comment

by:onlinerack
ID: 41829825
Thank you for your detailed response. I believe log shipping and rubyrep are asynchronous replication. I am looking for synchronous replication for relatively small DB. Any recommendations?

Regards,
J
0
 
LVL 40

Accepted Solution

by:
lcohan earned 500 total points
ID: 41831988
Well in that case please have a look at the "Table 25-1. High Availability, Load Balancing, and Replication Feature Matrix" to see your options.

https://www.postgresql.org/docs/9.0/static/different-replication-solutions.html

I personally used Slony http://www.slony.info/ for upgrades and data center migrations but...please be aware of the constraints.
A friend of mine used DBmirror https://github.com/metabrainz/dbmirror to do something similar to what you need and seems to be a pretty good product
2
 
LVL 5

Author Closing Comment

by:onlinerack
ID: 41833895
That gives me enough to investigate what I need.

Thank you.

J
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Employees depend heavily on their PCs, and new threats like ransomware make it even more critical to protect their important data.
In this Micro Tutorial viewers will learn how to restore their server from Bare Metal Backup image created with Windows Server Backup feature. As an example Windows 2012R2 is used.
This tutorial will walk an individual through the steps necessary to install and configure the Windows Server Backup Utility. Directly connect an external storage device such as a USB drive, or CD\DVD burner: If the device is a USB drive, ensure i…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question