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 <da...@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 <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>
