[Bucardo-general] Hot database synchronization
Lev Tannen
levtannen at gmail.com
Wed Mar 18 14:46:23 UTC 2009
Hello everybody, who participated in this discussion
I have tested Adam's approach and it works fine.
What I did:
On A site
1. Stopped Bucardo (bucardo_ctl stop 'testing hot restoration')
2. Inserted, deleted, updated records
3. Created dump (pg_dump --data-only --disable-triggers mydb > test.dump)
4 Inserted, delete, updated records.
5. Transferred the dump to the site B
6. To imitate the database crash. Deleted (truncated) all tables on site B
including bucardo_delta, bucardo_track and bucardo_delta_targets.
7. Restored dump (./bin/psql ; \i test.dump)
Note. Because I created the dump in the step 3 as a plain text I did not use
pg_restore).
8. Switched conflict_resolution value for all tables in the table goat from
skip to target
Note. I think this is an excessive precaution.
9. Started Bucardo (bucardo_ctl start 'testing hot restoration')
10. Was amused that Bucardo did not report any conflicts and restored all
changes made before and after the dump creation.
Lev
On Wed, Mar 18, 2009 at 7:50 AM, ericdes <eric at vcardprocessor.com> wrote:
> Ben,
>
> You're correct about the sequences' last values not being updated. I now
> understand why I got into that trouble recently!
>
> Eric.
>
> On 3/18/2009 10:23 AM, Ben Allen wrote:
> > Eric the problem with that approach is if you have any SEQUENCES,
> > SERIAL datatypes in place a full copy does not increment them as an
> > import of a data dump would do, at least thats what I found in my
> > testing. Also Lev's first requirement was to not to bring database A
> > down, hence the email title Hot database sync.
> >
> > Adam's scenario should work perfectly (I haven't gotten to testing it
> > yet) to keep database A alive through out the process, bring database
> > B up, and capture any new writes while database B is being restored.
> >
> > Regards,
> >
> > Ben
> >
> > On Mar 17, 2009, at 10:17 PM, ericdes wrote:
> >
> >> Lev,
> >>
> >> Why don't you recreate the structure of the B database (whichout the
> >> bucardo schema), switch the sync from 'swap' to 'fullcopy' (the entire
> >> source will be replicated), start the bucardo daemon, then once
> >> processed, stop the database A, the bucardo daemon, eventually delete
> >> the content of the tables in the bucardo schema of the database A,
> >> switch back to 'swap' (the bucardo schema in database A will be
> >> recreated), restart the database A and the bucardo daemon.
> >>
> >> Eric.
> >>
> >>
> >> On 3/18/2009 12:16 AM, Lev Tannen wrote:
> >>> Adam,
> >>>
> >>> You are right, there is a disable-triggers option in pg_dump. I
> >>> somehow
> >>> overlooked it. Probably because I looked for disable_triggers.
> >>> I am still having doubts about replaying inserts over the dump
> >>> (#9). But
> >>> because you did it and it worked I hope it will work for me as
> >>> well. I
> >>> will try and will let you know the result.
> >>> Thank you,
> >>> Lev Tannen
> >>>
> >>> On Tue, Mar 17, 2009 at 2:11 PM, Lev Tannen<levtannen at gmail.com
> >>> <mailto:levtannen at gmail.com>> wrote:
> >>>
> >>> Hello Adam and thank you for response.
> >>>
> >>> Most likely I just imagine things, but I see some problems with
> >>> your algorithm.
> >>> Bellow I sumarized my questions to you.
> >>> I would appreciate your futher clarifications.
> >>>
> >>>
> >>> 4. Recreate bucardo tables on whichever site you run bucardo,
> >>> goats/herd/sync (at this point bucardo should not be running)
> >>> My understanding is that populating the sync table will cause
> >>> creating triggers on site B because the type of replication was
> >>> "swap". These triggers, if I will not explicitly disable them,
> >>> will
> >>> cause Bucardo to try to move data from B to A later. I could
> >>> disable triggers, but having about 30 replicated tables I consider
> >>> that to be troublesome. I hope I could achieve the same result by
> >>> setting the replication type to pushdelta during the process and
> >>> switching it to "swap" later.
> >>>
> >>>
> >>> 5. Site A. Do data only dump with --disable-triggers
> >>> What does --disable-triggers mean? I am using Postgresql's
> >>> pd_dump.
> >>> I did not find such an option in its description. Should I use
> >>> another utility? If --disable-triggers means to insert into the
> >>> dump
> >>> instructions to suppress all trigers during the restoration it
> >>> would
> >>> eliminate the problem in p.4.
> >>>
> >>>
> >>> 9. Start bucardo! should be done
> >>> At that point I expect to get a number of conflicts because
> >>> Bucardo
> >>> will replicate not only events that occur after the dump was
> >>> created, but also those that occur before and during the
> >>> process of
> >>> creating a dump. Could I hope that Bucardo will behave correctly
> >>> without my intervention?
> >>>
> >>>
> >>> Thank you,
> >>> Lev Tannen
> >>> (Sorry for my English)
> >>>
> >>>
> >>> On Tue, Mar 17, 2009 at 1:14 PM, Adam Wendt<thelsdj at gmail.com
> >>> <mailto:thelsdj at gmail.com>> wrote:
> >>>
> >>> Here is how I do it:
> >>>
> >>> (assuming all of postgresql on site b is lost)
> >>>
> >>> 1. If bucardo db was on Site A then remove the old
> >>> sync/goat/herd etc
> >>> 2. Site A. Dump a schema only backup and exclude the bucardo
> >>> namespace so you aren't copying triggers
> >>> 3. Site B. Load schema, just table definitions, leave out
> >>> constraints and indexes as they will slow down load from
> >>> backup
> >>> 4. Recreate bucardo tables on whichever site you run bucardo,
> >>> goats/herd/sync (at this point bucardo should not be running)
> >>> 5. Site A. Do data only dump with --disable-triggers
> >>> 6. Transfer backup to Site B.
> >>> 7. Site B. Load backup
> >>> 8. Site B. Load indexes/constraints and vacuum analyze full if
> >>> you like doing that sort of thing
> >>> 9. Start bucardo! should be done
> >>>
> >>> Adam
> >>> Adam Wendt Consulting
> >>>
> >>> On Tue, Mar 17, 2009 at 9:04 AM, Lev Tannen<
> levtannen at gmail.com
> >>> <mailto:levtannen at gmail.com>> wrote:
> >>>
> >>> Hello,
> >>> Scenario:
> >>> Two Postgresql sites (A and B) with master to master to
> >>> master replication running for sometime. Site B crashed. I
> >>> would like to restore restore it from and to
> >>> synchronize it
> >>> with A. I want to do this without stopping the site A.
> >>> Could anybody point my to a posting with a detail
> >>> instruction on how to do this please.
> >>>
> >>> In the case there is no detailed instruction, bellow is
> >>> my
> >>> understanding on how this should be done. Please point me
> >>> where I am wrong or what I have missed.
> >>>
> >>> Thank you,
> >>> Lev
> >>>
> >>> Steps.
> >>>
> >>> 1. On A site. Create a backup using pg_dump (pg_dumpall).
> >>> Write down a backup start time.
> >>> 2. On A site. Delete all records from bucardo_delta with
> >>> starting date before the backup time.
> >>> Note this step is not necessary, but useful to prevent
> >>> excessive inserts later.
> >>> 3. Recreate and repopulate the Bucardo database if it was
> >>> destroyed (For example if it resided on site B)
> >>> 4. Change "swap" to "pushdelta" and set site A as the
> >>> source
> >>> and site B as the target).
> >>> 5. Change the type of conflict resolution to "source win".
> >>> 6. On B site. Use the backup to recreate or to replicate
> >>> site B.
> >>> 7. Start Bucardo. Wait until it replicate A to B. (If
> >>> necessary issue a kick).
> >>> 8. Return to "swap".
> >>>
> >>> I believe that will work, but I have some question.
> >>> When bucardo starts replication from A to B it can
> >>> encountert 3 types of conflicts.
> >>> 1. The row that it tries to delete does not exists. This
> >>> is
> >>> probably not a conflict at all.
> >>> 2. The row it tries to update had a value that differs
> >>> from
> >>> of what is expected. Setting the conflict resolution
> >>> type to
> >>> "source win" should handle this.
> >>> 3. The row it tries to insert already exists. The correct
> >>> behavior in this case would be to ignore the conflict
> >>> and do
> >>> nothing or to delete and reinsert the row. My question is
> >>> how to persuate Bucardo to behave one of this way.
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>> _______________________________________________
> >>> Bucardo-general mailing list
> >>> Bucardo-general at bucardo.org<mailto:
> Bucardo-general at bucardo.org
> >>> https://mail.endcrypt.com/mailman/listinfo/bucardo-general
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> ------------------------------------------------------------------------
> >>>
> >>> _______________________________________________
> >>> Bucardo-general mailing list
> >>> Bucardo-general at bucardo.org
> >>> https://mail.endcrypt.com/mailman/listinfo/bucardo-general
> >> _______________________________________________
> >> Bucardo-general mailing list
> >> Bucardo-general at bucardo.org
> >> https://mail.endcrypt.com/mailman/listinfo/bucardo-general
> >
> > Ben Allen
> >
> > System Administrator
> > Influenza Sequence Database
> > Los Alamos National Laboratory
> >
> > _______________________________________________
> > Bucardo-general mailing list
> > Bucardo-general at bucardo.org
> > https://mail.endcrypt.com/mailman/listinfo/bucardo-general
> >
>
> _______________________________________________
> Bucardo-general mailing list
> Bucardo-general at bucardo.org
> https://mail.endcrypt.com/mailman/listinfo/bucardo-general
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20090318/f09006f2/attachment.html
More information about the Bucardo-general
mailing list