Postgres pg_dump cache lookup failed for index

问题:

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.oid, 
        t.relname AS indexname, 
        pg_catalog.pg_get_indexdef(i.indexrelid) AS indexdef, 
        t.relnatts AS indnkeys, 
        i.indkey, 
        i.indisclustered, 
        false AS indisreplident, 
        t.relpages, 
        c.contype, 
        c.conname, 
        c.condeferrable, 
        c.condeferred, 
        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?

回答1:

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.

#!/bin/bash
host=$1
port=$2
user=$3
db=$4

# 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"
done

回答2:

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.

回答3:

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;