[GENERAL] pg_restore error -- missing pg table - Mailing list pgsql-general

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