Re: Downtime-free 'alter table set tablespace'

On Friday 23 May 2014 15:12:47 Chris Ernst wrote:
> On 05/23/2014 08:57 AM, Vincent de Phily wrote:
> > 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.
>
> You might look at pg_repack (https://github.com/reorg/pg_repack) The
> most recent version added the ability to repack a table (or just
> indexes) to a new tablespace. It won't be fast as it will essentially
> rebuild the entire table. But it only needs an exclusive lock for a
> brief moment, so there's virtually zero down time and no data loss.
>
> - Chris

That's pretty much what I was looking for, thanks. It's not perfect because it
still requires a fair amount of temporary space on the origin tablespace, but
it does the job in a cleaner way than what I was attempting.

Thanks Jerry too for the slony suggestion, I didn't think slony (which I've
used a bit) supported replicating to the same db in a different table name.

It'd still be nice to get support in core for "set tablespace concurrently"
because it has the potential to be much more efficient, but beggers can't be
choosers :p

--
Vincent de Phily