Hi list,
I'm in the process of moving some tables to a new tablespace ahead of disk
space issues. I'm on PG 9.1, using streaming replication.
I need to reduce downtime to a minimum, so I can't afford to let "alter table
set tablespace" take an exclusive lock on the table for the 2h it'll take to
copy the data.
I've searched the docs and internet but found nothing very exciting. The most
promissing was Josh's http://www.databasesoup.com/2013/11/moving-tablespaces.html but I'm on 9.1 and just want to move
oneobject to a
different tablespace, not move the whole existing tablespace to a different
partition.
My current attempt consists of renaming the old table, recreating it in the
correct tablespace, then progressively inserting data from the old table into
the new one (table is insert-only with periodic purge of old data; missing old
data for a while is an acceptable service degradation), and then cleaning up
after myself. It should work, but it's very error-prone (there are triggers
and foreign keys to deal with), slow, and app-specific.
Is there another low-level trick I missed ? Some combination of
pg_start_backup, rsync, and catalog update (which, if it was that simple,
would be great to have in core as a 'set tablespace concurrently' option) ?
Thanks.
--
Vincent de Phily