[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

replicating postgresql database

Posted on 2016-10-03
4
Medium Priority
?
143 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
  • 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 2000 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 Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Steps to fix error: “Couldn’t mount the database that you specified. Specified database: HU-DB; Error code: An Active Manager operation fail”
There are literally thousands of Exchange recovery applications out there. So how do you end up picking one that’s ideal for your business & purpose? By carefully scouting the product’s features, the benefits it offers you, & reading ample reviews f…
This tutorial will walk an individual through locating and launching the BEUtility application to properly change the service account username and\or password in situation where it may be necessary or where the password has been inadvertently change…
Planning to migrate your EDB file(s) to a new or an existing Outlook PST file? This video will guide you how to convert EDB file(s) to PST. Besides this, it also describes, how one can easily search any item(s) from multiple folders or mailboxes…

608 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