Downtime-free 'alter table set tablespace' - Mailing list pgsql-general

From Vincent de Phily
Subject Downtime-free 'alter table set tablespace'
Date
Msg-id 3894711.iIAW1mCiym@moltowork
Whole thread Raw
Responses Re: Downtime-free 'alter table set tablespace'  (Jerry Sievers <gsievers19@comcast.net>)
Re: Downtime-free 'alter table set tablespace'  (Chris Ernst <cernst@zvelo.com>)
List pgsql-general
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


pgsql-general by date:

Previous
From: Albe Laurenz
Date:
Subject: Re: Query with error - DOW FROM timestamp
Next
From: Catherine Devlin
Date:
Subject: Postgres Open CFP Brainstorm Hangouts, next Tues and Wed