Thread: pg_dump does not dump tables created in information_schema schema

Hi,

I was able to create a table in "information_schema" schema, but
pg_dump does not dumps the table that was created in
"information_schema" schema:
create table information_schema.t1(c1 int);

The similar problem exists in case of create publication, we are able
to create publications for tables present in "information_schema"
schema, but pg_dump does not dump the publication to include the
information_schema.t1 information.
create publication pub1 for table information_schema.t1;

Should tables be allowed to create in "information_schema" schema, if
yes should the tables/publications be dumped while dumping database
contents?
Thoughts?

Regards,
Vignesh

Should tables be allowed to create in "information_schema" schema, if
yes should the tables/publications be dumped while dumping database
contents?

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thursday, October 7, 2021, vignesh C <vignesh21@gmail.com> wrote:
>> Should tables be allowed to create in "information_schema" schema, if
>> yes should the tables/publications be dumped while dumping database
>> contents?

> I presume you have to be superuser to do this.  If so, this would seem to
> fit under the “we don’t stop you, but you shouldn’t” advice that we apply
> throughout the system, like in say modifying stuff in pg_catalog.
> Information_schema is an internal schema attached to an static for a given
> release.

It is (supposed to be) possible for a superuser to drop information_schema
post-initdb and then recreate it by sourcing the information_schema.sql
file.  In fact, I seem to recall that we've recommended doing so in past
minor releases to correct errors in information_schema declarations.
So it's fairly hard to see how we could enforce prohibitions against
changing information_schema objects without breaking that use-case.
On the other hand, just because you did that doesn't mean that you want
information_schema to start showing up in your dumps.  Quite the opposite
in fact, because then you'd have problems with trying to load the dump
into a newer PG version that might need different information_schema
contents.

So I agree: there's nothing to be done here, and the proposed scenario
is a case of "superusers should know better than to do that".

            regards, tom lane

On Thu, Oct 7, 2021 at 9:30 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > On Thursday, October 7, 2021, vignesh C <vignesh21@gmail.com> wrote:
> >> Should tables be allowed to create in "information_schema" schema, if
> >> yes should the tables/publications be dumped while dumping database
> >> contents?
>
> > I presume you have to be superuser to do this.  If so, this would seem to
> > fit under the “we don’t stop you, but you shouldn’t” advice that we apply
> > throughout the system, like in say modifying stuff in pg_catalog.
> > Information_schema is an internal schema attached to an static for a given
> > release.
>
> It is (supposed to be) possible for a superuser to drop information_schema
> post-initdb and then recreate it by sourcing the information_schema.sql
> file.  In fact, I seem to recall that we've recommended doing so in past
> minor releases to correct errors in information_schema declarations.
> So it's fairly hard to see how we could enforce prohibitions against
> changing information_schema objects without breaking that use-case.
> On the other hand, just because you did that doesn't mean that you want
> information_schema to start showing up in your dumps.  Quite the opposite
> in fact, because then you'd have problems with trying to load the dump
> into a newer PG version that might need different information_schema
> contents.
>
> So I agree: there's nothing to be done here, and the proposed scenario
> is a case of "superusers should know better than to do that".

Thanks for the clarification.

Regards,
Vignesh