Hi Lucas, yes I forgot a very important point: my steps are valid if the source db gets no data changes during the process. I put the application above the db in “maintenance mode” to prevent users using the system when I do this kind of things.
I understand your solution, seems good to me so thanks for sharing, it can be useful if someone has to activate data replication on a db that is always active! Cheers, Mattia From: Lucas Possamai <[email protected]> Sent: lunedì 17 maggio 2021 12:44 To: IT Technical Analyst FOD <[email protected]> Cc: [email protected] Subject: Re: [Bucardo-general] Question about order of the Bucardo setup Hello, Just to help someone who's looking for the same kind of information in the future. I have done the steps as Mattia mentioned, and they did not work for me. If I take a dump of the database and import it into my target db, before setting up bucardo, the changes that happened between pg_dump and the activation of Bucardo, won't be replicated. Instead, I had to do the following steps: 1. Take a pg dump of the schema only from my source db (EC2) 2. Import the schema into my target db (RDS) 3. Set up bucardo * bucardo add db (source) * bucardo add db (target) * bucardo add tables * bucardo add sequences * bucardo add dbgroup * bucardo add sync with autokick=0 * bucardo validate sync 1. Take a pg_dump (data only) of the source db 2. Disable RDS backups and remove MultiAZ (if enabled) 3. Disable triggers and FKs to speed up the import 4. import the dump into RDS (data only) 5. Enable triggers and FKs in target db 6. Modify the RDS parameter group to add "session_replication_role = replica" 7. Reboot RDS 8. Start Bucardo replication * bucardo update sync geoop_sync autokick=1 * bucardo start * Just a reminder that my source db has traffic to it at all times. The process above is to replicate a live DB. Cheers! --- Kind regards, Lucas Possamai On Tue, 11 May 2021 at 22:58, Lucas Possamai <[email protected]<mailto:[email protected]>> wrote: On Tue, 11 May 2021 at 19:53, IT Technical Analyst FOD <[email protected]<mailto:[email protected]>> wrote: Hello, I don’t understand very well why you need a dump of the db; if you use that for having 2 databases with exactly the same data, then I would proceed like that: 1. Dump database A 2. Import dump into target database B 3. Add your source db to bucardo (bucardo add db A, with tables etc) bucardo add db db_A dbhost=myhost dbport=5432 dbname=mydbname dbuser=mydbuser dbpass=mydbpassword 4. Add your target db to bucardo (bucardo add db B, with tables etc) bucardo add db db_B dbhost=myhost_B dbport=5432 dbname=mydbname dbuser=mydbuser dbpass=mydbpassword 5. Create a sync that has: a. database A as source, database B as target if you want a master to slave replication bucardo add dbgroup grp_m2s_mydbname db_A:source db_B:target bucardo add sync sync_mydbname_m2s dbs=grp_m2s_mydbname [‘tables=’] b. both databases as source if you want a master to master replication (you should choose a conflit resolution strategy, I use “bucardo_latest”) bucardo add dbgroup grp_m2m_mydbname db_A:source db_B:source bucardo add sync sync_mydbname_m2m dbs=grp_m2m_mydbname conflict_strategy=bucardo_latest [‘tables=’] More precisely I created a dbgroup with both databases, and then I add that dbgroup to the sync. The “tables” parameter is optional, to specify a subset of tables to be replicated. 6. Start bucardo Yep, that makes sense! I was actually setting up bucardo (adding DB, tables, sequence, sync, etc) prior dumping and restoring the DB in the target... but the way you do it is the same way Frank does it, as per his emails... so that's great. That's what I was looking for. Cheers!
_______________________________________________ Bucardo-general mailing list [email protected] https://bucardo.org/mailman/listinfo/bucardo-general
