replicating many to one
I have a situation where I need to replicate the databases in 4 clusters on 4 individual servers to a single cluster on 1 server. So far, the best option looks like pgpool statement-based replication, the major down side being sequences. Does anyone know a better way to achieve this type of replication?
Take a good look at http://2ndquadrant.com/en/resources/bdr/
On Thu, Jun 4, 2015 at 1:07 PM, Doiron, Daniel <DoironD@advisory.com> wrote:
I have a situation where I need to replicate the databases in 4 clusters
on 4 individual servers to a single cluster on 1 server. So far, the best
option looks like pgpool statement-based replication, the major down side
being sequences. Does anyone know a better way to achieve this type of
replication?
--
~Wayne
Wayne E. Seguin
wayneeseguin@gmail.com
wayneeseguin on irc.freenode.net
http://twitter.com/wayneeseguin/
https://github.com/wayneeseguin/
On 6/4/2015 10:07 AM, Doiron, Daniel wrote:
I have a situation where I need to replicate the databases in 4
clusters on 4 individual servers to a single cluster on 1 server. So
far, the best option looks like pgpool statement-based replication,
the major down side being sequences. Does anyone know a better way to
achieve this type of replication?
do all of your applications have inherent protection against
collisions? to use an overly simple example, if you have an inventory
table, what happens if two nodes concurrently sell the same last units
of a given SKU ? Or a banking example, whats to prevent concurrent
transactions on distributed servers handing out the same money ?
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
The four source/master servers all have different databases, lets call them A, B, C, and D. We'd like to replicate them to one cluster that will hold ABCD databases.
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of John R Pierce
Sent: Thursday, June 04, 2015 2:46 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] replicating many to one
On 6/4/2015 10:07 AM, Doiron, Daniel wrote:
I have a situation where I need to replicate the databases in 4
clusters on 4 individual servers to a single cluster on 1 server. So
far, the best option looks like pgpool statement-based replication,
the major down side being sequences. Does anyone know a better way to
achieve this type of replication?
do all of your applications have inherent protection against
collisions? to use an overly simple example, if you have an inventory
table, what happens if two nodes concurrently sell the same last units
of a given SKU ? Or a banking example, whats to prevent concurrent
transactions on distributed servers handing out the same money ?
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
We had similar database architecture like yours before. Our 4 databases are
for sharding purpose. We used Bucardo to replicate from 4 different
databases, aggregate them into 1 collector database.
For the 4 databases A,B,C,D, for their auto increment sequence, we set them
with different "start with" number, all having fixed "increment by". So
they never collide when aggregated into the collector database.
Database A:
currentval("seq_a") = 10
nextval(" seq_a") = 20
Database B:
currentval("seq_a") = 11
nextval("seq_a") = 21
Hope it helps.
The four source/master servers all have different databases, lets call them
A, B, C, and D. We'd like to replicate them to one cluster that will hold
ABCD databases.
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:
pgsql-general-owner@postgresql.org] On Behalf Of John R Pierce
Sent: Thursday, June 04, 2015 2:46 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] replicating many to one
On 6/4/2015 10:07 AM, Doiron, Daniel wrote:
I have a situation where I need to replicate the databases in 4
clusters on 4 individual servers to a single cluster on 1 server. So
far, the best option looks like pgpool statement-based replication,
the major down side being sequences. Does anyone know a better way to
achieve this type of replication?
do all of your applications have inherent protection against
collisions? to use an overly simple example, if you have an inventory
table, what happens if two nodes concurrently sell the same last units
of a given SKU ? Or a banking example, whats to prevent concurrent
transactions on distributed servers handing out the same money ?
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Thu, Jun 4, 2015 at 12:14 PM, Doiron, Daniel <DoironD@advisory.com> wrote:
The four source/master servers all have different databases, lets call them A, B, C, and D. We'd like to replicate them to one cluster that will hold ABCD databases.
You can use londiste [1]http://skytools.projects.pgfoundry.org/skytools-3.0/ or slony [2]http://www.slony.info/ to do that.
[1]: http://skytools.projects.pgfoundry.org/skytools-3.0/
--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA
http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (499) 346-7196, +7 (988) 888-1979
gray.ru@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general