Postgres Pro
Downloads
Home   >   mailing lists

Thread: Issue during postgresql startup

Issue during postgresql startup

From
Yogesh Sharma
Date:
Dear All,

Thanks for your support.

Could you please tell me, how to recover my system.

I am facing below errors.
---------------------------
could not open relation 1663/16385/1299988: No such file or directory
---------------------------

This message is continuous occurred in system .

I have tried below procedure for the same but after recover this system, all DB tables are destroyed.
http://www.hivelogik.com/blog/?p=513

Please let me know if any solution.

Regards,
Yogesh



DISCLAIMER:
-----------------------------------------------------------------------------------------------------------------------
The contents of this e-mail and any attachment(s) are confidential and
intended
for the named recipient(s) only.
It shall not attach any liability on the originator or NEC or its
affiliates. Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the
opinions of NEC or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification,
distribution and / or publication of
this message without the prior written consent of the author of this e-mail is
strictly prohibited. If you have
received this email in error please delete it and notify the sender
immediately. .
-----------------------------------------------------------------------------------------------------------------------

Re: Issue during postgresql startup

From
Achilleas Mantzios
Date:
On 28/04/2016 10:21, Yogesh Sharma wrote:
> Dear All,
>
> Thanks for your support.
>
> Could you please tell me, how to recover my system.
>
> I am facing below errors.
> ---------------------------
> could not open relation 1663/16385/1299988: No such file or directory
> ---------------------------
>
> This message is continuous occurred in system .
>
> I have tried below procedure for the same but after recover this system, all DB tables are destroyed.
> http://www.hivelogik.com/blog/?p=513
>
> Please let me know if any solution.

Any recent backups?

>
> Regards,
> Yogesh
>
>
>
> DISCLAIMER:
>
-----------------------------------------------------------------------------------------------------------------------
> The contents of this e-mail and any attachment(s) are confidential and
> intended
> for the named recipient(s) only.
> It shall not attach any liability on the originator or NEC or its
> affiliates. Any views or opinions presented in
> this email are solely those of the author and may not necessarily reflect the
> opinions of NEC or its affiliates.
> Any form of reproduction, dissemination, copying, disclosure, modification,
> distribution and / or publication of
> this message without the prior written consent of the author of this e-mail is
> strictly prohibited. If you have
> received this email in error please delete it and notify the sender
> immediately. .
>
-----------------------------------------------------------------------------------------------------------------------
>
>


--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: Issue during postgresql startup

From
John R Pierce
Date:
On 4/28/2016 12:21 AM, Yogesh Sharma wrote:
Could you please tell me, how to recover my system.

I am facing below errors.
---------------------------
could not open relation 1663/16385/1299988: No such file or directory
---------------------------

This message is continuous occurred in system .

I have tried below procedure for the same but after recover this system, all DB tables are destroyed.
http://www.hivelogik.com/blog/?p=513

did you get that 'no such file or directory' error before or after you reset the transaction logs ?

messing with the write-ahead logs is very dangerous.  that blog is talking about postgres 8.4, based on his paths.   is that what you're running?

I'd be hesitant to rely on random blog advise, without thoroughly understanding what you're doing.


-- 
john r pierce, recycling bits in santa cruz

Re: Issue during postgresql startup

From
Sameer Kumar
Date:


On Thu, 28 Apr 2016 15:22 Yogesh Sharma, <Yogesh1.Sharma@nectechnologies.in> wrote:
Dear All,

Thanks for your support.

Could you please tell me, how to recover my system.

I am facing below errors.
---------------------------
could not open relation 1663/16385/1299988: No such file or directory
---------------------------

This message is continuous occurred in system .


Looks like someone has been playing around with the database files or some disk issue.

Basically Postgres stores each table inside

$tablespace_dir/$database_dir/table_file

In this error it seems one of the files have been removed/lost



I have tried below procedure for the same but after recover this system, all DB tables are destroyed.
http://www.hivelogik.com/blog/?p=513


You might want to post the steps and backup file/date which you have used. 

Please let me know if any solution.

Regards,
Yogesh



DISCLAIMER:
-----------------------------------------------------------------------------------------------------------------------
The contents of this e-mail and any attachment(s) are confidential and
intended
for the named recipient(s) only.
It shall not attach any liability on the originator or NEC or its
affiliates. Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the
opinions of NEC or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification,
distribution and / or publication of
this message without the prior written consent of the author of this e-mail is
strictly prohibited. If you have
received this email in error please delete it and notify the sender
immediately. .
-----------------------------------------------------------------------------------------------------------------------


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
--
Best Regards
Sameer Kumar | DB Solution Architect 
ASHNIK PTE. LTD.

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com

Re: Issue during postgresql startup

From
Yogesh Sharma
Date:

Dear Sameer and all,

 

>Looks like someone has been playing around with the database files or some disk issue.

>Basically Postgres stores each table inside

>$tablespace_dir/$database_dir/table_file

>In this error it seems one of the files have been removed/lost

 

Yes, I am suspecting a disk error or so.

In that case I tried to add a dummy file “base/16385/1299988” using dd command. But while postgre start the file deleted automatically and it failed with same error.

 

Is there any way to recover from this error or take dump of data and recreate DB from same dump ?

 

BTW log files in pg_xlog also increased abruptly from the same time when this error started coming. Is it related to above error ?

 

Any help will be much appreciated !

 

Let me know if any other help is required.

 

 

Thanks,

Yogesh

From: Sameer Kumar [mailto:sameer.kumar@ashnik.com]
Sent: Thursday, April 28, 2016 1:45 PM
To: Yogesh Sharma; pgsql-general
Subject: Re: [GENERAL] Issue during postgresql startup

 

 

On Thu, 28 Apr 2016 15:22 Yogesh Sharma, <Yogesh1.Sharma@nectechnologies.in> wrote:

Dear All,

Thanks for your support.

Could you please tell me, how to recover my system.

I am facing below errors.
---------------------------
could not open relation 1663/16385/1299988: No such file or directory
---------------------------

This message is continuous occurred in system .

 

 

Looks like someone has been playing around with the database files or some disk issue.

 

Basically Postgres stores each table inside

 

$tablespace_dir/$database_dir/table_file

 

In this error it seems one of the files have been removed/lost

 

 


I have tried below procedure for the same but after recover this system, all DB tables are destroyed.
http://www.hivelogik.com/blog/?p=513

 

You might want to post the steps and backup file/date which you have used. 

 

Please let me know if any solution.

Regards,
Yogesh



DISCLAIMER:
-----------------------------------------------------------------------------------------------------------------------
The contents of this e-mail and any attachment(s) are confidential and
intended
for the named recipient(s) only.
It shall not attach any liability on the originator or NEC or its
affiliates. Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the
opinions of NEC or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification,
distribution and / or publication of
this message without the prior written consent of the author of this e-mail is
strictly prohibited. If you have
received this email in error please delete it and notify the sender
immediately. .
-----------------------------------------------------------------------------------------------------------------------


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--

--

Best Regards

Sameer Kumar | DB Solution Architect 

ASHNIK PTE. LTD.

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com


DISCLAIMER:
-----------------------------------------------------------------------------------------------------------------------
The contents of this e-mail and any attachment(s) are confidential and
intended
for the named recipient(s) only. 
It shall not attach any liability on the originator or NEC or its
affiliates. Any views or opinions presented in 
this email are solely those of the author and may not necessarily reflect the
opinions of NEC or its affiliates. 
Any form of reproduction, dissemination, copying, disclosure, modification,
distribution and / or publication of 
this message without the prior written consent of the author of this e-mail is
strictly prohibited. If you have 
received this email in error please delete it and notify the sender
immediately. .
-----------------------------------------------------------------------------------------------------------------------

Re: Issue during postgresql startup

From
Yogesh Sharma
Date:

 

Dear John,

 

>did you get that 'no such file or directory' error before or after you reset the transaction logs ?

The error message is coming before.

>messing with the write-ahead logs is very dangerous.  that blog is talking about postgres 8.4, based on his paths.   is that what you're running?

We are running postgres 8.3.

 

Regards,

Yogesh

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of John R Pierce
Sent: Thursday, April 28, 2016 1:44 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Issue during postgresql startup

 

On 4/28/2016 12:21 AM, Yogesh Sharma wrote:

Could you please tell me, how to recover my system.
 
I am facing below errors.
---------------------------
could not open relation 1663/16385/1299988: No such file or directory
---------------------------
 
This message is continuous occurred in system .
 
I have tried below procedure for the same but after recover this system, all DB tables are destroyed.
http://www.hivelogik.com/blog/?p=513


did you get that 'no such file or directory' error before or after you reset the transaction logs ?

messing with the write-ahead logs is very dangerous.  that blog is talking about postgres 8.4, based on his paths.   is that what you're running?

I'd be hesitant to rely on random blog advise, without thoroughly understanding what you're doing.



-- 
john r pierce, recycling bits in santa cruz

DISCLAIMER:
-----------------------------------------------------------------------------------------------------------------------
The contents of this e-mail and any attachment(s) are confidential and
intended
for the named recipient(s) only. 
It shall not attach any liability on the originator or NEC or its
affiliates. Any views or opinions presented in 
this email are solely those of the author and may not necessarily reflect the
opinions of NEC or its affiliates. 
Any form of reproduction, dissemination, copying, disclosure, modification,
distribution and / or publication of 
this message without the prior written consent of the author of this e-mail is
strictly prohibited. If you have 
received this email in error please delete it and notify the sender
immediately. .
-----------------------------------------------------------------------------------------------------------------------

Re: Issue during postgresql startup

From
John R Pierce
Date:
On 4/28/2016 1:33 AM, Yogesh Sharma wrote:

>did you get that 'no such file or directory' error before or after you reset the transaction logs ?

The error message is coming before.


that means your file system lost files.    there's a number of ways that can happen, mostly revolving around file systems with unreliable caching combined with a power failure, or physical hardware data corruption can do it too.   mucking about in the postgres data file system can do it, too, of course.   do you have backups from before this occurred ?


>messing with the write-ahead logs is very dangerous.  that blog is talking about postgres 8.4, based on his paths.   is that what you're running?

We are running postgres 8.3.


thats a completely obsolete version, 8.3.23 was last updated in early 2013.

current supported versions are 9.1, 9.2, 9.3, 9.4, and 9.5

current releases:  9.1.21, 9.2.16, 9.3.12, 9.4.7, and 9.5.2


-- 
john r pierce, recycling bits in santa cruz

Re: Issue during postgresql startup

From
Yogesh Sharma
Date:

Dear John,

 

>do you have backups from before this occurred ?

No, there is no backup available for the same.

 

Regards,

Yogesh

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of John R Pierce
Sent: Thursday, April 28, 2016 2:18 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Issue during postgresql startup

 

On 4/28/2016 1:33 AM, Yogesh Sharma wrote:

>did you get that 'no such file or directory' error before or after you reset the transaction logs ?

The error message is coming before.


that means your file system lost files.    there's a number of ways that can happen, mostly revolving around file systems with unreliable caching combined with a power failure, or physical hardware data corruption can do it too.   mucking about in the postgres data file system can do it, too, of course.   do you have backups from before this occurred ?



>messing with the write-ahead logs is very dangerous.  that blog is talking about postgres 8.4, based on his paths.   is that what you're running?

We are running postgres 8.3.


thats a completely obsolete version, 8.3.23 was last updated in early 2013.

current supported versions are 9.1, 9.2, 9.3, 9.4, and 9.5

current releases:  9.1.21, 9.2.16, 9.3.12, 9.4.7, and 9.5.2

 

-- 
john r pierce, recycling bits in santa cruz

DISCLAIMER:
-----------------------------------------------------------------------------------------------------------------------
The contents of this e-mail and any attachment(s) are confidential and
intended
for the named recipient(s) only. 
It shall not attach any liability on the originator or NEC or its
affiliates. Any views or opinions presented in 
this email are solely those of the author and may not necessarily reflect the
opinions of NEC or its affiliates. 
Any form of reproduction, dissemination, copying, disclosure, modification,
distribution and / or publication of 
this message without the prior written consent of the author of this e-mail is
strictly prohibited. If you have 
received this email in error please delete it and notify the sender
immediately. .
-----------------------------------------------------------------------------------------------------------------------

Re: Issue during postgresql startup

From
John R Pierce
Date:
On 4/28/2016 1:54 AM, Yogesh Sharma wrote:

 

>do you have backups from before this occurred ?

No, there is no backup available for the same.


oops.


it is possible some of your tables can be recovered by someone highly experienced in postgres data recovery.  such people work for the various postgres consultancies, like 2nd Quadrant, and so forth.   I sure don't know the intricacies of doing this.    8.3 is a really old obsolete version, too... its life cycle ran from Feb. 2008 to Feb 2013.




-- 
john r pierce, recycling bits in santa cruz

Re: Issue during postgresql startup

From
Yogesh Sharma
Date:

Dear All,

 

I am sharing my further observation regarding the system behavior.

 

The errors related to this missing table file started coming from 2016-04-24 20:37:11 till the end when pg_xlog filled up the disk space and postgres crashed eventually.

 

postgresql_log.Sun.gz:<%2016-04-24 20:37:11 GMT> ERROR:  could not open relation 1663/16385/1299988: No such file or directory

 

PostgreSQL:PANIC:  could not write to file "pg_xlog/xlogtemp.21498": No space left on device server closed the connection unexpectedly

        This probably means the server terminated abnormally

        before or while processing the request.

 

Furthermore, as a periodic maintenance REINDEXING of table executed at 2016-04-24 20:33:05, Although table file is recreated at index reconstruction, for some reason, the recreated table file 1299988 got deleted, and probably led to this issue.

 

There is a difference of roughly 4 minutes between the REINDEXING and the point where this error start coming. I am trying to find out if there is any sort of relation between postgres and table file deletion (Probably some sort of bug in postgres ?).

 

As an experiment I tried to create a dummy file “1663/16385/1299988” using dd command and triggered postgres start but postgres delete the dummy file automatically. Due to this sort of behavior I am suspecting that the actual file created after REINDEX might be having some sort of corruption and postgres deleted that file automatically.

 

I’ll appreciate any kind of opinion on my understanding.

 

Thanks,

Yogesh Sharma

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of John R Pierce
Sent: Thursday, April 28, 2016 2:51 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Issue during postgresql startup

 

On 4/28/2016 1:54 AM, Yogesh Sharma wrote:

 

>do you have backups from before this occurred ?

No, there is no backup available for the same.

 

oops.

 

it is possible some of your tables can be recovered by someone highly experienced in postgres data recovery.  such people work for the various postgres consultancies, like 2nd Quadrant, and so forth.   I sure don't know the intricacies of doing this.    8.3 is a really old obsolete version, too... its life cycle ran from Feb. 2008 to Feb 2013.

 

 

 

-- 
john r pierce, recycling bits in santa cruz

DISCLAIMER:
-----------------------------------------------------------------------------------------------------------------------
The contents of this e-mail and any attachment(s) are confidential and
intended
for the named recipient(s) only. 
It shall not attach any liability on the originator or NEC or its
affiliates. Any views or opinions presented in 
this email are solely those of the author and may not necessarily reflect the
opinions of NEC or its affiliates. 
Any form of reproduction, dissemination, copying, disclosure, modification,
distribution and / or publication of 
this message without the prior written consent of the author of this e-mail is
strictly prohibited. If you have 
received this email in error please delete it and notify the sender
immediately. .
-----------------------------------------------------------------------------------------------------------------------

Re: Issue during postgresql startup

From
Yogesh Sharma
Date:

Dear All,

 

Could you please update your suggestion on below mail

 

Regards,

Yogesh

 

From: Yogesh Sharma
Sent: Friday, April 29, 2016 3:57 PM
To: John R Pierce; pgsql-general@postgresql.org
Subject: RE: [GENERAL] Issue during postgresql startup

 

Dear All,

 

I am sharing my further observation regarding the system behavior.

 

The errors related to this missing table file started coming from 2016-04-24 20:37:11 till the end when pg_xlog filled up the disk space and postgres crashed eventually.

 

postgresql_log.Sun.gz:<%2016-04-24 20:37:11 GMT> ERROR:  could not open relation 1663/16385/1299988: No such file or directory

 

PostgreSQL:PANIC:  could not write to file "pg_xlog/xlogtemp.21498": No space left on device server closed the connection unexpectedly

        This probably means the server terminated abnormally

        before or while processing the request.

 

Furthermore, as a periodic maintenance REINDEXING of table executed at 2016-04-24 20:33:05, Although table file is recreated at index reconstruction, for some reason, the recreated table file 1299988 got deleted, and probably led to this issue.

 

There is a difference of roughly 4 minutes between the REINDEXING and the point where this error start coming. I am trying to find out if there is any sort of relation between postgres and table file deletion (Probably some sort of bug in postgres ?).

 

As an experiment I tried to create a dummy file “1663/16385/1299988” using dd command and triggered postgres start but postgres delete the dummy file automatically. Due to this sort of behavior I am suspecting that the actual file created after REINDEX might be having some sort of corruption and postgres deleted that file automatically.

 

I’ll appreciate any kind of opinion on my understanding.

 

Thanks,

Yogesh Sharma

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of John R Pierce
Sent: Thursday, April 28, 2016 2:51 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Issue during postgresql startup

 

On 4/28/2016 1:54 AM, Yogesh Sharma wrote:

 

>do you have backups from before this occurred ?

No, there is no backup available for the same.

 

oops.

 

it is possible some of your tables can be recovered by someone highly experienced in postgres data recovery.  such people work for the various postgres consultancies, like 2nd Quadrant, and so forth.   I sure don't know the intricacies of doing this.    8.3 is a really old obsolete version, too... its life cycle ran from Feb. 2008 to Feb 2013.

 

 

 

-- 
john r pierce, recycling bits in santa cruz

DISCLAIMER:
-----------------------------------------------------------------------------------------------------------------------
The contents of this e-mail and any attachment(s) are confidential and
intended
for the named recipient(s) only. 
It shall not attach any liability on the originator or NEC or its
affiliates. Any views or opinions presented in 
this email are solely those of the author and may not necessarily reflect the
opinions of NEC or its affiliates. 
Any form of reproduction, dissemination, copying, disclosure, modification,
distribution and / or publication of 
this message without the prior written consent of the author of this e-mail is
strictly prohibited. If you have 
received this email in error please delete it and notify the sender
immediately. .
-----------------------------------------------------------------------------------------------------------------------