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 one object 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
