Re: BDR: Can a node live alone after being detached

Hi Craig,

Thanks for your response.

Le 16/06/2015 03:58, Craig Ringer a écrit :
> On 15 June 2015 at 17:19, Sylvain MARECHAL <marechal.sylvain2@gmail.com> wrote:
>> Is it possible to completely detach a node so that it can live alone
> Yes. On a different node to the one you want to remove,
> bdr.bdr_part_by_node_names(ARRAY['the_node_to_remove'])  to safely
> remove it without disrupting the other nodes.
>
>> in particular that DDL are again possible on that node?
> DDL is possible on a BDR node anyway, just with some limitations.
>
>> I tried with a simple node without success:
>> <<<
>> postgres=# create database test template template0;
>> CREATE DATABASE
>> postgres=# \c test
>> You are now connected to database "test" as user "dbadmin".
>> test=# CREATE EXTENSION btree_gist;
>> CREATE EXTENSION
>> test=# CREATE EXTENSION bdr;
>> CREATE EXTENSION
>>
>> -- DDL are still possible
>> test=# create table before_node_creation (i int primary key not null);
>> CREATE TABLE
>>
>> -- Now create the group. After that, DDL are now forbidden
>> test=# SELECT bdr.bdr_group_create(local_node_name :=
>> 'node1',node_external_dsn := 'host=172.27.118.64 port=5432 dbname=test');
>>
>> test=# create table after_node_creation (i int primary key not null);
>> ERROR:  No peer nodes or peer node count unknown, cannot acquire DDL lock
>> HINT:  BDR is probably still starting up, wait a while
> Well, DDL is only forbidden because it hasn't successfully joined yet.
> Check the PostgreSQL logs to see what it's doing.

Nothing special in the logs here. What I would like to emphasize is that
a single node can not live alone.

>
>> -- Now detach the group, in the hope to create some table
>> -- But this does not work. DDL are still forbidden
>> test=# select bdr.bdr_part_by_node_names('{node1}');
> You shouldn't part a node from its self. The next revision will
> prevent this with an error.

Ok, this was not clear for me.

>
>> test=# create table after_node_creation (i int primary key not null);
>> ERROR:  No peer nodes or peer node count unknown, cannot acquire DDL lock
>> HINT:  BDR is probably still starting up, wait a while
> Currently, once detached, the BDR extension isn't disabled on the node.
>
> Support for that is possible, but not yet implemented. We need to add
> a two-phase part protocol where the node confirms it has left the
> system and disables BDR. At present removed nodes aren't really a
> focus; it's expected that you're removing the node because you're
> going to retire it and will be deleting the cluster or dropping the
> database.
>
> In the mean time you can remove the security label on the database to
> disable BDR once the node has been parted, so that BDR no longer
> activates on that node. The command filter prevents this so you'll
> have to do this with the command filter off.
>
> BEGIN;
> SET LOCAL bdr.permit_unsafe_ddl_commands = true;
> SET LOCAL bdr.skip_ddl_locking = true;
> security label for 'bdr' on database bdr_testdb is '{"bdr": false}';
> COMMIT;

Ok, I will do this as a workaround.
But having a function doing the detach() properly would be really nice.

>
> Out of interest, why do you want to detach a node and keep using it as
> a standalone DB?
>
>

This is related to the design of our application: We would like to
provide to our users a flexible way to add and remove nodes, for
failover (2 nodes) and scalability reasons (possibly more than 2 nodes
with load balancing).
If for some reason (long time maintenance for one of the machine,
testing the application ...) the user wants to remove the failover, we
would like him to be able to keep at least one DB without the need of
dropping/recreating it to minimize the down time.
Of course, in a real production scenario, the user won't probably change
often its configuration, but I think this is really useful to have this
kind of flexibility when taking control of the application.

Sylvain