Improve company productivity with a Business Account.Sign Up

x
?
Solved

replicating postgresql database

Posted on 2016-10-03
4
Medium Priority
?
151 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

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Exchange administrators are always vigilant about Exchange crashes and disasters that are possible any time. It is quite essential to identify the symptoms of a possible Exchange issue and be prepared with a proper recovery plan. There are multiple…
What is the biggest problem in managing an exchange environment today? It is the lack of backups, disaster recovery (DR) plan, testing of the DR plan or believing that it won’t happen to us.
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…
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…

595 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