Обсуждение: Postgresql, sql client and I/O exceptions

On Thu, Jul 18, 2013 at 9:20 AM, Paul Maddock
<paul.maddock@spidasoftware.com> wrote:
> I am having trouble with postgresql connection.
>
> I am using squirrel sql client to write sql code for data qc'ing.  On many
> occasions this has happened.  It closes my connection on my client and stops
> my sql transaction.
>
> There are roughly 3 million records in the database I am editing.
>
> Version of Postgresql:
> psql (PostgreSQL) 9.2.4
>
> JDBC for squirrel sql client:
> postgresql-9.2-1003.jdbc4.jar
>
> --------------------------------------
> syslog
> 276 Jul 18 09:10:08 test-minapps kernel: [173502.656976] postgres[15212]:
> segfault at 7f4c34e8a44e ip 00007f4c02d13605 sp 00007fff92ba69f0 error 4 in
> postgres[7f4c02c86000+54e000]
> 277 Jul 18 09:10:12 test-minapps lsassd[1136]: 0x7f1607fff700:Error code
> 40286 occurred during attempt 0 of a ldap search. Retrying.
>
> postgresql log
> """
> 2013-07-18 09:10:24 EDT LOG:  terminating any other active server processes
> 2013-07-18 09:10:24 EDT WARNING:  terminating connection because of crash of
> another server process
> 2013-07-18 09:10:24 EDT DETAIL:  The postmaster has commanded this server
> process to roll back the current transaction and exit, because another
> server process exited abnormally and possibly corrupted shared memory.
> """
> * ~20
>
> 2013-07-18 09:10:26 EDT LOG:  all server processes terminated;
> reinitializing
> 2013-07-18 09:10:28 EDT LOG:  database system was interrupted; last known up
> at 2013-07-18 09:03:54 EDT
> 2013-07-18 09:10:28 EDT LOG:  database system was not properly shut down;
> automatic recovery in progress
> 2013-07-18 09:10:28 EDT LOG:  redo starts at 39/BBAE7D60
> 2013-07-18 09:10:29 EDT LOG:  record with zero length at 39/BD17A7D8
> 2013-07-18 09:10:29 EDT LOG:  redo done at 39/BD17A7A8
> 2013-07-18 09:10:29 EDT LOG:  last completed transaction was at log time
> 2013-07-18 09:10:10.968978-04
> 2013-07-18 09:11:08 EDT LOG:  database system is ready to accept connections
>
>
> squirrel sql log
> Error: An I/O error occured while sending to the backend. SQLState:  08006
> ErrorCode: 0
> -------------------------------------
>
> I'm researching the issue myself, but if you might know the cause it would
> help me tremendously.

database is crashing.  typical culprits are 3rd party extensions,
userland C routines, bad hardware, non-up-to-date postgres, etc.
assuming none of those apply to you, next steps are to use various
debugging tools (gdb especially and strace), and logging.  Ideally you
could grab a core for postgres and post a backtrace.   Even more
ideally you could provide a reproducible crash case.

merlin

On Thu, 18 Jul 2013 10:20:07 -0400
Paul Maddock <paul.maddock@spidasoftware.com> wrote:

> I am having trouble with postgresql connection.
>
> I am using squirrel sql client to write sql code for data qc'ing.  On many
> occasions this has happened.  It closes my connection on my client and
> stops my sql transaction.
>
> There are roughly 3 million records in the database I am editing.
>
> Version of Postgresql:
> psql (PostgreSQL) 9.2.4
>
> JDBC for squirrel sql client:
> postgresql-9.2-1003.jdbc4.jar
>
> --------------------------------------
> *syslog*
> 276 Jul 18 09:10:08 test-minapps kernel: [173502.656976] postgres[15212]:
> segfault at 7f4c34e8a44e ip 00007f4c02d13605 sp 00007fff92ba69f0 error 4 in
> postgres[7f4c02c86000+54e000]
> 277 Jul 18 09:10:12 test-minapps lsassd[1136]: 0x7f1607fff700:Error code
> 40286 occurred during attempt 0 of a ldap search. Retrying.
>
> *postgresql log*
> *"""
> 2013-07-18 09:10:24 EDT LOG:  terminating any other active server processes
> 2013-07-18 09:10:24 EDT WARNING:  terminating connection because of crash
> of another server process
> 2013-07-18 09:10:24 EDT DETAIL:  The postmaster has commanded this server
> process to roll back the current transaction and exit, because another
> server process exited abnormally and possibly corrupted shared memory.
> """
> *
> ** ~20**
>
> 2013-07-18 09:10:26 EDT LOG:  all server processes terminated;
> reinitializing
> 2013-07-18 09:10:28 EDT LOG:  database system was interrupted; last known
> up at 2013-07-18 09:03:54 EDT
> 2013-07-18 09:10:28 EDT LOG:  database system was not properly shut down;
> automatic recovery in progress
> 2013-07-18 09:10:28 EDT LOG:  redo starts at 39/BBAE7D60
> 2013-07-18 09:10:29 EDT LOG:  record with zero length at 39/BD17A7D8
> 2013-07-18 09:10:29 EDT LOG:  redo done at 39/BD17A7A8
> 2013-07-18 09:10:29 EDT LOG:  last completed transaction was at log time
> 2013-07-18 09:10:10.968978-04
> 2013-07-18 09:11:08 EDT LOG:  database system is ready to accept connections
>
>
> *
> *squirrel sql log*
> Error: An I/O error occured while sending to the backend. SQLState:  08006
> ErrorCode: 0
> -------------------------------------
>
> I'm researching the issue myself, but if you might know the cause it would
> help me tremendously.
>

If it's under Linux, check that Out of Memory Killer don't slain some postmaster process. Disable it and try again.


> --
> Paul Anthony Maddock


---   ---
Eduardo Morras <emorrasg@yahoo.es>