We pretty much lost the data for the last two days, but since our postgresql wereN'T running well, it is fewer than it looks.
On Thu, Jan 19, 2012 at 3:45 PM, Samuel Hwang <samuel@xxxxxxxxxxxx> wrote:
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@xxxxxxxxxxxxx> 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 usWe are hiring! | search jobs
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