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@xxxxxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
