Re: SUBSCRIPTIONS and pg_upgrade

Peter,

* Peter Eisentraut (peter(dot)eisentraut(at)2ndquadrant(dot)com) wrote:
> Problem 1: pg_subscription.subconninfo can only be read by superuser.
> So non-superusers cannot dump subscriptions.

I'm not particularly happy with this.

> Precedent is pg_user_mapping. In that case, we just omit the
> user-mapping options if we're not a superuser. Pretty dubious, but in
> any case that won't work here, because you cannot create a subscription
> without a CONNECTION clause.

Given that you can create a disabled subscription, why is a connection
clause required..? I agree that simply excluding pg_user_mapping isn't
great but I don't really think we want to use something which we agree
isn't ideal as the best approach for this.

> Proposal: Dump subscriptions if running as superuser. If not, check if
> there are subscriptions and warn about that. Remove current pg_dump
> --include-subscriptions option.

That option was added, iirc, in part because pg_dump was including
subscriptions in things like explicit single-table dumps. I certainly
don't think we should start including all subscriptions in all dumps.

The question becomes if it's useful to include subscriptions when only
dumping a subset of objects or if they should *only* be included when
doing a full dump. My gut feeling is that if we care enough about blobs
to have an explicit option to include them, even when we aren't dumping
out everything, then having something similar for subscriptions makes
sense.

> Problem 2: Restoring a subscription immediately starts replication.
> Maybe you want that or maybe you don't. We could dump all subscriptions
> in DISABLED state. But then after restoring you have to go and manually
> enable all subscriptions. We don't have a command to turn all
> subscriptions back on at once. Maybe that is not a terrible issue,
> since one wouldn't normally have many subscriptions.

Having a way to turn them all on would be nice.

> Proposal: Dump all subscriptions in DISABLED state. Remove current
> pg_dump --no-subscription-connect option.

I like this idea in general, I'm just not sure if it's the right answer
when we're talking about pg_upgrade. At a minimum, if we go with this
approach, we should produce a warning when subscriptions exist during
the pg_upgrade that the user will need to go re-enable them.

Thanks!

Stephen