From: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> |
---|---|
To: | tpham <tpham(at)quantcast(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: [postgresql 9.3.5] autovacuums stuck on non-existent pg_toast tables |
Date: | 2015-02-25 19:42:13 |
Message-ID: | 20150225194213.GT5169@alvh.no-ip.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jeremy Harris | 2015-02-25 19:51:06 | Re: Some indexing advice for a Postgres newbie, please? |
Previous Message | David Steele | 2015-02-25 19:38:36 | Re: Locking during UPDATE query with SUBSELECT |