Refresh Publication takes hours and doesn´t finish

[PegoraroF10 <marcos@xxxxxxxxxx>]

We use logical replication from a PG version 10.6 to a 11.2. Both are Ubuntu 16.04.
We have a hundred schemas with more or less a hundred tables, so number of tables is about 10.000. All replication is ok but when we try to do a REFRESH SUBSCRIPTION because we added a new schema, it takes hours and doesn´t finish. Then, if I go to our master server and do a select * from pg_publication_tables it doesn´t respond too. Then, analysing the source of view pg_publication_tables ...

create view pg_publication_tables as
  SELECT p.pubname, n.nspname AS schemaname, c.relname AS tablename FROM pg_publication p,
  (pg_class c JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.oid IN (SELECT pg_get_publication_tables.relid FROM pg_get_publication_tables((p.pubname) :: text) pg_get_publication_tables (relid)));
If we run both statements of that view separately

SELECT string_agg(pg_get_publication_tables.relid::text,',') FROM pg_get_publication_tables(('MyPublication')::text) pg_get_publication_tables (relid);
put all those oids retrieved on that IN of the view
select * from pg_Class c JOIN pg_namespace n ON n.oid = c.relnamespace 
WHERE c.oid IN (
OIDs List
);
Then it responds immediatelly

So, the question is .. can we change this view to select faster ? Just rewriting that view to a better select will solve ?
Is this view used by REFRESH SUBSCRIPTION ? We think yes because if we run refresh subscription or select from view it doesn´t respond, so ...


	
	
	


Sent from the PostgreSQL - general mailing list archive at Nabble.com.