Re: Design for In-Core Logical Replication

On 07/20/2016 10:08 AM, Simon Riggs wrote:
> <sect1 id="logical-replication-monitoring">
> <title>Monitoring</title>
> <para>
> pg_stat_replication
> </para>
> <para>
> pg_stat_subscription
> </para>
> </sect1>

and probably also `pg_stat_publication` or some other way to see, what
tables are currently in a PUBLICATION, who has subscribed etc.

> <programlisting>
> CREATE PUBLICATION mypub;
> ALTER PUBLICATION mypub ADD TABLE users, departments;
> </programlisting>
Would a subscription just be a logical grouping or would it be something
stronger
like meaning atomic subscriptions and/or a dedicated replication slot ?

Can you subscribe to multiple publications through single SUBSCRIPTION ?

What is supposed to happen if table A is in two subscriptions S1 and S2,
and you
subscribe to both? Will you get table a only once (both initial copy and
events)?

Would a subscription of "mypub" pop up on subscriber side atomically, or
will subscribed
tables appear one-by one when they are ready (initial copy + catchup
event replay completed) ?

I recall that one of the drivers of developing pgq/skytools to replace
Slony was the
fact that Slony's "replication group" design made it very easy to
blunder subscription
changes in more complex topologies which manifested in deadlocks.

PGQ-s table-by-table subscription avoided this entirely at the cost on
non-atomic
subscribed table appearance.

Of course once subscribed, everything was transaction-consistent again.

> <programlisting>
> CREATE SUBSCRIPTION mysub WITH CONNECTION <quote>dbname=foo host=bar
> user=repuser</quote> PUBLICATION mypub;
> </programlisting>
For the pgq-like version which consider a PUBLICATION just as list of
tables to subscribe, I would add

CREATE SUBSCRIPTION mysub WITH CONNECTION 'dbname=foo host=bar
user=repuser' PUBLICATION mypub, mypub1;

ALTER SUBSCRIPTION mysub DROP PUBLICATION mypub1;

ALTER SUBSCRIPTION mysub ADD PUBLICATION mypub2;

--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ