Postgres Pro
Downloads
Home   >   mailing lists

Re: bloated postgres data folder, clean up - Mailing list pgsql-general

From Rémi Cura
Subject Re: bloated postgres data folder, clean up
Date
Msg-id CAJvUf_uFjOm6L7SEZupz+g2FgkYJ7QNSbggeXwOyM-7nwVNZ=A@mail.gmail.com
Whole thread Raw
In response to Re: bloated postgres data folder, clean up  (Julien Rouhaud <julien.rouhaud@dalibo.com>)
List pgsql-general
Tree view
​Hey Julien,
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@dalibo.com>:
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 default
​​
tablespace (and to never blindly remove
files in the PGDATA of course).
​​

pgsql-general by date:

Previous
From: Rémi Cura
Date:
Subject: Re: PLPythonu for production server
Next
From: Koray Eyidoğan
Date:
Subject: Re: Export binary data - PostgreSQL 9.2