[Bucardo-general] Failed to add sync: DBD::Pg::st execute failed: ERROR: DBD::Pg::db do failed

[David Christensen]

Hi Joel,

Does this error show up if you exclude this specific table from the sync?  Can you log in with the indicated rds_superuser role and verify that you can issue:

    BEGIN; SET session_replication_role = replica;

and it sticks?
--
David Christensen
Senior Software and Database Engineer
End Point Corporation
david at endpoint.com
785-727-1171


> On Jan 30, 2020, at 1:55 AM, Joel Ng <joel.ng at versafleet.co> wrote:
> 
> Hello,
> 
> I am trying to set up multi-master replication on AWS RDS using this guide: https://medium.com/preply-engineering/postgres-multimaster-34f2446d5e14
> 
> So far it works well (with some pg_hba.conf changes), but I'm stuck at the `bucardo add sync mydb_sync herd=sample_herd dbs=mydb_servers_group` step.
> 
> I get this error:
> 
> WARNING:  Issuing rollback() due to DESTROY without explicit disconnect() of DBD::Pg::db handle dbname=vf_production;host=vf-production-test-copy.redacted.ap-southeast-1.rds.amazonaws.com;port=5432 at line 29.
> CONTEXT:  PL/Perl function "validate_sync"
> SQL statement "SELECT validate_sync('mydb_sync')"
> PL/Perl function "validate_sync"
> Failed to add sync: DBD::Pg::st execute failed: ERROR:  DBD::Pg::db do failed: ERROR:  permission denied for relation spatial_ref_sys at line 126. at line 30.
> CONTEXT:  PL/Perl function "validate_sync" at /usr/bin/bucardo line 4612.
> 
> From Googling around it seems that all users in the bucardo.db table needs to be superusers. I'm unsure how to check it, are these the correct users to check?:
> 
> bucardo=# \du
>                                      List of roles
>    Role name   |                         Attributes                         | Member of
> ---------------+------------------------------------------------------------+-----------
>  bucardo       | Superuser                                                  | {}
>  postgres      | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
> 
> bucardo=# SELECT * FROM db;
> -[ RECORD 1 ]--------+------------------------------------------------------------------------
> name                 | source_db
> dbtype               | postgres
> dbhost               | vf-production-test-copy.redacted.ap-southeast-1.rds.amazonaws.com
> dbport               | 5432
> dbname               | vf_production
> dbuser               | vf_db
> dbpass               | redacted
> dbconn               |
> dbservice            |
> pgpass               |
> status               | active
> server_side_prepares | t
> makedelta            | f
> cdate                | 2020-01-30 05:41:21.796437+00
> -[ RECORD 2 ]--------+------------------------------------------------------------------------
> name                 | dest_db
> dbtype               | postgres
> dbhost               | vf-production-test-copy-2.redacted.ap-southeast-1.rds.amazonaws.com
> dbport               | 5432
> dbname               | vf_production
> dbuser               | vf_db
> dbpass               | redacted
> dbconn               |
> dbservice            |
> pgpass               |
> status               | active
> server_side_prepares | t
> makedelta            | f
> cdate                | 2020-01-30 06:11:16.029221+00
> 
> vf_db is an rds_superuser in the RDS.
> 
> I'm not sure what else I can check or do, why can't I sync?
> 
> Thank you.
> 
> _______________________________________________
> Bucardo-general mailing list
> Bucardo-general at bucardo.org
> https://bucardo.org/mailman/listinfo/bucardo-general

-------------- 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/20200130/8a6ffe08/attachment.sig>