Обсуждение: Help, server doesn't start

El 24/06/2012 09:53 p.m., Anibal David Acosta escribió:

We have an old postgres installation (8.3) running on windows 2003

The server run perfect for many years but today it crash L

 

We have a very old backup so I’ll try desperate to repair.

If I can’t repair I want to skip the error and start server with only healthy data, no matter if I lose some table page, entire table or even entire database because the server has many databases inside.

 

 

Currently the database is not starting, when I try to start I got the in the log:

 

2012-06-25 02:22:33 GMT LOG:  database system was interrupted while in recovery at 2012-06-25 02:22:26 GMT

2012-06-25 02:22:33 GMT HINT:  This probably means that some data is corrupted and you will have to use the last backup for recovery.

2012-06-25 02:22:33 GMT LOG:  database system was not properly shut down; automatic recovery in progress

2012-06-25 02:22:33 GMT LOG:  redo starts at 17C/E1C42040

2012-06-25 02:22:33 GMT LOG:  loaded library "$libdir/plugins/plugin_debugger.dll"

2012-06-25 02:22:33 GMT FATAL:  the database system is starting up

 

[LAST TWO LINES REPEAT MANY TIMES]

 

2012-06-25 02:22:36 GMT LOG:  startup process (PID 1120) was terminated by exception 0xC000000D

2012-06-25 02:22:36 GMT HINT:  See C include file "ntstatus.h" for a description of the hexadecimal value.

2012-06-25 02:22:36 GMT LOG:  aborting startup due to startup process failure

2012-06-25 02:22:36 GMT FATAL:  the database system is starting up

2012-06-25 02:22:36 GMT LOG:  loaded library "$libdir/plugins/plugin_debugger.dll"

2012-06-25 02:22:36 GMT FATAL:  the database system is starting up

 

 

 

The first advice here is:

• backup your data files before any attempt to fix the issue.

It might be a hardware failure that corrupted your data (specifically your transaction logs).
It might be a bug on that specific version, what is your minor release number?

• As a last resort you could use pg_resetlog, it's a binary that resets the information in the control file and transaction logs.

http://www.postgresql.org/docs/8.3/static/app-pgresetxlog.html
but please backup your data directory first.
After that, if the server starts up successfully, perform a pg_dump / pg_dumpall A.S.A.P and reload your data on a new data cluster.

I'm on twitter @iCodeiExist

El 24/06/2012 11:30 p.m., René Romero Benavides escribió:

El 24/06/2012 09:53 p.m., Anibal David Acosta escribió:

We have an old postgres installation (8.3) running on windows 2003

The server run perfect for many years but today it crash L

 

We have a very old backup so I’ll try desperate to repair.

If I can’t repair I want to skip the error and start server with only healthy data, no matter if I lose some table page, entire table or even entire database because the server has many databases inside.

 

 

Currently the database is not starting, when I try to start I got the in the log:

 

2012-06-25 02:22:33 GMT LOG:  database system was interrupted while in recovery at 2012-06-25 02:22:26 GMT

2012-06-25 02:22:33 GMT HINT:  This probably means that some data is corrupted and you will have to use the last backup for recovery.

2012-06-25 02:22:33 GMT LOG:  database system was not properly shut down; automatic recovery in progress

2012-06-25 02:22:33 GMT LOG:  redo starts at 17C/E1C42040

2012-06-25 02:22:33 GMT LOG:  loaded library "$libdir/plugins/plugin_debugger.dll"

2012-06-25 02:22:33 GMT FATAL:  the database system is starting up

 

[LAST TWO LINES REPEAT MANY TIMES]

 

2012-06-25 02:22:36 GMT LOG:  startup process (PID 1120) was terminated by exception 0xC000000D

2012-06-25 02:22:36 GMT HINT:  See C include file "ntstatus.h" for a description of the hexadecimal value.

2012-06-25 02:22:36 GMT LOG:  aborting startup due to startup process failure

2012-06-25 02:22:36 GMT FATAL:  the database system is starting up

2012-06-25 02:22:36 GMT LOG:  loaded library "$libdir/plugins/plugin_debugger.dll"

2012-06-25 02:22:36 GMT FATAL:  the database system is starting up

 

 

 

The first advice here is:

• backup your data files before any attempt to fix the issue.

It might be a hardware failure that corrupted your data (specifically your transaction logs).
It might be a bug on that specific version, what is your minor release number?

• As a last resort you could use pg_resetlog, it's a binary that resets the information in the control file and transaction logs.

http://www.postgresql.org/docs/8.3/static/app-pgresetxlog.html
but please backup your data directory first.
After that, if the server starts up successfully, perform a pg_dump / pg_dumpall A.S.A.P and reload your data on a new data cluster.

I'm on twitter @iCodeiExist

and please, check and double check that the server process is not active, if it's not and pg_resetxlog complains that it is, check again and then delete the postmaster.pid file.

Yes, we must upgrade.

The value of the shared_preload_libraries is

 

shared_preload_libraries = '$libdir/plugins/plugin_debugger.dll'                               # (change requires restart)

Change that to:

shared_preload_libraries = ''

(two single quotes, not a double quote)

It's unlikely that the PL/PgSQL debugger plugin is the issue, but since it's repeated in your logs, it's worth a go.

 When I login into the server the disk used by Postgres installation was without space (0Bytes available).

 

OK, so you probably do just have xlogs that're cut short. So long as nobody tried to "fix" the problem by deleting things out of the PostgreSQL data directory I expect you'll be OK.

AFAIK Pg is supposed to recover gracefully from out-of-disk situations, but this _is_ quite an old version.

I wonder if there are any out-of-disk tests in the Pg unit tests? It'd be somewhat tricky to automate testing for, but really good to do if it's practical. Something for my bored-weekend TODO I guess, even if it's just a standalone test set.

Right now I am making a file-level copy of the entire postgres folder in order to run some corruption recover method

Great.

--
Craig Ringer

  We plan to upgrade to version 9.1, do you know if file result of the pg_dump in 8.3 can be restored in 9.1?

Yes, pg_dump output from 8.3 should restore fine to 9.1. PLEASE READ THE RELEASE NOTES for the intermediate major versions (8.4 and 9.0) for upgrade advice and caveats.

http://www.postgresql.org/docs/current/static/release-8-4.html
http://www.postgresql.org/docs/current/static/release-9-0.html
http://www.postgresql.org/docs/current/static/release-9-1.html

Do not just try to upgrade your production database. Do a test with a test copy first, and test your application against it; don't just assume it'll all work.

--
Craig Ringer

> Yes, pg_dump output from 8.3 should restore fine to 9.1.

Pardon my ignorance if this changed in recent versions, but shouldn't that read:

Yes, pg_dump 9.1 output from 8.3 should restore fine to 9.1?

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

On 06/25/2012 09:27 PM, Alban Hertroys wrote:
>> Yes, pg_dump output from 8.3 should restore fine to 9.1.
> Pardon my ignorance if this changed in recent versions, but shouldn't that read:
>
> Yes, pg_dump 9.1 output from 8.3 should restore fine to 9.1?
Very good point and an important catch. Thank-you. Always dump with
pg_dump from the newer database.

--
Craig Ringer