Postgres Pro
Downloads
Home   >   mailing lists

Thread: Easy way of pruning pg_data?

Easy way of pruning pg_data?

From
"Lee Crampton"
Date:
Hi All
given the folowing:

ls -l pg_data/base
drwx--S---    2 postgres postgres     2048 Apr 15  2001 1
drwx--S---    2 postgres postgres     2048 Aug 27 12:26 1007014
drwx--S---    2 postgres postgres     2048 Sep 15 08:57 1045851
drwx--S---    2 postgres postgres     2048 Apr 15  2001 18719
drwx--S---    2 postgres postgres     2048 Apr 15  2001 18735
drwx--S---    2 postgres postgres     2048 Apr 15  2001 18891
drwx--S---    2 postgres postgres     2048 Apr 15  2001 18916
drwx--S---    2 postgres postgres     4096 Oct 12 20:09 19100
drwx--S---    2 postgres postgres     2048 Apr 15  2001 192660
drwx--S---    2 postgres postgres     2048 Apr 15  2001 206353
drwx--S---    2 postgres postgres     2048 Apr 15  2001 206889
drwx--S---    2 postgres postgres     2048 Apr 15  2001 207360
drwx--S---    2 postgres postgres     2048 Jun  5 10:44 639889
drwx--S---    2 postgres postgres     2048 Jun  5 11:19 643727
drwx--S---    2 postgres postgres     2048 Aug  1 12:28 953493

Is there an easy way to housekeep (prune) these directories?
Thanks in advance
Lee

Re: Easy way of pruning pg_data?

From
David Link
Date:
What are those files?  my $PGDATA/base only contains subdirectores, one
for each database name.  Are those your database names?

Lee Crampton wrote:
>
> Hi All
> given the folowing:
>
> ls -l pg_data/base
> drwx--S---    2 postgres postgres     2048 Apr 15  2001 1
> drwx--S---    2 postgres postgres     2048 Aug 27 12:26 1007014
> drwx--S---    2 postgres postgres     2048 Sep 15 08:57 1045851
> drwx--S---    2 postgres postgres     2048 Apr 15  2001 18719
> drwx--S---    2 postgres postgres     2048 Apr 15  2001 18735
> drwx--S---    2 postgres postgres     2048 Apr 15  2001 18891
> drwx--S---    2 postgres postgres     2048 Apr 15  2001 18916
> drwx--S---    2 postgres postgres     4096 Oct 12 20:09 19100
> drwx--S---    2 postgres postgres     2048 Apr 15  2001 192660
> drwx--S---    2 postgres postgres     2048 Apr 15  2001 206353
> drwx--S---    2 postgres postgres     2048 Apr 15  2001 206889
> drwx--S---    2 postgres postgres     2048 Apr 15  2001 207360
> drwx--S---    2 postgres postgres     2048 Jun  5 10:44 639889
> drwx--S---    2 postgres postgres     2048 Jun  5 11:19 643727
> drwx--S---    2 postgres postgres     2048 Aug  1 12:28 953493
>
> Is there an easy way to housekeep (prune) these directories?
> Thanks in advance
> Lee
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

Re: Easy way of pruning pg_data?

From
Doug McNaught
Date:
David Link <dlink@soundscan.com> writes:

> What are those files?  my $PGDATA/base only contains subdirectores, one
> for each database name.  Are those your database names?

Recent versions of Postgres (since 7.1 IIRC) store the databases by
IOD number instead of by name (to avoid certain problems with
renaming).

The only way to "prune" the files under your data/base directory is to
VACUUM and to drop and recreate your indexes, both of which will free
up space.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863

Re: Easy way of pruning pg_data?

From
"lee@avbrief"
Date:
No they are not.
I wondered if they were temporary space or something other.
I might take a punt at moving them, but then again :-(!
Thanks
Lee

----- Original Message -----
From: "David Link" <dlink@soundscan.com>
To: "Lee Crampton" <lee@avbrief.com>
Cc: <pgsql-general@postgresql.org>
Sent: Monday, October 15, 2001 7:48 PM
Subject: Re: [GENERAL] Easy way of pruning pg_data?


> What are those files?  my $PGDATA/base only contains subdirectores, one
> for each database name.  Are those your database names?
>
> Lee Crampton wrote:
> >
> > Hi All
> > given the folowing:
> >
> > ls -l pg_data/base
> > drwx--S---    2 postgres postgres     2048 Apr 15  2001 1
> > drwx--S---    2 postgres postgres     2048 Aug 27 12:26 1007014
> > drwx--S---    2 postgres postgres     2048 Sep 15 08:57 1045851
> > drwx--S---    2 postgres postgres     2048 Apr 15  2001 18719
> > drwx--S---    2 postgres postgres     2048 Apr 15  2001 18735
> > drwx--S---    2 postgres postgres     2048 Apr 15  2001 18891
> > drwx--S---    2 postgres postgres     2048 Apr 15  2001 18916
> > drwx--S---    2 postgres postgres     4096 Oct 12 20:09 19100
> > drwx--S---    2 postgres postgres     2048 Apr 15  2001 192660
> > drwx--S---    2 postgres postgres     2048 Apr 15  2001 206353
> > drwx--S---    2 postgres postgres     2048 Apr 15  2001 206889
> > drwx--S---    2 postgres postgres     2048 Apr 15  2001 207360
> > drwx--S---    2 postgres postgres     2048 Jun  5 10:44 639889
> > drwx--S---    2 postgres postgres     2048 Jun  5 11:19 643727
> > drwx--S---    2 postgres postgres     2048 Aug  1 12:28 953493
> >
> > Is there an easy way to housekeep (prune) these directories?
> > Thanks in advance
> > Lee
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html

Re: Easy way of pruning pg_data?

From
"Oliver Elphick"
Date:
"lee@avbrief" wrote:
  >No they are not.
  >I wondered if they were temporary space or something other.

No.  They are your databases.  Database directory names are now based on the
oid of the database in pg_database.

  >I might take a punt at moving them, but then again :-(!

Don't!

  >
  >----- Original Message -----
  >From: "David Link" <dlink@soundscan.com>
  >To: "Lee Crampton" <lee@avbrief.com>
  >Cc: <pgsql-general@postgresql.org>
  >Sent: Monday, October 15, 2001 7:48 PM
  >Subject: Re: [GENERAL] Easy way of pruning pg_data?
  >
  >
  >> What are those files?  my $PGDATA/base only contains subdirectores, one
  >> for each database name.  Are those your database names?
  >>
  >> Lee Crampton wrote:
  >> >
  >> > Hi All
  >> > given the folowing:
  >> >
  >> > ls -l pg_data/base
  >> > drwx--S---    2 postgres postgres     2048 Apr 15  2001 1
  >> > drwx--S---    2 postgres postgres     2048 Aug 27 12:26 1007014
  >> > drwx--S---    2 postgres postgres     2048 Sep 15 08:57 1045851
  >> > drwx--S---    2 postgres postgres     2048 Apr 15  2001 18719
  >> > drwx--S---    2 postgres postgres     2048 Apr 15  2001 18735
  >> > drwx--S---    2 postgres postgres     2048 Apr 15  2001 18891
  >> > drwx--S---    2 postgres postgres     2048 Apr 15  2001 18916
  >> > drwx--S---    2 postgres postgres     4096 Oct 12 20:09 19100
  >> > drwx--S---    2 postgres postgres     2048 Apr 15  2001 192660
  >> > drwx--S---    2 postgres postgres     2048 Apr 15  2001 206353
  >> > drwx--S---    2 postgres postgres     2048 Apr 15  2001 206889
  >> > drwx--S---    2 postgres postgres     2048 Apr 15  2001 207360
  >> > drwx--S---    2 postgres postgres     2048 Jun  5 10:44 639889
  >> > drwx--S---    2 postgres postgres     2048 Jun  5 11:19 643727
  >> > drwx--S---    2 postgres postgres     2048 Aug  1 12:28 953493
  >> >
  > > Is there an easy way to housekeep (prune) these directories?

Vacuum your databases.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C

     "Trust in the LORD, and do good..."        Psalms 37:3