[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 133
  • Last Modified:

replicating postgresql database

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
onlinerack
Asked:
onlinerack
  • 2
  • 2
1 Solution
 
lcohanDatabase AnalystCommented:
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
 
onlinerackAuthor Commented:
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
 
lcohanDatabase AnalystCommented:
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
 
onlinerackAuthor Commented:
That gives me enough to investigate what I need.

Thank you.

J
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now