Обсуждение: Extraneous Files
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... Thanks! Ian postgresql 9.0.4 on linux
On Thu, Oct 20, 2011 at 1:12 PM, Ian Harding <harding.ian@gmail.com> wrote:
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
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Thu, Oct 20, 2011 at 02:32:18PM -0400, Scott Mead wrote: > On Thu, Oct 20, 2011 at 1:12 PM, Ian Harding <harding.ian@gmail.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, Well, Ian isn't talking about removing data. What he was asking (I believe) is how to remove from the data directory files which got nothing to do with the database in question (but probably look like database files because, say, someone copied *another* database into the directory ;-) Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On 10/20/11 12:14 PM, Karsten Hilbert wrote: > Well, Ian isn't talking about removing data. What he was > asking (I believe) is how to remove from the data directory > files which got nothing to do with the database in question > (but probably look like database files because, say, someone > copied*another* database into the directory ;-) pg_dumpall stop postgres save the .conf files wipe $PGDATA initdb restore the .conf files restart postgres restore the dumpall -- john r pierce N 37, W 122 santa cruz ca mid-left coast
Well, they are actually streaming replication slaves, and I boogered up the rsync command, so there they are. I diffed the directories from the master to the slave, and think I will go ahead and delete all the files that don't appear in both places and see what happens. Worst case, I have to set them back up again. No big deal... Thanks! - Ian On Thu, Oct 20, 2011 at 12:24 PM, John R Pierce <pierce@hogranch.com> wrote: > On 10/20/11 12:14 PM, Karsten Hilbert wrote: >> >> Well, Ian isn't talking about removing data. What he was >> asking (I believe) is how to remove from the data directory >> files which got nothing to do with the database in question >> (but probably look like database files because, say, someone >> copied*another* database into the directory ;-) > > pg_dumpall > stop postgres > save the .conf files > wipe $PGDATA > initdb > restore the .conf files > restart postgres > restore the dumpall > > -- > john r pierce N 37, W 122 > santa cruz ca mid-left coast > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >