Re: oids on disk not in pg_class

On Mon, Oct 07, 2013 at 06:32:57PM -0400, Guy Rouillier wrote:
- On 10/7/2013 5:58 PM, Steve Atkins wrote:
- >
- >On Oct 7, 2013, at 2:48 PM, Guy Rouillier <guy.rouillier@gmail.com>
- >wrote:
- >
- >>We have a fairly large (1 TB) database we put on all SSDs because
- >>of a very high insert and update rate (38 million rows/day).  As
- >>our business has grown, we've been running into space constraints,
- >>so we went looking for files we might be able to delete.
- >>
- >>We found a large number (662 out of 1465 total ) and size (219 GB)
- >>of files in the data directory whose name does not correspond to
- >>an oid in the pg_class system catalog table.  That amount of space
- >>would address our current space constraint problems.  Some of
- >>these tables are recent (from today), while others are quite old
- >>(large volume in August and May, with some smaller ones as far back
- >>as February.
- >
- >You need to be looking at pg_class.relfilenode, not pg_class.oid.
- >They're often the same value, but often not.
-
- Steve, thanks for the quick reply.  I reran the calculations using
- relfilenode instead of oid; they are now showing 214 unrecorded
- filenodes consuming 163 GB.  The older tables (on or before May) are no
- longer showing as unrecorded.  Of the filenodes that are still not
- matching pg_class.relfilenode, I see just one that is consuming the
- majority of the space: 614804 (153 files, 163 GB).
-
- Failed to mention our runtime platform: we are running Enterprise DB
- 9.2.1.3 on 64-bit Linux (Oracle Linux Server release 6.3).  EDB pointed
- us to this note in the PG documentation
- (http://www.postgresql.org/docs/9.2/static/storage-file-layout.html):
-
- Note that while a table's filenode often matches its OID, this is not
- necessarily the case; some operations, like TRUNCATE, REINDEX, CLUSTER
- and some forms of ALTER TABLE, can change the filenode while preserving
- the OID. Avoid assuming that filenode and table OID are the same. Also,
- for certain system catalogs including pg_class itself,
- pg_class.relfilenode contains zero.  The actual filenode number of these
- catalogs is stored in a lower-level data structure, and can be obtained
- using the pg_relation_filenode() function.
-
- So, I ran "select pg_relation_filenode(614804)" and got no results.  Any
- suggestions on how I can uncover the identify of this node?
-
- Thanks much.

You could try oid2name: http://www.postgresql.org/docs/current/static/oid2name.html