BDR with Postgres

Hi Pushkar,

1. How does the initial data sync of node3 with node2 takes place?
2. For node3 and replication data to/from node2, does node2 needs a
restart or it is not required?

Have you looked at?
https://wiki.postgresql.org/wiki/BDR_Administration

HTH

Kind regards/met vriendelijke groet,

Serge Fonville

http://www.sergefonville.nl

2015-03-17 10:01 GMT+01:00 Deole, Pushkar (Pushkar) <pdeole@avaya.com>:

The documentation says that all the existing nodes need to be restarted while adding a new node since the existing nodes need to establish connection to the new node. However, this doesn’t seem feasible for production deployments because existing nodes might be serving clients which would fail if we need to restart them. Is there a mechanism a new node gets added on the fly ?

From: Serge Fonville [mailto:serge.fonville@gmail.com]
Sent: Tuesday, March 17, 2015 5:58 PM
To: Deole, Pushkar (Pushkar)
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] BDR with Postgres

Hi Pushkar,

1. How does the initial data sync of node3 with node2 takes place?
2. For node3 and replication data to/from node2, does node2 needs a restart or it is not required?

Have you looked at?
https://wiki.postgresql.org/wiki/BDR_Administration&lt;https://urldefense.proofpoint.com/v2/url?u=https-3A__wiki.postgresql.org_wiki_BDR-5FAdministration&amp;d=AwMFaQ&amp;c=BFpWQw8bsuKpl1SgiZH64Q&amp;r=9JxdEvGq7qy3NnL3UxN47w&amp;m=uzHADaMRzzW17Xx0OXx6-6OZEITXmVACUrU1dndvTUU&amp;s=o3PYGQE6Cfwl2xQoqinchdAVlBxD6s2JRxy4TvoZ0Q4&amp;e=&gt;
HTH

Kind regards/met vriendelijke groet,

Serge Fonville

http://www.sergefonville.nl&lt;https://urldefense.proofpoint.com/v2/url?u=http-3A__www.sergefonville.nl&amp;d=AwMFaQ&amp;c=BFpWQw8bsuKpl1SgiZH64Q&amp;r=9JxdEvGq7qy3NnL3UxN47w&amp;m=uzHADaMRzzW17Xx0OXx6-6OZEITXmVACUrU1dndvTUU&amp;s=apjqpPqkiT3NqDTud9ZVHxsD5_tzo2W28oQbKM_NzIo&amp;e=&gt;

2015-03-17 10:01 GMT+01:00 Deole, Pushkar (Pushkar) <pdeole@avaya.com<mailto:pdeole@avaya.com>>:
Hi,

I just tried a POC with PostgreSQL’s streaming replication for the use case of my product. Since streaming replication is master-slave, when failover occurs there are some configuration changes required to promote slave to master. Also, my requirement is to failback to old master again after the old master is started back (i.e. when old master is started back, it should become the master rather than the slave), which is bit tricky and is not straight forward to achieve without restarting any of the nodes.

I am also evaluating BDR which is asynchronous master-master replication. Since both nodes will be master, I don’t need to worry about promoting slave to master etc. However, I have a query regarding adding a new node to the BDR. Suppose I have a BDR setup done with 2 nodes (Node1 and Node2). Let’s assume node1 goes down. Node2 is up and running. I now add a new node say node3 and setup BDR with node2.
In this scenario…

1. How does the initial data sync of node3 with node2 takes place?

2. For node3 and replication data to/from node2, does node2 needs a restart or it is not required?

I would appreciate if you can give any information on this scenario as that would give me an understanding of BDR. I would be trying a POC soon however not able to find time since stuck up with another task at the moment.

Thanks a lot…
Pushkar

On 17 March 2015 at 20:33, Deole, Pushkar (Pushkar) <pdeole@avaya.com>
wrote:

The documentation says that all the existing nodes need to be restarted
while adding a new node since the existing nodes need to establish
connection to the new node.

It sounds like you're talking about BDR here.

If so, that requirement will no longer apply in the coming 0.9.0 release.
Nodes may be added without restarting existing nodes. You can try it out if
you like, grab the code in the bdr-plugin/next branch from git.

However, this doesn’t seem feasible for production deployments because
existing nodes might be serving clients which would fail if we need to
restart them.

(The following isn't BDR specific at all and applies to Pg HA in general):

If a client cannot cope with a backend disconnecting then that's a buggy
client. Yes, that makes most clients out there buggy.

It's very common - and wrong - programming practice to assume that if a
connection is usable when you start a transaction then it'll stay that way.
Also to assume that if you don't do anything that could cause a transaction
abort (SERIALIZABLE isolation, etc) then one won't occur. That's just
setting yourself up for problems, because your app will fail visibly
(errors to user, etc) if you restart the DB server, cancel a query for load
reasons, the DB does crash-recovery after a backend panic/crash, your
application deadlocks with its self or something else and triggers the
deadlock detector, etc.

IMO apps should always do their work in a retry loop. If the tx is aborted
for some reason, retry the whole tx. If the connection is closed, reconnect
and retry. Only if you can't complete the work after a few tries and a
timeout should you give up with an error.

If your apps aren't written that way then you clearly don't mind showing
errors to the user that much after all.

Is there a mechanism a new node gets added on the fly ?

It's been added in the coming 0.9.0 release.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 17 March 2015 at 17:01, Deole, Pushkar (Pushkar) <pdeole@avaya.com>
wrote:

Hi,

I just tried a POC with PostgreSQL’s streaming replication for the use
case of my product. Since streaming replication is master-slave, when
failover occurs there are some configuration changes required to promote
slave to master.

repmgr can help here - see http://repmgr.org/ .

Also, my requirement is to failback to old master again after the old
master is started back (i.e. when old master is started back, it should
become the master rather than the slave), which is bit tricky and is not
straight forward to achieve without restarting any of the nodes.

You have to *resynchronise* the old master from the newly promoted master -
essentially, take a new base backup and overwrite the old master with the
new base backup from the newly promoted master.

Otherwise the old master won't have writes since the new master took over,
and that's ... well, bad. You'll also be unable to demote the new master to
follow the old master again, since it will have increased its timeline
specifically to prevent that.

Pg 9.4 adds wal_log_hints which permits the external pg_rewind extension to
do this re-syncing with less I/O and less work. Or you can enable checksums
(since 9.3). See
http://michael.otacoo.com/postgresql-2/postgres-9-4-feature-highlight-wal_log_hints-for-consistent-relation-page-tracking/
.

I am also evaluating BDR which is asynchronous master-master
replication. Since both nodes will be master, I don’t need to worry about
promoting slave to master etc. However, I have a query regarding adding a
new node to the BDR. Suppose I have a BDR setup done with 2 nodes (Node1
and Node2). Let’s assume node1 goes down. Node2 is up and running. I now
add a new node say node3 and setup BDR with node2.

In this scenario…

1. How does the initial data sync of node3 with node2 takes place?

If you use the bdr_init_copy command, by basebackup. If you use
bdr_init_replica via the config, it's done internally with a dump and
reload.

2. For node3 and replication data to/from node2, does node2 needs
a restart or it is not required?

In 0.8.0 and older, adding a node requires a restart of all existing nodes.

This requirement no longer exists in the pending 0.9.0 release. However,
all nodes are expected to be up and reachable when you add a new node. Node
addition won't complete until they are. Allowing node addition while one or
more peers are down is not on the current roadmap. If you have a dead node
you must remove it before adding new nodes.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services