[Bucardo-general] Removing schema from busy database

[David Christensen]

> On May 30, 2018, at 12:45 PM, Pete Fuller <pfuller at 3sitracking.com> wrote:
> 
> Had attempted to use Bucardo for an upgrade between a 9.2 postgres instance to a new 9.6 instance, and it worked beautifully in my development environment.  In my production environment, due to the structure and busyness of some tables, it was adding too much load during the initial copy of tables and I’d like to start over with just a few tables, starting from a restored backup.
> 
> Attempts to remove the bucardo schema via "DROP SCHEMA bucardo CASCADE;”  end in a deadlock.    Is there a clean way to remove the schema, delta tables, etc?

Hi Pete,

You can remove pieces incrementally, for instance removing all of the triggers first, then dropping the rest of the schema.  You could generate a list of statements to run via something similar to this:

psql> select format('DROP TRIGGER %I ON %I;', tgname, tgrelid::regclass) from pg_trigger where tgname like 'bucardo_%’  \o drop_triggers.sql
psql> \i drop_triggers.sql
psql> DROP SCHEMA bucardo CASCADE;

If run as multiple transactions this should run without issues, and once the triggers are cleaned up the actual DROP SCHEMA should work without issue.

HTH,

David
--
David Christensen
End Point Corporation
david at endpoint.com
785-727-1171



-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 833 bytes
Desc: Message signed with OpenPGP
URL: <https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20180530/057d4ded/attachment.sig>