pg_restore error -- missing pg table

From: "" <kbrannen(at)pwhome(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: pg_restore error -- missing pg table
Date: 2017-09-11 16:08:51
Message-ID: 20170911090851.33A43B22@m0117458.ppops.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message techmail+pgsql 2017-09-11 17:15:29 Re: pg_ident mapping Kerberos Usernames
Previous Message Amee Sankhesara - Quipment India 2017-09-11 14:17:55 Major Version Upgradation from 9.4 to 9.6