Thread: BDR with Postgres

BDR with Postgres

From

"Deole, Pushkar (Pushkar)"

Date:

17 March 2015, 12:01:58

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

Re: BDR with Postgres

From

Serge Fonville

Date:

17 March 2015, 15:28:28

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>:

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

Re: BDR with Postgres

From

"Deole, Pushkar (Pushkar)"

Date:

17 March 2015, 15:33:12

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

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>:

Hi,

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?

Thanks a lot…

Pushkar

Re: BDR with Postgres

From

Craig Ringer

Date:

17 March 2015, 16:10:27

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

Re: BDR with Postgres

From

Craig Ringer

Date:

17 March 2015, 16:21:40

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