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

[Joel]

Hi David,

Removing the table worked! Fortunately that table is just a PostGIS 
extension and there's probably no syncing needed.

Also, the command works well:
vf_production=> BEGIN; SET session_replication_role = replica;
BEGIN
SET

Thank you, this is good enough. Everything seems to be working now.

On 31-Jan-20 1:06 AM, David Christensen wrote:
> 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