Re: BDR problem rejoining node

[Sylvain Marechal <marechal.sylvain2@xxxxxxxxx>]

2017-02-11 1:34 GMT+01:00 Tanner Kerr <tanner@xxxxxxxx>:

I have two databases being replicated across three nodes with bdr. The third node filled up and crashed. I removed this node from the group successfully, but now I'm having trouble rejoining it. I'm able to re-join the one database no problem. However, trying to do a bdr join on the other causes the two active nodes to slow down considerably, and refuses to join. This database copies the data after a few minutes, however, the join fails and bdr.bdr_nodes on the third node shows only itself in the group with status 'i'. The active nodes don't show anything different in the bdr.bdr_nodes. All they can see is a new pg_replication_slot for that node. The logs say "previous init failed, manual cleanup is required" Even though there are no bdr_connections or broken bdr_nodes entries. Everytime I've tried, I've removed the pg_replication slots, the bdr_connections, and the bdr_nodes (bdr.remove_bdr_from_local_node()). I've also dropped the cluster and recreated it again each time with:

pg_dropcluster 9.4 main --stop

/usr/bin/pg_createcluster --start 9.4 -d /var/lib/postgresql/9.4/main main

The database failing to copy is small only around 60M. The one being copied successfully, is around 1600M. 

The join command I've been using:

select bdr.bdr_group_join(local_node_name := 'NODENAMEOFREBUILTNODE', node_external_dsn := 'host=IPOFREBUILTNODE port=5432 dbname=MYDB connect_timeout=50 keepalives_idle=5 keepalives_interval=1', join_using_dsn := 'host=ACTIVENODEIP  port=5432 dbname=MYDB connect_timeout=50 keepalives_idle=5 keepalives_interval=1');

I'm not sure why it works for one database and not the other.

The log from the failed join attemp is below:

2017-02-10 14:49:24 PST [6981-10] LOG:  Creating replica with: /usr/lib/postgresql/9.4/bin/bdr_initial_load --snapshot 00C58720-1 --source "host=ACTIVEIP  port=5432 dbname=MYDB connect_timeout=5

0 keepalives_idle=5 keepalives_interval=1" --target "host=REBUILDHOST port=5432 dbname=MYDB connect_timeout=50 keepalives_idle=5 keepalives_interval=1" --tmp-directory "/tmp/postgres-bdr-00C58720-1

.6981", --pg-dump-path "/usr/lib/postgresql/9.4/bin/bdr_dump", --pg-restore-path "/usr/lib/postgresql/9.4/bin/pg_restore"

Dumping remote database "connect_timeout=30 keepalives=1 keepalives_idle=20 keepalives_interval=20 keepalives_count=5   host=ACTIVEIP  port=5432 dbname=MYDB connect_timeout=50 keepalives_idle=5 

keepalives_interval=1 fallback_application_name='bdr (6385577151748866557,1,16391,): init_replica dump'" with 1 concurrent workers to "/tmp/postgres-bdr-00C58720-1.6981"

Restoring dump to local DB "host=REBUILDHOST port=5432 dbname=MYDB connect_timeout=50 keepalives_idle=5 keepalives_interval=1 fallback_application_name='bdr (6385577151748866557,1,16391,): init_replica restore' options='-c bdr.do_not_replicate=on  -c bdr.permit_unsafe_ddl_commands=on -c bdr.skip_ddl_replication=on -c bdr.skip_ddl_locking=on -c session_replication_role=replica'" with 1 concurrent workers from "/tmp/postgres-bdr-00C58720-1.6981"

2017-02-10 14:57:16 PST [7271-1] postgres@MYDB NOTICE:  extension "btree_gist" already exists, skipping

2017-02-10 14:57:16 PST [7271-2] postgres@MYDB NOTICE:  extension "bdr" already exists, skipping

2017-02-10 14:57:16 PST [7271-3] postgres@MYDB NOTICE:  extension "plpgsql" already exists, skipping

2017-02-10 14:57:16 PST [7271-4] postgres@MYDB NOTICE:  extension "pgcrypto" already exists, skipping

2017-02-10 14:57:16 PST [7271-5] postgres@MYDB NOTICE:  extension "uuid-ossp" already exists, skipping

*alters tables and builds indexes*

2017-02-10 14:57:23 PST [6981-10] DEBUG:  syncing bdr_nodes and bdr_connections

2017-02-10 14:57:23 PST [6981-11] ERROR:  BEGIN or table locking on remote failed: 

2017-02-10 14:57:23 PST [6884-11] LOG:  worker process: bdr db: MYDB (PID 6981) exited with exit code 1

2017-02-10 14:57:28 PST [6884-12] LOG:  starting background worker process "bdr db: MYDB"

2017-02-10 14:57:28 PST [7274-1] NOTICE:  version "1.0" of extension "btree_gist" is already installed

2017-02-10 14:57:28 PST [7274-2] NOTICE:  version "1.0.1.0" of extension "bdr" is already installed

2017-02-10 14:57:28 PST [7274-3] DEBUG:  per-db worker for node bdr (6385577151748866557,1,16391,) starting

2017-02-10 14:57:28 PST [7274-4] DEBUG:  init_replica init from remote host=ACTIVEIP  port=5432 dbname=MYDB connect_timeout=50 keepalives_idle=5 keepalives_interval=1

2017-02-10 14:57:28 PST [7274-5] ERROR:  previous init failed, manual cleanup is required

2017-02-10 14:57:28 PST [7274-6] DETAIL:  Found bdr.bdr_nodes entry for bdr (6385577151748866557,1,16391,) with state=i in remote bdr.bdr_nodes

2017-02-10 14:57:28 PST [7274-7] HINT:  Remove all replication identifiers and slots corresponding to this node from the init target node then drop and recreate this database and try again

Tanner

Did you check on the 2 other nodes if cleanup was complete, as suggested by the error message ?

- bdr.bdr_nodes and bdr.bdr_connections tables must not reference the node you have removed

- no orphan pg_replication_identifier related to the third node must be present (select * from pg_replication_identifier do see them, select pg_replication_identifier_drop('the_id'); to remove the orphan id(s))

- no orphan slot must be present (select * from pg_replication_slots  to list them, select pg_drop_replication_slot('the_slot'); to remove the orphan slot(s))

May be you could check if there are no dead worker on the alive node too (in pg_stat_activity)

And last, you may try to restart postgres on the alive nodes (I notice this seems sometimes necessary, but I am unable to explain why; at least, this makes some errors messages related to the dead node to disappear)

Hope this helps,

Sylvain