3

I have encountered a problem that has known solutions if you are hosting your own server, but I do not know how to resolve on an Azure managed PostgreSQL DB instance.

I was performing a VACUUM FULL on an Azure managed PostgreSQL DB instance. The vast majority of data in the DB is in one large table.

The DB instance has 250GB allocated to it. During the vacuum I tracked the server metrics on Azure and could see that the space usage grew from about 30% of available space to about 60% of available space. However at that point the server seemed to hang. The metrics stopped reporting data (as if the server was completely idle) and I was not able to contact it.

I restarted the DB instance on Azure and performed the VACUUM FULL again. This time during the VACUUM the space usage grew from 60% to almost 90%, but the VACUUM finished uneventfully and overall usage dropped to just below 60%.

The performances of the server is now fine, but even though no data has been added to it since I started the first vacuum, the amount of space consumed has approximately doubled. I have lost approximately 80GB of space due to the initial failed vacuum.

From searching the web I see that this situation is probably due to temporary files created by the vacuum that were not properly deleted because of the interrupted vacuum. Easy enough if I was running my own server, but I do not know how to get the space back on a managed server.

Is there a known procedure to fix this (besides exporting the data and recreating the instance?)

Marcel
  • 41
  • 5

1 Answers1

2

It seems like you crashed the database server in the middle of a VACUUM (FULL). That produced some "orphaned" files in the data directory that you cannot get rid of.

There is no way to reclaim that space if you have no access to the file system, and even with access it is difficult and dangerous (manually modifying the data directory is risky and unsupported).

The only remedy I can think of is to create a new database and copy your database with pg_dump/pg_restore.

Next time, cancel the VACUUM rather than crashing the server. That would have cleaned upnthe garbage.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263