Re: Help! PostgreSQL stuck at starting up after crash

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(at)beechglen(dot)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 <http://www.facebook.com/Replicon.inc> |
twitter<http://twitter.com/Replicon>|
blog <http://www.replicon.com/blog/> | contact
us<http://www.replicon.com/about_replicon/contact_us.aspx>

*We are hiring!* | search
jobs<http://tbe.taleo.net/NA2/ats/careers/searchResults.jsp?org=REPLICON&cws=1&act=sort&sortColumn=1&__utma=1.651918544.1299001662.1299170819.1299174966.10&__utmb=1.8.10.1299174966&__utmc=1&__utmx=-&__utmz=1.1299174985.10.3.utmcsr=google%7Cutmccn=%28organic%29%7Cutmcmd=organic%7Cutmctr=replicon%20careers&__utmv=1.%7C3=Visitor%20Type=Prospects=1,&__utmk=40578466>