Re: Help! PostgreSQL stuck at starting up after crash - Mailing list pgsql-admin

pg_resetxlog does the trick and db can be started and readable.

I am dumping the data out and import to a newly created database cluster.

We pretty much lost the data for the last two days, but since our postgresql were running well, it is fewer than it looks.

Thanks a lot for the help.

On Thu, Jan 19, 2012 at 7:30 AM, David Hornsby <david@beechglen.com> wrote:

Sounds like you have a corrupt wal files that you will have to reset the
wal logs with pgresetxlog.

http://www.postgresql.org/docs/8.2/static/app-pgresetxlog.html

This will result in missing transactions so before you do this shutdown
postgres and make a copy of the database files first. That way if you
don't like what happens you can always go back to the way things were.

Also right now would be a good time to evaluate your backup strategy,
which is a different topic for a different thread, but I can certainly
help with that as well.

-David Hornsby

> version Postgresql 9.1.1 on centos5 x64
>
> We experience slow performance and found the server is running 3 vacuum
> process on the same db which use up 99% of CPU.
> Then we kill -9 one of those process which cause postgresql to crash and
> it
> tried to restart after the crash
> However when the starting process reach the last WAL files, it just stuck
> there
>
> pg_controldata shows the db is in Archive Recovery mode and when using
> psql
> to connect the db, it says FATAL: the database system starting up.
>
> I took a chance and upgrade to PostgreSql 9.1.2 and see if anything
> changed
> it still stuck at the end of recovery.
> pg_controldata shows db is in Crash recovery, but that probably different
> wording I think
> using psql to connect the db, it says FATAL: the database system is
> starting up.
>
> I pretty much run out of idea here.
> Can anyone help what to go from here?
>
> Samuel
>

--

Shian-Miin Samuel Hwang | Software Developer | Phone 1-403-2626519 (ext. 276) | Fax 1-403-233-8046

Replicon | Hassle-Free Time & Expense Management Software - 7,300 Customers - 70 Countries
www.replicon.com | facebook | twitter | blog | contact us

We are hiring! | search jobs