Re: [GENERAL] PG_DUMP/RESTORE Would like an explanation of these (non-critical) errors

[ANdreas Wenk]

James B. Byrne wrote:

Hi,

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 4; 2615 2200 SCHEMA
public postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  cannot
drop
schema public because other objects depend on it
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
Command was: DROP SCHEMA public;
pg_restore: [archiver (db)] could not execute query: ERROR:  schema
"public" already exists
Command was: CREATE SCHEMA public;
WARNING: errors ignored on restore: 2


The pg_dump command is:

pg_dump --create --format=c --user=postgres --verbose hll_redmine |
gzip > /var/data/pas-redmine/db/dump/hll_redmine.pgsql.gz && rsync
-avz --bwlimit=35 --delete-after --exclude="database.yml"
--exclude="*.log" --exclude="*cache" --exclude="*ruby_sess*"
/var/data/pas-redmine inet03.mississauga.harte-lyne.ca:/var/data 1>
/dev/null


--create is not working here because you select a custom format for your 
dump. --create is only working with plain SQL dumps.



The pg_restore command, which generates the error, is:

gunzip < /var/data/pas-redmine/db/dump/hll_redmine.pgsql.gz |
pg_restore --clean --user=postgres --dbname=hll_redmine ; vacuumdb
--user=postgres --full --analyze hll_redmine 1> /dev/null


with the --clean parameter you delete existing objects in hll_redmine 
but there are dependant objects. A common way to avoid this is to drop 
the whole database first, create a new one and then restore the dump 
into it. Means use --create instead of --clean.


Dropping the database can cause problems because you have to cut all 
client connections before being able to cut it. So maybe this approach 
is not working for you.


Another idea is not to use any of these parameters and dump only the data.

pg_restore -a

Cheers Andy



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] PG_DUMP/RESTORE Would like an explanation of these (non-critical) errors

[James B. Byrne]

I move a compressed pg_dump archives across the wire to a remote
host on a regular schedule.  The process completes and the archives
are restored on the remote site and the resulting database performs
as expected.


However, I get this returned to me at the end of each
dump/transfer/restore

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 4; 2615 2200 SCHEMA
public postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  cannot
drop
schema public because other objects depend on it
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
Command was: DROP SCHEMA public;
pg_restore: [archiver (db)] could not execute query: ERROR:  schema
"public" already exists
Command was: CREATE SCHEMA public;
WARNING: errors ignored on restore: 2


The pg_dump command is:

pg_dump --create --format=c --user=postgres --verbose hll_redmine |
gzip > /var/data/pas-redmine/db/dump/hll_redmine.pgsql.gz && rsync
-avz --bwlimit=35 --delete-after --exclude="database.yml"
--exclude="*.log" --exclude="*cache" --exclude="*ruby_sess*"
/var/data/pas-redmine inet03.mississauga.harte-lyne.ca:/var/data 1>
/dev/null

The pg_restore command, which generates the error, is:

gunzip < /var/data/pas-redmine/db/dump/hll_redmine.pgsql.gz |
pg_restore --clean --user=postgres --dbname=hll_redmine ; vacuumdb
--user=postgres --full --analyze hll_redmine 1> /dev/null


I speculate that I have set some options on the restore that
conflict with those set on the dump, perhaps --create.  Regrettably,
I lack the expertise to determine if this is the cause or not; and,
as this is a production environment, I lack the ability to play with
them to determine what is incorrect.

If anything is obviously wrong, or even mildly suspicious, I would
appreciate a nudge in the right direction.  This issue at least has
no overlong dates.


-- 
***  E-Mail is NOT a SECURE channel  ***
James B. Byrnemailto:byrn...@harte-lyne.ca
Harte & Lyne Limited  http://www.harte-lyne.ca
9 Brockley Drive  vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada  L8E 3C3


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general