Symlinking (specific) tables to different Drives

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.

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.

Regards

MP

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 9/4/07, Ow Mun Heng <Ow.Mun.Heng@wdc.com> wrote:

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?

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

Regards

MP

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)