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

On 15 June 2015 at 17:19, Sylvain MARECHAL <marechal(dot)sylvain2(at)gmail(dot)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.

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

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

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

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