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.klaver@aklaver.com