

Hi !!

   We have "PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96"
installed on Linux (RedHat 7.2)
Our database size is 15 GB.
Since the database size was increasing and was about to cross the actual 
Hard Disk parttion Size, we moved the datafiles (also the index files) to 
another partition  and created link to them from the data directory.
This was working fine.
But what we found was , the index files(2 files) were not getting updated 
in the new partition, instead postgres had created another index file with 
name
"tableID".1  in the original data directory. The size of this file was 
356MB, 
The actual size of the data table is 1GB. and there were 2 indexes for the 
table. which were of size approximately=150MB.

But after we created link, those 2 index files were not getting updated, 
instead the new file with ".1" extension got created in the data directory
(old parttion) and the same is getting updated everyday.

We dropped the table but the file with ".1" extension was not getting 
removed from data directory. We manually had to remove it.

Can U please suggest some way to avoid the file getting created when we 
move the data file (along with the index files) to another partition.


Thanks in Advance.



Regards,
Pragati.





Wed, 26 Feb 2003 11:56:48 +0530 (IST)
PRAGATI SAVAIKAR <pragati@phildigital.com>
Index File growing big.

I remember this is your second posting for the same problem.

I hope you are aware that postgres can manage multiple databases that 
lie in different partitions.

If its feasible for you you may think of moving the *big* tables in another
database which can be initialised in another partition. it depends on ur app
design as you will have to create a new db connection.

another possibility is to buy a new bigger hdd ofcourse ;-)
and migrate the data.

also i think the <tableid>.1 or <tableid>.2 are extensions of the 
datafile not index files. index files have seperate id of their own.
pg_class have that id in relfilenode.


in case i am getting the problem wrong , my query is have you relocated the
index files also and created the symlinks ? (using ln -s)

Also 7.2.1 is too old an version to use in 7.2.x series 7.2.4 is latest
and in 7.3.x 7.3.2 is latest.

regds
mallah.



On Wednesday 26 February 2003 11:56 am, PRAGATI SAVAIKAR wrote:
> Hi !!
>
>    We have "PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96"
> installed on Linux (RedHat 7.2)
> Our database size is 15 GB.
> Since the database size was increasing and was about to cross the actual
> Hard Disk parttion Size, we moved the datafiles (also the index files) to
> another partition  and created link to them from the data directory.
> This was working fine.
> But what we found was , the index files(2 files) were not getting updated
> in the new partition, instead postgres had created another index file with
> name
> "tableID".1  in the original data directory. The size of this file was
> 356MB,
> The actual size of the data table is 1GB. and there were 2 indexes for the
> table. which were of size approximately=150MB.
>
> But after we created link, those 2 index files were not getting updated,
> instead the new file with ".1" extension got created in the data directory
> (old parttion) and the same is getting updated everyday.
>
> We dropped the table but the file with ".1" extension was not getting
> removed from data directory. We manually had to remove it.
>
> Can U please suggest some way to avoid the file getting created when we
> move the data file (along with the index files) to another partition.
>
>
> Thanks in Advance.
>
>
>
> Regards,
> Pragati.
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

-- 

Regds
Mallah

----------------------------------------
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.

Wed, 26 Feb 2003 12:59:23 +0530
Rajesh Kumar Mallah <mallah@trade-india.com>
Re: Index File growing big.
On Wed, Feb 26, 2003 at 11:56:48AM +0530, PRAGATI SAVAIKAR wrote:
> 
> Can U please suggest some way to avoid the file getting created when we 
> move the data file (along with the index files) to another partition.

Yes.  Submit a patch which implements tablespaces ;-)

Seriously, there is no way to avoid this in the case where you are
moving the files by hand.  The suggestions for how to move files
around note this.

If this is merely a disk-size problem, why not move the entire
postgres installation to another disk, and make a link to it.  If you
still need to spread things across disks, you can move things which
don't change in size very much.  A good candidate here is the WAL
(pg_xlog), since it grows to a predictable size.  You even get a
performance benefit.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110


Wed, 26 Feb 2003 07:00:40 -0500
Andrew Sullivan <andrew@libertyrms.info>
Re: Index File growing big.
> > Can U please suggest some way to avoid the file getting created when we
> > move the data file (along with the index files) to another partition.
>
> Yes.  Submit a patch which implements tablespaces ;-)

You should note that someone already has sent in a patch for tablespaces, it
hasn't been acted on though - can't quite remember why.  Maybe we should
resurrect it...

Chris



Thu, 27 Feb 2003 09:19:32 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Index File growing big.
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
>> Yes.  Submit a patch which implements tablespaces ;-)

> You should note that someone already has sent in a patch for tablespaces, it
> hasn't been acted on though - can't quite remember why.  Maybe we should
> resurrect it...

It's been awhile, but my recollection is that the patch had restricted
functionality (which would be okay for a first cut) and it invented SQL
syntax that seemed to lock us into that restricted functionality
permanently (not so okay).  Details are fuzzy though...

			regards, tom lane

Thu, 27 Feb 2003 01:56:49 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Index File growing big. 
> > You should note that someone already has sent in a patch for
tablespaces, it
> > hasn't been acted on though - can't quite remember why.  Maybe we should
> > resurrect it...
>
> It's been awhile, but my recollection is that the patch had restricted
> functionality (which would be okay for a first cut) and it invented SQL
> syntax that seemed to lock us into that restricted functionality
> permanently (not so okay).  Details are fuzzy though...

Well, I'll resurrect it and see if it can be improved.  Tablespaces seem to
be a requested feature these days...

Chris



Thu, 27 Feb 2003 17:03:37 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Index File growing big. 
All,

I was the person who submitted the patch.  I tried to a generic syntax.  I also tried to keep the tablespace concept
simple.  See my posting on HACKERS/GENERAL from a week or 2 ago about the syntax.  I am still interested in working on
this patch with others.  I have many system here that are 500+ gigabytes and growing.  It is a real pain to add more
disk space (I have to backup, drop database(s), rebuild raid set (I am using raid 10) and reload data).

Jim



> > > You should note that someone already has sent in a patch for
> tablespaces, it
> > > hasn't been acted on though - can't quite remember why.  Maybe we should
> > > resurrect it...
> >
> > It's been awhile, but my recollection is that the patch had restricted
> > functionality (which would be okay for a first cut) and it invented SQL
> > syntax that seemed to lock us into that restricted functionality
> > permanently (not so okay).  Details are fuzzy though...
> 
> Well, I'll resurrect it and see if it can be improved.  Tablespaces seem to
> be a requested feature these days...
> 
> Chris
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster





Thu, 27 Feb 2003 07:34:12 -0500
"Jim Buttafuoco" <jim@contactbda.com>
Re: Index File growing big. 
Hi Jim,

Do you have a version of the patch that's synced against CVS HEAD?

Chris

> All,
>
> I was the person who submitted the patch.  I tried to a generic syntax.  I
also tried to keep the tablespace concept
> simple.  See my posting on HACKERS/GENERAL from a week or 2 ago about the
syntax.  I am still interested in working on
> this patch with others.  I have many system here that are 500+ gigabytes
and growing.  It is a real pain to add more
> disk space (I have to backup, drop database(s), rebuild raid set (I am
using raid 10) and reload data).
>
> Jim



Fri, 28 Feb 2003 09:57:43 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: [PERFORM] Index File growing big. 

Yes, the issue was that it only had places for heap and index location,
not more generic.

I can work with a few folks to get this done. I think it can be done in
a few stages:

	Decide on syntax/functionality
	Update grammer to support it
	Update system catalogs to hold information
	Update storage manager to handle storage locations

If folks can decide on the first item, I can do the second and third
ones.

---------------------------------------------------------------------------

Jim Buttafuoco wrote:
> All,
> 
> I was the person who submitted the patch.  I tried to a generic syntax.  I also tried to keep the tablespace concept
> simple.  See my posting on HACKERS/GENERAL from a week or 2 ago about the syntax.  I am still interested in working on
> this patch with others.  I have many system here that are 500+ gigabytes and growing.  It is a real pain to add more
> disk space (I have to backup, drop database(s), rebuild raid set (I am using raid 10) and reload data).
> 
> Jim
> 
> 
> 
> > > > You should note that someone already has sent in a patch for
> > tablespaces, it
> > > > hasn't been acted on though - can't quite remember why.  Maybe we should
> > > > resurrect it...
> > >
> > > It's been awhile, but my recollection is that the patch had restricted
> > > functionality (which would be okay for a first cut) and it invented SQL
> > > syntax that seemed to lock us into that restricted functionality
> > > permanently (not so okay).  Details are fuzzy though...
> > 
> > Well, I'll resurrect it and see if it can be improved.  Tablespaces seem to
> > be a requested feature these days...
> > 
> > Chris
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> 
> 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Thu, 6 Mar 2003 15:18:56 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Index File growing big.