Downtime-free 'alter table set tablespace'

From: Vincent de Phily <vincent(dot)dephily(at)mobile-devices(dot)fr>
To: pgsql-general(at)postgresql(dot)org
Subject: Downtime-free 'alter table set tablespace'
Date: 2014-05-23 14:57:41
Message-ID: 3894711.iIAW1mCiym@moltowork
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Catherine Devlin 2014-05-23 15:40:14 Postgres Open CFP Brainstorm Hangouts, next Tues and Wed
Previous Message Albe Laurenz 2014-05-23 09:51:30 Re: Query with error - DOW FROM timestamp