Re: [Bug] pg_upgrade could fail for non-superuser subscriptions using foreign servers

On Thu, 23 Jul 2026 at 08:30, Hayato Kuroda (Fujitsu)
<kuroda(dot)hayato(at)fujitsu(dot)com> wrote:
>
> Hi hackers,
>
> > My primitive idea for fix is to introduce a new TOC entry to record the OWNER
> > command, in the pg_dump. Unlike the normal CREATE SUBSCRIPTION, this entry
> > can be handled as RESTORE_PASS_POST_ACL.
> > I'm locally working on the idea and will post tomorrow.
>
> So here is a patch. While coding, I found another issue that TOC entry for
> "SUBSCRIPTION TABLE" may not be skipped in case of --no-subscriptions.
> This can be an issue when:
>
> 1) there are tuples in pg_subscription_rel,
> 2) pg_dump is done with --binary-upgrade then
> 3) pg_restore is done with --no-subscription.
>
> I think it seldom happens, but I could not find reasons to retain.
>
> How do you feel?
>
Hi Kuroda-san,

I tried to reproduce the issue. To reproduce this issue, pg_restore
must be run against a server started in binary upgrade mode.

Also I noticed when we try to use pg_restore with --no-subscription,
in this case we get an error:
pg_restore: error: could not execute query: ERROR: subscription
"sub1" does not exist
Command was:
-- For binary upgrade, must preserve the subscriber table.
SELECT pg_catalog.binary_upgrade_add_sub_rel_state('sub1', 16384, 'r',
'0/01750B90');

I have verified that the fix in 0001 addresses this issue.
Although this combination of options is uncommon, it seems more
consistent for --no-subscriptions to skip SUBSCRIPTION TABLE entries
as well to avoid the above error. I think we should keep this fix.

I am also able to reproduce the issue in [1] with the steps provided
and I am still reviewing the fix in 0002 patch.
[1]: https://www.postgresql.org/message-id/OS9PR01MB12149C3ED34272966B25DB173F5C12@OS9PR01MB12149.jpnprd01.prod.outlook.com

Thanks,
Shlok Kyal