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

[Donald StDenis]

Hi,

OK, took a while to do a binary search for the tables causing the issues. (NOTE: I am successfully using onetimecopy=2 and it seems to work, except for the duplication).

If I only sync public.all_search_events, then no duplication.

If I sync with these tables: public.search_events_y2015_m** I get duplication in public.all_search_events:

These tables in sync:
                 public.all_search_events \
                 public.search_events \
                 public.search_events_y2015_m09 \
                 public.search_events_y2015_m10 \
                 public.search_events_y2015_m11 \
                 public.search_events_y2015_m12 \
                 public.search_events_y2016_m01 \
                 public.search_events_y2016_m02 \
                 public.search_events_y2016_m03 \
                 public.search_events_y2016_m04 \
                 public.search_events_y2016_m05 \
                 public.search_events_y2016_m06 \
                 public.search_events_y2016_m07 \
                 public.search_events_y2016_m08 \
                 public.search_events_y2016_m09 \
                 public.search_events_y2016_m10 \              


Here is the create table info:

good_rails_production=# \d public.all_search_events
                                        Table "public.all_search_events"
     Column      |            Type             |                           Modifiers
-----------------+-----------------------------+----------------------------------------------------------------
 id              | integer                     | not null default nextval('all_search_events_id_seq'::regclass)
 charity_id      | integer                     |
 user_id         | integer                     |
 query           | citext                      |
 ip              | citext                      |
 campaign_id     | integer                     |
 toolbar_id      | citext                      |
 created_at      | timestamp without time zone | not null
 updated_at      | timestamp without time zone | not null
 amount          | numeric                     | not null default 0.01
 utm_campaign_id | character varying(255)      |
Indexes:
    "all_search_events_pkey" PRIMARY KEY, btree (id)
Triggers:
    bucardo_delta AFTER INSERT OR DELETE OR UPDATE ON all_search_events FOR EACH ROW EXECUTE PROCEDURE delta_public_all_search_events()
    bucardo_kick_one_table_sync AFTER INSERT OR DELETE OR UPDATE OR TRUNCATE ON all_search_events FOR EACH STATEMENT EXECUTE PROCEDURE bucardo_kick_one_table_sync()
    bucardo_kick_the_sync AFTER INSERT OR DELETE OR UPDATE OR TRUNCATE ON all_search_events FOR EACH STATEMENT EXECUTE PROCEDURE bucardo_kick_the_sync()
    bucardo_note_trunc_one_table_sync AFTER TRUNCATE ON all_search_events FOR EACH STATEMENT EXECUTE PROCEDURE bucardo_note_truncation('one_table_sync')
    bucardo_note_trunc_the_sync AFTER TRUNCATE ON all_search_events FOR EACH STATEMENT EXECUTE PROCEDURE bucardo_note_truncation('the_sync')
Number of child tables: 17 (Use \d+ to list them.)

And one of the tables causing duplicates during the onetimecopy=2:

good_rails_production=# \d public.search_events_y2015_m11
                                   Table "public.search_events_y2015_m11"
     Column      |            Type             |                         Modifiers
-----------------+-----------------------------+------------------------------------------------------------
 id              | integer                     | not null default nextval('search_events_id_seq'::regclass)
 charity_id      | integer                     |
 user_id         | integer                     |
 query           | citext                      |
 ip              | citext                      |
 campaign_id     | integer                     |
 toolbar_id      | citext                      |
 created_at      | timestamp without time zone | not null
 updated_at      | timestamp without time zone | not null
 amount          | numeric                     | not null default 0.01
 utm_campaign_id | character varying(255)      |
Indexes:
    "search_events_y2015_m11_pkey" PRIMARY KEY, btree (id)
    "search_events_y2015_m11_charity_id_idx" btree (charity_id)
    "search_events_y2015_m11_created_at_idx" btree (created_at)
    "search_events_y2015_m11_lower_ip_idx" btree (lower(ip::text) varchar_pattern_ops)
    "search_events_y2015_m11_lower_query_idx" btree (lower(query::text) varchar_pattern_ops)
    "search_events_y2015_m11_updated_at_idx" btree (updated_at)
    "search_events_y2015_m11_user_id_idx" btree (user_id)
Check constraints:
    "search_events_y2015_m11_created_at_check" CHECK (created_at >= '2015-11-01'::date AND created_at < '2015-12-01'::date)
Triggers:
    bucardo_delta AFTER INSERT OR DELETE OR UPDATE ON search_events_y2015_m11 FOR EACH ROW EXECUTE PROCEDURE delta_public_search_events_y2015_m11()
    bucardo_kick_the_sync AFTER INSERT OR DELETE OR UPDATE OR TRUNCATE ON search_events_y2015_m11 FOR EACH STATEMENT EXECUTE PROCEDURE bucardo_kick_the_sync()
    bucardo_note_trunc_the_sync AFTER TRUNCATE ON search_events_y2015_m11 FOR EACH STATEMENT EXECUTE PROCEDURE bucardo_note_truncation('the_sync')
Inherits: all_search_events





> On Jan 23, 2017, at 8:31 AM, Greg Sabino Mullane <greg at endpoint.com> wrote:
> 
> On Wed, Jan 18, 2017 at 04:40:59PM -0800, Don St. Denis wrote:
>> 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.
> 
> That's a lot of variables to work through. Could you create a new sync 
> with just a single table and see if you can duplicate the problem? 
> Showing the log.bucardo output for that sync run would help a lot.
> Perhaps this small table?:
> 
>>   < Number of records in public.search_monthly_amounts_y2006_m04: 0
>>   ---
>>> Number of records in public.search_monthly_amounts_y2006_m04: 6988
> 
> 
> -- 
> Greg Sabino Mullane greg at endpoint.com
> End Point Corporation
> PGP Key: 2529 DF6A B8F7 9407 E944  45B4 BC9B 9067 1496 4AC8
> 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20170125/3ff0c456/attachment.html>