Re: [GENERAL] Unable to determine what has a particular OID

2010-06-21 Thread Greg Smith
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 possi

Re: [GENERAL] Unable to determine what has a particular OID

2010-06-18 Thread Tom Lane
Greg Smith 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

Re: [GENERAL] Unable to determine what has a particular OID

2010-06-18 Thread Greg Smith
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

Re: [GENERAL] Unable to determine what has a particular OID

2010-06-18 Thread Andy Dale
On 18 June 2010 17:27, Tom Lane wrote: > Andy Dale 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 co

Re: [GENERAL] Unable to determine what has a particular OID

2010-06-18 Thread Tom Lane
Andy Dale 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 reaso

Re: [GENERAL] Unable to determine what has a particular OID

2010-06-18 Thread Andy Dale
On 18 June 2010 17:00, Tom Lane wrote: > Andy Dale 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

Re: [GENERAL] Unable to determine what has a particular OID

2010-06-18 Thread Andy Dale
> To the best of my knowledge we have not had any DB crashes as yet. I am > also replicating the DB using slony, could this be the cause of these weird > orphans ? (it is neither of the sl_log_ tables) > Also looking at the timestamps of the files, they all seem to have been created within a very

Re: [GENERAL] Unable to determine what has a particular OID

2010-06-18 Thread Tom Lane
Andy Dale 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 th

Re: [GENERAL] Unable to determine what has a particular OID

2010-06-18 Thread Andy Dale
On 18 June 2010 16:37, Tom Lane wrote: > Andy Dale writes: > > On 18 June 2010 16:06, Tom Lane 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 lik

Re: [GENERAL] Unable to determine what has a particular OID

2010-06-18 Thread Andy Dale
> > > > Try: > SELECT oid,* from pg_database ; > > I suspect the relfilnode you are looking at is another database in the > cluster. > > -- > Adrian Klaver > adrian.kla...@gmail.com > Maybe I am possibly doing that. I did a du -h --max-depth=1 in the base/ within the data directory. This showed

Re: [GENERAL] Unable to determine what has a particular OID

2010-06-18 Thread Tom Lane
Andy Dale writes: > On 18 June 2010 16:06, Tom Lane 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

Re: [GENERAL] Unable to determine what has a particular OID

2010-06-18 Thread Adrian Klaver
On Friday 18 June 2010 7:15:48 am Andy Dale wrote: > On 18 June 2010 16:06, Tom Lane wrote: > > Andy Dale 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 > > >

Re: [GENERAL] Unable to determine what has a particular OID

2010-06-18 Thread Andy Dale
On 18 June 2010 16:06, Tom Lane wrote: > Andy Dale 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 M

Re: [GENERAL] Unable to determine what has a particular OID

2010-06-18 Thread Tom Lane
Andy Dale 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 dir

[GENERAL] Unable to determine what has a particular OID

2010-06-18 Thread Andy Dale
Hi, We currently have a DB (8.3.7) in production that seem to be taking up more space on the HDD than was anticipated. 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