Multiple synchronous_standby_names rules
Hello,
When working with a production (P) and a DR (D) environment it is often a
requirement to be able to protect against data loss when promoting within a
site, and also when losing A and promoting a master at D.
The current synchronous_standby_names do not allow this.
In a simple example we could take the following nodes:
P1 (current master), P2, P3
D1, D2, D3
Where P1 is replicating to (P2, P3, D1, D2, D3).
The closest synchronous_standby_names setting you could get to my use case
would be:
1 (D1, D2, D3)
This would allow the loss of either site without losing data - however it
would not allow promotion within site P from P1 -> (P2 | P3) without the
potential for data loss.
What is needed to support this is the ability to configure Px with
something like:
1 (P1, P2, P3), 1 (D1, D2, D3)
Would there be any appetite for this - or would it be seen as over
complication of the current rules?
Cheers,
James Sewell,
PostgreSQL Team Lead / Solutions Architect
Suite 112, Jones Bay Wharf, 26-32 Pirrama Road, Pyrmont NSW 2009
*P *(+61) 2 8099 9000 <(+61)%202%208099%209000> *W* www.jirotech.com *F *
(+61) 2 8099 9099 <(+61)%202%208099%209000>
--
------------------------------
The contents of this email are confidential and may be subject to legal or
professional privilege and copyright. No representation is made that this
email is free of viruses or other defects. If you have received this
communication in error, you may not copy or distribute any part of it or
otherwise disclose its contents to anyone. Please advise the sender of your
incorrect receipt of this correspondence.
On Thu, Jan 12, 2017 at 9:53 AM, James Sewell <james.sewell@jirotech.com> wrote:
What is needed to support this is the ability to configure Px with something like:
1 (P1, P2, P3), 1 (D1, D2, D3)
Would there be any appetite for this - or would it be seen as over complication of the current rules?
There have been discussions about being able to do that and there are
really use cases where that would be useful. As lately quorum commit
has been committed, we have a better idea of the grammar to use
(yeah!), though there are a couple of things remaining regarding the
design of node subsets:
- How to define group names? Making them mandatory would likely be the
way to go.
- How to represent that intuitively in pg_stat_replication? Perhaps
the answer here is an extra column in this system view.
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Thu, 12 Jan 2017 at 12:06, Michael Paquier <michael.paquier@gmail.com>
wrote:
On Thu, Jan 12, 2017 at 9:53 AM, James Sewell <james.sewell@jirotech.com>
wrote:What is needed to support this is the ability to configure Px with
something like:
1 (P1, P2, P3), 1 (D1, D2, D3)
Would there be any appetite for this - or would it be seen as over
complication of the current rules?
There have been discussions about being able to do that and there are
really use cases where that would be useful. As lately quorum commit
has been committed, we have a better idea of the grammar to use
(yeah!), though there are a couple of things remaining regarding the
design of node subsets:
- How to define group names? Making them mandatory would likely be the
way to go.
- How to represent that intuitively in pg_stat_replication? Perhaps
the answer here is an extra column in this system view.
I'm coming back round to this as it's becoming increasingly discussed as we
look into systems with an RPO of (close to) 0 spanning multiple locations.
Before I start hacking - does anyone have any preference for syntax?
To me the best approach seems to be a list of items like:
ANY 2 (a,b) AS SiteA, ANY 2(c,d) AS SiteB
FIRST 2 (a,b,c) AS SiteA, d AS SiteB, e AS SiteC
You need a group name for the replication view as Michael noted, I suppose
you'd need to also allow no group name (one would be autogenerated??) for
backwards compatibility OR enforce group names when there are more than one
elements in the list.
I don't want to do embedded groups like:
ANY 2 (ANY 2 (a,b,c) AS SiteA, d as SiteB, c as SiteB)
Because that just seems like it's overcomplicating the issue.
thoughts?
James
--
The contents of this email are confidential and may be subject to legal or
professional privilege and copyright. No representation is made that this
email is free of viruses or other defects. If you have received this
communication in error, you may not copy or distribute any part of it or
otherwise disclose its contents to anyone. Please advise the sender of your
incorrect receipt of this correspondence.