8

I faced cumputer crash while doing clustering. The temp files has not got cleared after computer resume. How to clear them safely now?

Update

Windows OS. pgsql_tmp directory is empty but disk space still occupied by clustering temp files located in the table base folder.

1
  • Are you talking about files in pgsql_tmp, or something else?
    – jjanes
    Commented Jun 22, 2020 at 15:09

1 Answer 1

13

temporary files in the pgsql_tmp directory

You'd have to remove them by manually deleting the files in the pgsql_tmp subdirectory.

But be careful – manually messing with the data directory is always dangerous. I recommend only removing files whose modification timestamp is safely in the past, on Unix systems perhaps

find pgsql_tmp -mtime +1 -mindepth 1 -maxdepth 1 -exec rm {} \;

Other files

If you are not talking about temporary files at all, but about files that got left behind after a crash during an operation that rewrites a table, like VACUUM (FULL), CLUSTER or ALTER TABLE, the case is not so simple.

The proper, safe way to do this

Back up the database with pg_dump, drop it and restore it.

Then all those files should be gone.

The messy, dangerous way if you cannot afford the down time

Again, manually messing with the data directory should be left to an expert, and if you delete something that is vital for your database, you have lost your database. Don't do that unless you have a good physical backup of the database.

Let's assume you are talking about the default tablespace, the base subdirectory of the PostgreSQL data directory.

Change into the directory for your database, whose name you can find with

SELECT oid FROM pg_database
WHERE datname = 'my_database';

The following statement will create a list of all “filenodes” of objects in the default tablespace:

SELECT pg_relation_filenode(oid)
FROM pg_class
WHERE reltablespace = 0;

If the number 12345 occurs in the result, the following files are part of that table, index or other object: 12345, 12345.1, 12345.2, ..., 12345_vm, 12345_fsm (basically everything that starts with 12345).

The files that got orphaned by the crash are those that are left behind if you discount all the above files for all the objects in pg_class. They could in principle be deleted.

0

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.