Re: SUBSCRIPTIONS and pg_upgrade

Peter,

* Peter Eisentraut (peter(dot)eisentraut(at)2ndquadrant(dot)com) wrote:
> On 2/16/17 21:04, Stephen Frost wrote:
> > I'm not entirely sure about the reasoning behind requiring a flag to
> > include subscriptions in pg_dump output,
>
> One reason is that pg_subscription is only readable by a superuser, so
> we can't even dump them unless we're superuser.

Sure, but that's true of roles and other things.. On a system with RLS,
likely only the database superuser can actually read everything.

> Also, restoring a subscription will immediately attempt to start
> replication, which might be kind of surprising.

Isn't that what the 'disabled' option is for? Also, when it comes to
pg_upgrade, isn't that what you would probably want?

Perhaps it shouldn't start immediately, but rather create them as
disabled at first and then start them at the end of the restore.

> We had pondered this issue extensively during early review. I don't
> think we're all happy with the current behavior, but it's probably the
> safest and easiest one for now.
>
> Better ideas are welcome.

I don't think we can simply punt entirely on this, particularly for the
pg_upgrade case. At the least, I think we should be exporting the
subscriptions in a disabled fashion, so that at least the user *has*
them in the backup, even if they aren't enabled. With pg_upgrade, I
would think we'd want to have some option to tell pg_upgrade to include
subscriptions, or to enable them afterwards if they're included but off
by default.

I've added it to the open items for PG10. Perhaps we can get some other
input/suggestions on it.

Thanks!

Stephen