Re: BDR problem rejoining node

2017-02-11 1:34 GMT+01:00 Tanner Kerr <tanner(at)itel(dot)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(at)MYDB NOTICE: extension
> "btree_gist" already exists, skipping
> 2017-02-10 14:57:16 PST [7271-2] postgres(at)MYDB NOTICE: extension "bdr"
> already exists, skipping
> 2017-02-10 14:57:16 PST [7271-3] postgres(at)MYDB NOTICE: extension
> "plpgsql" already exists, skipping
> 2017-02-10 14:57:16 PST [7271-4] postgres(at)MYDB NOTICE: extension
> "pgcrypto" already exists, skipping
> 2017-02-10 14:57:16 PST [7271-5] postgres(at)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