From: | Rémi Cura <remi(dot)cura(at)gmail(dot)com> |
---|---|
To: | Julien Rouhaud <julien(dot)rouhaud(at)dalibo(dot)com> |
Cc: | Johnny Morano <johnny(dot)morano(at)payon(dot)com>, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: bloated postgres data folder, clean up |
Date: | 2016-03-04 10:02:33 |
Message-ID: | CAJvUf_uFjOm6L7SEZupz+g2FgkYJ7QNSbggeXwOyM-7nwVNZ=A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
<http://www.postgresql.org/docs/current/static/storage-file-layout.html>)
, 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(dot)rouhaud(at)dalibo(dot)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).
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Koray Eyidoğan | 2016-03-04 10:16:47 | Re: Export binary data - PostgreSQL 9.2 |
Previous Message | Rémi Cura | 2016-03-04 09:46:52 | Re: PLPythonu for production server |