Postgres Pro
Downloads
Home   >   mailing lists

Thread: pg_dump/pg_restore fail for TAR_DUMP and CUSTOM_DUMP from v94/v95/v96to v11/master.

pg_dump/pg_restore fail for TAR_DUMP and CUSTOM_DUMP from v94/v95/v96to v11/master.

From
Prabhat Sahu
Date:
Hi,

I got a failure in pg_dump/pg_restore as below:
pg_dump/pg_restore fails with 'ERROR: schema "public" already exists' for TAR_DUMP and CUSTOM_DUMP from v94/v95/v96 to v11/master.

-- Take pg_dump in v94/v95/v96:
[prabhat@localhost bin]$ ./pg_dump -f /tmp/tar_dump_PG94.tar -Ft postgres -p 9000
[prabhat@localhost bin]$ ./pg_dump -f /tmp/custom_dump_PG94.sql -Fc postgres -p 9000

-- Try to restore the above dump into v11/master:
[prabhat@localhost bin]$ ./pg_restore -F t -U prabhat -d db3 -p 9001 /tmp/tar_dump_PG94.tar
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 6; 2615 2200 SCHEMA public prabhat
pg_restore: [archiver (db)] could not execute query: ERROR:  schema "public" already exists
    Command was: CREATE SCHEMA public;



WARNING: errors ignored on restore: 1

[prabhat@localhost bin]$ ./pg_restore -F c -U prabhat -d db4 -p 9001 /tmp/custom_dump_PG94.sql
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 6; 2615 2200 SCHEMA public prabhat
pg_restore: [archiver (db)] could not execute query: ERROR:  schema "public" already exists
    Command was: CREATE SCHEMA public;



WARNING: errors ignored on restore: 1

Note: I am able to perform "Plain dump/restore" across the branches.


--


With Regards,

Prabhat Kumar Sahu
Skype ID: prabhat.sahu1984
EnterpriseDB Corporation

The Postgres Database Company

Re: pg_dump/pg_restore fail for TAR_DUMP and CUSTOM_DUMP fromv94/v95/v96 to v11/master.

From
Suraj Kharage
Date:
Hi,

The Commit 5955d934194c3888f30318209ade71b53d29777f has changed the logic to avoid dumping creation and comment commands for the public schema. 
From v11 onwards, we are using the DUMP_COMPONENT_ infrastructure in selectDumpableNamespace() to skip the public schema creation.

As reported by Prabhat, if we try to restore the custom/tar dump taken from v10 and earlier versions, we get the reported error for public schema. 
The reason for this error is, when we take custom/tar dump from v10 and earlier version, it has "CREATE SCHEMA public;" statement and v11 failed to bypass that as per the current logic.

The plain format does not produces the error in this case, because in all versions, pg_dump in plain format does not generate that "CREATE SCHEMA public". In v10 and earlier, we filter out that public schema creation in _printTocEntry() while pg_dump.

In custom/tar format, pg_dump in V10 and earlier versions generate the schema creation statement for public schema but again while pg_restore in same or back branches, it get skipped through same _printTocEntry() function.

I think we can write a logic in -
1) BulidArchiveDependencies() to avoid dumping creation and comment commands for the public schema since we do not have DUMP_COMPONENT_ infrastructure in all supported back-branches.
or
2) dumpNamespace() to not include public schema creation.

Thoughts?

Regards,
Suraj

Re: pg_dump/pg_restore fail for TAR_DUMP and CUSTOM_DUMP from v94/v95/v96 to v11/master.

From
Tom Lane
Date:
Suraj Kharage <suraj.kharage@enterprisedb.com> writes:
> The Commit 5955d934194c3888f30318209ade71b53d29777f has changed the logic
> to avoid dumping creation and comment commands for the public schema.

Yup.

> As reported by Prabhat, if we try to restore the custom/tar dump taken from
> v10 and earlier versions, we get the reported error for public schema.

Yes.  We're not intending to do anything about that.  The previous scheme
also caused pointless errors in some situations, so this isn't really a
regression.  The area is messy enough already that trying to avoid errors
even with old (wrong) archives would almost certainly cause more problems
than it solves.  In particular, it's *not* easy to fix things in a way
that works conveniently for both superuser and non-superuser restores.
See the mail thread referenced by 5955d9341.

(Note that it's only been very recently that anyone had any expectation
that pg_dump scripts could be restored with zero errors in all cases;
the usual advice was just to ignore noncritical errors.  I'm not that
excited about it if the old advice is still needed when dealing with old
archives.)

            regards, tom lane