Re: SUBSCRIPTIONS and pg_upgrade

On Tue, Apr 11, 2017 at 9:48 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Mon, Apr 10, 2017 at 1:58 PM, Peter Eisentraut
> <peter(dot)eisentraut(at)2ndquadrant(dot)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