Thread: could not open relation...No such file or directory
We are using postgresql 8.2.17 on Gentoo Our postgresql database stopped working with the error ERROR: could not open relation 1663/26468499/26470404: No such file or directory After one minute of these errors on every statement run we had what looks like a memory failure: Dec 22 16:37:44 localhost kernel: [279820.425911] postgres invoked oom-killer: gfp_mask=0x280da, order=0, oom_adj=0 Dec 22 16:37:44 localhost kernel: [279820.425917] postgres cpuset=/ mems_allowed=0 Dec 22 16:37:44 localhost kernel: [279820.425922] Pid: 26209, comm: postgres Not tainted 2.6.32-gentoo-r7 #12 We cannot connect to the database using psql as it gives the could not open relation error. We ran fsck and did not find any problems. I searched through google and found suggestions such as reindex, but we can't connect to the db. We also can't take a backup of the database as pg_dump gives the same error. This is our production database and crashed at the end of the day. If possible, I would really not like to lose a full days work by restoring yesterdays backup. Thanks Sim
On Wed, Dec 22, 2010 at 10:59 AM, Sim Zacks <sim@compulab.co.il> wrote: > This is our production database and crashed at the end of the day. If > possible, I would really not like to lose a full days work by restoring > yesterdays backup. > So the errors in postgres started after your server crashed? Sounds like the server crash caused some file system corruption.
On 12/22/2010 07:59 AM, Sim Zacks wrote: > We are using postgresql 8.2.17 on Gentoo > > Our postgresql database stopped working with the error > ERROR: could not open relation 1663/26468499/26470404: No such file or > directory > > After one minute of these errors on every statement run we had what > looks like a memory failure: > Dec 22 16:37:44 localhost kernel: [279820.425911] postgres invoked > oom-killer: gfp_mask=0x280da, order=0, oom_adj=0 > Dec 22 16:37:44 localhost kernel: [279820.425917] postgres cpuset=/ > mems_allowed=0 > Dec 22 16:37:44 localhost kernel: [279820.425922] Pid: 26209, comm: > postgres Not tainted 2.6.32-gentoo-r7 #12 > > We cannot connect to the database using psql as it gives the could not > open relation error. We ran fsck and did not find any problems. I > searched through google and found suggestions such as reindex, but we > can't connect to the db. We also can't take a backup of the database as > pg_dump gives the same error. Does the not connect also apply to starting postgres in single user mode? > > This is our production database and crashed at the end of the day. If > possible, I would really not like to lose a full days work by restoring > yesterdays backup. > > Thanks > Sim > -- Adrian Klaver adrian.klaver@gmail.com
On 12/22/2010 06:08 PM, Vick Khera wrote: > On Wed, Dec 22, 2010 at 10:59 AM, Sim Zacks<sim@compulab.co.il> wrote: >> This is our production database and crashed at the end of the day. If >> possible, I would really not like to lose a full days work by restoring >> yesterdays backup. > So the errors in postgres started after your server crashed? Sounds > like the server crash caused some file system corruption. No. The errors started and then a minute later the server crashed.
On 12/22/2010 06:28 PM, Adrian Klaver wrote: > On 12/22/2010 07:59 AM, Sim Zacks wrote: >> We are using postgresql 8.2.17 on Gentoo >> >> Our postgresql database stopped working with the error >> ERROR: could not open relation 1663/26468499/26470404: No such file or >> directory >> >> After one minute of these errors on every statement run we had what >> looks like a memory failure: >> Dec 22 16:37:44 localhost kernel: [279820.425911] postgres invoked >> oom-killer: gfp_mask=0x280da, order=0, oom_adj=0 >> Dec 22 16:37:44 localhost kernel: [279820.425917] postgres cpuset=/ >> mems_allowed=0 >> Dec 22 16:37:44 localhost kernel: [279820.425922] Pid: 26209, comm: >> postgres Not tainted 2.6.32-gentoo-r7 #12 >> >> We cannot connect to the database using psql as it gives the could not >> open relation error. We ran fsck and did not find any problems. I >> searched through google and found suggestions such as reindex, but we >> can't connect to the db. We also can't take a backup of the database as >> pg_dump gives the same error. > > Does the not connect also apply to starting postgres in single user mode? Same error with single user mode. postgres --single -D /var/lib/postgresql/8.2/data clstock FATAL: could not open relation 1663/26468499/1259: No such file or directory > >> >> This is our production database and crashed at the end of the day. If >> possible, I would really not like to lose a full days work by restoring >> yesterdays backup. >> >> Thanks >> Sim >> > >
On 12/22/2010 08:37 AM, Sim Zacks wrote: > On 12/22/2010 06:28 PM, Adrian Klaver wrote: > >> On 12/22/2010 07:59 AM, Sim Zacks wrote: >>> We are using postgresql 8.2.17 on Gentoo >>> >>> Our postgresql database stopped working with the error >>> ERROR: could not open relation 1663/26468499/26470404: No such file or >>> directory >>> >>> After one minute of these errors on every statement run we had what >>> looks like a memory failure: >>> Dec 22 16:37:44 localhost kernel: [279820.425911] postgres invoked >>> oom-killer: gfp_mask=0x280da, order=0, oom_adj=0 >>> Dec 22 16:37:44 localhost kernel: [279820.425917] postgres cpuset=/ >>> mems_allowed=0 >>> Dec 22 16:37:44 localhost kernel: [279820.425922] Pid: 26209, comm: >>> postgres Not tainted 2.6.32-gentoo-r7 #12 >>> >>> We cannot connect to the database using psql as it gives the could not >>> open relation error. We ran fsck and did not find any problems. I >>> searched through google and found suggestions such as reindex, but we >>> can't connect to the db. We also can't take a backup of the database as >>> pg_dump gives the same error. >> >> Does the not connect also apply to starting postgres in single user mode? > Same error with single user mode. > postgres --single -D /var/lib/postgresql/8.2/data clstock > FATAL: could not open relation 1663/26468499/1259: No such file or > directory Well it is failing on a different relation. Is the FATAL message correct, are the relations shown in fact not on the file system? > >> >>> >>> This is our production database and crashed at the end of the day. If >>> possible, I would really not like to lose a full days work by restoring >>> yesterdays backup. >>> >>> Thanks >>> Sim >>> >> >> > > -- Adrian Klaver adrian.klaver@gmail.com
Sim Zacks <sim@compulab.co.il> writes:
> Our postgresql database stopped working with the error
> ERROR: could not open relation 1663/26468499/26470404: No such file or
> directory
>> Does the not connect also apply to starting postgres in single user mode?
> Same error with single user mode.
> postgres --single -D /var/lib/postgresql/8.2/data clstock
> FATAL: could not open relation 1663/26468499/1259: No such file or
> directory
I begin to wonder if the subdirectory $PGDATA/base/26468499 is there at
all?
regards, tom lane
On 12/22/2010 06:45 PM, Adrian Klaver wrote: > On 12/22/2010 08:37 AM, Sim Zacks wrote: >> On 12/22/2010 06:28 PM, Adrian Klaver wrote: >> >>> On 12/22/2010 07:59 AM, Sim Zacks wrote: >>>> We are using postgresql 8.2.17 on Gentoo >>>> >>>> Our postgresql database stopped working with the error >>>> ERROR: could not open relation 1663/26468499/26470404: No such file or >>>> directory >>>> >>>> After one minute of these errors on every statement run we had what >>>> looks like a memory failure: >>>> Dec 22 16:37:44 localhost kernel: [279820.425911] postgres invoked >>>> oom-killer: gfp_mask=0x280da, order=0, oom_adj=0 >>>> Dec 22 16:37:44 localhost kernel: [279820.425917] postgres cpuset=/ >>>> mems_allowed=0 >>>> Dec 22 16:37:44 localhost kernel: [279820.425922] Pid: 26209, comm: >>>> postgres Not tainted 2.6.32-gentoo-r7 #12 >>>> >>>> We cannot connect to the database using psql as it gives the could not >>>> open relation error. We ran fsck and did not find any problems. I >>>> searched through google and found suggestions such as reindex, but we >>>> can't connect to the db. We also can't take a backup of the >>>> database as >>>> pg_dump gives the same error. >>> >>> Does the not connect also apply to starting postgres in single user >>> mode? >> Same error with single user mode. >> postgres --single -D /var/lib/postgresql/8.2/data clstock >> FATAL: could not open relation 1663/26468499/1259: No such file or >> directory > > Well it is failing on a different relation. Is the FATAL message > correct, are the relations shown in fact not on the file system? Throughout the log, I noticed a bunch of different relations that are missing. It seems to me that they are not in the file system. data/base/26468499 exists with a large number of files, but the ones it mentions are not there. >> >>> >>>> >>>> This is our production database and crashed at the end of the day. If >>>> possible, I would really not like to lose a full days work by >>>> restoring >>>> yesterdays backup. >>>> >>>> Thanks >>>> Sim >>>> >>> >>> >> >> > >
On 12/22/2010 07:03 PM, Tom Lane wrote: > Sim Zacks<sim@compulab.co.il> writes: >> Our postgresql database stopped working with the error >> ERROR: could not open relation 1663/26468499/26470404: No such file or >> directory >>> Does the not connect also apply to starting postgres in single user mode? >> Same error with single user mode. >> postgres --single -D /var/lib/postgresql/8.2/data clstock >> FATAL: could not open relation 1663/26468499/1259: No such file or >> directory > I begin to wonder if the subdirectory $PGDATA/base/26468499 is there at > all? > > regards, tom lane /var/lib/postgresql/8.2/data/base/26468499 does exist, and there are 1212 files in it, but so far all the relationships that I have seen in the error logs are not in the directory.
On 12/22/2010 09:03 AM, Tom Lane wrote: > Sim Zacks<sim@compulab.co.il> writes: >> Our postgresql database stopped working with the error >> ERROR: could not open relation 1663/26468499/26470404: No such file or >> directory >>> Does the not connect also apply to starting postgres in single user mode? >> Same error with single user mode. >> postgres --single -D /var/lib/postgresql/8.2/data clstock >> FATAL: could not open relation 1663/26468499/1259: No such file or >> directory > > I begin to wonder if the subdirectory $PGDATA/base/26468499 is there at > all? > > regards, tom lane > Alright I am going to show my ignorance here, but why would it not be? $PGDATA/base/1663/26468499 -- Adrian Klaver adrian.klaver@gmail.com
Sim Zacks <sim@compulab.co.il> writes:
>>> FATAL: could not open relation 1663/26468499/1259: No such file or
>>> directory
>> I begin to wonder if the subdirectory $PGDATA/base/26468499 is there at
>> all?
> /var/lib/postgresql/8.2/data/base/26468499 does exist, and there are
> 1212 files in it, but so far all the relationships that I have seen in
> the error logs are not in the directory.
Well, 1259 is pg_class, so if you've lost that then this database is
toast. I think your filesystem must've had a hiccup.
regards, tom lane
On 12/22/2010 07:20 PM, Tom Lane wrote: > Sim Zacks<sim@compulab.co.il> writes: >>>> FATAL: could not open relation 1663/26468499/1259: No such file or >>>> directory >>> I begin to wonder if the subdirectory $PGDATA/base/26468499 is there at >>> all? >> /var/lib/postgresql/8.2/data/base/26468499 does exist, and there are >> 1212 files in it, but so far all the relationships that I have seen in >> the error logs are not in the directory. > Well, 1259 is pg_class, so if you've lost that then this database is > toast. I think your filesystem must've had a hiccup. > > regards, tom lane Is there any way I can see the pg_class table so that I know which relations are missing, if I have a list of all the relations that are not there? Or better, is there a way I can dump what is there?
On 12/22/2010 07:20 PM, Tom Lane wrote: > Sim Zacks<sim@compulab.co.il> writes: >>>> FATAL: could not open relation 1663/26468499/1259: No such file or >>>> directory >>> I begin to wonder if the subdirectory $PGDATA/base/26468499 is there at >>> all? >> /var/lib/postgresql/8.2/data/base/26468499 does exist, and there are >> 1212 files in it, but so far all the relationships that I have seen in >> the error logs are not in the directory. > Well, 1259 is pg_class, so if you've lost that then this database is > toast. I think your filesystem must've had a hiccup. > > regards, tom lane In my log file there are 13 files that can't be open and 3 of those actually exist, the rest do not.
Adrian Klaver <adrian.klaver@gmail.com> writes: > On 12/22/2010 09:03 AM, Tom Lane wrote: >> I begin to wonder if the subdirectory $PGDATA/base/26468499 is there at >> all? > Alright I am going to show my ignorance here, but why would it not be? > $PGDATA/base/1663/26468499 1663 is the pg_default tablespace, which refers to $PGDATA/base (hardwired knowledge in the file-path-construction code). See http://developer.postgresql.org/pgdocs/postgres/storage-file-layout.html regards, tom lane
On 12/22/2010 10:41 AM, Tom Lane wrote: > Adrian Klaver<adrian.klaver@gmail.com> writes: >> On 12/22/2010 09:03 AM, Tom Lane wrote: >>> I begin to wonder if the subdirectory $PGDATA/base/26468499 is there at >>> all? > >> Alright I am going to show my ignorance here, but why would it not be? >> $PGDATA/base/1663/26468499 > > 1663 is the pg_default tablespace, which refers to $PGDATA/base > (hardwired knowledge in the file-path-construction code). See > http://developer.postgresql.org/pgdocs/postgres/storage-file-layout.html > > regards, tom lane <Slap forehead> Remember tablespaces. Thanks. -- Adrian Klaver adrian.klaver@gmail.com