Re: [postgresql 9.3.5] autovacuums stuck on non-existent pg_toast tables

tpham wrote:
> Hi everyone,
>
> Two weeks ago, one of our Postgres databases crashed violently and had to be
> brought up again. This certainly resulted in some lost pg_clog files, and we
> had to zero-fill them in one by one to get autovacuum up and running again.

You should never lose pg_clog files to any type of crash. This kind of
problem shows up when you have broken config somehow, perhaps running
with fsync=off or your disks have write cache enabled and no
battery-backup for it. This seems like a serious issue that should be
investigated more closely.

Your filesystem might have put the lost files in lost+found.

> Now, we have two autovacuuming processes constantly stuck at two pg_toast
> tables that are nonexistent:
>
> autovacuum: VACUUM pg_toast.pg_toast_455742374 (runs up to twelve hours with
> no progress)
>
> # select 455742374::regclass;
> regclass
> -----------
> 455742374
> (1 row)

So, you could look at the toast table directly in pg_class, and perhaps
delete the pg_class entry for the stale pg_toast table and the file if
there's any. For instance, try

select relfilenode from pg_class where relname = 'pg_toast_455742374'

which would give you the file name of the offending toast table so that
you can remove it in the filesystem. You can then run a DELETE against
pg_class.

Another (probably better) way to look for the table would be something
like

select *
from pg_class
where reltoastrelid = (select oid
from pg_class
where relname = 'pg_toast_455742374');
or similar.

I think you should pg_dump this database and restore it in a newly
initdb'd directory. Otherwise, who knows what other inconsistency you
might have in your data.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services