Обсуждение: autovacuum template0
I've got a pg 8.1.1 server running autovacuum, and it started attempting to vacuum template0. 2006-06-22 15:00:50 EDT LOG: autovacuum: processing database "template0" 2006-06-22 15:00:50 EDT ERROR: could not access status of transaction 3242180415 2006-06-22 15:00:50 EDT DETAIL: could not open file "pg_clog/0C13": No such file or directory I found a thread about the same problem at http://archives.postgresql.org/pgsql-hackers/2006-03/msg01294.php, but it doesn't say if the resolution works. Does anyone have experience with this? I'm at a loss as to what I should do. Thanks, -- Jacob Coby
Jacob Coby <jcoby@listingbook.com> writes:
> I've got a pg 8.1.1 server running autovacuum, and it started attempting
> to vacuum template0.
> 2006-06-22 15:00:50 EDT LOG: autovacuum: processing database "template0"
> 2006-06-22 15:00:50 EDT ERROR: could not access status of transaction
> 3242180415
template0 should surely not contain any such transaction number. Either
there's something you're not telling us about the history of this
installation, or template0 contains corrupt data, or ???.
What does the pg_database row for template0 contain? If you clone
template0 (CREATE DATABASE foo WITH TEMPLATE template0), does a vacuum
in the new database succeed?
regards, tom lane
Tom Lane wrote: > Jacob Coby <jcoby@listingbook.com> writes: >> I've got a pg 8.1.1 server running autovacuum, and it started attempting >> to vacuum template0. > >> 2006-06-22 15:00:50 EDT LOG: autovacuum: processing database "template0" >> 2006-06-22 15:00:50 EDT ERROR: could not access status of transaction >> 3242180415 > > template0 should surely not contain any such transaction number. Either > there's something you're not telling us about the history of this > installation, or template0 contains corrupt data, or ???. database has been running for ~6 months now. no history of crashing, no history of issues. this problem started on the 10th of this month. > > What does the pg_database row for template0 contain? If you clone > template0 (CREATE DATABASE foo WITH TEMPLATE template0), does a vacuum > in the new database succeed? create database works. vacuum verbose does have 1 error, which is probably very helpful: INFO: vacuuming "pg_catalog.pg_statistic" ERROR: could not access status of transaction 3242180415 DETAIL: could not open file "pg_clog/0C13": No such file or directory I then tried: foo=# select * from pg_catalog.pg_statistic ; ERROR: could not access status of transaction 3242180415 DETAIL: could not open file "pg_clog/0C13": No such file or directory so it seems that pg_catalog.pg_statistic has somehow become corrupt on template0? -- Jacob Coby
Jacob Coby <jcoby@listingbook.com> writes: > I then tried: > foo=# select * from pg_catalog.pg_statistic ; > ERROR: could not access status of transaction 3242180415 > DETAIL: could not open file "pg_clog/0C13": No such file or directory > so it seems that pg_catalog.pg_statistic has somehow become corrupt on > template0? Yeah, that's what it looks like. If you're interested in digging into why, it'd be productive to run "pg_filedump -i -f" on the table (see http://sources.redhat.com/rhdb/ for that utility). If you just want to get out of the problem, it's fortunately not hard because pg_statistic is all derived data. Go into template0, TRUNCATE pg_statistic, and then VACUUM ANALYZE to regenerate it and VACUUM FREEZE to re-freeze template0. (You'll need to fool with pg_database.datallowconn to let yourself into template0. Read the manual's bit about template databases if you aren't sure what you're doing here.) regards, tom lane
Tom Lane wrote: > Jacob Coby <jcoby@listingbook.com> writes: >> I then tried: >> foo=# select * from pg_catalog.pg_statistic ; >> ERROR: could not access status of transaction 3242180415 >> DETAIL: could not open file "pg_clog/0C13": No such file or directory > >> so it seems that pg_catalog.pg_statistic has somehow become corrupt on >> template0? > > Yeah, that's what it looks like. If you're interested in digging into > why, it'd be productive to run "pg_filedump -i -f" on the table > (see http://sources.redhat.com/rhdb/ for that utility). I wasn't able to build that utility due to dependencies that I didn't have the patience to chase down. > If you just want to get out of the problem, it's fortunately not hard > because pg_statistic is all derived data. Go into template0, TRUNCATE > pg_statistic, and then VACUUM ANALYZE to regenerate it and VACUUM FREEZE > to re-freeze template0. (You'll need to fool with > pg_database.datallowconn to let yourself into template0. Read the > manual's bit about template databases if you aren't sure what you're > doing here.) That worked perfectly. Thanks.