Backup and Restore (pg_dump & pg_restore)

On Sat, Apr 20, 2019 at 06:50:47PM +0000, 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;

How is this related to performance? Please send it to pgsql-general, and
include information about how you created the dump.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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;

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

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