Re: cannot start 9.3 after system crash

On Tue, Jan 27, 2015 at 5:42 AM, Andreas Laggner
<andreas(dot)laggner(at)ti(dot)bund(dot)de> wrote:
> Hi list,
>
> the system HDD of a server running postgresql 9.3 died (sudden electronical
> death) during a pg_dump. The database is located on a hardware raid system
> (another HDD). I installed a new ubuntu 14.04 with postgresql 9.3 postgis
> 2.1. But i cannot start the server. First i got the error:
>
> 2015-01-27 09:37:11 CET LOG: database system was shut down at 2015-01-23
> 16:37:02 CET
> 2015-01-27 09:37:11 CET LOG: invalid primary checkpoint record
> 2015-01-27 09:37:11 CET LOG: invalid secondary checkpoint record
> 2015-01-27 09:37:11 CET PANIC: could not locate a valid checkpoint record
> 2015-01-27 09:37:11 CET LOG: startup process (PID 5309) was terminated by
> signal 6: Aborted
> 2015-01-27 09:37:11 CET LOG: aborting startup due to startup process
> failure
>
> I solved this by executing pg_resetxlog.
>
> Now i get another error and i do not know what else i can do:
>
>
> 2015-01-27 12:02:22 CET LOG: database system was interrupted; last known
> up at 2015-01-27 10:24:56 CET
> 2015-01-27 12:02:22 CET LOG: database system was not properly shut down;
> automatic recovery in progress
> 2015-01-27 12:02:22 CET LOG: record with zero length at 360/E20001C8
> 2015-01-27 12:02:22 CET LOG: redo is not required
> 2015-01-27 12:02:22 CET [unknown] [unknown] LOG: incomplete startup packet
> 2015-01-27 12:02:22 CET FATAL: could not access status of transaction
> 214707
> 2015-01-27 12:02:22 CET DETAIL: Could not open file "pg_clog/0000": Datei
> oder Verzeichnis nicht gefunden.
> 2015-01-27 12:02:22 CET LOG: startup process (PID 6520) exited with exit
> code 1
> 2015-01-27 12:02:22 CET LOG: aborting startup due to startup process
> failure

Before doing anything take a filesystem level backup of the entire
database and set it aside. You're probably facing corrupted data.
Once you've made the backup, you might try reconstituting the clog
file to see where you stand. You can do this by 'making' a clog file
filled with bytes that indicate committed transactions...0x55 hex.
Once done you can trying bringing up the database to see where you
stand although as a precaution i'd disable autovacuum first.

merlin