We have a Postgres 8.4 installation which hosts both our Defect tracking and Code management (Surround) systems. We are looking at converting and migrating Surround to MSSQL on another server. Our Surround structure contains multiple DBs one of which is 121GB, which in order to carry out the migration needs to be purged of its cache.

I have carried out a restore onto my local machine, utilising pg_dump, which restores the DB to a size of 82GB. The premise here was to see if carrying out this process would be efficient, before the migration / conversion, which it very much is.

The problem I'm having is I can't carry out the full VACUUM against this DB, due to the HD space considerations with regards to duplicating its size for this process.

I have come across a very helpful post below, where I can re-initialize the data dictionary after a pg_dumpall and then restore, removing the need for extra HD space required for the VACUUM. The problem here however is that the other DBs will also be included in this process, which is too much downtime. Is there something similar I can carry out against just the effected DB to purge its cache?

I need to run VACUUM FULL with no available disk space

Thanks for your time in advanced.

  • 1
    With pg_dump you only dump a single DB (unlike with pg_dumpall in the linked post). With dumping, DROP DATABASE and restoring this DB, the other ones should not be affected at all. – dezso Feb 16 '15 at 10:44
  • Hi @dezso, do I not need to also clear the data dictionary, as specified in the link? My issue here is that to clear the data dictionary for a specific tablespace I would be trying to unravel the various directories the table space is stored (it seems to be stored in various directories using symbolic links) and not just the logical one, according to the tablespace name, which worries me. – Benjin Feb 16 '15 at 11:31
  • 2
    What @dezso suggests is ideal if you can take that database offline for the time of the reload. But if you do that, you must ignore everything that is in the answer you linked and especially don't rm manually anything. That's a completely different method. – Daniel Vérité Feb 16 '15 at 11:50
  • @Daniel, thanks for your input. So if I carry out a dump, drop and restore, this will remove the legacy items/cache in the tablespace, effectively cleaning the data dictionary? I am looking to clean up the HD disk as well as the database size. – Benjin Feb 16 '15 at 12:35
  • 2
    Yes. Compare the free space on partition just before the DROP DATABASE and just after. You should see the space given back immediately to the file system. – Daniel Vérité Feb 16 '15 at 13:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.