Thread: Found Large Files.. what objects are they?
I found bunch of large files (more than 1 gb) in one of database directory.
The files looks like this:
69233123
69233123.1
69233123.2
69233123.3
69233123.4
...and so on.
These large files very delay the dumping process.
Anyone know what it could be & how to delete the object related? How to find a table by oid?
Thanks
Yudie
Centuries ago, Nostradamus foresaw when yudie@axiontech.com ("Yudie") would write: > I found bunch of large files (more than 1 gb) in one of database directory. > > The files looks like this: > > > 69233123 > > 69233123.1 > > > 69233123.2 > > 69233123.3 > > 69233123.4 > > ...and so on. > > These large files very delay the dumping process. > > Anyone know what it could be & how to delete the object related? How to find a table by oid? The phenomenon you are seeing occurs when there is a table with a great deal of data. Look for the table via the query: select * from pg_class where oid = 69233123; Presumably these files are for a table that contains multiple GB of data. Or they could be for an index on a very large table. If the data in the table/index is useless to you, you might consider dropping the table/index. -- let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;; http://www3.sympatico.ca/cbbrowne/wp.html DO IT -- it's easier to get forgiveness than permission.
On Wed, Dec 01, 2004 at 12:07:20PM -0600, Yudie wrote: > I found bunch of large files (more than 1 gb) in one of database directory. > The files looks like this: > 69233123 > 69233123.1 > 69233123.2 > 69233123.3 > 69233123.4 > ...and so on. > > These large files very delay the dumping process. > Anyone know what it could be & how to delete the object related? When a file exceeds 1GB, PostgreSQL divides it into segments named N, N.1, N.2, etc. > How to find a table by oid? contrib/oid2name should be helpful. You could also query the system catalogs: the files' parent directory should be the database's OID, so for a file named .../12345/69233123 you could identify the database with the following query: SELECT datname FROM pg_database WHERE oid = 12345; Connect to that database and find out which table or index uses the files: SELECT relname FROM pg_class WHERE relfilenode = 69233123; Once you've done that, figure out why the files are so large: Could it simply be that the table stores a lot of data? Have you been running VACUUM? If not, and if you've made a lot of updates or deletes, then you'll have a lot of dead tuples; contrib/pgstattuple can be useful for checking on that. If that's the case, then VACUUM FULL and/or REINDEX should recover the dead space, but be aware that they'll acquire exclusive locks on the objects they're working with and they might take a long time (hours) to run. Also, after a REINDEX a table's indexes will probably be stored in different files; you can find out the new file names by querying pg_class and looking at the relfilenode field. Another possibility would be to dump, drop, recreate, and restore the table and see if the file sizes shrink. Again, the file names will probably change, so query pg_class to see what the new ones are. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
"Yudie" <yudie@axiontech.com> writes: > Anyone know what it could be & how to delete the object related? How to = > find a table by oid? Look at pg_class.relfilenode, not OID. Or try contrib/oid2name. regards, tom lane