[Bucardo-general] onetimecopy=1 (fullcopy) replica not the same number records all tables

[Don St. Denis]

Hi,

I've installed Bucardo to upgrade a database from Pg9.2->Pg9.6.

I originally tried setting the sync onetimecopy=2 to do the first 
synchronization but the copy from fully truncated tables never happen 
and only a couple tables get records at all, then the system sits for 
hours doing nothing (the source is not doing any data updates - its a 
off-line staging system).

When I set onetimecopy=1 I get the majority of the database system 
copied but there are discrepancies on a few of the 277 tables and 100 
sequences in the herd. The one time copy takes about 4 hours. I 
performed this 2x and see the same exact discrepancies both times.

    $ diff ~/tmp/ale-pg1/tmp/ale-pg1_table-counts.txt ~/tmp/ale-pg1/tmp/stout-pg1_table-counts.txt
    2c2
    < Number of records in public.all_search_events: 47229304
    ---
    > Number of records in public.all_search_events: 24605524
    69c69
    < Number of records in public.search_clicks: 43458876
    ---
    > Number of records in public.search_clicks: 22649270
    73c73
    < Number of records in public.search_clicks_y2015_m12: 0
    ---
    > Number of records in public.search_clicks_y2015_m12: 1839664
    84,85c84,85
    < Number of records in public.search_events: 6576820
    < Number of records in public.search_events_y2015_m09: 0
    ---
    > Number of records in public.search_events: 3288410
    > Number of records in public.search_events_y2015_m09: 1981744
    101c101
    < Number of records in public.search_monthly_amounts: 11043050
    ---
    > Number of records in public.search_monthly_amounts: 5525019
    107c107
    < Number of records in public.search_monthly_amounts_y2006_m04: 0
    ---
    > Number of records in public.search_monthly_amounts_y2006_m04: 6988

All other tables appear to be correct.

I have not tested the onetimecopy=0 since that is for ongoing replication.

What should I try next?

Don.


On 01/14/2017 11:17 AM, Greg Sabino Mullane wrote:
> On Thu, Jan 12, 2017 at 10:36:00AM -0800, Donald StDenis wrote:
>> So I was able to make it start replication by allowing Postgres
>> user bucardo to have "trust"ed access on the local socket.
>>
>> This is slowing me to make progress right now but is not a
>> recommended solution for production.
> Agreed, not ideal: you want to use 'md5' instead of 'peer' or 'trust'.
> Then you can store the password in the ~postgres/.pgpass file.
>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20170118/28eaabdf/attachment.html>