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.

Search Discussions

  • Mikko Partio at Sep 4, 2007 at 4:09 am

    On 9/4/07, Ow Mun Heng 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
  • Ow Mun Heng at Sep 4, 2007 at 4:22 am

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


    On 9/4/07, Ow Mun Heng 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...
    >
  • Mikko Partio at Sep 4, 2007 at 4:49 am

    On 9/4/07, Ow Mun Heng wrote:
    On Tue, 2007-09-04 at 07:09 +0300, Mikko Partio wrote:


    On 9/4/07, Ow Mun Heng 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
  • Ow Mun Heng at Sep 6, 2007 at 2:44 am

    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)

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedSep 4, '07 at 3:52a
activeSep 6, '07 at 2:44a
posts5
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Ow Mun Heng: 3 posts Mikko Partio: 2 posts

People

Translate

site design / logo © 2021 Grokbase