Monitoring, Management & Location Tracking

Bug in Postgresql v8.1.3

Problem: AMP becomes very slow and messages like these appear in /var/log/pgsql:

> Jun 13 04:05:06 host postgres[24085]: [1-1] 2007-06-13 04:05:06 EDT[24085]LOG: 00000: autovacuum: processing database "template0"
> Jun 13 04:05:06 host postgres[24085]: [1-2] 2007-06-13 04:05:06 EDT[24085]LOCATION: AutoVacMain, autovacuum.c:408
> Jun 13 04:05:07 host postgres[24085]: [2-1] 2007-06-13 04:05:07 EDT[24085]ERROR: 58P01: could not access status of transaction 3221454196
> Jun 13 04:05:07 host postgres[24085]: [2-2] 2007-06-13 04:05:07 EDT[24085]DETAIL: could not open file "pg_clog/0C00": No such file or directory

There is no such file as /var/lib/pgsql/data/pg_clog/0C00.

Solution:

These threads on the postgresql website describe the problem:

http://archives.postgresql.org/pgsql-general/2006-06/thrd2.php#00983
http://archives.postgresql.org/pgsql-general/2007-02/thrd5.php#00844

Apparently, there is a bug in posgresql 8.1.x where the template0 
database eventually gets corrupted and fails autovacuum, which prevents 
autovacuum from succeeding on other database on the system.

The somewhat hairy fix is to first create the desired temp file as 256k 
of 0x55 characters (which signifies "all transactions committed"). I 
used dd fom /dev/zero with tr to change the zeros to 0x55. There are 
undoubtedly other ways.

Then, you have to alter the template0 database to allow connections:

su postgres -c psql
update pg_database set datallowconn='t' where datname = 'template0';

Then, connect to the template0 databse and run 'vacuum freeze.' This is 
supposed to freeze the template0 database so that the problem never 
reoccurs. This vacuum deletes the now unneeded temp file.

Then alter the template0 database back to not allow connections:

su postgres -c psql
update pg_database set datallowconn='f' where datname = 'template0';

Now autovacuum succeeds and the world is a happier place.

AMP 5.2 will include a newer version of PostgreSQL that does not suffer from this issue.
Problem: AMP becomes very slow and messages like these appear in /var/log/pgsql:

> Jun 13 04:05:06 host postgres[24085]: [1-1] 2007-06-13 04:05:06 EDT[24085]LOG: 00000: autovacuum: processing database "template0"
> Jun 13 04:05:06 host postgres[24085]: [1-2] 2007-06-13 04:05:06 EDT[24085]LOCATION: AutoVacMain, autovacuum.c:408
> Jun 13 04:05:07 host postgres[24085]: [2-1] 2007-06-13 04:05:07 EDT[24085]ERROR: 58P01: could not access status of transaction 3221454196
> Jun 13 04:05:07 host postgres[24085]: [2-2] 2007-06-13 04:05:07 EDT[24085]DETAIL: could not open file "pg_clog/0C00": No such file or directory

There is no such file as /var/lib/pgsql/data/pg_clog/0C00.

Solution:

These threads on the postgresql website describe the problem:

http://archives.postgresql.org/pgsql-general/2006-06/thrd2.php#00983
http://archives.postgresql.org/pgsql-general/2007-02/thrd5.php#00844

Apparently, there is a bug in posgresql 8.1.x where the template0 
database eventually gets corrupted and fails autovacuum, which prevents 
autovacuum from succeeding on other database on the system.

The somewhat hairy fix is to first create the desired temp file as 256k 
of 0x55 characters (which signifies "all transactions committed"). I 
used dd fom /dev/zero with tr to change the zeros to 0x55. There are 
undoubtedly other ways.

Then, you have to alter the template0 database to allow connections:

su postgres -c psql
update pg_database set datallowconn='t' where datname = 'template0';

Then, connect to the template0 databse and run 'vacuum freeze.' This is 
supposed to freeze the template0 database so that the problem never 
reoccurs. This vacuum deletes the now unneeded temp file.

Then alter the template0 database back to not allow connections:

su postgres -c psql
update pg_database set datallowconn='f' where datname = 'template0';

Now autovacuum succeeds and the world is a happier place.

AMP 5.2 will include a newer version of PostgreSQL that does not suffer from this issue.

Version History
Revision #:
1 of 1
Last update:
‎06-06-2014 04:25 PM
Updated by:
 
Labels (1)
Contributors
Search Airheads
Showing results for 
Search instead for 
Did you mean: 
Is this a frequent problem?

Request an official Aruba knowledge base article to be written by our experts.