Vincent de Phily <vincent.dephily@mobile-devices.fr> writes:
> 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
moveone 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) ?
Not low level that I'm aware of.
But I'd suggest you explore Slony or Skytools for doing a logical
replica of the table that needs moving and this will get you through it
with very low downtime or possibly none if you're clever and careful.
Learning how to configure and work with either of these solutions
unfortunately takes a bit of work.
>
>
> Thanks.
>
> --
> Vincent de Phily
--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800
