On Wed, 13 Jan 2016, Tory M Blue wrote:
Tory,
You talk about slon 'initializing'. When subscriptions start it needs to
wait until all in progress transactions are committed before starting the
copy. Once your cluster is subscribed a create index shouldn't block things.
Postgres 9.4.5 slon 2.2.3 CentOS 6.6
Afternoon
Based on some searching this is not new, but I have failed to find an answer
for it.. I had an index running on a non
replicated table but slon would not initialize due to that index, something
about set 1 failed due to a previous PID.
Now I'm trying to to reindex 2 tables that are not in the slon relationship,
but slon is backing up, once I stop the
reindex, slon replicates just fine (immediately). Something is wonky (its a
technical term, you can look it up!: ) )
So killing the index, I was able to add my host, but now I'm trying to add
another 2 indexes and it's backing up slon, and
or one is holding up the other.
So now I'm trying to create 2 indexes concurrently...
These are the 2 i am creating.
One is waiting on the other. (Waiting is true in pg_stat_Activity)
If I kill the 1st the second starts. Nothing is querying this table.
CREATE INDEX CONCURRENTLY idx_impsarchive_psttstamp
ON torque.impressions_archive
USING btree
(timezone('US/Pacific'::text, timezone('UTC'::text, log_tstamp)))
TABLESPACE torquespace;
-- Index: torque.idx_impsarchive_system
-- DROP INDEX torque.idx_impsarchive_system;
CREATE INDEX CONCURRENTLY idx_impsarchive_system
ON torque.impressions_archive
USING btree
(system COLLATE pg_catalog."default")
TABLESPACE torquespace;
Also when I had it running non-concurrently they both ran at the same time. But
slony got backed up. It didn’t explicitly
say that it was waiting. But after about 45 minutes, data had not replicated. I
killed the indices and it replicated
immediately. This table is not being replicated.
I also queried sl_table on the master and this table is not there
tab_id | tab_reloid | tab_relname | tab_nspname | tab_set |
tab_idxname | tab_altered | tab
_comment
--------+------------+---------------------+-------------+---------+--------------------------+-------------+-----------
-----------------
225 | 17959 | variables | torque | 2 |
pk_variables_name | f |
replicatedtable
226 | 17935 | impsbulkimporteven | torque | 2 |
pk_impsbulkimporteven_id | f |
torque.impsbulkimporteven
228 | 17947 | impsbulkimportodd | torque | 2 |
pk_impsbulkimportodd_id | f |
torque.impsbulkimportodd
233 | 17919 | impressions_daily | torque | 2 |
pk_impsdaily_id | f |
torque.impressions_daily
235 | 17907 | impressions | torque | 2 |
pk_impressions_id | f | torque.impressions
(5 rows)
So I want to create these indexes, they are not being replicated but they
completely freeze replication. Same thing happened
when I added a node, slon would not initialize until we killed the same index
creation on this same non replicated table.
I'm not sure what is going on and my apologies i deleted the set 1 failed, due
to a previous pid message, but if this table
is not in replication, why is it causing slon to be blocked?
Thanks
Tory
_______________________________________________
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general
