Thread: Symlinking (specific) tables to different Drives

Hi,

I'm running out of space on one of my partitions and I still have not
gotten all the data loaded yet. I've read that one could symlink the
pg_pg_xlog directory to another drive. I'm wondering if I can do the
same for specific tables as well.

Thanks.

I've already done a pg_dump of the entire schema but have not dropped /
re-init the DB to another location cos I'm afraid I'll lose some items.
(I've to drop the DB, format the partition, merge it w/ another
partition and re-init the DB then restore the DB from the dump)

sigh.. wish it was easier, (meaning, like SQL Server where one can
detach an entire DB/tablespace and then re-attach it elsewhere)

Anyway....
Thanks for the input.

On Tue, 2007-09-04 at 07:09 +0300, Mikko Partio wrote:
>
>
> On 9/4/07, Ow Mun Heng <Ow.Mun.Heng@wdc.com> wrote:
>         Hi,
>
>         I'm running out of space on one of my partitions and I still
>         have not
>         gotten all the data loaded yet. I've read that one could
>         symlink the
>         pg_pg_xlog directory to another drive. I'm wondering if I can
>         do the
>         same for specific tables as well.
>
>
> Create another tablespace to the new location and the ALTER TABLE ...
> TABLESPACE newtablespace.
>

OOooohhh... I didn't know one could use tablespaces like that. (I mean,
I did read the docs, but it just didn't register that it _can_ do
something like that)

additional question.. do I need to change the search_path?

>
>         Thanks.
>
>         I've already done a pg_dump of the entire schema but have not
>         dropped /
>         re-init the DB to another location cos I'm afraid I'll lose
>         some items.
>         (I've to drop the DB, format the partition, merge it w/
>         another
>         partition and re-init the DB then restore the DB from the
>         dump)
>
>         sigh.. wish it was easier, (meaning, like SQL Server where one
>         can
>         detach an entire DB/tablespace and then re-attach it
>         elsewhere)
>
> If you are moving the whole cluster and can afford the downtime, you
> can shutdown the postmaster, move $PGDATA to a new location and then
> start postmaster from that new location.

It's not a cluster. Its a devel DB in my laptop so.. no issues w/
dropping everything and re-creating. Just exploring my options..

The tablespace thing looks/sounds interesting though...
>

On Tue, 2007-09-04 at 07:49 +0300, Mikko Partio wrote:

>
> No (changing tablespaces does not change your logical schema).

I just tested this "feature" with a temp table and it works as
advertised. (In progress of moving a table there now actually)

2nd question.. reading the docs, it says that moving a table doesn't
imply that the corresponding table's indexes are also moved to a that
same tablespace. eg: dbspace2

alter table foo set tablespace dbspace2;

how do I move the indexes to that space space?
I tried using pgadmin3 (1.4.3) but the option to move the index to
another tablespace is greyed out.

hmm.. forget the question.. seems like this works.. so I'm posting it to
be archived in the list.

alter table idx_foo_value set tablespace dbspace2

works.

This is missing in the docs (afaict)