Thread: Help, server doesn't start

Help, server doesn't start

From
"Anibal David Acosta"
Date:

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

 

 

 

Re: Help, server doesn't start

From
René Romero Benavides
Date:
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

Re: Help, server doesn't start

From
Raghavendra
Date:

On Mon, Jun 25, 2012 at 8:23 AM, Anibal David Acosta <aa@devshock.com> wrote:

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.

 

Side-by, you should also consider upgrading PG versions to latest. 
 

 

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

 

 

Can you also paste whats there in shared_preload_libraries setting from $PGDATA/postgresql.conf ?

---
Regards,
Raghavendra
EnterpriseDB Corporation

Re: Help, server doesn't start

From
René Romero Benavides
Date:
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.

-

Re: Help, server doesn't start

From
"Anibal David Acosta"
Date:

Yes, we must upgrade.

The value of the shared_preload_libraries is

 

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

 

An additional information….

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

 

Also is possible that someone (I am checking now) restart the Postgres service in the middle of this situation

 

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

 

Thanks!

 

 

De: Raghavendra [mailto:raghavendra.rao@enterprisedb.com]
Enviado el: lunes, 25 de junio de 2012 12:31 a.m.
Para: Anibal David Acosta
CC: pgsql-general@postgresql.org
Asunto: Re: [GENERAL] Help, server doesn't start

 

 

On Mon, Jun 25, 2012 at 8:23 AM, Anibal David Acosta <aa@devshock.com> wrote:

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.

 

Side-by, you should also consider upgrading PG versions to latest. 

 

 

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

 

 

Can you also paste whats there in shared_preload_libraries setting from $PGDATA/postgresql.conf ?

 

---

Regards,

Raghavendra

EnterpriseDB Corporation

Re: Help, server doesn't start

From
Raghavendra
Date:

On Mon, Jun 25, 2012 at 10:10 AM, Anibal David Acosta <aa@devshock.com> wrote:

Yes, we must upgrade.

The value of the shared_preload_libraries is

 

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


First and most important is take complete backup of the $PGDATA before giving any tries. 
Can you put blank in and try restarting .
Eg:-
shared_preload_libraries=''
 

 

An additional information….

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


Thats strange... Seems some disk issue. 

 

Also is possible that someone (I am checking now) restart the Postgres service in the middle of this situation


Not sure...
 

 

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

 

Sounds good. 
 
--Raghav

Re: Help, server doesn't start

From
Craig Ringer
Date:
On 06/25/2012 12:40 PM, Anibal David Acosta wrote:

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

Re: Help, server doesn't start

From
"Anibal David Acosta"
Date:

shared_preload_libraries changed to '', no more errors about this issue but server doesn’t start

 

So, reading some information I found that pg_resetxlog maybe solve the problem, and maybe some data will lose

 

I execute the pg_resetxlog and now everything is working, just god will know what I lose J, but due te importance of the service we decide this instead of a long service down

 

Thanks!

 

 

 

De: Craig Ringer [mailto:ringerc@ringerc.id.au]
Enviado el: lunes, 25 de junio de 2012 12:54 a.m.
Para: Anibal David Acosta
CC: 'Raghavendra'; pgsql-general@postgresql.org
Asunto: Re: [GENERAL] Help, server doesn't start

 

On 06/25/2012 12:40 PM, Anibal David Acosta wrote:

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

Re: Help, server doesn't start

From
"Anibal David Acosta"
Date:

Some possible scenario of my last problem.

 

1.       Everything was working fine

2.       Server get out of space on the Postgres installation disk

3.       Some body try to stop and restart the Postgres due to inconvenientes with related services (ignoring the out of space problem)

4.       Postgres server doesn’t respond to the windows service STOP command and this people kill postgres processes

5.       The “Some body” try to start again the postgres service, Postgres maybe try to repair last abrupt shutdown but has no space to do anything

6.       Steps 4 & 5 maybe was repeated many times

 

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?

 

 

Thanks!

 

De: Craig Ringer [mailto:ringerc@ringerc.id.au]
Enviado el: lunes, 25 de junio de 2012 12:54 a.m.
Para: Anibal David Acosta
CC: 'Raghavendra'; pgsql-general@postgresql.org
Asunto: Re: [GENERAL] Help, server doesn't start

 

On 06/25/2012 12:40 PM, Anibal David Acosta wrote:

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

Re: Help, server doesn't start

From
Craig Ringer
Date:
On 06/25/2012 08:02 PM, Anibal David Acosta wrote:

  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

Re: Help, server doesn't start

From
Alban Hertroys
Date:
> 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.

Re: Help, server doesn't start

From
Craig Ringer
Date:
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