BDR problem rejoining node

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

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