Thanks, I removed the other nodes from bdr.bdr_nodes table, deleted all the bdr_connections and pg_replication_identifier entries, dropped the pg_replication_slots restarted the instance and then trying the ALTER statement resulted in:
ERROR: No peer nodes or peer node count unknown, cannot acquire DDL lock
HINT: BDR is probably still starting up, wait a while
The only way I could issue the statement is run the following to convert the node to a standalone instance:
BEGIN;
SET LOCAL bdr.permit_unsafe_ddl_commands = true;
SET LOCAL bdr.skip_ddl_locking = true;
security label for 'bdr' on database deliver is '{"bdr": false}';
COMMIT;
I am still puzzled as to why the bdr_nodes node_status was reporting "i" when there were no errors in the logs.
-Selim
________________________________________
From: pgsql-general-owner@xxxxxxxxxxxxxx [pgsql-general-owner@xxxxxxxxxxxxxx] on behalf of Andreas Kretschmer [andreas@xxxxxxxxxxxxxxx]
Sent: Friday, December 04, 2015 9:59 AM
To: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: BDR: ALTER statement hanging
> Selim Tuvi <stuvi@xxxxxxx> hat am 4. Dezember 2015 um 18:46 geschrieben:
>
>
> Yes they seem to be active:
>
> deliver=# select * from pg_replication_slots;
> slot_name | plugin | slot_type | datoid |
> database | active | xmin | catalog_xmin | restart_lsn
> ------------------------------------------+--------+-----------+--------+----------+--------+------+--------------+-------------
> bdr_533136_6223770712502831127_1_16389__ | bdr | logical | 533136 |
> deliver | t | | 182302 | 0/9C8A5598
> bdr_533136_6223800735012265413_1_16389__ | bdr | logical | 533136 |
> deliver | t | | 182302 | 0/9C8A5598
> (2 rows)
>
> Although when I look at bdr.bdr_nodes I see the status as still initializing
> for the other two nodes, I don't know if that could cause this problem:
>
> deliver=# select * from bdr.bdr_nodes;
> node_sysid | node_timeline | node_dboid | node_status |
> node_name |
> node_local_dsn
> |
> node_init_from_dsn
> ---------------------+---------------+------------+-------------+-------------------------------------+---------------------------------------------------------------
> -----------------------------------------+------------------------------------------------------------------------------------------------------
> 6212648563684174798 | 1 | 533136 | r |
> pe-deliverdb-sf-01v | host=pe-deliverdb-sf-01v port=5432 dbname=deliver
> user=deliver_admin password=xxxxx |
> 6223770712502831127 | 1 | 16389 | i |
> pe-deliverdb-sing-01v | host=pe-deliverdb-sing-01v port=5432 dbname=deliver
> user=deliver_admin password=xxxxx | host=pe-deliverdb-sf-01v port=5432
> dbname=deliver user=deliver_admin password=xxxxx
> 6223800735012265413 | 1 | 16389 | i |
> pe-deliverdb-lon-01v | host=pe-deliverdb-lon-01v port=5432 dbname=deliver
> user=deliver_admin password=xxxxx | host=pe-deliverdb-sf-01v port=5432
> dbname=deliver user=deliver_admin password=xxxxx
>
> -Selim
>
I think, the state 'i' is the main reason for your problem, because of: "i-
Joining: The node is doing initial slot creation or an initial dump and load".
But i can't tell you why this nodes are in this state.
Regards, Andreas
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
