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 <[email protected]> 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 [email protected]
> End Point Corporation
> PGP Key: 2529 DF6A B8F7 9407 E944 45B4 BC9B 9067 1496 4AC8
_______________________________________________
Bucardo-general mailing list
[email protected]
https://mail.endcrypt.com/mailman/listinfo/bucardo-general
