Re: [GENERAL] Help, server doesn't start
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:ring...@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: [GENERAL] Help, server doesn't start
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:ring...@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: [GENERAL] Help, server doesn't start
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/9.1/static/release-8-4.html http://www.postgresql.org/docs/ http://www.postgresql.org/docs/9.1/static/release-9-0.htmlcurrent http://www.postgresql.org/docs/9.1/static/release-8-4.html/static/release-9-0.html http://www.postgresql.org/docs/9.1/static/release-9-0.html http://www.postgresql.org/docs/ http://www.postgresql.org/docs/9.1/static/release-9-1.htmlcurrent http://www.postgresql.org/docs/9.1/static/release-8-4.html/static/release-9-1.html http://www.postgresql.org/docs/9.1/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: [GENERAL] Help, server doesn't start
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Help, server doesn't start
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 0xC00D 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: [GENERAL] Help, server doesn't start
On Mon, Jun 25, 2012 at 8:23 AM, Anibal David Acosta a...@devshock.comwrote: 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 0xC00D 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 Blog: http://raghavt.blogspot.com/
Re: [GENERAL] Help, server doesn't start
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 0xC00D 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. -http://sharingtechknowledge.blogspot.com/
Re: [GENERAL] Help, server doesn't start
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@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 a...@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 0xC00D 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 Blog: http://raghavt.blogspot.com/
Re: [GENERAL] Help, server doesn't start
On Mon, Jun 25, 2012 at 10:10 AM, Anibal David Acosta a...@devshock.comwrote: 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: [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
