Postgres Pro
Downloads
Home   >   mailing lists

Re: Reg: PostgreSQL Server "base/data" recovery - Mailing list pgsql-general

From Prajilal KP
Subject Re: Reg: PostgreSQL Server "base/data" recovery
Date
Msg-id CABsTp+D7J7oZKat8NC52WADTixtbOJ55CE1Cr6cxCNaoSBa8yQ@mail.gmail.com
Whole thread Raw
In response to Re: Reg: PostgreSQL Server "base/data" recovery  (Michael Paquier <michael.paquier@gmail.com>)
Responses Re: Reg: PostgreSQL Server "base/data" recovery
List pgsql-general
Tree view
Thank you Michael for the reply.

Here is the other log from pg_log directory

ERROR:  could not open file "base/16384/7969143.26" (target block 13148261): No such file or directory
CONTEXT:  SQL statement "select min(id), max(id)                             from requests where id > last_max_id"
PL/pgSQL function "f_raw_logs_check_for_new_data" line 33 at SQL statement
STATEMENT:  select f_raw_logs_check_for_new_data(10000, 10001)
ERROR:  function f_etl_task_end(integer, integer, integer, integer, unknown) does not exist at character 8
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
STATEMENT:  select f_etl_task_end(7161174, 1, 0, 3, E'could not open file "base/16384/7969143.26" (target block 13148261): No such file or directory
CONTEXT:  SQL statement "select min(id), max(id)                             from requests where id > last_max_id"
PL/pgSQL function "f_raw_logs_check_for_new_data" line 33 at SQL statement
')
ERROR:  could not open file "base/16384/7969143.26" (target block 13148261): No such file or directory

When i see check the this file, the file itself exists but the size is "0" byte.  

The server is writing the whole log in to the mounted network storage, NFS. I have scanned the storage for any errors and nothing found.

I will consider your suggestion to upgrade the PostgreSQL version.


Regards,
Prajilal

On Thu, Mar 19, 2015 at 5:46 PM, Michael Paquier <michael.paquier@gmail.com> wrote:
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

pgsql-general by date:

Previous
From: Michael Paquier
Date:
Subject: Re: Reg: PostgreSQL Server "base/data" recovery
Next
From: Tim Uckun
Date:
Subject: Re: Name spacing functions and stored procedures