Thread: vacuum error
I am seeing the following error in pgsql 8.1.2:
2007-03-05 10:00:51.106 PST [9834] DEBUG: vacuuming "pg_toast.pg_toast_1260"
2007-03-05 10:00:51.106 PST [9834] DEBUG: index "pg_toast_1260_index" now contains 0 row versions in 1 pages
2007-03-05 10:00:51.106 PST [9834] DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
2007-03-05 10:00:51.106 PST [9834] DEBUG: "pg_toast_1260": found 0 removable, 0 nonremovable row versions in 0
pages
2007-03-05 10:00:51.106 PST [9834] DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
2007-03-05 10:00:51.106 PST [9834] DEBUG: vacuuming "pg_catalog.pg_statistic"
2007-03-05 10:00:51.107 PST [9834] ERROR: could not access status of transaction 3229475082
2007-03-05 10:00:51.107 PST [9834] DETAIL: could not open file "pg_clog/0C07": No such file or directory
What does it mean, and what should I do about it?
TIA.
Ed
Ed L. wrote: > I am seeing the following error in pgsql 8.1.2: > ERROR: could not access status of transaction 3229475082 > DETAIL: could not open file "pg_clog/0C07": No such file or directory > > What does it mean, and what should I do about it? 1. Read this thread: http://archives.postgresql.org/pgsql-general/2007-02/msg00820.php 2. Upgrade to the latest 8.1.* release. 3. If that doesn't help, check your system for faulty hardware, in particular for bad RAM. -- Peter Eisentraut http://developer.postgresql.org/~petere/
On Tuesday March 6 2007 12:20 pm, Peter Eisentraut wrote: > Ed L. wrote: > > I am seeing the following error in pgsql 8.1.2: > > > > ERROR: could not access status of transaction 3229475082 > > DETAIL: could not open file "pg_clog/0C07": No such file or > > directory > > > > What does it mean, and what should I do about it? > > 1. Read this thread: > http://archives.postgresql.org/pgsql-general/2007-02/msg00820. >php > > 2. Upgrade to the latest 8.1.* release. > > 3. If that doesn't help, check your system for faulty > hardware, in particular for bad RAM. This is a 200gb DB with ~300 transactions/second serving 5 busy facilities, so downtime comes at a premium. We have some maintenance downtime planned for 2 weeks from now. I'm trying to understand if this can wait that long. It appears the only failure occurs during an autovacuum-initiated "VACUUM FREEZE" on template0 when it hits the pg_statistics table. However, that abort appears to be causing autovacuum to skip all its other duties as it endlessly restarts and fails again. Do I care if template0 gets a "VACUUM FREEZE"? Assuming not, is there a simple way to make autovacuum skip over template0 so it can tend to the important data in the other databases? Is restarting with 8.1.8 a known solution for this problem? Or is an initdb required to fix it? If initdb is required, we might as well move to the latest stable 8.2 version. I understand my options to minimize downtime to be limited to async replication. Other ideas? BTW, the RAM looks good. TIA. Ed
> If initdb is required, we might as well move to the latest stable > 8.2 version. I understand my options to minimize downtime to be > limited to async replication. Other ideas? > > BTW, the RAM looks good. > > You can update to 8.1.8 (if you are running 8.1.x) without an initdb. Joshua D. Drake > TIA. > Ed > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > >
On Tuesday March 6 2007 3:53 pm, Joshua D. Drake wrote: > > > Is restarting with 8.1.8 a known solution for this problem? > > Or is an initdb required to fix it? > > You can update to 8.1.8 (if you are running 8.1.x) without an > initdb. Right. I'm asking if the fix for this problem is in the new 8.1.8 software, or in the new DB structure resulting from the initdb, or perhaps both. Ed
Ed L. wrote: > Right. I'm asking if the fix for this problem is in the new > 8.1.8 software, or in the new DB structure resulting from the > initdb, or perhaps both. There is no new DB structure in 8.1.8, which is why you can update without initdb. Consult the release notes for details. -- Peter Eisentraut http://developer.postgresql.org/~petere/
On Tuesday March 6 2007 11:52 pm, Peter Eisentraut wrote: > Ed L. wrote: > > Right. I'm asking if the fix for this problem is in the new > > 8.1.8 software, or in the new DB structure resulting from > > the initdb, or perhaps both. > > There is no new DB structure in 8.1.8, which is why you can > update without initdb. Consult the release notes for details. Perhaps my question was not clear enough. Let me rephrase: Does the fix for this problem comes from a *fresh* DB structure resulting from the initdb, or from a software fix in 8.1.8, or both? The answer makes a big difference with a 200gb database. If it's in the software alone, we can simply restart. If it's in the DB structure, we have to migrate 200gb of data from one PGDATA to another. Ed
On Wed, Mar 07, 2007 at 02:29:08AM -0700, Ed L. wrote: > Perhaps my question was not clear enough. Let me rephrase: Does > the fix for this problem comes from a *fresh* DB structure > resulting from the initdb, or from a software fix in 8.1.8, or > both? The answer makes a big difference with a 200gb database. > If it's in the software alone, we can simply restart. If it's > in the DB structure, we have to migrate 200gb of data from one > PGDATA to another. It would come from upgrading the software. Reinitdbing won't actually make a difference. The whole point of minor versions is that you don't need to reinit the DB, otherwise it'd be a major version... In your case you could also get running by creating the missing file, but whether that's appropriate depends on the cause. I'd check the release notes to see if any related bugs have been fixed since then. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
On Wednesday March 7 2007 3:13 am, Martijn van Oosterhout wrote: > On Wed, Mar 07, 2007 at 02:29:08AM -0700, Ed L. wrote: > > Perhaps my question was not clear enough. Let me rephrase: > > Does the fix for this problem comes from a *fresh* DB > > structure resulting from the initdb, or from a software fix > > in 8.1.8, or both? The answer makes a big difference with a > > 200gb database. If it's in the software alone, we can simply > > restart. If it's in the DB structure, we have to migrate > > 200gb of data from one PGDATA to another. > > In your case you could also get running by creating the > missing file, but whether that's appropriate depends on the > cause. I'd check the release notes to see if any related bugs > have been fixed since then. How would I go about correctly creating the missing file? That sounds appealing, as if it were something I could do without taking downtime. Is it? In reviewing the release notes between 8.1.2 and 8.1.8, there are a number of vacuum fixes along with many others. It is unclear if any of them are related. Given this error is occurring while doing a "VACUUM FREEZE" of the template0 database, I wonder if that creates any options for me? Frankly, I'd be happy to simply drop the template0 DB if I could, since I'm not aware that we ever need it for anything. Ed
On Wed, Mar 07, 2007 at 07:07:45PM -0700, Ed L. wrote: > How would I go about correctly creating the missing file? That > sounds appealing, as if it were something I could do without > taking downtime. Is it? Depends if it's because the file got deleted prematurly, or because it's the result of corruption in the table. If it's just that it got deleted prematurely, you should just be able to create the file with 256KB of zeros. > In reviewing the release notes between 8.1.2 and 8.1.8, there are > a number of vacuum fixes along with many others. It is unclear > if any of them are related. There definitly was a bug related to this and you should upgrade to the latest minor release anyway, because it will probably bite you again. > Given this error is occurring while doing a "VACUUM FREEZE" of > the template0 database, I wonder if that creates any options for > me? Frankly, I'd be happy to simply drop the template0 DB if I > could, since I'm not aware that we ever need it for anything. You can drop and recreate the template0 database, instructions are somewhere. I don't think you can do without because I beleive pg_dump needs it. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.