Обсуждение: Unable to determine what has a particular OID

Andy Dale <andy.dale@gmail.com> writes:
> After looking into the the data directory a particular file/oid is around 21
> GB (the oid has 21 files), this OID can then be found in the pg_class table,
> the explanation for the size being that the table has around 12.5 Million
> rows.  During the inspection of the data directory I observed another OID
> that is around 8.5GB, but I cannot locate the corresponding entry in the
> pg_class table (even after dumping the table into a file and grep-ing for
> the OID in question).

You should be looking at pg_class.relfilenode, not OID.  See
http://www.postgresql.org/docs/8.3/static/storage.html

            regards, tom lane

On Friday 18 June 2010 7:15:48 am Andy Dale wrote:
> On 18 June 2010 16:06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Andy Dale <andy.dale@gmail.com> writes:
> > > After looking into the the data directory a particular file/oid is
> > > around
> >
> > 21
> >
> > > GB (the oid has 21 files), this OID can then be found in the pg_class
> >
> > table,
> >
> > > the explanation for the size being that the table has around 12.5
> > > Million rows.  During the inspection of the data directory I observed
> > > another OID that is around 8.5GB, but I cannot locate the corresponding
> > > entry in the pg_class table (even after dumping the table into a file
> > > and grep-ing for the OID in question).
> >
> > You should be looking at pg_class.relfilenode, not OID.  See
> > http://www.postgresql.org/docs/8.3/static/storage.html
> >
> >                        regards, tom lane
>
> Ok, but when I have dumped the pg_class table into a file like so:
>
> -[ RECORD 1
> ]--+----------------------------------------------------------------
> relname        | pg_type
> relnamespace   | 11
> reltype        | 71
> relowner       | 10
> relam          | 0
> relfilenode    | 1247
> reltablespace  | 0
> relpages       | 19
> reltuples      | 796
> reltoastrelid  | 0
> reltoastidxid  | 0
> relhasindex    | t
> relisshared    | f
> relkind        | r
> relnatts       | 26
> relchecks      | 0
> reltriggers    | 0
> relukeys       | 0
> relfkeys       | 0
> relrefs        | 0
> relhasoids     | t
> relhaspkey     | f
> relhasrules    | f
> relhassubclass | f
> relfrozenxid   | 379
> relacl         | {=r/postgres}
> reloptions     |
> -[ RECORD 2
> ]--+----------------------------------------------------------------
> relname        | triggered_update_columns
> relnamespace   | 11313
> reltype        | 11477
> relowner       | 10
> relam          | 0
> relfilenode    | 11476
> reltablespace  | 0
> relpages       | 0
> reltuples      | 0
> reltoastrelid  | 0
>
> Grep-ing  for the correct oid/filenode (110660 in my case), nothing is
> returned.
>
> Cheers,
>
> Andy

Try:
SELECT oid,* from pg_database ;

I suspect the relfilnode you are looking at is another database in the cluster.

--
Adrian Klaver
adrian.klaver@gmail.com

Andy Dale <andy.dale@gmail.com> writes:
> On 18 June 2010 16:06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> You should be looking at pg_class.relfilenode, not OID.  See
>> http://www.postgresql.org/docs/8.3/static/storage.html

> Ok, but when I have dumped the pg_class table into a file like so:
> ...
> Grep-ing  for the correct oid/filenode (110660 in my case), nothing is
> returned.

Hm.  You're sure you're looking at pg_class in the right database?

If there really isn't a pg_class entry matching that relfilenode,
then the files are orphans and can be removed.  There are some scenarios
where orphan files can be left behind, but they generally involve
database crashes ... have you had any of those?

            regards, tom lane

Andy Dale <andy.dale@gmail.com> writes:
>> Try:
>> SELECT oid,* from pg_database ;
>>
>> I suspect the relfilnode you are looking at is another database in the
>> cluster.

> Maybe I am possibly doing that.

There isn't any "maybe" involved here.  Follow Adrian's advice and
determine for sure exactly which database that subdirectory represents.
The subdirectory name will match the OID in the pg_database row.

It's possible for the same table OID to be in use in more than one
database, so the fact that you got a match to the other table doesn't
in itself prove you're looking at the right database.

            regards, tom lane

Andy Dale <andy.dale@gmail.com> writes:
> OK, I have just run the query, and both are in the same database.

OK.  Given the narrow range of timestamps on the files, the most
likely bet here is that you're looking at the leftovers from a
"CREATE TABLE AS" or similar command that failed partway through and
for some reason didn't clean up the files it'd created.  As I mentioned,
this'd be unsurprising if there'd been a database crash or kill -9
or something similar involved.  Anyway I think you've done enough to
establish that "rm'ing" those files will be safe.  (But if you're
paranoid you might want to save them someplace first.)

            regards, tom lane

Andy Dale wrote:
> I will however save/backup the files before removing them (just in
> case anything goes wrong).

If you do go through with this, I would recommend doing a complete
database dump afterwards using pg_dump/pg_dumpall.  That should make it
immediately obvious if you broke something with the change.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us

Greg Smith <greg@2ndquadrant.com> writes:
> Andy Dale wrote:
>> I will however save/backup the files before removing them (just in
>> case anything goes wrong).

> If you do go through with this, I would recommend doing a complete
> database dump afterwards using pg_dump/pg_dumpall.  That should make it
> immediately obvious if you broke something with the change.

Doing pg_dump would expose the mistake if you'd removed an actual
table's files.  But I'm not sure it would expose it if you removed
an index ...

            regards, tom lane

Tom Lane wrote:
> Doing pg_dump would expose the mistake if you'd removed an actual
> table's files.  But I'm not sure it would expose it if you removed
> an index ...
>

Right, but in theory if you screwed up and accidentally deleted a file
holding an index, you could recover from that in the possibly distant
future by rebuilding it, with some pain but no expected loss.  Whereas
if you deleted some data by removing a file, you really want to know
that's what you did immediately, so you can put it back before you
forget where it all was at.

Sometimes people who have fully setup replication for backup purposes
ask me if they should continue saving pg_dump output somewhere.  I think
it's reasonable to generate a dump using it periodically whether or not
you intend to save the result permanently, just as a paranoid sanity
check that you can still read everything.  I don't trust disks and
filesystems that much.

(If you're reading this and feel the need to write a pro-ZFS essay at
this point, consider yourself trolled)

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us