Re: Extraneous Files

On Thu, Oct 20, 2011 at 1:12 PM, Ian Harding <harding(dot)ian(at)gmail(dot)com> wrote:

> If someone happened to accidentally end up with a lot of files that
> were NOT part of their database in the data/base/XXXXX directory, how
> could they go about getting a reliable list of files they could safely
> delete? The files were there before the current incarnation of the
> database, so have ctime before the good ones, but may be valid because
> they were updated by rsync, but I don't know that mtime is reliable
> for every file either, since some may have been unchanged from the
> first time this someone ran rsync.
>
> Not a super big deal, just wasting a lot of space...
>

It is a big deal, one should NEVER EVER manually delete files from under the
data directory. If you want to remove the data, you can figure out what
relations the files tie back to by examining 'relfilenode' on pg_class...

select relname from pg_class where relfilenode='XXXXX'
-- XXXX is the name of the file before a period

If you want to delete the files, you can then 'drop table relname' . If
you value the logical consistency of your database, I wouldn't delete
anything unless you go through the database engine with a 'drop' command.

--Scott

>
> Thanks!
>
> Ian
>
> postgresql 9.0.4 on linux
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>