Re: restoring from dump

Hi Alvaro,

I just looked in pg_clog directory and there is
only one file:
-rw------- 1 postgres postgres 24576 Aug 25 20:18 0000
I saw the same file about 10:30 am and I believe
it was roughly the same size. This is a brand new
installation of 8.3.3.

Regards,

Tena Sakai
tsakai(at)gallo(dot)ucsf(dot)edu

-----Original Message-----
From: Alvaro Herrera [mailto:alvherre(at)commandprompt(dot)com]
Sent: Mon 8/25/2008 7:34 PM
To: Tena Sakai
Cc: Marcelo Martins; jeff(at)frostconsultingllc(dot)com; pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] restoring from dump

Tena Sakai wrote:
> Hi,
>
> I downloaded the latest release (8.3.3), followed the
> instruction on the manual to install and restored the
> file I had made by pg_dumpall, thereby bypassing the
> trouble with files in pg_clog directory.
>
> I just read a bit about these files. They are made
> as a result of autovacuum. Does anybody have notion
> as to how long they should be kept, what recycling
> policy there ought to be, etc?

They are not created by autovacuum. They are a very central part of the
system. Autovacuum can cause them to be removed when they are no longer
needed (manual vacuuming can cause them to be deleted too). If they are
removed before every last bit that depends on them is, then we have a
bug.

This is very strange:

> > > I went into pg_clog directory and issued:
> > > ls -lt | head
> > > and it told me:
> > > -rw------- 1 postgres postgres 163840 Aug 24 09:57 0088
> > > drwx------ 11 postgres postgres 4096 Aug 22 13:56 ..
> > > drwx------ 2 postgres postgres 4096 Jun 30 16:03 .
> > > -rw------- 1 postgres postgres 262144 Jun 1 20:04 0084
> > > -rw------- 1 postgres postgres 262144 Apr 4 15:48 0083
> > > -rw------- 1 postgres postgres 262144 Mar 26 18:25 0082
> > > -rw------- 1 postgres postgres 262144 Mar 26 01:28 0081
> > > -rw------- 1 postgres postgres 262144 Mar 25 23:05 0080
> > > -rw------- 1 postgres postgres 262144 Mar 25 20:39 007F
> > >
> > > It seems that 0088 was generated at the time very close to
> > > my issuing dropdb.

When a file is deleted, all files prior to it must be deleted too. If
files prior to it remained, what it suggests is that the deletion did
not happen because of vacuum or autovacuum, but because of something
else that's not related to Postgres.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.