Re: 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(dot)klaver(at)aklaver(dot)com