Обсуждение: Reg: PostgreSQL Server "base/data" recovery

On Thu, Mar 19, 2015 at 5:19 PM, Prajilal KP <prajilal.kp@gmail.com> wrote:
> Recently i have noticed that my PostgreSQL server is returning the error
> messages like "Could not read block 6160722 in file
> "base/data/16384/11033028.47".

Don't you have more details to share? There should be some hint here
as well, and usually you may be facing such things because of OS or
hardware problems.

> When i have checked the "base/data/16384" directory, some of the file's data
> size is zero and exactly from that time i have started receiving the said
> error messages.
>
> Could you please advise the way to recover the Database without loosing any
> data?

If you are facing hardware problems, this may be a time to deploy a
backup and replay WAL up to where you wish to on clean disks.

> PostgreSQL version : 9.0.4

9.0.4 has been released in 2011, so you are missing 4 years worth of
bug fixes, the latest minor version of the 9.0.X release being 9.0.19.
Note as well that 9.0 will be EOL at the end of the year, hence you
could do even better: an upgrade to a newer major version.

Regards,
--
Michael

On Thu, Mar 19, 2015 at 07:02:28PM +0900, Prajilal KP wrote:
>
> When i see check the this file, the file itself exists but the size is "0"
> byte.

That suggests you have data corruption, and that you need to restore from
backup.

> The server is writing the whole log in to the mounted network storage, NFS.

There are reasons that people get nervous about databases on NFS.  Are
you ensuring that Postgres fsync() calls (like when COMMIT happens)
are not being handled asynchronously?

Also, a trivial scan of the release notes in the 9.0.x series shows a
number of data corruption fixes since 9.0.4.  You should always try to
stay on the latest minor release of your version of Postgres.

Best regards,

A

--
Andrew Sullivan
ajs@crankycanuck.ca

On Thu, Mar 19, 2015 at 8:12 PM, Prajilal KP <prajilal.kp@gmail.com> wrote:
> I see, As there are many reasons exists for the data corruption i couldn't
> figure out it exactly.
> Unfortunately there is no backup for this system and is not possible to
> restore from the backup.

Well, then, lost data is lost.

> I have been using the NFS storage since 2013 and didn't experience this
> issue before, now i am afraid.
> We haven't set to run the fysnc() in our environment.
>
> If i set "fysnc to on" now, did it make any impact to current flow?

Having fsync = off in postgresql.conf is a perfect method to corrupt
your data, you should really not set it to off.
--
Michael

> > I see, As there are many reasons exists for the data corruption i couldn't
> > figure out it exactly.
> > Unfortunately there is no backup for this system and is not possible to
> > restore from the backup.
>
> Well, then, lost data is lost.

Oh.. My bad ...
>
> > I have been using the NFS storage since 2013 and didn't experience this
> > issue before, now i am afraid.
> > We haven't set to run the fysnc() in our environment.
> >
> > If i set "fysnc to on" now, did it make any impact to current flow?
>
> Having fsync = off in postgresql.conf is a perfect method to corrupt
> your data, you should really not set it to off.

Okay, I will take action immediately to set the fync=on. I hope it with save me for a period.

I will consider upgrading to a later version at the earliest..

Thank you very much
Prajilal

> > I see, As there are many reasons exists for the data corruption i couldn't
> > figure out it exactly.
> > Unfortunately there is no backup for this system and is not possible to
> > restore from the backup.
>
> Well, then, lost data is lost.

Oh.. My bad ...
>
> > I have been using the NFS storage since 2013 and didn't experience this
> > issue before, now i am afraid.
> > We haven't set to run the fysnc() in our environment.
> >
> > If i set "fysnc to on" now, did it make any impact to current flow?
>
> Having fsync = off in postgresql.conf is a perfect method to corrupt
> your data, you should really not set it to off.

Okay, I will take action immediately to set the fync=on. I hope it with save me for a period.

I will consider upgrading to a later version at the earliest..

Thank you very much
Prajilal