From: | Willy-Bas Loos <willybas(at)gmail(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | cache lookup failed for index |
Date: | 2016-06-28 17:14:31 |
Message-ID: | CAHnozTjkOP8o0MqNZtuc5HgPD1tLRmTQvZAKY+RNNvOkmMbK0A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
I have a londiste consumer database that has some additional user data in
it.
The user data is in schema's with the prefix oz_
Every night we dump those schema's with pg_dump.
About 2-3 times per week cron emails me that something went wrong.
That means that 4-5 day per week, everything works fine. The data is there
too, i haven't yet been able to look if anything's missing when it goes
wrong.
This is the error:
---------------------
pg_dump: [archiver (db)] query failed: ERROR: cache lookup failed for
index 231808363
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 =
'231800968'::pg_catalog.oid AND i.indisvalid AND i.indisready ORDER BY
indexname
---------------------
The index number is a different one every time.
The log says the same thing.
interesting detail:
On the days that i have successful dumps from, the last dump is finished
within 1:15 hours.
But the errors occur at very different times, sometimes 14 hours after the
job started (or maybe it started on a different day).
But munin doesn't show clear peak loads on the server.
And this is the script:
---------------------
#!/bin/bash
HOST=localhost
PGPORT=5432
BACKUPDIR=/data/dump/afolder
DATABASE=adatabase
SCHEMAS=`psql -p $PGPORT $DATABASE -t --command "select schema_name from
information_schema.schemata where schema_name LIKE 'oz_%'"`
for SCHEMA in $SCHEMAS
do
# mv $BACKUPDIR/$SCHEMA.backup.1 $BACKUPDIR/$SCHEMA.backup.2
mv $BACKUPDIR/$SCHEMA.backup $BACKUPDIR/$SCHEMA.backup.1
pg_dump -Fc -Z3 -p $PGPORT -n $SCHEMA $DATABASE >
$BACKUPDIR/$SCHEMA.backup
done
---------------------
It seems like an index gets deleted while pg_dump has it in some task list,
and by the time pg_dump wants to dump it, it's gone.
But that should not be possible, because of transactions.
Does anyone know what's up?
--
Willy-Bas Loos
From | Date | Subject | |
---|---|---|---|
Next Message | Arthur Silva | 2016-06-28 17:24:38 | Re: jsonb search |
Previous Message | Riccardo Vianello | 2016-06-28 15:25:27 | Re: questions about how to implement a gist index |