Thread: Index File growing big.
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.
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.
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
> > 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
"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
> > 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
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
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