Re: Logical Replication WIP

On 07/09/16 02:56, Peter Eisentraut wrote:
> Review of 0002-Add-SUBSCRIPTION-catalog-and-DDL.patch:
>
> Similar concerns as before about ALTER syntax, e.g., does ALTER
> SUBSCRIPTION ... PUBLICATION add to or replace the publication set?
>

It sets.

> For that matter, why is there no way to add?
>

Didn't seem all that useful, the expectation here is that most
subscriptions will use one couple of publications.

>
> I think we should allow creating subscriptions initally without
> publications. This could be useful for example to test connections,
> or create slots before later on adding publications. Seeing that
> there is support for changing the publications later, this shouldn't
> be a problem.
>

Sure, but they need to be created disabled then.

>
> Larger conceptual issues:
>
> I haven't read the rest of the code yet to understand why
> pg_subscription needs to be a shared catalog, but be that as it may, I
> would still make it so that subscription names appear local to the
> database. We already have the database OID in the pg_subscription
> catalog, so I would make the key (subname, subdatid). DDL commands
> would only operate on subscriptions in the current database (or you
> could use "datname"."subname" syntax), and \drs would only show
> subscriptions in the current database. That way the shared catalog is
> an implementation detail that can be changed in the future. I think
> it would be very helpful for users if publications and subscriptions
> appear to work in a parallel way. If I have two databases that I want
> to replicate between two servers, I might want to have a publication
> "mypub" in each database and a subscription "mysub" in each database.
> If I learn that the subscriptions can't be named that way, then I have
> to go back to rename to the publications, and it'll all be a bit
> frustrating.

Okay that makes sense. The pg_subscription is shared catalog so that we
can have one launcher per cluster instead one per database. Otherwise
there is no reason why it could not behave like local catalog.

>
> Some thoughts on pg_dump and such:
>
> Even an INITIALLY DISABLED subscription needs network access to create
> the replication slot. So restoring a dump when the master is not
> available will have some difficulties. And restoring master and slave
> at the same time (say disaster recovery) will not necessarily work
> well either. Also, the general idea of doing network access during a
> backup restore without obvious prior warning sounds a bit unsafe.
>
> I imagine maybe having three states for subscriptions: DISABLED,
> PREPARED, ENABLED (to use existing key words). DISABLED just exists
> in the catalog, PREPARED has the slots set up, ENABLED starts
> replicating. So you can restore a dump with all slots disabled. And
> then it might be good to have a command to "prepare" or "enable" all
> subscriptions at once.

Well the DISABLED keyword is also used in alter to stop the subscription
but not remove it, that would not longer map well if we used the
behavior you described. That being said I agree with the idea of having
subscription that exists just locally in catalog, we just need to figure
out better naming.

>
> As I had mentioned privately before, I would perhaps have CREATE
> SUBSCRIPTION use the foreign server infrastructure for storing
> connection information.
>

Hmm, yeah it's an idea. My worry there is that it will make it bit more
complex to setup as user will have to first create server and user
mapping before creating subscription.

> We'll have to keep thinking about ways to handle abandonded
> replication slots. I imagine that people will want to create
> subscriber instances in fully automated ways. If that fails every so
> often and requires manual cleanup of replication slots on the master
> some of the time, that will get messy. I don't have well-formed ideas
> about this, though.
>

Yes it's potential issue, don't have good solution for it either. It's
loosely coupled system so we can't have 100% control over everything.

--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services