Solved

replicating postgresql database

Posted on 2016-10-03
4
74 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 39

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 39

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

One of the frustrating downsides to using third party RAID hardware is the frequent lack of native driver support in the standard OS.  During install, Windows prompts for third party storage drivers from CD or USB so it is straightforward, but it ca…
In this article we have discussed the manual scenarios to recover data from Windows 10 through some backup and recovery tools which are offered by it.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
In this Micro Tutorial viewers will learn how to use Boot Corrector from Paragon Rescue Kit Free to identify and fix the boot problems of Windows 7/8/2012R2 etc. As an example is used Windows 2012R2 which lost its active partition flag (often happen…

830 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