PostgreSQL 9.4 – How to Restore Crashed Database

postgresql

Can anyone help me to restore my crashed database. Is there a way to use pg_resetxlog. I read a number of documentations telling about it but i'm always seeing the same log when i restart my postgresql service

2016-04-11 8:47:59 p.m. GMT [1710-1] LOG: the database system was interrupted during a restore at 2016-04-11 11:04:19 GMT
2016-04-11 8:47:59 p.m. GMT [1710-2] TIP: This probably means that the data has been corrupted and you
need to use the last backup for recovery.
2016-04-11 8:47:59 p.m. GMT [1710-3] LOG: the database system was not shut properly; restoration
automatic current
2016-04-11 8:47:59 p.m. GMT [1710-4] LOG: the re-execution starts at 4 / 32DF8E90
2016-04-11 8:47:59 p.m. GMT [1710-5] FATAL: could not access the transaction status 5598
2016-04-11 8:47:59 p.m. GMT [1710-6] DETAIL: Could not read the file "pg_multixact / members / 0000" at offset 57344: Success
2016-04-11 8:47:59 p.m. GMT [1710-7] BACKGROUND: xlog redo create mxiD 5598 offset 11603 Nmembers 2: 6486476 (keysh) 6,486,482 (keysh)
2016-04-11 8:47:59 p.m. GMT [1709-1] LOG: launch process (PID 1710) exits with exit code 1
2016-04-11 8:47:59 p.m. GMT [1709-2] LOG: start cancellation due to a failure in the launch process

I tried to fix my problem like this :

First one, saving my data in a external drive

scp -r /var/lib/postgresql/9.4/main /media/myhdd/data

Second One, as postgres user i run the pg_resetxlog command like this :

./pg_resetxlog -f /var/lib/postgresql/9.4/main/

The command result after pg_resetxlog is

Resetting the transaction log

But when i try to restart my postgresql service i get the same error message. Am i doing the right command whith pg_resetxlog ? I'm new on postgres

Best Answer

I finally solve my problem. I recreate pg_multiaxt/members/0000 file by giving more offset. My first offset was 55344 and the new one is 100192. The command is :

dd if=/dev/zero of=0000 bs=1 count=100192

After restarting my postgresql service my database is now working correctly.

Related Question