0

I have a postgres database with tablespaces spread over two disks. (one SSD disc for current data, one magnetic drive for older data)

Occasionally I move table partitions that contain 'old' data to the tablespace on the backup drive using....

ALTER TABLE..... SET TABLESPACE....

Sometimes the command will fail after a few minutes with a space warning. The source table may be (say) 30Gbytes, the disk the table is on may be down to it's last 5Gbytes of free space (according to windows) and the destination tablespace might have several hundred Gbytes free.

Questions

  1. Why is freespace required on the source disk - i.e. the disk the data is already on?
  2. Is there anyway of reducing (or removing) the requirement for freespace?
  3. Is there anyway of calculating how much free space is going to be required to allow the SET TABLESPACE command to work?
1
  • Please add the exact error message. Perhaps you are running out of WAL space. Sep 11, 2022 at 14:22

1 Answer 1

0

Unless your wal_level is set to 'minimal', the entire table has to be written to the WAL stream when you move it to a new tablespace. This could easily run you out of space if pg_wal is on the same disk/partition as the old tablespace is.

2
  • thanks for that, so I guess if the table partition is 30GByte I need 30Gbyte free space before attempting the move? Sep 11, 2022 at 15:12
  • Not necessarily. The WAL could get recycled fast enough so not all of it would need to be stored simultaneously, depending on your settings about checkpoints and WAL management. Unfortunately there is no way to throttle the WAL generation, so it can be hard to make sure of this.
    – jjanes
    Sep 11, 2022 at 16:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.