pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
vacuuming template0 gave ERROR
9+ messages / 3 participants
[nested] [flat]

* vacuuming template0 gave ERROR
@ 2005-12-26 11:34  Gourish Singbal <gourish@gmail.com>
  0 siblings, 2 replies; 9+ messages in thread

From: Gourish Singbal @ 2005-12-26 11:34 UTC (permalink / raw)
  To: pgsql-admin

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

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Fwd: vacuuming template0 gave ERROR
@ 2005-12-26 12:32  Gourish Singbal <gourish@gmail.com>
  parent: Gourish Singbal <gourish@gmail.com>
  1 sibling, 0 replies; 9+ messages in thread

From: Gourish Singbal @ 2005-12-26 12:32 UTC (permalink / raw)
  To: pgsql-performance@postgresql.org

---------- Forwarded message ----------
From: Gourish Singbal <gourish@gmail.com>
Date: Dec 26, 2005 5:04 PM
Subject: vacuuming template0 gave ERROR
To: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>


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
\c template0
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 database1 -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


--
Best,
Gourish Singbal

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: vacuuming template0 gave ERROR
@ 2005-12-26 16:02  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Gourish Singbal <gourish@gmail.com>
  1 sibling, 1 reply; 9+ messages in thread

From: Tom Lane @ 2005-12-26 16:02 UTC (permalink / raw)
  To: Gourish Singbal <gourish@gmail.com>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: vacuuming template0 gave ERROR
@ 2005-12-27 07:05  Gourish Singbal <gourish@gmail.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 9+ messages in thread

From: Gourish Singbal @ 2005-12-27 07:05 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin

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

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: vacuuming template0 gave ERROR
@ 2005-12-27 07:16  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Gourish Singbal <gourish@gmail.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Tom Lane @ 2005-12-27 07:16 UTC (permalink / raw)
  To: Gourish Singbal <gourish@gmail.com>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: vacuuming template0 gave ERROR
@ 2005-12-27 07:20  Gourish Singbal <gourish@gmail.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 9+ messages in thread

From: Gourish Singbal @ 2005-12-27 07:20 UTC (permalink / raw)
  To: pgsql-admin

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

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Fwd: vacuuming template0 gave ERROR
@ 2006-07-31 00:16  adey <adey11@gmail.com>
  0 siblings, 1 reply; 9+ messages in thread

From: adey @ 2006-07-31 00:16 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin; PostgreSQL Admin <postgres@productivitymedia.com>; postgresql-l <postgresql-l@Groups.ittoolbox.com>

 I have the discussion below in which it is advised that template0 should
not be touched, however, my production v8.1.4 template0 and 1 database XID
"age", determined with the following query, changes constantly:-

select datname, age(datfrozenxid), datfrozenxid, current_date, current_time
FROM pg_database

If template0 shouldn't be touched (and I don't know of deliberate access to
it), why is this changing please? template0 currently returns -1797761636 (a
minus number), and template1 returns 1436634067 and is growing constantly.
Should I be doing a vacuum full on them both, and is this activity normal
please? I'm concerned the XID / wraparound age will expire eventually.

Adrian
---------- Forwarded message ----------
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Dec 27, 2005 5:02 AM
Subject: Re: [ADMIN] vacuuming template0 gave ERROR
To: Gourish Singbal <gourish@gmail.com>
Cc: "pgsql-admin@postgresql.org " <pgsql-admin@postgresql.org>

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
      choose an index scan if your joining column's datatypes do not
      match




------------------------------
At the Datamail Group we value team commitment, respect, achievement,
customer focus, and courage.

This email with any attachments is confidential and may be subject to legal
privilege. If it is not intended for you please advise by reply immediately,
destroy it and do not copy, disclose or use it in any way.
------------------------------





------------------------------
This email has been scanned by the DMZGlobal Business Quality Electronic
Messaging Suite.
Please see http://www.dmzglobal.com/services/bqem.htm for details.
------------------------------

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Fwd: vacuuming template0 gave ERROR
@ 2006-07-31 01:30  Tom Lane <tgl@sss.pgh.pa.us>
  parent: adey <adey11@gmail.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Tom Lane @ 2006-07-31 01:30 UTC (permalink / raw)
  To: adey <adey11@gmail.com>; +Cc: pgsql-admin; PostgreSQL Admin <postgres@productivitymedia.com>; postgresql-l <postgresql-l@Groups.ittoolbox.com>

adey <adey11@gmail.com> writes:
> select datname, age(datfrozenxid), datfrozenxid, current_date, current_time
> FROM pg_database

> If template0 shouldn't be touched (and I don't know of deliberate access to
> it), why is this changing please?

Because current xid is changing and template0's datfrozenxid is not.

Nonetheless, if template0 is properly frozen then you don't need to
vacuum it, regardless of what age(datfrozenxid) says.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Fwd: vacuuming template0 gave ERROR
@ 2006-07-31 02:07  adey <adey11@gmail.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 9+ messages in thread

From: adey @ 2006-07-31 02:07 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin; PostgreSQL Admin <postgres@productivitymedia.com>; postgresql-l <postgresql-l@groups.ittoolbox.com>

Thanks Tom - datfrozenxid has been the same for at least the last month on
template0 and 1

On 7/31/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> adey <adey11@gmail.com> writes:
> > select datname, age(datfrozenxid), datfrozenxid, current_date,
> current_time
> > FROM pg_database
>
> > If template0 shouldn't be touched (and I don't know of deliberate access
> to
> > it), why is this changing please?
>
> Because current xid is changing and template0's datfrozenxid is not.
>
> Nonetheless, if template0 is properly frozen then you don't need to
> vacuum it, regardless of what age(datfrozenxid) says.
>
>                        regards, tom lane
>

^ permalink  raw  reply  [nested|flat] 9+ messages in thread


end of thread, other threads:[~2006-07-31 02:07 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-26 11:34 vacuuming template0 gave ERROR Gourish Singbal <gourish@gmail.com>
2005-12-26 12:32 ` Gourish Singbal <gourish@gmail.com>
2005-12-26 16:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-12-27 07:05   ` Gourish Singbal <gourish@gmail.com>
2005-12-27 07:16     ` Tom Lane <tgl@sss.pgh.pa.us>
2005-12-27 07:20       ` Gourish Singbal <gourish@gmail.com>
2006-07-31 00:16 Fwd: vacuuming template0 gave ERROR adey <adey11@gmail.com>
2006-07-31 01:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-07-31 02:07   ` adey <adey11@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox