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

Hi Alvaro,

Thank you for your quick response!

We do have fsync turned on, and there was no disk failure. The database
had to be shut down forcefully because it was becoming nonresponsive
(probably due to inadequate earlier vacuuming) and we could not get the
remaining queries to terminate with normal cancel/terminate operations (so
a SIGKILL was used ‹ which was bad.)

I did look inside pg_class, but there is absolutely no mention of this
pg_toast table. This database has a streaming replica that has been
healthy through the previous crash. If we decide to switch over to this
replica, do you think these kinds of inconsistencies would be on the
replica as well? A pg_dumpall is possible but might take too much time.

Thanks!

Regards,
Tong

On 2/25/15, 11:42 AM, "Alvaro Herrera" <alvherre(at)2ndquadrant(dot)com> wrote:

>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