Re: Added schema level support for publication.

On Tue, 17 Dec 2024 at 10:43, vignesh C <vignesh21@gmail.com> wrote:
> > If I understand your suggestion correctly I think this will break the
> > "--exclude-schema" option of pg_dump. That change will dump all
> > mappings between publications and schemas for publications which are
> > dumped.
> >
> > That solves the issue with special schemas, but restore will fail if
> > some schemas were explicitly excluded. pg_dump will include in the
> > dump ALTER PUBLICATION <pub> ADD TABLES IN SCHEMA <schema> even for
> > those schemas which are not created during restore.
>
> This is already the case in the existing implementation, so users
> should not be surprised by the proposed change.

Currently the behavior isn't the same as the proposed change.

Sorry, I might have been not clear when I described what might be
wrong with this. Here is the example with the proposed patch [1].

Create necessary objects to test:

    create schema nsp;
    create publication pub for tables in schema nsp;

If you run pg_dump excluding the schema "nsp":

    pg_dump -d postgres -U postgres -f backup --exclude-schema=nsp

In the resulting file "backup" you will have:

    ...
    ALTER PUBLICATION pub ADD TABLES IN SCHEMA nsp;
    ...

which you won't have on the current master. And I think this is not
what users might expect and it can break some of the scenarios because
during restore they will have an error:

    ERROR:  schema "nsp" does not exist


1. https://www.postgresql.org/message-id/CALDaNm1TQqBC5ZP5BsNf2LKVu1kEJNJn2spFwbAtyLn1FoAFGQ%40mail.gmail.com

-- 
Kind regards,
Artur