[Bucardo-general] Hot database synchronization
Ben Allen
bsa at lanl.gov
Wed Mar 18 04:53:31 UTC 2009
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
More information about the Bucardo-general
mailing list