Обсуждение: adding a bdr node using bcv backup
Hello there...
I'm new to postgres and I'm trying out BDR replication...
I know that when I issue the bdr.bdr_group_join command, it will copy the entire database from the host I specify on parameter 'join_using_dsn' and this may take a while depending on the network and the size of the database...
What I wanted to know is if I can leverage a bcv backup... Is it possible?
I have a 2 nodes on my test environment and wanted to add a 3rd using this approach... But when I restored the backup, the bdr information from node1 went along, so when I use 'bdr.bdr_group_join', it tells me:
ERROR: This node is already a member of a BDR group
HINT: Connect to the node you wish to add and run bdr_group_join from it instead
On 21 January 2016 at 08:29, (Daniel Stolf) <dstolf@gmail.com> wrote:
Hello there...I'm new to postgres and I'm trying out BDR replication...I know that when I issue the bdr.bdr_group_join command, it will copy the entire database from the host I specify on parameter 'join_using_dsn' and this may take a while depending on the network and the size of the database...What I wanted to know is if I can leverage a bcv backup... Is it possible?
BCV seems to be an EMC backup system. It looks like a snapshot. If the snapshot taken is consistent and atomic, and if it includes both pg_xlog and the rest of the datadir and all tablespaces in the SAME snapshot taken at the SAME instant, then you can treat it much like a pg_basebackup. In that case you can use bdr_init_copy to bring it up as a new BDR node. You must either stop all writes to all other nodes or pre-create the replication slots *before* taking the snapshot though, otherwise the new node won't be able to catch up to writes done after the snapshot and before it was started.
If this sounds too complex then stick to the documented methods that work. Working from separately taken snapshots is hard to get right and could lead to subtle data problems if you get it wrong.
Hi Craig, how are you?
Thanks for your answer. It doesn't seems too complex... Also, it's just a test scenario, I don't intend to use as a production setup or to recommend as such, at least not until I'm 100% sure I got it right...
So, assuming I get the snapshot right... The steps would be...
1) create replication slots on prior nodes before taking the snapshot (not sure how to do that, which command would it be? );
2) take the snapshot;
3) bring it up on another server;
4) use bdr_init_copy
I'm not at work right now, but I remember two things...
On node 3 I brought up the copy, if I try get local node name, it says node1, which is the node I got the copy from, ... Wouldn't I also have to do something about that? Like, delete the previous information on bdr database that went along?
Em qui, 21 de jan de 2016 00:50, Craig Ringer <craig@2ndquadrant.com> escreveu:
On 21 January 2016 at 08:29, (Daniel Stolf) <dstolf@gmail.com> wrote:Hello there...I'm new to postgres and I'm trying out BDR replication...I know that when I issue the bdr.bdr_group_join command, it will copy the entire database from the host I specify on parameter 'join_using_dsn' and this may take a while depending on the network and the size of the database...What I wanted to know is if I can leverage a bcv backup... Is it possible?BCV seems to be an EMC backup system. It looks like a snapshot. If the snapshot taken is consistent and atomic, and if it includes both pg_xlog and the rest of the datadir and all tablespaces in the SAME snapshot taken at the SAME instant, then you can treat it much like a pg_basebackup. In that case you can use bdr_init_copy to bring it up as a new BDR node. You must either stop all writes to all other nodes or pre-create the replication slots *before* taking the snapshot though, otherwise the new node won't be able to catch up to writes done after the snapshot and before it was started.If this sounds too complex then stick to the documented methods that work. Working from separately taken snapshots is hard to get right and could lead to subtle data problems if you get it wrong.--
Ok, I'm at work now and I have access to my lab...
==== On Node 1: ====
bdrdemo=# select bdr.bdr_get_local_nodeid();
bdr_get_local_nodeid
-------------------------------
(6239328434665526195,1,16385)
(1 row)
bdrdemo=# select bdr.bdr_get_local_node_name();
bdr_get_local_node_name
-------------------------
node1
(1 row)
bdrdemo=# select bdr.bdr_get_local_nodeid();
bdr_get_local_nodeid
-------------------------------
(6239328434665526195,1,16385)
(1 row)
bdrdemo=# select bdr.bdr_get_local_node_name();
bdr_get_local_node_name
-------------------------
node1
(1 row)
================
=== On Node 2: ===
bdrdemo=# select bdr.bdr_get_local_nodeid();
bdr_get_local_nodeid
-------------------------------
(6239328434665526195,1,16385)
(1 row)
bdrdemo=# select bdr.bdr_get_local_node_name();
bdr_get_local_node_name
-------------------------
node1
(1 row)
================
Now, I take a snapshot from node1 and bring up a clone on node3... Here's what I got on node3:
=== On Node 3: ===
bdr_get_local_nodeid
-------------------------------
(6239328434665526195,1,16385)
(1 row)
bdrdemo=# select bdr.bdr_get_local_node_name();
bdr_get_local_node_name
-------------------------
node1
(1 row)
bdrdemo=# SELECT * FROM pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | active | xmin | catalog_xmin | restart_lsn
-----------+--------+-----------+--------+----------+--------+------+--------------+-------------
(0 rows)
================
As you can see, when I brought up a clone of node1 on node3, it got the same node name and id as node1...
So here's what I don't get:
1) if I have to create a new replication slots on node1 and 2 beforehand using "pg_create_physical_replication_slot" , don't they need the if of node3 on their name?
2) If node3 has the same name and if as node1, won't that introduce a conflic? Don't I need to clean that up before node3 can join the replication group?
Regards,
Daniel Stolf
On Thu, Jan 21, 2016 at 8:34 AM (Daniel Stolf) <dstolf@gmail.com> wrote:
Hi Craig, how are you?
Thanks for your answer. It doesn't seems too complex... Also, it's just a test scenario, I don't intend to use as a production setup or to recommend as such, at least not until I'm 100% sure I got it right...
So, assuming I get the snapshot right... The steps would be...
1) create replication slots on prior nodes before taking the snapshot (not sure how to do that, which command would it be? );
2) take the snapshot;
3) bring it up on another server;
4) use bdr_init_copyI'm not at work right now, but I remember two things...
On node 3 I brought up the copy, if I try get local node name, it says node1, which is the node I got the copy from, ... Wouldn't I also have to do something about that? Like, delete the previous information on bdr database that went along?
Em qui, 21 de jan de 2016 00:50, Craig Ringer <craig@2ndquadrant.com> escreveu:On 21 January 2016 at 08:29, (Daniel Stolf) <dstolf@gmail.com> wrote:Hello there...I'm new to postgres and I'm trying out BDR replication...I know that when I issue the bdr.bdr_group_join command, it will copy the entire database from the host I specify on parameter 'join_using_dsn' and this may take a while depending on the network and the size of the database...What I wanted to know is if I can leverage a bcv backup... Is it possible?BCV seems to be an EMC backup system. It looks like a snapshot. If the snapshot taken is consistent and atomic, and if it includes both pg_xlog and the rest of the datadir and all tablespaces in the SAME snapshot taken at the SAME instant, then you can treat it much like a pg_basebackup. In that case you can use bdr_init_copy to bring it up as a new BDR node. You must either stop all writes to all other nodes or pre-create the replication slots *before* taking the snapshot though, otherwise the new node won't be able to catch up to writes done after the snapshot and before it was started.If this sounds too complex then stick to the documented methods that work. Working from separately taken snapshots is hard to get right and could lead to subtle data problems if you get it wrong.--
I'm sorry, I didn't send the correct information about node 2...
Here's what I get on all three nodes after I take a snapshot on node 1 and bring it up on node 3...
=== On Node 1: ===
bdrdemo=# select bdr.bdr_get_local_nodeid();
bdr_get_local_nodeid
-------------------------------
(6239328434665526195,1,16385)
(1 row)
bdrdemo=# select bdr.bdr_get_local_node_name();
bdr_get_local_node_name
-------------------------
node1
(1 row)
bdrdemo=# SELECT * FROM pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | active | xmin | catalog_xmin | restart_lsn
-----------------------------------------+--------+-----------+--------+----------+--------+------+--------------+-------------
bdr_16385_6241964183952916534_1_16385__ | bdr | logical | 16385 | bdrdemo | t | | 4593 | 0/123E9808
=== On Node 2: ===
bdrdemo=# select bdr.bdr_get_local_nodeid();
bdr_get_local_nodeid
-------------------------------
(6241964183952916534,1,16385)
(1 row)
bdrdemo=# select bdr.bdr_get_local_node_name();
bdr_get_local_node_name
-------------------------
node2
(1 row)
bdrdemo=# SELECT * FROM pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | active | xmin | catalog_xmin | restart_lsn
-----------------------------------------+--------+-----------+--------+----------+--------+------+--------------+-------------
bdr_16385_6239328434665526195_1_16385__ | bdr | logical | 16385 | bdrdemo | t | | 3039 | 0/4EB0D28
(1 row)
=== On Node 3: ===
bdrdemo=# select bdr.bdr_get_local_nodeid();
bdr_get_local_nodeid
-------------------------------
(6239328434665526195,1,16385)
(1 row)
bdrdemo=# select bdr.bdr_get_local_node_name();
bdr_get_local_node_name
-------------------------
node1
(1 row)
bdrdemo=# SELECT * FROM pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | active | xmin | catalog_xmin | restart_lsn
-----------+--------+-----------+--------+----------+--------+------+--------------+-------------
(0 rows)
On Thu, Jan 21, 2016 at 10:46 AM (Daniel Stolf) <dstolf@gmail.com> wrote:
Ok, I'm at work now and I have access to my lab...==== On Node 1: ====bdrdemo=# select bdr.bdr_get_local_nodeid();bdr_get_local_nodeid-------------------------------(6239328434665526195,1,16385)(1 row)bdrdemo=# select bdr.bdr_get_local_node_name();bdr_get_local_node_name-------------------------node1(1 row)bdrdemo=# select bdr.bdr_get_local_nodeid();bdr_get_local_nodeid-------------------------------(6239328434665526195,1,16385)(1 row)bdrdemo=# select bdr.bdr_get_local_node_name();bdr_get_local_node_name-------------------------node1(1 row)=================== On Node 2: ===bdrdemo=# select bdr.bdr_get_local_nodeid();bdr_get_local_nodeid-------------------------------(6239328434665526195,1,16385)(1 row)bdrdemo=# select bdr.bdr_get_local_node_name();bdr_get_local_node_name-------------------------node1(1 row)================Now, I take a snapshot from node1 and bring up a clone on node3... Here's what I got on node3:=== On Node 3: ===bdr_get_local_nodeid-------------------------------(6239328434665526195,1,16385)(1 row)bdrdemo=# select bdr.bdr_get_local_node_name();bdr_get_local_node_name-------------------------node1(1 row)bdrdemo=# SELECT * FROM pg_replication_slots;slot_name | plugin | slot_type | datoid | database | active | xmin | catalog_xmin | restart_lsn-----------+--------+-----------+--------+----------+--------+------+--------------+-------------(0 rows)================As you can see, when I brought up a clone of node1 on node3, it got the same node name and id as node1...So here's what I don't get:1) if I have to create a new replication slots on node1 and 2 beforehand using "pg_create_physical_replication_slot" , don't they need the if of node3 on their name?2) If node3 has the same name and if as node1, won't that introduce a conflic? Don't I need to clean that up before node3 can join the replication group?Regards,Daniel StolfOn Thu, Jan 21, 2016 at 8:34 AM (Daniel Stolf) <dstolf@gmail.com> wrote:Hi Craig, how are you?
Thanks for your answer. It doesn't seems too complex... Also, it's just a test scenario, I don't intend to use as a production setup or to recommend as such, at least not until I'm 100% sure I got it right...
So, assuming I get the snapshot right... The steps would be...
1) create replication slots on prior nodes before taking the snapshot (not sure how to do that, which command would it be? );
2) take the snapshot;
3) bring it up on another server;
4) use bdr_init_copyI'm not at work right now, but I remember two things...
On node 3 I brought up the copy, if I try get local node name, it says node1, which is the node I got the copy from, ... Wouldn't I also have to do something about that? Like, delete the previous information on bdr database that went along?
Em qui, 21 de jan de 2016 00:50, Craig Ringer <craig@2ndquadrant.com> escreveu:On 21 January 2016 at 08:29, (Daniel Stolf) <dstolf@gmail.com> wrote:Hello there...I'm new to postgres and I'm trying out BDR replication...I know that when I issue the bdr.bdr_group_join command, it will copy the entire database from the host I specify on parameter 'join_using_dsn' and this may take a while depending on the network and the size of the database...What I wanted to know is if I can leverage a bcv backup... Is it possible?BCV seems to be an EMC backup system. It looks like a snapshot. If the snapshot taken is consistent and atomic, and if it includes both pg_xlog and the rest of the datadir and all tablespaces in the SAME snapshot taken at the SAME instant, then you can treat it much like a pg_basebackup. In that case you can use bdr_init_copy to bring it up as a new BDR node. You must either stop all writes to all other nodes or pre-create the replication slots *before* taking the snapshot though, otherwise the new node won't be able to catch up to writes done after the snapshot and before it was started.If this sounds too complex then stick to the documented methods that work. Working from separately taken snapshots is hard to get right and could lead to subtle data problems if you get it wrong.--
On 21 January 2016 at 20:46, (Daniel Stolf) <dstolf@gmail.com> wrote:
So here's what I don't get:1) if I have to create a new replication slots on node1 and 2 beforehand using "pg_create_physical_replication_slot" , don't they need the if of node3 on their name?
You need to create a logical replication slot with the 'bdr' plugin, since that's what BDR uses.
2) If node3 has the same name and if as node1, won't that introduce a conflic? Don't I need to clean that up before node3 can join the replication group?
It will not have the same sysid. bdr_init_copy resets it normally. If you're doing it manually you'd have to run pg_resetxlog with the option to reset the sysid, create the new slots with the new sysid, then make sure bdr_init_copy doesn't reset the sysid again it afterwards when it brings the new node up.
Honestly I don't remember the exact steps that had to be performed before bdr_init_copy got support for automating the pg_basebackup step. That's the supported way to do it. I'm trying to prepare some conference presentations and a new pglogical release so I can't presently dig into it further for you; you may need to take a look at the bdr_init_copy sources and/or study how the node bringup works in more detail.
I can see it being useful to add a new mode to bdr_init_copy where you tell it to generate a sysid and make new slots for that sysid; *then* you make a snapshot and restore it, then you run bdr_init_copy again to finish bringup, resetting the sysid to the new value and finishing setup. There's nothing like that now though.
Hi Craig, how are you?
Just as an update, I figured it out...
First, here's my setup:
Node 1 and Node 2 running postgresql-bdr94-bdr and bdr enabled.
Node 3 with the same setup, ready to receive the snapshot clone.
These were my steps:
1) pg_start_backup
2) take snapshot from Node 1
3) pg_stop_backup
(alternatively, one could bring a node down and take the snapshot, instead of the start_backup/stop_backup procedure)
4) bring clone up on Node 3
5) no need for pg_resetxlog -s, otherwise you'll get error 'postgresql-bdr94-bdr'
6) bdr_init_copy
7) checking the logs, I see the error 'ERROR: replication slot "bdr_16385_6241964183952916534_1_16385__" already exists'. It's trying to create the replication slot for Node 2, even though it's already there
8) pg_drop_replication_slot('bdr_16385_6241964183952916534_1_16385__')
9) bdr on Node 3 finally (re)creates the replication slot for Node 2 and resumes its operations.
Make some tests on all nodes, delete some previous records, insert new ones... All seems to be working... Except for that minor hickup on step 7, all went fine. Maybe bdr could check if the replication slot is already exists before trying to create it and move along if it's already there?
Thanks a lot!
On Fri, Jan 22, 2016 at 6:57 AM Craig Ringer <craig@2ndquadrant.com> wrote:
On 21 January 2016 at 20:46, (Daniel Stolf) <dstolf@gmail.com> wrote:So here's what I don't get:1) if I have to create a new replication slots on node1 and 2 beforehand using "pg_create_physical_replication_slot" , don't they need the if of node3 on their name?You need to create a logical replication slot with the 'bdr' plugin, since that's what BDR uses.2) If node3 has the same name and if as node1, won't that introduce a conflic? Don't I need to clean that up before node3 can join the replication group?It will not have the same sysid. bdr_init_copy resets it normally. If you're doing it manually you'd have to run pg_resetxlog with the option to reset the sysid, create the new slots with the new sysid, then make sure bdr_init_copy doesn't reset the sysid again it afterwards when it brings the new node up.Honestly I don't remember the exact steps that had to be performed before bdr_init_copy got support for automating the pg_basebackup step. That's the supported way to do it. I'm trying to prepare some conference presentations and a new pglogical release so I can't presently dig into it further for you; you may need to take a look at the bdr_init_copy sources and/or study how the node bringup works in more detail.I can see it being useful to add a new mode to bdr_init_copy where you tell it to generate a sysid and make new slots for that sysid; *then* you make a snapshot and restore it, then you run bdr_init_copy again to finish bringup, resetting the sysid to the new value and finishing setup. There's nothing like that now though.--