Hey Julien,
thanks for the original query !
thanks for the original query !
There was a slight mistake in the query,
it was comparing the file name with
pg_class.relfilenode.
It is not safe in some case (see doc : "caution" in here)
, so better use the pg_relation_filenode() function.
AS a result this database could not be started anymore (no worry I had copy).
However using pg_relation_filenode() seems to be safe (passes vacuum full analyse).
I'll modify the query as soon as I have access to gist.
I agree the warning about tablespace is also important.
I'll put all of this on the wiki as soon has I have permission to create a new page
(man, how long is this "cool-off", it's been already several days !).
Cheers,
Rémi-C
2016-03-03 20:10 GMT+01:00 Julien Rouhaud <julien.rouhaud@xxxxxxxxxx>:
On 03/03/2016 18:15, Rémi Cura wrote:
> Hey,
Hello Rémi,
> first I forgot something in the querry to remove the annoying .XXX :
> -----------
> SELECT distinct substring(file_name from '\d+' )
> FROM find_useless_postgres_file('your_database_name') ;
> -----------
>
Thanks for working on this :)
I added a comment on the gist URL you provided. It's a simplified
version of the main query that should work fine and detect more orphan
files. Double checking it would be a good idea though.
Also, as you can't check other databases than the one you're connected
to, I used current_database() instead of user defined database name.
It's also important to warn that all of this only work for finding
orphan files on the defaulttablespace (and to never blindly remove
files in the PGDATA of course).
