Re: SUBSCRIPTIONS and pg_upgrade

On Tue, Apr 11, 2017 at 9:48 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Mon, Apr 10, 2017 at 1:58 PM, Peter Eisentraut
> <peter.eisentraut@2ndquadrant.com> wrote:
>> 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.
>
> +1. I don't totally love it, but I don't have a better idea.
>
>> 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.
>
> +1. I like this a lot.

Oops, forgot to copy the list.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company