Re: bloated postgres data folder, clean up

Hey,
this is quite the *opposite*.
The function find files in the postgres database folder that are not used
by the database.

To use it :
* connect to the database you want to analyse ( **mandatory** ).
* create the function (execute function definition)
* Execute `SELECT * FROM find_useless_postgres_file('your_database_name')`

This will output a list of files that are on the disk but not used by
postgres,
and so can be removed.

To be extra sure, you should use oid2name programme to check that the
useless files are really useless.

For this :
* output the list of potential useless files with copy for instance
ex :
COPY ( SELECT file_name
FROM find_useless_postgres_file('your_database_name')
) TO 'path_to_you_database_folder/potential_useless.txt'

now you've got a file with a list of potential erroneous files.

* Then use oid2name
`$su postgres
$cd path_to_you_database_folder
$while read i; do oid2name -f "$i" -i -S -q -d your_database_name; done
< potential_useless.txt
`

Nothing should show, meaning that every potential erroneous file
has not been recognized by oid2name !
If you feel unconvinced, you can manually try oid2name on some
of the potential erroneous files, to be extra sure.
It should not find anything.

* Now delete all the files in `potential_useless.txt`.
It could be wiser to not delete the files but rename those
(for instance, adding `.potentially_useless` as a postfix)
so if it breaks something, you have an easy way to revert everything.

Anyway, use *-*extra extra*-* caution if you delete.
Except a backup, there would be no easy way to correct a mistake.

Cheers,
Rémi-C

2016-03-02 15:38 GMT+01:00 Johnny Morano <johnny(dot)morano(at)payon(dot)com>:

> Hi Remi!
>
>
>
> This SQL function you have provided, seems to return all valid files, is
> that correct? In my case, it returned all my ‘base/’ files. Is that normal?
>
> If yes, maybe you rename the function to ‘find_useful_postgres_files’ ;-)
>
>
>
> Could you explain in steps how to use this function to make a cleanup of
> bloated data? (like in an example with commands and example output, if
> possible of course)
>
>
>
> Thanks!
>
>
>
>
>
> Mit besten Grüßen / With best regards,
>
> Johnny Morano
>
> ____________________________________________________
>
>
>
> *Johnny Morano | Principal Systems Engineer*
>
>
>
> PAY.ON GmbH | AN ACI WORLDWIDE COMPANY | WWW.PAYON.COM
> <http://www.payon.com/>
>
> Jakob-Haringer-Str. 1 | 5020 Salzburg | Austria
>
> Registered at: LG Salzburg | Company number: FN 315081 f | VAT-ID:
> ATU64439405
>
> Managing Director: Christian Bamberger
>
>
>
>
>
> Follow us on:
>
>
>
> [image: cid:image001(dot)jpg(at)01D126D0(dot)E1AB0670] <http://blog.payon.com/> [image:
> cid:image002(dot)jpg(at)01D126D0(dot)E1AB0670]
> <http://www.linkedin.com/company/146260?trk=tyah> [image:
> cid:image003(dot)jpg(at)01D126D0(dot)E1AB0670] <https://twitter.com/PAYON_com>
>
>
>
> This email message and any attachments may contain confidential,
> proprietary or non-public information. This information is intended solely
> for the designated recipient(s). If an addressing or transmission error has
> misdirected this email, please notify the sender immediately and destroy
> this email. Any review, dissemination, use or reliance upon this
> information by unintended recipients is prohibited. Any opinions expressed
> in this email are those of the author personally.
>
>
>
> This message and any attachments have been scanned for viruses prior
> leaving PAY.ON; however, PAY.ON does not guarantee the security of this
> message and will not be responsible for any damages arising as a result of
> any virus being passed on or arising from any alteration of this message by
> a third party. PAY.ON may monitor e-mails sent to and from PAY.ON.
>
>
>
>
>
>
>
>
>
> *From:* pgsql-general-owner(at)postgresql(dot)org [mailto:
> pgsql-general-owner(at)postgresql(dot)org] *On Behalf Of *Rémi Cura
> *Sent:* Mittwoch, 2. März 2016 14:58
> *To:* Alvaro Herrera
> *Cc:* PostgreSQL General
> *Subject:* Re: [GENERAL] bloated postgres data folder, clean up
>
>
>
> Would gladly do it,
>
> but still this "wiki cooloff" stuff,
>
> can't create a page
>
> Cheers,
>
> Rémi-C
>
>
>
> 2016-02-29 20:44 GMT+01:00 Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>:
>
> Rémi Cura wrote:
> > Hey dear list,
> > after a fex years of experiments and crash,
> > I ended up with a grossly bloated postgres folder.
> > I had about 8 Go of useless files.
>
> Would you add a new page to the wiki with this?
>
> https://wiki.postgresql.org/wiki/Category:Administrative_Snippets
>
> --
> Álvaro Herrera http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
>
>