pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedPG replication across DataCenters
19+ messages / 10 participants
[nested] [flat]
* PG replication across DataCenters
@ 2013-11-22 10:43 Kaushal Shriyan <kaushalshriyan@gmail.com>
0 siblings, 2 replies; 19+ messages in thread
From: Kaushal Shriyan @ 2013-11-22 10:43 UTC (permalink / raw)
To: pgsql-general@postgresql.org
Hi,
I have read on the web that Postgresql DB supports replication across data
centers. Any real life usecase examples if it has been implemented by
anyone. Please also help me understand the caveats i need to take care if i
implement this setup.
Regards,
Kaushal
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: PG replication across DataCenters
@ 2013-11-22 10:57 Albe Laurenz <laurenz.albe@wien.gv.at>
parent: Kaushal Shriyan <kaushalshriyan@gmail.com>
1 sibling, 2 replies; 19+ messages in thread
From: Albe Laurenz @ 2013-11-22 10:57 UTC (permalink / raw)
To: Kaushal Shriyan *EXTERN* <kaushalshriyan@gmail.com>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Kaushal Shriyan wrote:
> I have read on the web that Postgresql DB supports replication across data centers. Any real life
> usecase examples if it has been implemented by anyone.
Well, we replicate a 1 TB database between two locations.
It is a fairly active OLTP application, but certainly not
pushing the limits of what PostgreSQL can do in transactions
per second.
But I get the impression that replication is widely accepted
and used by now.
> Please also help me understand the caveats i
> need to take care if i implement this setup.
Don't use synchronous replication if you have a high transaction
rate and a noticable network latency between the sites.
Wait for the next bugfix release, since a nasty bug has just
been discovered.
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: PG replication across DataCenters
@ 2013-11-22 11:04 Edson Richter <edsonrichter@hotmail.com>
parent: Kaushal Shriyan <kaushalshriyan@gmail.com>
1 sibling, 0 replies; 19+ messages in thread
From: Edson Richter @ 2013-11-22 11:04 UTC (permalink / raw)
To: pgsql-general@postgresql.org
Em 22/11/2013 08:43, Kaushal Shriyan escreveu:
> Hi,
>
> I have read on the web that Postgresql DB supports replication across
> data centers. Any real life usecase examples if it has been
> implemented by anyone. Please also help me understand the caveats i
> need to take care if i implement this setup.
>
> Regards,
>
> Kaushal
We have used asynchronous replication across datacenters with 100%
success since 9.1. Currently we use 9.2.
Our setup involves a internet tunnel between servers. Servers have about
2.000km of distance from each other.
The only points you need to take attention is tuning number of
wal_keep_segments and timeout, and the initial load (that can be huge,
depends on your data).
Regards,
Edson
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: PG replication across DataCenters
@ 2013-11-22 12:19 Torsten Förtsch <torsten.foertsch@gmx.net>
parent: Albe Laurenz <laurenz.albe@wien.gv.at>
1 sibling, 1 reply; 19+ messages in thread
From: Torsten Förtsch @ 2013-11-22 12:19 UTC (permalink / raw)
To: Albe Laurenz <laurenz.albe@wien.gv.at>; Kaushal Shriyan *EXTERN* <kaushalshriyan@gmail.com>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
On 22/11/13 11:57, Albe Laurenz wrote:
> Don't use synchronous replication if you have a high transaction
> rate and a noticable network latency between the sites.
>
> Wait for the next bugfix release, since a nasty bug has just
> been discovered.
Can you please explain or provide a pointer for more information?
We have recently started to use sync replication over a line with >80ms
latency. It works for small transactions with a relatively low
transaction rate.
Avoid transactions using NOTIFY. Those acquire an exclusive lock during
commit that is released only when the remote host has also done its
commit. So, only one such transaction can be committing at time.
Async replication works just fine.
Torsten
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: PG replication across DataCenters
@ 2013-11-22 12:44 Albe Laurenz <laurenz.albe@wien.gv.at>
parent: Torsten Förtsch <torsten.foertsch@gmx.net>
0 siblings, 2 replies; 19+ messages in thread
From: Albe Laurenz @ 2013-11-22 12:44 UTC (permalink / raw)
To: Torsten Förtsch *EXTERN* <torsten.foertsch@gmx.net>; Kaushal Shriyan *EXTERN* <kaushalshriyan@gmail.com>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Torsten Förtsch wrote:
>> Don't use synchronous replication if you have a high transaction
>> rate and a noticable network latency between the sites.
>>
>> Wait for the next bugfix release, since a nasty bug has just
>> been discovered.
>
> Can you please explain or provide a pointer for more information?
If you mean the bug I mentioned, see this thread:
http://www.postgresql.org/message-id/20131119142001.GA10498@alap2.anarazel.de
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: PG replication across DataCenters
@ 2013-11-22 13:03 Kaushal Shriyan <kaushalshriyan@gmail.com>
parent: Albe Laurenz <laurenz.albe@wien.gv.at>
1 sibling, 1 reply; 19+ messages in thread
From: Kaushal Shriyan @ 2013-11-22 13:03 UTC (permalink / raw)
To: Albe Laurenz <laurenz.albe@wien.gv.at>; +Cc: Torsten Förtsch *EXTERN* <torsten.foertsch@gmx.net>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
On Fri, Nov 22, 2013 at 6:14 PM, Albe Laurenz <laurenz.albe@wien.gv.at>wrote:
> Torsten Förtsch wrote:
> >> Don't use synchronous replication if you have a high transaction
> >> rate and a noticable network latency between the sites.
> >>
> >> Wait for the next bugfix release, since a nasty bug has just
> >> been discovered.
> >
> > Can you please explain or provide a pointer for more information?
>
> If you mean the bug I mentioned, see this thread:
>
> http://www.postgresql.org/message-id/20131119142001.GA10498@alap2.anarazel.de
>
> Yours,
> Laurenz Albe
>
Hi,
I am not sure i understand the difference between async and sync
replication and on what scenarios i should use async or sync replication.
Does it mean if it is within same DC then sync replication is the best and
if it is across DC replication async is better than sync. Please help me
understand.
Regards,
Kaushal
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: PG replication across DataCenters
@ 2013-11-22 13:37 Michael Paquier <michael.paquier@gmail.com>
parent: Kaushal Shriyan <kaushalshriyan@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Michael Paquier @ 2013-11-22 13:37 UTC (permalink / raw)
To: Kaushal Shriyan <kaushalshriyan@gmail.com>; +Cc: Albe Laurenz <laurenz.albe@wien.gv.at>; Torsten Förtsch *EXTERN* <torsten.foertsch@gmx.net>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
On Fri, Nov 22, 2013 at 10:03 PM, Kaushal Shriyan
<kaushalshriyan@gmail.com> wrote:
> I am not sure i understand the difference between async and sync replication
> and on what scenarios i should use async or sync replication. Does it mean
> if it is within same DC then sync replication is the best and if it is
> across DC replication async is better than sync. Please help me understand.
In the case of synchronous replication, master node waits for the
confirmation that a given transaction has committed on slave side
before committing itself. This wait period can cause some delay, hence
it is preferable to use sync replication with nodes that far from each
other.
--
Michael
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: PG replication across DataCenters
@ 2013-11-22 13:38 Michael Paquier <michael.paquier@gmail.com>
parent: Albe Laurenz <laurenz.albe@wien.gv.at>
1 sibling, 0 replies; 19+ messages in thread
From: Michael Paquier @ 2013-11-22 13:38 UTC (permalink / raw)
To: Albe Laurenz <laurenz.albe@wien.gv.at>; +Cc: Torsten Förtsch *EXTERN* <torsten.foertsch@gmx.net>; Kaushal Shriyan *EXTERN* <kaushalshriyan@gmail.com>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
On Fri, Nov 22, 2013 at 9:44 PM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
> Torsten Förtsch wrote:
>>> Don't use synchronous replication if you have a high transaction
>>> rate and a noticable network latency between the sites.
>>>
>>> Wait for the next bugfix release, since a nasty bug has just
>>> been discovered.
>>
>> Can you please explain or provide a pointer for more information?
>
> If you mean the bug I mentioned, see this thread:
> http://www.postgresql.org/message-id/20131119142001.GA10498@alap2.anarazel.de
Bug that has just been fixed btw:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=98f58a30c1beb6ec0870d6520f49fb40d9...
Regards,
--
Michael
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: PG replication across DataCenters
@ 2013-11-22 14:46 Albe Laurenz <laurenz.albe@wien.gv.at>
parent: Michael Paquier <michael.paquier@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Albe Laurenz @ 2013-11-22 14:46 UTC (permalink / raw)
To: Michael Paquier *EXTERN* <michael.paquier@gmail.com>; Kaushal Shriyan <kaushalshriyan@gmail.com>; +Cc: Torsten Förtsch *EXTERN* <torsten.foertsch@gmx.net>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Michael Paquier wrote:
> On Fri, Nov 22, 2013 at 10:03 PM, Kaushal Shriyan <kaushalshriyan@gmail.com> wrote:
>> I am not sure i understand the difference between async and sync replication
>> and on what scenarios i should use async or sync replication. Does it mean
>> if it is within same DC then sync replication is the best and if it is
>> across DC replication async is better than sync. Please help me understand.
> In the case of synchronous replication, master node waits for the
> confirmation that a given transaction has committed on slave side
> before committing itself. This wait period can cause some delay, hence
> it is preferable to use sync replication with nodes that far from each
> other.
I am sure that you wanted to say
"with nodes *not* that far from each other".
Basically, you have to choose between these options:
- Slow down processing, but don't lose a transaction on failover
(this would be synchronous, nodes close to each other)
- Replicate over longer distances, but possibly lose some
transactions on failover (that would be asynchronous).
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: PG replication across DataCenters
@ 2013-11-23 08:09 Michael Paquier <michael.paquier@gmail.com>
parent: Albe Laurenz <laurenz.albe@wien.gv.at>
0 siblings, 0 replies; 19+ messages in thread
From: Michael Paquier @ 2013-11-23 08:09 UTC (permalink / raw)
To: Albe Laurenz <laurenz.albe@wien.gv.at>; +Cc: Kaushal Shriyan <kaushalshriyan@gmail.com>; Torsten Förtsch *EXTERN* <torsten.foertsch@gmx.net>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
On Fri, Nov 22, 2013 at 11:46 PM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
> Michael Paquier wrote:
>> On Fri, Nov 22, 2013 at 10:03 PM, Kaushal Shriyan <kaushalshriyan@gmail.com> wrote:
>>> I am not sure i understand the difference between async and sync replication
>>> and on what scenarios i should use async or sync replication. Does it mean
>>> if it is within same DC then sync replication is the best and if it is
>>> across DC replication async is better than sync. Please help me understand.
>
>> In the case of synchronous replication, master node waits for the
>> confirmation that a given transaction has committed on slave side
>> before committing itself. This wait period can cause some delay, hence
>> it is preferable to use sync replication with nodes that far from each
>> other.
>
> I am sure that you wanted to say
> "with nodes *not* that far from each other".
Oops sorry for the typo. Yes I meant of course "not that far".
--
Michael
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: PG replication across DataCenters
@ 2013-12-09 16:09 Thomas Harold <thomas-lists@nybeta.com>
parent: Albe Laurenz <laurenz.albe@wien.gv.at>
1 sibling, 3 replies; 19+ messages in thread
From: Thomas Harold @ 2013-12-09 16:09 UTC (permalink / raw)
To: Albe Laurenz <laurenz.albe@wien.gv.at>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
On 11/22/2013 5:57 AM, Albe Laurenz wrote:
> Kaushal Shriyan wrote:
>> I have read on the web that Postgresql DB supports replication
>> across data centers. Any real life usecase examples if it has been
>> implemented by anyone.
>
> Well, we replicate a 1 TB database between two locations. It is a
> fairly active OLTP application, but certainly not pushing the limits
> of what PostgreSQL can do in transactions per second.
>
Something that section 25 in the pgsql documentation is not clear about
for hot-standby with WAL log shipping using the built-in streaming:
Can you choose which databases / tables on the master server get
streamed to the hot-standby read-only server at the remote site? If
not, I suspect we'll have to go with either Slony or Bucardo.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: PG replication across DataCenters
@ 2013-12-09 16:24 Ben Chobot <bench@silentmedia.com>
parent: Thomas Harold <thomas-lists@nybeta.com>
2 siblings, 0 replies; 19+ messages in thread
From: Ben Chobot @ 2013-12-09 16:24 UTC (permalink / raw)
To: Thomas Harold <thomas-lists@nybeta.com>; +Cc: pgsql-general General <pgsql-general@postgresql.org>
On Dec 9, 2013, at 8:09 AM, Thomas Harold wrote:
> On 11/22/2013 5:57 AM, Albe Laurenz wrote:
>> Kaushal Shriyan wrote:
>>> I have read on the web that Postgresql DB supports replication
>>> across data centers. Any real life usecase examples if it has been
>>> implemented by anyone.
>>
>> Well, we replicate a 1 TB database between two locations. It is a
>> fairly active OLTP application, but certainly not pushing the limits
>> of what PostgreSQL can do in transactions per second.
>>
>
> Something that section 25 in the pgsql documentation is not clear about for hot-standby with WAL log shipping using the built-in streaming:
>
> Can you choose which databases / tables on the master server get streamed to the hot-standby read-only server at the remote site? If not, I suspect we'll have to go with either Slony or Bucardo.
No, with the built-in binary replication, it's all or nothing, and the slaves have to have the exact same schema as the master (no adding or removing indices, for example.)
Out of curiosity what did you find unclear about http://www.postgresql.org/docs/9.3/static/different-replication-solutions.html?=
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: PG replication across DataCenters
@ 2013-12-09 16:38 Andreas Kretschmer <akretschmer@spamfence.net>
parent: Thomas Harold <thomas-lists@nybeta.com>
2 siblings, 0 replies; 19+ messages in thread
From: Andreas Kretschmer @ 2013-12-09 16:38 UTC (permalink / raw)
To: pgsql-general@postgresql.org
Thomas Harold <thomas-lists@nybeta.com> wrote:
> On 11/22/2013 5:57 AM, Albe Laurenz wrote:
>> Kaushal Shriyan wrote:
>>> I have read on the web that Postgresql DB supports replication
>>> across data centers. Any real life usecase examples if it has been
>>> implemented by anyone.
>>
>> Well, we replicate a 1 TB database between two locations. It is a
>> fairly active OLTP application, but certainly not pushing the limits
>> of what PostgreSQL can do in transactions per second.
>>
>
> Something that section 25 in the pgsql documentation is not clear about
> for hot-standby with WAL log shipping using the built-in streaming:
>
> Can you choose which databases / tables on the master server get
> streamed to the hot-standby read-only server at the remote site? If
> not, I suspect we'll have to go with either Slony or Bucardo.
WAL's contains transaction informations for the whole cluster, you can't
choose particular databases or tables.
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: PG replication across DataCenters
@ 2013-12-10 11:37 Bill Moran <wmoran@potentialtech.com>
parent: Thomas Harold <thomas-lists@nybeta.com>
2 siblings, 1 reply; 19+ messages in thread
From: Bill Moran @ 2013-12-10 11:37 UTC (permalink / raw)
To: Thomas Harold <thomas-lists@nybeta.com>; +Cc: Albe Laurenz <laurenz.albe@wien.gv.at>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
On Mon, 09 Dec 2013 11:09:21 -0500 Thomas Harold <thomas-lists@nybeta.com> wrote:
> On 11/22/2013 5:57 AM, Albe Laurenz wrote:
> > Kaushal Shriyan wrote:
> >> I have read on the web that Postgresql DB supports replication
> >> across data centers. Any real life usecase examples if it has been
> >> implemented by anyone.
> >
> > Well, we replicate a 1 TB database between two locations. It is a
> > fairly active OLTP application, but certainly not pushing the limits
> > of what PostgreSQL can do in transactions per second.
> >
>
> Something that section 25 in the pgsql documentation is not clear about
> for hot-standby with WAL log shipping using the built-in streaming:
>
> Can you choose which databases / tables on the master server get
> streamed to the hot-standby read-only server at the remote site? If
> not, I suspect we'll have to go with either Slony or Bucardo.
Go with Slony. Trust me.
People seem to shy away from the comlexity of slony, only to realize
later that they're hurting because the solution they chose instead
doesn't have the features they need. Keep in mind some things that
slony does that aren't yet available with streaming:
* Cascading replication chains (a really big deal when you want
multiple slaves in the secondary facility and don't want to hog
your bandwidth)
* Quick and easy movement of the master to any of the database in
the cluster without destroying replication.
* Seeding of new slaves without interrupting existing nodes (assuming
your hardware has a little free capacity)
* Selective replication of tables, potentially in complex arrangements
where some tables are replicated to only to A and some only to B
and some to A and B, etc, etc.
I was about to go on and type more, but really, those three things
make a huge difference in day to day operations, when problems occur,
and when the unexpected (but joyful) "we never expected this much
activity" happens.
Streaming replication is great, but unless you're 100% sure you'll
be OK with the restrictions it imposes, I recommend taking the time
to learn how to manage slony, as the advantages far outweigh the
additional management overhead.
--
Bill Moran <wmoran@potentialtech.com>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: PG replication across DataCenters
@ 2013-12-24 06:39 Sameer Kumar <sameer.kumar@ashnik.com>
parent: Bill Moran <wmoran@potentialtech.com>
0 siblings, 1 reply; 19+ messages in thread
From: Sameer Kumar @ 2013-12-24 06:39 UTC (permalink / raw)
To: Bill Moran <wmoran@potentialtech.com>; +Cc: Thomas Harold <thomas-lists@nybeta.com>; Albe Laurenz <laurenz.albe@wien.gv.at>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Though I will agree that slony is a nice and a great tool w.r.t.
replication (specifically selective replication). But I would dis-agree on
below points:
* Cascading replication chains (a really big deal when you want
multiple slaves in the secondary facility and don't want to hog
your bandwidth)
Really? which version of Postgres are we talking about? I think cascaded
replication facility is available since v9.2
http://www.postgresql.org/docs/9.2/static/warm-standby.html#CASCADING-REPLICATION
* Quick and easy movement of the master to any of the database in
the cluster without destroying replication.
Again, which version? Re-mastering is made simple in v9.3.
* Seeding of new slaves without interrupting existing nodes (assuming
your hardware has a little free capacity)
AFAIK, streaming replication does not cause any interruption while you add
a new node.
* Selective replication of tables, potentially in complex arrangements
where some tables are replicated to only to A and some only to B
and some to A and B, etc, etc.
Agree.
In general I do not like trigger based (replication) solutions for huge
clusters [this is my personal opinion and does not necessarily indicate my
employer's opinion ;-)] and for databases which has huge write volume
specifically if you do bulk insert/delete/update operations.
I think if it's slony or streaming replication will depend on below factors:
1) The change-set that you want to replicate contributes how much of your
total change set? e.g. on a per minute basis if it's 70% or above, I will
recommend you to go for streaming replication
2) Do you have too many tables to be added to replication set? lets say
above 70% of your total tables needs to be replication (unless rest 30%
have high write operations), then go for streaming replication
3) Do you too many bulk operations happening on the tables which needs to
be replicated
4) To some extent your choice will be influenced by the motivation behind
replication, DR, HA, reporting application (esp if you are particular about
replicating only selective tables for reports)
There are few easier ways of managing a slony cluster:
1)
http://shoaibmir.wordpress.com/2009/08/05/setting-up-slony-cluster-with-perltools/<http://shoaibm...;
2) I think even pgadmin supports slony replication (not sure if its slony-I
or slony-II)
Regards
Sameer
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: PG replication across DataCenters
@ 2013-12-29 13:19 Bill Moran <wmoran@potentialtech.com>
parent: Sameer Kumar <sameer.kumar@ashnik.com>
0 siblings, 1 reply; 19+ messages in thread
From: Bill Moran @ 2013-12-29 13:19 UTC (permalink / raw)
To: Sameer Kumar <sameer.kumar@ashnik.com>; +Cc: Thomas Harold <thomas-lists@nybeta.com>; Albe Laurenz <laurenz.albe@wien.gv.at>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
On Tue, 24 Dec 2013 14:39:42 +0800 Sameer Kumar <sameer.kumar@ashnik.com> wrote:
>
> * Cascading replication chains (a really big deal when you want
>
> multiple slaves in the secondary facility and don't want to hog
>
> your bandwidth)
>
> Really? which version of Postgres are we talking about? I think cascaded
> replication facility is available since v9.2
> http://www.postgresql.org/docs/9.2/static/warm-standby.html#CASCADING-REPLICATION
Nice. Seems it's been longer than I realized since I last evaluated
what streaming replication is capble of.
> * Quick and easy movement of the master to any of the database in
>
> the cluster without destroying replication.
>
> Again, which version? Re-mastering is made simple in v9.3.
I'm not seeing that in the documentation. In fact, what I'm finding
seems to suggest the opposite: that each node's master is configured
in a config file, so in the case of a complicated replication setup,
I would have to run around editing config files on multiple servers
to move the master ... unless I'm missing something in the documentation.
> * Seeding of new slaves without interrupting existing nodes (assuming
>
> your hardware has a little free capacity)
>
> AFAIK, streaming replication does not cause any interruption while you add
> a new node.
The process is still significantly more involved than Slony's subscription
commands. In our lab setups, I've watched junior DBA's fail time and time
again to get a proper seed with streaming replication.
> In general I do not like trigger based (replication) solutions for huge
> clusters [this is my personal opinion and does not necessarily indicate my
> employer's opinion ;-)] and for databases which has huge write volume
> specifically if you do bulk insert/delete/update operations.
There are definitely drawbacks, I'll grant you that. If it's raw throughput
you need, Slony probably isn't going to cut it for you.
> I think if it's slony or streaming replication will depend on below factors:
>
> 1) The change-set that you want to replicate contributes how much of your
> total change set? e.g. on a per minute basis if it's 70% or above, I will
> recommend you to go for streaming replication
While this is a strong argument in favor of streaming over Slony, the
70% number seems rather arbitrary, and you're advocating that this point
alone is enough to outweight the other advantages of Slony, which may be
more important in a particular case.
> 2) Do you have too many tables to be added to replication set? lets say
> above 70% of your total tables needs to be replication (unless rest 30%
> have high write operations), then go for streaming replication
Again, this seems arbitrary. If the management that Slony provides is
needed, then why would I care what percentage of tables are involved?
> 3) Do you too many bulk operations happening on the tables which needs to
> be replicated
This is arguably a shortcoming of trigger-based replication that trumps just
about everything else. If Slony just can't keep up, then you don't have much
choice.
> 4) To some extent your choice will be influenced by the motivation behind
> replication, DR, HA, reporting application (esp if you are particular about
> replicating only selective tables for reports)
In my experience, this is usually the largest factor.
Once argument in favor of streaming that you missed is when you have no
control over the schema (for example, when it's 3rd party, like an openfire
database). In those cases, the application frequently omits things like
primary keys (which are required for slony) and has an upgrade process that
assumes it can change database tables without impacting anything else.
> There are few easier ways of managing a slony cluster:
>
> 1)
> http://shoaibmir.wordpress.com/2009/08/05/setting-up-slony-cluster-with-perltools/<http://shoaibm...;
>
> 2) I think even pgadmin supports slony replication (not sure if its slony-I
> or slony-II)
I'll add dbsteward to this list, as we wrote it (in part) to make slony
management easier on systems that experience frequent change.
--
Bill Moran <wmoran@potentialtech.com>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: PG replication across DataCenters
@ 2013-12-29 16:15 Sameer Kumar <sameer.kumar@ashnik.com>
parent: Bill Moran <wmoran@potentialtech.com>
0 siblings, 1 reply; 19+ messages in thread
From: Sameer Kumar @ 2013-12-29 16:15 UTC (permalink / raw)
To: Bill Moran <wmoran@potentialtech.com>; +Cc: Thomas Harold <thomas-lists@nybeta.com>; Albe Laurenz <laurenz.albe@wien.gv.at>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
>> > * Quick and easy movement of the master to any of the database in
>> >
>> > the cluster without destroying replication.
>> >
>> > Again, which version? Re-mastering is made simple in v9.3.
>> I'm not seeing that in the documentation. In fact, what I'm finding
>> seems to suggest the opposite: that each node's master is configured
>> in a config file, so in the case of a complicated replication setup,
>> I would have to run around editing config files on multiple servers
>> to move the master ... unless I'm missing something in the documentation.
Well, the pain can be minimized if you can write some simple shell scripts
for this. Or if you can have a floating/virtual IP.
>>> * Seeding of new slaves without interrupting existing nodes (assuming
>>>
>>> your hardware has a little free capacity)
>>>
>>> AFAIK, streaming replication does not cause any interruption while you
add
>>> a new node.
>>The process is still significantly more involved than Slony's subscription
>>commands. In our lab setups, I've watched junior DBA's fail time and time
>>again to get a proper seed with streaming replication.
Try the pg_basebackup options in v9.3. Creating a streaming replica has
been made easy. It's still a little painful if you want to move your WALs
to a different LUW/HDD on your replica
>>> I think if it's slony or streaming replication will depend on below
factors:
>>>
>>> 1) The change-set that you want to replicate contributes how much of
your
>>> total change set? e.g. on a per minute basis if it's 70% or above, I
will
>>> recommend you to go for streaming replication
>>While this is a strong argument in favor of streaming over Slony, the
>>70% number seems rather arbitrary, and you're advocating that this point
>>alone is enough to outweight the other advantages of Slony, which may be
>>more important in a particular case.
I gave an example. It will definately vary from case to case and
implementation to implementation.
>>> 4) To some extent your choice will be influenced by the motivation
behind
>>> replication, DR, HA, reporting application (esp if you are particular
about
>>> replicating only selective tables for reports)
>>In my experience, this is usually the largest factor.
>>>To some extent your choice will be influenced <<<
Let me correct myself:
To a large extent your choice will be influnced :)
>>Once argument in favor of streaming that you missed is when you have no
>>control over the schema (for example, when it's 3rd party, like an
openfire
>>database). In those cases, the application frequently omits things like
>>primary keys (which are required for slony) and has an upgrade process
that
>>assumes it can change database tables without impacting anything else.
That's a good one and quite apt too!
Regards
Sameer
Ashnik Pte. Ltd.
Singapore
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: PG replication across DataCenters
@ 2013-12-29 17:08 Bill Moran <wmoran@potentialtech.com>
parent: Sameer Kumar <sameer.kumar@ashnik.com>
0 siblings, 1 reply; 19+ messages in thread
From: Bill Moran @ 2013-12-29 17:08 UTC (permalink / raw)
To: Sameer Kumar <sameer.kumar@ashnik.com>; +Cc: Thomas Harold <thomas-lists@nybeta.com>; Albe Laurenz <laurenz.albe@wien.gv.at>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
On Mon, 30 Dec 2013 00:15:37 +0800 Sameer Kumar <sameer.kumar@ashnik.com> wrote:
> >> > * Quick and easy movement of the master to any of the database in
> >> >
> >> > the cluster without destroying replication.
> >> >
> >> > Again, which version? Re-mastering is made simple in v9.3.
>
> >> I'm not seeing that in the documentation. In fact, what I'm finding
> >> seems to suggest the opposite: that each node's master is configured
> >> in a config file, so in the case of a complicated replication setup,
> >> I would have to run around editing config files on multiple servers
> >> to move the master ... unless I'm missing something in the documentation.
>
> Well, the pain can be minimized if you can write some simple shell scripts
> for this. Or if you can have a floating/virtual IP.
This is probably the only point that we're not seeing eye to eye on.
Take a real scenario I have to maintain. There is a single master and 11
replicas spread across 2 datacenters. Some of these replicas are read-only
for the application, 1 is for analytics, another supports development,
another is a dedicated backup system. The rest are purely for DR..
Now, in a facility failure scenario, all is well, we just promote
the DR master in the secondary datacenter and go back to work -- this should
be equally easy with either Slony or streaming
What I don't see streaming working for is DR drills. I need to, in a
controlled manner, move the entire application to the secondary datacenter,
while keeping all the nodes in sync, make sure everything operates properly
from there (which means allowing database updates), then move it all back
to the primary datacenter, without losing sync on any slaves (this is a 2T
database, which I'm sure isn't the largest anyone has dealt with, but it
means that reseeding slaves is a multi-hour endeavour). With Slony, these
drills are easy: a single slonik command relocates the master to the DR
datacenter while keeping everything in sync, and when testing is complete,
another slonik command puts everything back the way it was, without any
data loss and with minimal chance for human error.
If you feel that the current implementation of streaming replication is
able to do that task, then I'll have to move up my timetable to re-evaluate
it. It _has_ been a few versions since I've taken a good look at it.
--
Bill Moran <wmoran@potentialtech.com>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: PG replication across DataCenters
@ 2013-12-29 18:13 Sameer Kumar <sameer.kumar@ashnik.com>
parent: Bill Moran <wmoran@potentialtech.com>
0 siblings, 0 replies; 19+ messages in thread
From: Sameer Kumar @ 2013-12-29 18:13 UTC (permalink / raw)
To: Bill Moran <wmoran@potentialtech.com>; +Cc: Thomas Harold <thomas-lists@nybeta.com>; Albe Laurenz <laurenz.albe@wien.gv.at>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
>
> What I don't see streaming working for is DR drills. I need to, in a
> controlled manner, move the entire application to the secondary datacenter,
> while keeping all the nodes in sync, make sure everything operates properly
> from there (which means allowing database updates), then move it all back
> to the primary datacenter, without losing sync on any slaves (this is a 2T
> database, which I'm sure isn't the largest anyone has dealt with, but it
> means that reseeding slaves is a multi-hour endeavour). With Slony, these
> drills are easy: a single slonik command relocates the master to the DR
> datacenter while keeping everything in sync, and when testing is complete,
> another slonik command puts everything back the way it was, without any
> data loss and with minimal chance for human error.
I guess I got your point :)
Agree to you now! :)
With v9.3 I think I would be easy to swap the roles for primary and DR. (I
need to test this before I can say for sure).
But still it will be a pain if one needs to shift all the operations to DR
(which is a a valid case, e.g. you would do that for testing the readiness
of your DR site by doing a mock failover) and then shift back to Primary
Site (assuming while operations were going on on DR site, primary site was
kept down purposefully). This will involve taking a backup from DR to
primary and then swapping the roles.
I guess this limitation will be soon waived off. I guess v9.4 or next one
should have this feature (no backups as long as your wal_keep_segment is
high enough to cater to your testing/mock failover window).
I agree slonik and few other utilities/tool around it
administration/management is quite easy.
If you feel that the current implementation of streaming replication is
> able to do that task, then I'll have to move up my timetable to re-evaluate
> it. It _has_ been a few versions since I've taken a good look at it.
Given your expectation above, v9.3 is a good candidate. But you can afford
to give it a miss.
You must try once v9.4 is out.
Regards
Sameer
Ashnik Pte. Ltd.
Singapore
^ permalink raw reply [nested|flat] 19+ messages in thread
end of thread, other threads:[~2013-12-29 18:13 UTC | newest]
Thread overview: 19+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-11-22 10:43 PG replication across DataCenters Kaushal Shriyan <kaushalshriyan@gmail.com>
2013-11-22 10:57 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2013-11-22 12:19 ` Torsten Förtsch <torsten.foertsch@gmx.net>
2013-11-22 12:44 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2013-11-22 13:03 ` Kaushal Shriyan <kaushalshriyan@gmail.com>
2013-11-22 13:37 ` Michael Paquier <michael.paquier@gmail.com>
2013-11-22 14:46 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2013-11-23 08:09 ` Michael Paquier <michael.paquier@gmail.com>
2013-11-22 13:38 ` Michael Paquier <michael.paquier@gmail.com>
2013-12-09 16:09 ` Thomas Harold <thomas-lists@nybeta.com>
2013-12-09 16:24 ` Ben Chobot <bench@silentmedia.com>
2013-12-09 16:38 ` Andreas Kretschmer <akretschmer@spamfence.net>
2013-12-10 11:37 ` Bill Moran <wmoran@potentialtech.com>
2013-12-24 06:39 ` Sameer Kumar <sameer.kumar@ashnik.com>
2013-12-29 13:19 ` Bill Moran <wmoran@potentialtech.com>
2013-12-29 16:15 ` Sameer Kumar <sameer.kumar@ashnik.com>
2013-12-29 17:08 ` Bill Moran <wmoran@potentialtech.com>
2013-12-29 18:13 ` Sameer Kumar <sameer.kumar@ashnik.com>
2013-11-22 11:04 ` Edson Richter <edsonrichter@hotmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox