[Bucardo-general] Hot database synchronization
ericdes
eric at vcardprocessor.com
Wed Mar 18 11:50:53 UTC 2009
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
>
More information about the Bucardo-general
mailing list