I wrote a migration for a web application that ran
CREATE INDEX as part of its deploy. When it took too long to create, our deployment system ruled it as a failed deploy, killed the deployment, and tried again. It did this until our deployment system gave up on deploying it altogether.
This led to about 20 CREATE INDEX processes on a large table, which was locking rows and pegging the CPU/IO, so we killed the pids.
Now, outside of this deploy process, I'm running a (more responsible, with time to run)
CREATE INDEX CONCURRENTLY, but getting:
ERROR: could not write to file "base/pgsql_tmp/pgsql_tmp18746.4": No space left on device
It could very well be that we're just out of space, but I suspect brutally killing ~20 large index creation processes left some large files that Postgres isn't able to use.
- Is there a way to inspect/remove such files, if they exist?
- Any other poor states I should look for?