[Bucardo-general] Locking issues migrating from PG9.6 to PG11 in AWS Aurora

[David Christensen]

> On Feb 7, 2020, at 12:39 PM, Alfonso Perez <fon at revenuecat.com> wrote:
> 
> Hi list,
> 
> We have attempted to use Bucardo to migrate from PG9.6 to PG11 in AWS
> Aurora[1], but unfortunately, after about an hour of running sync,
> locking queries start piling up like crazy until the DB becomes
> unreachable, a failover is triggered, and we are able to drop the
> bucardo schema, resolving the locking, which gets the DB healthy
> again.

Hi Alfonso,

So to confirm, the sync got added successfully, it added all of the triggers, etc, and this is happening in the initial copy phase?

> Any tips on where to start trying to figure out the cause?
> Unfortunately, I do not have available backups of the database when
> the schema was created, and bucardo logs don't show anything
> meaningful.

I’d look at locks initially.  Broadly speaking for a workaround, you could look at adding the sync without the onetimecopy option and transfer the data out-of-band for the initial pgdump.

> [1] bucardo add sync pg11migration relgroup=alpha
> dbs=aurora9:source,aurora11:target onetimecopy=2
> conflict_strategy=bucardo_source

BTW, you will not need to specify a conflict strategy if there is only a single source.

Best,

David
--
David Christensen
Senior Software and Database Engineer
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://bucardo.org/pipermail/bucardo-general/attachments/20200207/fe88c4c5/attachment.sig>