Postgres Pro
Downloads
Home   >   mailing lists

Thread: DB fails to start: "Could not read from file "pg_clog/0003" at offset 212992: No error.

DB fails to start: "Could not read from file "pg_clog/0003" at offset 212992: No error.

From
Olga Vingurt
Date:

Hi,

 

We are using PostgreSQL 8.3 on Windows Server.

The db crashed and now it fails to start up.

 

The error is:

FATAL:  could not access status of transaction 4020264

DETAIL:  Could not read from file "pg_clog/0003" at offset 212992: No error.

 

Additional log from event viewer:

LOG:  database system was interrupted; last known up at 2012-07-12 11:35:00 CEST

 

 

How can we recover from the current state?

 

Thanks,

Olga

 

Re: DB fails to start: "Could not read from file "pg_clog/0003" at offset 212992: No error.

From
raghu ram
Date:


On Mon, Jul 16, 2012 at 5:44 PM, Olga Vingurt <olgavi@checkpoint.com> wrote:

Hi,

 

We are using PostgreSQL 8.3 on Windows Server.

The db crashed and now it fails to start up.

 

The error is:

FATAL:  could not access status of transaction 4020264

DETAIL:  Could not read from file "pg_clog/0003" at offset 212992: No error.

 

Additional log from event viewer:

LOG:  database system was interrupted; last known up at 2012-07-12 11:35:00 CEST

 

 

How can we recover from the current state?

 



Please perform below steps:

1. Backup the current pg_clog/0003 file in different directory

2. Create a file by assumption of  make the uncommitted record as they haven't been committed. command as follows:

dd if=/dev/zero of=<data directory location>/pg_clog/0003   bs=256K count=1 

3. Start the Cluster


--

Thanks & Regards,

Raghu Ram

EnterpriseDB Corporation

Blog:http://raghurc.blogspot.in/



Re: DB fails to start: "Could not read from file "pg_clog/0003" at offset 212992: No error.

From
Raghavendra
Date:
I think you need to download windows version DD and use it.

---
Regards,
Raghavendra
EnterpriseDB Corporation



On Mon, Jul 16, 2012 at 7:25 PM, raghu ram <raghuchennuru@gmail.com> wrote:


On Mon, Jul 16, 2012 at 5:44 PM, Olga Vingurt <olgavi@checkpoint.com> wrote:

Hi,

 

We are using PostgreSQL 8.3 on Windows Server.

The db crashed and now it fails to start up.

 

The error is:

FATAL:  could not access status of transaction 4020264

DETAIL:  Could not read from file "pg_clog/0003" at offset 212992: No error.

 

Additional log from event viewer:

LOG:  database system was interrupted; last known up at 2012-07-12 11:35:00 CEST

 

 

How can we recover from the current state?

 



Please perform below steps:

1. Backup the current pg_clog/0003 file in different directory

2. Create a file by assumption of  make the uncommitted record as they haven't been committed. command as follows:

dd if=/dev/zero of=<data directory location>/pg_clog/0003   bs=256K count=1 

3. Start the Cluster


--

Thanks & Regards,

Raghu Ram

EnterpriseDB Corporation

Blog:http://raghurc.blogspot.in/




Re: DB fails to start: "Could not read from file "pg_clog/0003" at offset 212992: No error.

From
Craig Ringer
Date:
On 07/16/2012 09:55 PM, raghu ram wrote:


On Mon, Jul 16, 2012 at 5:44 PM, Olga Vingurt <olgavi@checkpoint.com> wrote:

Hi,

 

We are using PostgreSQL 8.3 on Windows Server.

The db crashed and now it fails to start up.


Crashed how? Did the *server* crash, or the database?

When is your last backup from?

Have you made a complete file-system level copy of the database yet? See:

http://wiki.postgresql.org/wiki/Corruption

There's no sign of corruption and an issue with the clog doesn't imply data coruption, but you should still take a complete copy before proceeding unless your backups are current and trusted.

Raghu: It may be best to advise people with DB damage issues to make a full file-system level copy before attempting repair. Once they start trying to fix things it's much harder to go back to the start if something breaks worse.

Please perform below steps:

1. Backup the current pg_clog/0003 file in different directory

2. Create a file  by assumption of  make the uncommitted record as they haven't been committed. command as follows:

dd if=/dev/zero of=<data directory location>/pg_clog/0003   bs=256K count=1

This is just a 256k zero-byte file. Here's one I made earlier:

  http://www.postnewspapers.com.au/~craig/0003.zip

I don't know if the above advice is safe, but so long as you've made a backup of your datadir it's worth a go.

--
Craig Ringer


Re: DB fails to start: "Could not read from file "pg_clog/0003" at offset 212992: No error.

From
Olga Vingurt
Date:

> Crashed how? Did the *server* crash, or the database?
> When is your last backup from?
> Have you made a complete file-system level copy of the database yet?

 

 

I don’t know how it crashed yet, I’m checking what the reason was.

We will check if we have a recent backup.

Yes, we made a compile file-system level copy of the database.

 

 

> There's no sign of corruption and an issue with the clog doesn't imply data coruption, but you should still take a complete copy before proceeding unless your backups are current and trusted.

 

What does the issue with clog imply?

We have 5 files in the clog folder:

  1. Files 0000-0002 are 256K filled with ‘U’
  2. File 0003 is 184K and at the end there is junk like ‘ZZZZZiiiiiii™eiY’
  3. File 0004 is 24K and filled with ‘U’

 

 

> Please perform below steps:

> 1. Backup the current pg_clog/0003 file in different directory

> 2. Create a file  by assumption of  make the uncommitted record as they haven't been committed. command as follows:

>  dd if=/dev/zero of=<data directory location>/pg_clog/0003   bs=256K count=1

> This is just a 256k zero-byte file. Here's one I made earlier:
> http://www.postnewspapers.com.au/~craig/0003.zip

 

 

I performed the procedure and the db started. How can I check if it’s corrupted or not?

 

 

 

> Create a file by assumption of make the uncommitted record as they haven't been committed. command as follows:

 

What does the file replacement actually did?  Which records is marked as uncommitted?

 

 

Thanks a lot for your help