[Bucardo-general] errors when setting new sync - role already exists?

[Perez Sanchez, Enrique]

Hello,

I have some difficulty understanding your situation. 
However:
- You seem to have Bucardo installed in both nodes, you only need it in 1 which can be either or even a 3rd node.
- Bucardo will add a schema and triggers on all your tables being replicated in both dbs if you go for a multimaster setup.
- I'd start from scratch making sure you only have the bucardo software running in 1 node.

BR!

> -----Original Message-----
> From: Bucardo-general <bucardo-general-bounces at bucardo.org> On Behalf
> Of Michael Richardson
> Sent: viernes, 17 de febrero de 2023 22:44
> To: bucardo-general at bucardo.org
> Subject: [Bucardo-general] errors when setting new sync - role already exists?
> 
> 
> After having setup bucardo successful in a test environment, I tried to
> operationalize my stuff on some virtual appliances.  The goal is to do forklift
> upgrades of the virtual appliances, but use bucardo to synchronize the
> databases before turning off the old machine.
> (Or to gain resilience by having a multimaster setup)
> 
> I've run into a blocker that I can't seem to get any additional info on.
> In this case the "old" machine shipped without bucardo, and I installed
> bucardo on it via apt-get, while the new machine had bucardo as part of the
> build process.  Aside from ~6 months differences in some packages, they are
> mostly identical.  There is certificate authentication between the two
> machines, and that seems to work:
> 
> bucardo at minerva-fountain:~$ psql
> psql (13.9 (Debian 13.9-0+deb11u1))
> Type "help" for help.
> 
> bucardo=# select * from bucardo.sync;
> bucardo=# \q
> bucardo at minerva-fountain:~$ psql -h minerva-fountain-01 psql (13.9
> (Debian 13.9-0+deb11u1)) SSL connection (protocol: TLSv1.3, cipher:
> TLS_AES_256_GCM_SHA384, bits: 256, compression: off) Type "help" for
> help.
> 
> bucardo=# select * from bucardo.sync;
> bucardo=# \q
> 
> ("minerva-fountain" is new machine, "minerva-fountain-01" is old machine.
> Names according to mdns)
> 
> I was looking at the sync table on both machines, and it's empty.
> 
> bucardo at minerva-fountain:~$ export BUCARDO_DEBUG=true
> bucardo at minerva-fountain:~$ bucardo add sync resilience
> relgroup=resilience dbs=old,me
> SQL: INSERT INTO bucardo.sync (dbs,name,herd) VALUES (?,?,?)
> $VAR1 = 'resilience';
> $VAR2 = 'resilience';
> $VAR3 = 'resilience';
> WARNING:  Issuing rollback() due to DESTROY without explicit disconnect() of
> DBD::Pg::db handle dbname=fountain at line 189.
> Failed to add sync: DBD::Pg::st execute failed: ERROR:  DBD::Pg::db do failed:
> ERROR:  role "bucardo" already exists at line 189. at line 30.
> CONTEXT:  PL/Perl function "validate_sync" at /usr/bin/bucardo line 4670.
> 
> My guess is that validate_sync thinks that bucardo is not properly installed on
> the old machine, and is trying to install it, that is failing.
> 
> I've tried to drop the bucardo table and role from the old machine, but:
> 
> postgres at minerva-fountain-01:~$ dropuser bucardo
> dropuser: error: removal of role "bucardo" failed: ERROR:  role "bucardo"
> cannot be dropped because some objects depend on it
> DETAIL:  60 objects in database fountain
> 
> where "fountain" is the database that I want to actually replicate.
> I don't quite understand why bucardo owns objects in my target database.
> 
> bucardo at minerva-fountain:~$ bucardo list all
> -- dbgroups:
> dbgroup: resilience  Members: me:target old:source
> -- databases:
> Database: me   Status: active  Conn: psql -U bucardo -d fountain
> Database: old  Status: active  Conn: psql -U bucardo -d fountain -h minerva-
> fountain-01
> -- relgroup:
> Relgroup: resilience  DB: old  Members: public.administrators,
> public.administrators_id_seq, public.ar_internal_metadata,
> public.certificates, public.certificates_id_seq, public.device_types,
> public.device_types_id_seq, public.devices, public.devices_id_seq,
> public.manufacturers, public.manufacturers_id_seq,
> public.schema_migrations, public.system_variables,
> public.system_variables_id_seq, public.voucher_requests,
> public.voucher_requests_id_seq, public.vouchers, public.vouchers_id_seq
> -- syncs:
> No syncs found
> -- tables:
> 9.  Table: public.administrators        DB: old  PK: id (bigint)
> 3.  Table: public.ar_internal_metadata  DB: old  PK: key (varchar)
> 4.  Table: public.certificates          DB: old  PK: id (integer)
> 11. Table: public.device_types          DB: old  PK: id (integer)
> 10. Table: public.devices               DB: old  PK: id (integer)
> 8.  Table: public.manufacturers         DB: old  PK: id (integer)
> 2.  Table: public.schema_migrations     DB: old  PK: version (varchar)
> 5.  Table: public.system_variables      DB: old  PK: id (integer)
> 6.  Table: public.voucher_requests      DB: old  PK: id (integer)
> 7.  Table: public.vouchers              DB: old  PK: id (integer)
> -- sequences:
> Sequence: public.administrators_id_seq    DB: old
> Sequence: public.certificates_id_seq      DB: old
> Sequence: public.devices_id_seq           DB: old
> Sequence: public.device_types_id_seq      DB: old
> Sequence: public.manufacturers_id_seq     DB: old
> Sequence: public.system_variables_id_seq  DB: old
> Sequence: public.voucher_requests_id_seq  DB: old
> Sequence: public.vouchers_id_seq          DB: old