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