Home Postgres pg_dump cache lookup failed for index
Reply: 3

Postgres pg_dump cache lookup failed for index

Matthew C
Matthew C Published in 2017-12-07 22:22:35Z

I am trying to create a back-up of a postgres database using the following command (omitting details):

$ pg_dump -h $host -p 5432 -U $user $db > db.sql

After some time, I get an error (formatted)

pg_dump: [archiver (db)] query failed: ERROR:  cache lookup failed for index 1184605879
pg_dump: [archiver (db)] query was: 
SELECT  t.tableoid, 
        t.relname AS indexname, 
        pg_catalog.pg_get_indexdef(i.indexrelid) AS indexdef, 
        t.relnatts AS indnkeys, 
        false AS indisreplident, 
        c.tableoid AS contableoid, 
        c.oid AS conoid, 
        pg_catalog.pg_get_constraintdef(c.oid, false) AS condef, 
        (SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS tablespace, 
        t.reloptions AS indreloptions 
FROM pg_catalog.pg_index i 
JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) 
LEFT JOIN pg_catalog.pg_constraint c ON (i.indrelid = c.conrelid AND i.indexrelid = c.conindid AND c.contype IN ('p','u','x')) 
WHERE i.indrelid = '1184605870'::pg_catalog.oid AND i.indisvalid AND i.indisready 
ORDER BY indexname

I then verified the index ID in pg_index does not exist. Is there some way to fix this, or some way to ignore this error and continue so I can continue exporting non-pg_catalog data?

Laurenz Albe
Laurenz Albe Reply to 2017-12-07 22:43:01Z

Your database is corrupted, and you should restore from a backup.

If are not afraid of messing with the catalogs (make a physical backup first!):

Set the configuration parameter allow_system_table_mods to on so you can modify system catalogs. Then

DELETE FROM pg_catalog.pg_index WHERE indexrelid = 1184605879;

Maybe that gets your database in a state where you can dump it (minus that index).

If there are more problems, you might end up extracting table data one by one with COPY and see what else you can salvage.

Michel Milezzi
Michel Milezzi Reply to 2017-12-08 00:25:52Z

It's possible that a system index get corrupted. I'd try the following:

  1. Stop database server
  2. Start database in single-user mode, ignoring system and user-defined indexes:

    postgres --single -D /path/to/data --ignore_system_indexes=on --enable_indexscan=off --enable_bitmapscan=off <database>

  3. Reindex entire database (including catalog): REINDEX DATABASE <database>;

  4. Restart database and try dumping again...

More: ignore_system_indexes, REINDEX, enable_bitmapscan/enable_indexscan, single-user mode;

Matthew C
Matthew C Reply to 2017-12-14 22:31:33Z

I wrote a quick script that dumped each table individually to a sql file and found that it was some temporary login information that was causing the cache to fail. I simply excluded that table from the export and it was fine.

I'm not the best bash programmer so it can definitely be improved, but here is the script I used to dump each table individually to find which table was causing the error.


# Get all table names that you will be dumping individually
query="SELECT tablename FROM pg_tables WHERE tableowner=$user;"
tables=( $(psql -h $host -p $port -U $user $d -c $query) )
mkdir db_dump

dump_command="pg_dump -h $host -p $port -U $user"
for table in $(tables[@]); do
    eval "$dump_command -t $table $db > db_dump/$table.sql"
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.314683 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO