Обсуждение: 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.
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
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?
On Mon, Jul 16, 2012 at 5:44 PM, Olga Vingurt <olgavi@checkpoint.com> wrote:
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 directory2. 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=13. Start the Cluster
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 directory2. 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
> 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:
- Files 0000-0002 are 256K filled with ‘U’
- File 0003 is 184K and at the end there is junk like ‘ZZZZZiiiiiii™eiY’
- 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