We've been doing backups and restores with many tests and uses and never had a problem until now. Sadly, I'm not sure
whereto even begin looking to figure out what to do, so any pointers would be very much appreciated!
The vital stats:
Pg: 9.5.1 (this will get upgraded to 10.0 when it's available)
OS: Centos 5.9 (the upgrade to 6.x is scheduled for real soon now)
For a little background. Our DB is broken into 3 schemas: public, logging, common. We do that because logging data can
getquite large and is not the data that is needed if something goes wrong and we need to restore -- that data sits in
public.The common schema holds the few functions that both need so a drop of public or logging doesn't hurt the other.
Ilike to think this is all pretty straight forward with no surprises.
We do backups like this (simplified):
cd $EXP
rm -f $EXP/*
$PGPATH/pg_dump --clean --create --format=d --jobs=2 --file=$EXP --dbname=nms public
/bin/tar -czf $TARNAME .
We do the restore like this (simplified):
cd $EXP
rm -f $EXP/*
tar -xzf $TARNAME
# stop services & users
# rename schema in case we need to restore
# create an empty schema to restore into
$PGPATH/pg_restore $VERBOSE --jobs=2 --dbname=nms public .
# check for errors and restore saved schema if required
The error we're getting, with supporting SQL, looks like:
DROP SCHEMA IF EXISTS savepublic CASCADE;
ALTER SCHEMA public RENAME TO savepublic;
CREATE SCHEMA public AUTHORIZATION nmsroot;
/opt/pgsql-9.5/bin/pg_restore -v --clean --create --dbname=nms --schema=public .
...
pg_restore: processing item 446 VIEW pg_all_foreign_keys
pg_restore: creating VIEW "public.pg_all_foreign_keys"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 446; 1259 136598 VIEW pg_all_foreign_keys nmsroot
pg_restore: [archiver (db)] could not execute query: ERROR: function _pg_sv_column_array(oid, smallint[]) does not
exist
LINE 6: _pg_sv_column_array(k1.conrelid, k1.conkey) AS fk_column... ^
HINT: No function matches the given name and argument types. You might need to add explicit type casts. Command was:
CREATEVIEW pg_all_foreign_keys ASSELECT n1.nspname AS fk_schema_name, c1.relname AS fk_table_name, k1.conname AS
...
pg_restore: [archiver (db)] could not execute query: ERROR: relation "pg_all_foreign_keys" does not exist Command
was:ALTER TABLE pg_all_foreign_keys OWNER TO nmsroot;
pg_restore: processing item 6841 ACL pg_all_foreign_keys
...
pg_restore: setting owner and privileges for ACL "public.pg_all_foreign_keys"
pg_restore: [archiver (db)] Error from TOC entry 6841; 0 0 ACL pg_all_foreign_keys nmsroot
pg_restore: [archiver (db)] could not execute query: ERROR: relation "pg_all_foreign_keys" does not exist Command
was:REVOKE ALL ON TABLE pg_all_foreign_keys FROM PUBLIC;
REVOKE ALL ON TABLE pg_all_foreign_keys FROM nmsroot;
GRANT ALL ON TABL...
...
WARNING: errors ignored on restore: 3
DROP SCHEMA IF EXISTS public CASCADE;
ALTER SCHEMA savepublic RENAME TO public;
Error: Problem with pg_restore, reverted to saved database copy.
------------------------------------------------------------------------
I don't understand why pg_all_foreign_keys is having issues here, nor even what to start investigating. To the best of
myknowledge, the server never ran out of disk space so it should be a complete backup. None of the files from the
backupare corrupt that I can tell. The server and database both seem fine (other than the missing data that was
accidentallydropped and is forcing this restore).
What am I missing and what do I need to investigate? Has anyone else ever seen this before and if so what did you do to
fixit?
Thanks!
Kevin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general