On tuesday, February 26, 2013, Freddie Burgess wrote:
We have a Postgres database that was recently upgraded from 8.4.3 to 9.1.6. We have noticed unusual growth in the data files and generated a script to perform the following actions.
1. Query pg_class for all records
2. Generate a file listing of all postgres data files
3. Compare the two lists and eliminate all files that are contained within pg_class
There are 17359 data files. After running the script, there are 5802 data files remaining that are not listed in pg_class. Due to the size of the (5802) data files (~4TB), I am not comfortable about deleting them from the file system. Does postgres 9.1.6 catalog every data file in pg_class? Or does it leave some data files off of this table? If so, how can I determine if I have stale, unnecessary data files on my file system?
1. Query pg_class for all records
2. Generate a file listing of all postgres data files
3. Compare the two lists and eliminate all files that are contained within pg_class
There are 17359 data files. After running the script, there are 5802 data files remaining that are not listed in pg_class. Due to the size of the (5802) data files (~4TB), I am not comfortable about deleting them from the file system. Does postgres 9.1.6 catalog every data file in pg_class? Or does it leave some data files off of this table? If so, how can I determine if I have stale, unnecessary data files on my file system?