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

Tong Pham wrote:

> 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.)

Yes, SIGKILL is a bad idea, but it shouldn't lead to lost pg_clog files
nonetheless. Is there a network filesystem involved perhaps?

> I did look inside pg_class, but there is absolutely no mention of this
> pg_toast table.

Well, so you can DELETE FROM pg_class the broken entry and you will get
rid of the autovacuum problem. (Removing from the filesystem would be
good as well if the file is large, to save disk space.)

> 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?

No idea there. Depending on how the files disappeared, maybe the
replica has the same problem.

> A pg_dumpall is possible but might take too much time.

There's a nice saying, "I can make this arbitrarily fast if it doesn't
have to give the right answer".

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