Thread: one table from the db on a separate drive

Hi,

how can i place a table from my db on a separate drive than the whole database. I have 2 HDD in my system and my db resides on one of them. i want to move a single table from one hdd to another on the same system.
Is it posible? if yes, how??

Regards

Pradeep

am  15.02.2006, um 11:25:03 +0530 mailte Pradeep Parmar folgendes:
> Hi,
>
> how can i place a table from my db on a separate drive than the whole
> database. I have 2 HDD in my system and my db resides on one of them. i want
> to move a single table from one hdd to another on the same system.
> Is it posible? if yes, how??

You can create a new tablespace and use this. Read
http://www.postgresql.org/docs/8.1/interactive/manage-ag-tablespaces.html


HTH, Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47215,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===

On Wed, Feb 15, 2006 at 11:25:03AM +0530, Pradeep Parmar wrote:
> how can i place a table from my db on a separate drive than the whole
> database. I have 2 HDD in my system and my db resides on one of them. i want
> to move a single table from one hdd to another on the same system.
> Is it posible? if yes, how??

What version of PostgreSQL are you using?  If 8.0 or later then
check out tablespaces and ALTER TABLE SET TABLESPACE.

http://www.postgresql.org/docs/8.1/interactive/manage-ag-tablespaces.html
http://www.postgresql.org/docs/8.1/interactive/sql-altertable.html

--
Michael Fuhr

Le Mercredi 15 Février 2006 06:55, Pradeep Parmar a écrit :
> Hi,
>
> how can i place a table from my db on a separate drive than the whole
> database. I have 2 HDD in my system and my db resides on one of them. i
> want to move a single table from one hdd to another on the same system. Is
> it posible? if yes, how??

If you are using postgresql 8.0 or highter:

http://www.postgresql.org/docs/8.1/static/manage-ag-tablespaces.html

and change the tablespace of you table.

Otherwise you have to play with env variable with lower version, don't
remember how.


>
> Regards
>
> Pradeep

Hi,

>>What version of PostgreSQL are you using?  If 8.0 or later then
>>check out tablespaces and ALTER TABLE SET TABLESPACE.

I'm using Postgres 7.4. Is the procedure different in this version than Postgres 8.0

Regards

Pradeep

Le Mercredi 15 Février 2006 07:38, Pradeep Parmar a écrit :
> Hi,
>
> >>What version of PostgreSQL are you using?  If 8.0 or later then
> >>check out tablespaces and ALTER TABLE SET TABLESPACE.
>
> I'm using Postgres 7.4. Is the procedure different in this version than
> Postgres 8.0

Yes, you have to set a envirronement variable pointing to the new location,
restart postgresql with this env variable set, and then setting your table
using the new location:

http://www.postgresql.org/docs/7.4/interactive/manage-ag-alternate-locs.html

Hi,

I went thru the docs at this link:

http://www.postgresql.org/docs/7.4/interactive/manage-ag-alternate-locs.html

But, it describes creating database at alternative location.
What i want is to have a table stored on a separate disk than the rest of the database.
Is there any equivalent of tablespaces (in 8.1) in Postgres 7.4
I'm using Postgres 7.4

Regards

Pradeep

At 02:06 AM 2/15/06, Pradeep Parmar wrote:
>I went thru the docs at this link:
>

><http://www.postgresql.org/docs/7.4/interactive/manage-ag-alternate-locs.html>http://www.postgresql.org/docs/7.4/interactive/manage-ag-alternate-locs.html
>
>But, it describes creating database at alternative location.
>What i want is to have a table stored on a separate disk than the rest of
>the database.
>Is there any equivalent of tablespaces (in 8.1) in Postgres 7.4
>I'm using Postgres 7.4


         - Identify file to be moved (see docs)
         - shutdown postmaster
         - use shell command to move file
         - create link from old location to new location
         - restart postmaster

Caveat: I'm guessing that if you drop/create table it will go back to
original location.