Thread: Downtime-free 'alter table set tablespace'
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
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
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
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