0

I use PostgreSQL on an embedded system with limited drive space. Now the DB-drive is full. When I delete data, it doesn't seem to free up any space. I tried to VACUUM FULL, but that requires space. So does deleting the last remaining index.

Any ideas on how to free up space without randomly deleting stuff? I can afford to lose some of the data from back when, but I can't seem to actually do it, since there isn't enough space to VACUUM FULL.

Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
MrB
  • 2,155
  • 7
  • 27
  • 33
  • See answers below, but a general note: If you run a general-purpose OLTP-style PostgreSQL database and your disk runs full, you're screwed. Add monitoring and keep some spare disk space around for these situations. – Peter Eisentraut May 04 '11 at 17:14

3 Answers3

2

PostgreSQL uses MVCC model which means that deleted records mark their space as free (after the transaction which deleted them had been committed) but it is still reserved by the table.

Prior to PostgreSQL 9.0, VACUUM FULL used to move the data inside the table without need for additional space.

In PostgreSQL 9.0, behavior of VACUUM FULL had changed and now it requires additional space for the full copy of the table.

You may try to drop the indexes from the tables and vacuum them one by one, starting from the least one.

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
0

The easiest answer at this point would be to dump the database to a different drive/computer (for instance, using pg_dump, or pg_dumpall if you have more than one db, and keeping in mind things like Large Objects that need special backup/restore processes) then drop and recreate the database.

If there's a tiny bit of space left, you might try vacuum full smallesttable, which might be able to finish and free up some space to vacuum the next smallest table, and so on.

If you end up filling the drive completely, the database server will probably refuse to start and you won't be able to do either of those. In that case, you could move the entire data directory to another computer with the same CPU architecture and more disk space, then start postgresql there to perform the vacuum.

DerfK
  • 273
  • 2
  • 14
  • The problem is I am connected to the embedded system over 56k modem.. so backing up several hundred megs of DB is impossible (disconnect every few minutes due to bad connection). And there is only really 1 table needing much space, so I can't really vaccum the smallest first - there is only one that needs has all the data. – MrB May 04 '11 at 13:08
  • Oh, one idea: There are so called checkpoints from the Write Ahead Lock (or something) that take a lot of space (several 100MB all in all). Can I somehow delete these checkpoints? That should be enough. – MrB May 04 '11 at 13:13
  • @MrB the Write-Ahead Log files are where postgresql stores data that hasn't been written to the database yet, if you aren't using archiving it reuses the same files in a loop, so even after a checkpoint (where it takes the data from the WAL and writes it to the DB files) you can't just delete files. What is your `checkpoint_segments` setting in postgresql.conf? – DerfK May 04 '11 at 15:24
  • @MrB do you have any other space that can be used on the device, maybe in a different partition? Since it's an embedded system I'm guessing that there isn't enough ram to make a ramdisk that could hold something long enough to get the vacuum done (though you would lose whatever it was if the system crashed while working in RAM). – DerfK May 04 '11 at 21:50
0

In certain situations VACUUM (not full) can reclaim some disk space. (I think it will return pages that are totally dead to the OS.) That might free up enough space to begin with the VACUUM FULL. But it's not a good idea to let one table grow to more than the amount of disk free space.

Andrew Lazarus
  • 18,205
  • 3
  • 35
  • 53