We've been doing backups and restores with many tests and uses and never had a problem until now. Sadly, I'm not sure where to 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 get quite 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. I like 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: CREATE VIEW pg_all_foreign_keys AS
SELECT 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 my knowledge, the server never ran out of disk space so it should be a complete backup. None of the files from the backup are corrupt that I can tell. The server and database both seem fine (other than the missing data that was accidentally dropped 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 fix it?
Thanks!
Kevin
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
