Postgres Pro
Downloads
Home   >   mailing lists

Thread: vacuuming template0 gave ERROR

vacuuming template0 gave ERROR

From
Gourish Singbal
Date:
 
Guys,
 
Got the following ERROR when i was vacuuming the template0 database.
postgresql server version is 7.4.5 and stats info in postgresql.conf is

# - Query/Index Statistics Collector -

stats_start_collector = true
stats_command_string = true
stats_block_level = true
stats_row_level = tue
#stats_reset_on_server_start = true

============================

step 1

update pg_database set datallowconn=TRUE where datname='template0';

 
step 2
vacuum analyze verbose
......
.....
INFO:  vacuuming "pg_catalog.pg_statistic"
ERROR:  could not access status of transaction 1107341112
DETAIL:  could not open file "/home/postgres/data/pg_clog/0420": No such file or directory
 
step 3
postgres@test1:~> /usr/local/pgsql/bin/psql ecommerce -c 'SELECT datname, age(datfrozenxid) FROM pg_database';
  datname  |    age
-----------+------------
 template0 | 1112108248
 database1 | 1074511487
 template1 | 1073987669
(3 rows)
 
Files in the pg_clog are:-
postgres@test1:~/data/pg_clog> ls -lart
total 417
-rw-------  1 postgres postgres 262144 2005-12-26 02:09 0443
drwx------  2 postgres postgres     96 2005-12-26 02:17 ./
drwx------  6 postgres postgres    640 2005-12-26 03:22 ../
-rw-------  1 postgres postgres 163840 2005-12-26 03:23 0444
 
Problem: template0 is not getting vacuumed due to the above ERROR.. please let me know whats the solution.
--
Best,
Gourish Singbal

Re: vacuuming template0 gave ERROR

From
Tom Lane
Date:
Gourish Singbal <gourish@gmail.com> writes:
> Got the following ERROR when i was vacuuming the template0 database.

Why were you doing that in the first place?  template0 shouldn't ever
be touched.

> postgresql server version is 7.4.5

The underlying cause is likely related to this 7.4.6 bug fix:

2004-10-13 18:22  tgl

    * contrib/pgstattuple/pgstattuple.c,
    src/backend/access/heap/heapam.c,
    src/backend/utils/adt/ri_triggers.c (REL7_4_STABLE): Repair
    possible failure to update hint bits back to disk, per
    http://archives.postgresql.org/pgsql-hackers/2004-10/msg00464.php.
    I plan a more permanent fix in HEAD, but for the back branches it
    seems best to just touch the places that actually have a problem.


> INFO:  vacuuming "pg_catalog.pg_statistic"
> ERROR:  could not access status of transaction 1107341112
> DETAIL:  could not open file "/home/postgres/data/pg_clog/0420": No such
> file or directory

Fortunately for you, pg_statistic doesn't contain any irreplaceable
data.  So you could get out of this via

    TRUNCATE pg_statistic;
    VACUUM ANALYZE;  -- rebuild contents of pg_statistic
    VACUUM FREEZE;   -- make sure template0 needs no further vacuuming

Then reset template0's datallowconn to false, and get rid of that code
to override it.  And then update to a more recent release ;-)

(I don't recall exactly what rules 7.4 uses, but likely you'll find that
you need to run a standalone backend with -O switch to perform
TRUNCATE on a system catalog.)

            regards, tom lane

Re: vacuuming template0 gave ERROR

From
Gourish Singbal
Date:
 
Tom,
I got the followign Erorr when i tried to trucate the table.
 /usr/local/pgsql/bin/postgres -D data -O -o standalone_log template0
 
2005-12-26 22:48:12 ERROR:  expected one dependency record for TOAST table, found 0
2005-12-26 22:48:31 ERROR:  could not access status of transaction 1107341112
DETAIL:  could not open file "/home/postgres/data/pg_clog/0420": No such file or directory
2005-12-26 22:48:41 LOG:  shutting down
2005-12-26 22:48:41 LOG:  database system is shut down

please suggest ?.
 
On 12/26/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Gourish Singbal <gourish@gmail.com> writes:
> Got the following ERROR when i was vacuuming the template0 database.

Why were you doing that in the first place?  template0 shouldn't ever
be touched.

> postgresql server version is 7.4.5

The underlying cause is likely related to this 7.4.6 bug fix:

2004-10-13 18:22  tgl

       * contrib/pgstattuple/pgstattuple.c,
       src/backend/access/heap/heapam.c,
       src/backend/utils/adt/ri_triggers.c (REL7_4_STABLE): Repair
       possible failure to update hint bits back to disk, per
       http://archives.postgresql.org/pgsql-hackers/2004-10/msg00464.php.
       I plan a more permanent fix in HEAD, but for the back branches it
       seems best to just touch the places that actually have a problem.


> INFO:  vacuuming "pg_catalog.pg_statistic"
> ERROR:  could not access status of transaction 1107341112
> DETAIL:  could not open file "/home/postgres/data/pg_clog/0420": No such
> file or directory

Fortunately for you, pg_statistic doesn't contain any irreplaceable
data.  So you could get out of this via

       TRUNCATE pg_statistic;
       VACUUM ANALYZE;  -- rebuild contents of pg_statistic
       VACUUM FREEZE;   -- make sure template0 needs no further vacuuming

Then reset template0's datallowconn to false, and get rid of that code
to override it.  And then update to a more recent release ;-)

(I don't recall exactly what rules 7.4 uses, but likely you'll find that
you need to run a standalone backend with -O switch to perform
TRUNCATE on a system catalog.)

                       regards, tom lane



--
Best,
Gourish Singbal

Re: vacuuming template0 gave ERROR

From
Tom Lane
Date:
Gourish Singbal <gourish@gmail.com> writes:
> I got the followign Erorr when i tried to trucate the table.
>  /usr/local/pgsql/bin/postgres -D data -O -o standalone_log template0

> 2005-12-26 22:48:12 ERROR:  expected one dependency record for TOAST table,
> found 0

[ raised eyebrow... ]  Probably time to pg_dump, initdb, reload.  You
seem to be suffering multiple problems.  If you aren't aware of any
catastrophe that would explain all these holes in your DB, then it's
also time to start running some hardware diagnostics ...

            regards, tom lane

Re: vacuuming template0 gave ERROR

From
Gourish Singbal
Date:
 
Thanks a million tom,
 
But guess what we think alike, have taken the dump and am in to process of restoring it right now.
 
thanks for the help.

 
On 12/27/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Gourish Singbal <gourish@gmail.com> writes:
> I got the followign Erorr when i tried to trucate the table.
>  /usr/local/pgsql/bin/postgres -D data -O -o standalone_log template0

> 2005-12-26 22:48:12 ERROR:  expected one dependency record for TOAST table,
> found 0

[ raised eyebrow... ]  Probably time to pg_dump, initdb, reload.  You
seem to be suffering multiple problems.  If you aren't aware of any
catastrophe that would explain all these holes in your DB, then it's
also time to start running some hardware diagnostics ...

                       regards, tom lane



--
Best,
Gourish Singbal