X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id EE7E59FA169 for ; Fri, 26 May 2006 04:08:23 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 49262-06 for ; Fri, 26 May 2006 04:08:11 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.172]) by postgresql.org (Postfix) with ESMTP id 2A8699F9EEB for ; Fri, 26 May 2006 04:08:10 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id m3so43702ugc for ; Fri, 26 May 2006 00:08:09 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=cofK0f3WORonbCzWg/PFo50AFcs+NNSvw8WoYCi+6Ka7RG0oFfm24CX7QFB2cKuh0osMwCZ683eAeMgg/MFFCxw5uhxb+o2aFJlxQJYRmjUAicUkJX+z+XBOjFOlUMl+E7F0HBdfDHIhLk6RXczsKM9SBxyAWTuqLxk3amdZTBk= Received: by 10.66.216.20 with SMTP id o20mr155009ugg; Fri, 26 May 2006 00:08:09 -0700 (PDT) Received: by 10.66.222.12 with HTTP; Fri, 26 May 2006 00:08:09 -0700 (PDT) Message-ID: <674d1f8a0605260008h1826dc50t21f4e52fab8e41b6@mail.gmail.com> Date: Fri, 26 May 2006 12:38:09 +0530 From: "Gourish Singbal" To: pgsql-admin@postgresql.org Subject: Re: pg_clog questions In-Reply-To: <674d1f8a0605252339k248ed8fay33c56e3939843417@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_112324_20868477.1148627289789" References: <8511B4970E0D124898E973DF496F9B432516EA@stash.stackdump.local> <20060525223944.GO13700@surnet.cl> <674d1f8a0605252339k248ed8fay33c56e3939843417@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/375 X-Sequence-Number: 21907 ------=_Part_112324_20868477.1148627289789 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Wanted to know when is the feature of automatically removing the unwanted pg_clog files going to be implemented in postgresql ... http://www.postgresql.org/docs/7.4/interactive/wal-benefits-later.html this link is not present in 8.1.4 docs ... Thanks for all the help On 5/26/06, Gourish Singbal wrote: > > > Ok After reading this mail i too went to check out pg_clog and noticed > that there were 368 file dating back to the day we had installed postgres= ql > and occuping 92 MB od space. > > So i decided to vacuum the postgres and template1 databases since the > other databases are database - wide vacuumed daily. > > vacuuming postgres database did not do any good so went to template1 . On > completion found that the files had reduced to 109 number and size of > pg_clog was 27 MB now. > > But still these were too many files hence decide to peform vacuum freeze > on template1 and postgres databases. on finishing .. checked the pg_clog = dir > and found that there was just one file there > > drwx------ 10 postgres wheel 632 2006-05-25 23:15 .. > drwx------ 2 postgres wheel 72 2006-05-25 23:20 . > -rw------- 1 postgres wheel 172032 2006-05-25 23:35 016C > > and if i am not mistaken this is the only file that the postgres is using > at this time. > > also age went down to > postgres=3D# SELECT datname, age(datfrozenxid) FROM pg_database; > datname | age > ---------------------+------------ > postgres | 73 > template1 | 128 > template0 | 382360894 > ...etc > > thanks for the info in the email thread. > > > On 5/26/06, Alvaro Herrera wrote: > > > > Benjamin Krajmalnik wrote: > > > I just ran both VACUUM ANALYZE and VACUUM on all of the databases (th= e > > > project database and the database created for the default user). > > > All of the commit logs are still there. > > > > Did you vacuum the template1 (and possibly postgres) databases as well? > > > > > Do they get cleared only by VACUUM FULL? > > > > No, VACUUM suffices. > > > > > On another subject, is there a query I can run to find out who the > > owner > > > of a filenode is? > > > > Sure -- pg_class.relowner from pg_class where relfilenode =3D ' ... ' > > > > -- > > Alvaro Herrera > > http://www.CommandPrompt.com/ > > PostgreSQL Replication, Consulting, Custom Development, 24x7 support > > > > ---------------------------(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 > > > > > > -- > Best, > > Gourish Singbal > --=20 Best, Gourish Singbal ------=_Part_112324_20868477.1148627289789 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
 
Wanted to know when is the feature of automatically removing the  = ;unwanted pg_clog files going to be implemented in postgresql ...
 
 
this link is not present in 8.1.4 docs ...
 
Thanks for all the help
 
 
On 5/26/06, = Gourish Singbal <gourish@gmail.= com> wrote:
 
Ok After reading this mail i too went to check out pg_clog and noticed= that there were 368 file dating back to the day we had installed postgresq= l and occuping 92 MB od space.
 
So i decided to vacuum the postgres and template1 databases since the = other databases are database - wide vacuumed daily.
 
vacuuming postgres database did not do any good so went to template1 .= On completion found that the files had reduced to 109 number and size of p= g_clog was 27 MB now.
 
But still these were too many files hence decide to peform vacuum free= ze on template1 and postgres databases. on finishing .. checked the pg_clog= dir and found that there was just one file there
 
drwx------  10 postgres wheel    632 2006-05-25 23= :15 ..
drwx------   2 postgres wheel     7= 2 2006-05-25 23:20 .
-rw-------   1 postgres wheel 172032 2006= -05-25 23:35 016C
 
and if i am not mistaken this is the only file that the postgres = is using at this time.
 
also age went down to
postgres=3D# SELECT datname, age(datfrozenxid) FROM pg_database;
&n= bsp;      datname     &nb= sp; |    age
---------------------+------------
 = postgres            = |         73
 template1&nbs= p;          |   = ;     128
 template0    &nb= sp;      |  382360894=20
...etc
 
thanks for the info in the email thread.

 
On 5/26/06, = Alvaro Herrera <alvher= re@commandprompt.com > wrote:=20
Benjamin Krajmalnik wrote:
&g= t; I just ran both VACUUM ANALYZE and VACUUM on all of the databases (the > project database and the database created for the default user).
&g= t; All of the commit logs are still there.

Did you vacuum the templa= te1 (and possibly postgres) databases as well?

> Do they get clea= red only by VACUUM FULL?=20

No, VACUUM suffices.

> On another subject, is there a que= ry I can run to find out who the owner
> of a filenode is?

Sur= e -- pg_class.relowner from pg_class where relfilenode =3D ' ... '

--
Alvaro Herrera         &= nbsp;           &nbs= p;          http://www.CommandPrompt.com/
PostgreSQ= L Replication, Consulting, Custom Development, 24x7 support

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


--
Best,
 
Gourish Singbal


--
Best,
Gourish Singbal=20 ------=_Part_112324_20868477.1148627289789--