Re: SUBSCRIPTIONS and pg_upgrade

OK, we need to come to a conclusion here. To summarize:

Problem 1: pg_subscription.subconninfo can only be read by superuser.
So non-superusers cannot dump subscriptions.

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.

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.

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.

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

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services