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

[Donald StDenis]

I’m not a trained DB Admin so sorry if I don’t understand completely what you are asking for, but yes I can show these things if you can explain what they are exactly.

I believe you want the tigger information from the slave and I did include both tables from the slave by doing a ‘\d’, copied again here.  I see trigger stuff in there, but is there another sql command that would give better information?  Or if you need information from the master - I can get that too.

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

Right now I get a full schema dump with no data from the master to the slave using this command: 

+ pg_dump --host=stout-pg1.goodsearchcorp.com --dbname=good_rails_production --user=bucardo --schema-only --create --format=plain
+ echo 'CREATE USER *** WITH LOGIN SUPERUSER ENCRYPTED PASSWORD ‘\’******'\''; CREATE USER *** WITH LOGIN ENCRYPTED PASSWORD '\’’*****\'';'
+ psql bucardo
ERROR:  role “***" already exists
ERROR:  role “****" already exists
+ psql --dbname=postgres --user=bucardo -f schema-all.sql

If there is another command with more information on triggers I’d be happy to oblige.

Don.


> On Jan 26, 2017, at 7:03 AM, Greg Sabino Mullane <greg at endpoint.com> wrote:
> 
> On Wed, Jan 25, 2017 at 10:00:46AM -0800, Donald StDenis wrote:
>> If I sync with these tables: public.search_events_y2015_m** 
>> I get duplication in public.all_search_events:
> ....
>> Number of child tables: 17 (Use \d+ to list them.)
> 
> Ah...inheritance...and partitioning. That could certainly cause some of 
> the issues you are seeing.
> 
> I would suspect that the bucardo triggers are firing on the parent table and storing the 
> primary key, but the data is going to the child tables. Can you show the trigger or 
> rule, if any, that is on the parent table? Bucardo and partitioning is not an 
> area that is highly used: it is also possible Bucardo is missing, for example, 
> and ONLY somewhere in its calls, which would further gum things up, as generally, 
> we do not want to involve child tables unless being explicit.
> 
> -- 
> 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/20170126/ca57d1fa/attachment-0001.html>