Thread: Backup and Restore (pg_dump & pg_restore)

On 4/21/19 9:35 AM, Daulat Ram wrote:
> Hello Team,
> 
> We are getting below error while migrating pg_dump from Postgresql 9.6 
> to Postgresql 11.2 via pg_restore in docker environment.
> 
> 90d4c9f363c8:~$ pg_restore -d kbcn "/var/lib/kbcn_backup19"
> 
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> 
> pg_restore: [archiver (db)] Error from TOC entry 3; 2615 2200 SCHEMA 
> public postgres
> 
> pg_restore: [archiver (db)] could not execute query: ERROR: schema 
> "public" already exists
> 
> Command was: CREATE SCHEMA public;

Expected as the public schema is there by default. It is an 
informational error, you can ignore it.

If you want to not see it and want a clean install on the 11.2 side use:

-c
--clean

     Output commands to clean (drop) database objects prior to 
outputting the commands for creating them. (Unless --if-exists is also 
specified, restore might generate some harmless error messages, if any 
objects were not present in the destination database.)

     This option is only meaningful for the plain-text format. For the 
archive formats, you can specify the option when you call pg_restore.

on pg_restore side(along with --if-exists to remove other harmless error 
messages).

FYI the -W on the pg_dump is redundant as the password will be prompted 
for without it:

-W
--password

     Force pg_dump to prompt for a password before connecting to a database.

     This option is never essential, since pg_dump will automatically 
prompt for a password if the server demands password authentication. 
However, pg_dump will waste a connection attempt finding out that the 
server wants a password. In some cases it is worth typing -W to avoid 
the extra connection attempt.


> 
> Script used for pg_dump:
> 
> -------------------------------------
> 
> pg_dump -h 10.26.33.3 -p 5432 -U postgres -W -F c -v -f 
> tmp/postgres/backup/backup10/ kbcn_backup19  kbcn >& 
> tmp/postgres/backup/backup10/ kbcn_backup19.log; echo $? > 
> tmp/postgres/backup/backup10/_'date+%Y-%m-%d.%H:%M:%S'
> 
> Please advise.
> 
> Regards,
> 
> Daulat
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 4/21/19 9:35 AM, Daulat Ram wrote:
>> pg_restore: [archiver (db)] could not execute query: ERROR: schema 
>> "public" already exists
>> Command was: CREATE SCHEMA public;

> Expected as the public schema is there by default. It is an 
> informational error, you can ignore it.

It's expected only if you made a dump file with 9.6's pg_dump and
restored it with a later pg_restore; there were some changes in
how the public schema got handled between the two versions.

The usual recommendation when you are doing a version migration
is to use the newer release's pg_dump to suck the data out of
the older server.  If you can't do that, it'll (probably)
still work, but you may have cosmetic issues like this one.

            regards, tom lane

On 4/21/19 1:46 PM, Adrian Klaver wrote:
> On 4/21/19 9:35 AM, Daulat Ram wrote:
>> Hello Team,
>>
>> We are getting below error while migrating pg_dump from Postgresql 9.6 to 
>> Postgresql 11.2 via pg_restore in docker environment.
>>
>> 90d4c9f363c8:~$ pg_restore -d kbcn "/var/lib/kbcn_backup19"
>>
>> pg_restore: [archiver (db)] Error while PROCESSING TOC:
>>
>> pg_restore: [archiver (db)] Error from TOC entry 3; 2615 2200 SCHEMA 
>> public postgres
>>
>> pg_restore: [archiver (db)] could not execute query: ERROR: schema 
>> "public" already exists
>>
>> Command was: CREATE SCHEMA public;
>
> Expected as the public schema is there by default. It is an informational 
> error, you can ignore it.

"Informational error" is a contradiction in terms.

-- 
Angular momentum makes the world go 'round.

On 4/21/19 1:42 PM, Ron wrote:
> On 4/21/19 1:46 PM, Adrian Klaver wrote:
>> On 4/21/19 9:35 AM, Daulat Ram wrote:
>>> Hello Team,
>>>
>>> We are getting below error while migrating pg_dump from Postgresql 
>>> 9.6 to Postgresql 11.2 via pg_restore in docker environment.
>>>
>>> 90d4c9f363c8:~$ pg_restore -d kbcn "/var/lib/kbcn_backup19"
>>>
>>> pg_restore: [archiver (db)] Error while PROCESSING TOC:
>>>
>>> pg_restore: [archiver (db)] Error from TOC entry 3; 2615 2200 SCHEMA 
>>> public postgres
>>>
>>> pg_restore: [archiver (db)] could not execute query: ERROR: schema 
>>> "public" already exists
>>>
>>> Command was: CREATE SCHEMA public;
>>
>> Expected as the public schema is there by default. It is an 
>> informational error, you can ignore it.
> 
> "Informational error" is a contradiction in terms.
> 


1) Well the public schema was in the dump, so the OP wanted it.
2) It also existed in the target database.
3) The error let you know 1) & 2)
4) To my way of thinking it was a 'no harm, no foul' situation where the 
error just informed you that the target database took a side track to 
get where you wanted to be anyway.

I see this sort of thing in monitoring systems e.g. environmental 
controls all the time. Things get flagged because they wander over set 
points intermittently. It is technically an error but unless they stay 
over the line it is just another data point.

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 4/21/19 3:58 PM, Adrian Klaver wrote:
> On 4/21/19 1:42 PM, Ron wrote:
>> On 4/21/19 1:46 PM, Adrian Klaver wrote:
>>> On 4/21/19 9:35 AM, Daulat Ram wrote:
>>>> Hello Team,
>>>>
>>>> We are getting below error while migrating pg_dump from Postgresql 9.6 
>>>> to Postgresql 11.2 via pg_restore in docker environment.
>>>>
>>>> 90d4c9f363c8:~$ pg_restore -d kbcn "/var/lib/kbcn_backup19"
>>>>
>>>> pg_restore: [archiver (db)] Error while PROCESSING TOC:
>>>>
>>>> pg_restore: [archiver (db)] Error from TOC entry 3; 2615 2200 SCHEMA 
>>>> public postgres
>>>>
>>>> pg_restore: [archiver (db)] could not execute query: ERROR: schema 
>>>> "public" already exists
>>>>
>>>> Command was: CREATE SCHEMA public;
>>>
>>> Expected as the public schema is there by default. It is an 
>>> informational error, you can ignore it.
>>
>> "Informational error" is a contradiction in terms.
>>
>
>
> 1) Well the public schema was in the dump, so the OP wanted it.
> 2) It also existed in the target database.
> 3) The error let you know 1) & 2)
> 4) To my way of thinking it was a 'no harm, no foul' situation where the 
> error just informed you that the target database took a side track to get 
> where you wanted to be anyway.
>
> I see this sort of thing in monitoring systems e.g. environmental controls 
> all the time. Things get flagged because they wander over set points 
> intermittently. It is technically an error but unless they stay over the 
> line it is just another data point.

Errors need to be fixed.  If the restore can proceed without harm, then it's 
an Informational message.

-- 
Angular momentum makes the world go 'round.

On 4/21/19 2:20 PM, Ron wrote:

>> I see this sort of thing in monitoring systems e.g. environmental 
>> controls all the time. Things get flagged because they wander over set 
>> points intermittently. It is technically an error but unless they stay 
>> over the line it is just another data point.
> 
> Errors need to be fixed.  If the restore can proceed without harm, then 
> it's an Informational message.

That is a choice thing:

https://www.postgresql.org/docs/11/app-pgrestore.html

"
-e
--exit-on-error

     Exit if an error is encountered while sending SQL commands to the 
database. The default is to continue and to display a count of errors at 
the end of the restoration.
"



It is also one of those eye of the beholder things as evidenced by:

https://www.postgresql.org/docs/11/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT

Severity     Usage             syslog         eventlog
...
ERROR         Reports an error ...     WARNING     ERROR
...

Edited to keep on one line.


-- 
Adrian Klaver
adrian.klaver@aklaver.com