Обсуждение: SSL connection lost after long-lasting copy command
Hi,
While copying a large'ish file (~ 7.6 million rows) into a table with:
\copy [tablename] ([columns]) FROM '/path/to/large/file' NULL AS ''
The command stopped running after a few minutes with the message:
---<--------------------cut here---------------start------------------->---
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
---<--------------------cut here---------------end--------------------->---
Checking /var/log/postgresql/postgresql-8.4-main.log, there's a long
series of checkpoint_segments (only the last 2 shown) and an SSL error:
---<--------------------cut here---------------start------------------->---
2010-02-09 10:47:03 CST LOG: checkpoints are occurring too frequently (6 seconds apart)
2010-02-09 10:47:03 CST HINT: Consider increasing the configuration parameter "checkpoint_segments".
2010-02-09 10:47:10 CST LOG: checkpoints are occurring too frequently (7 seconds apart)
2010-02-09 10:47:10 CST HINT: Consider increasing the configuration parameter "checkpoint_segments".
2010-02-09 10:47:17 CST LOG: SSL renegotiation failure
2010-02-09 10:47:17 CST LOG: SSL renegotiation failure
2010-02-09 10:47:17 CST LOG: SSL error: internal error
2010-02-09 10:47:17 CST LOG: could not send data to client: Connection reset by peer
2010-02-09 10:47:17 CST LOG: SSL error: internal error
2010-02-09 10:47:17 CST LOG: could not receive data from client: Connection reset by peer
2010-02-09 10:47:17 CST LOG: unexpected EOF on client connection
---<--------------------cut here---------------end--------------------->---
Any pointers appreciated. Thanks.
--
Seb
Seb escribió: > ---<--------------------cut here---------------start------------------->--- > 2010-02-09 10:47:03 CST LOG: checkpoints are occurring too frequently (6 seconds apart) > 2010-02-09 10:47:03 CST HINT: Consider increasing the configuration parameter "checkpoint_segments". > 2010-02-09 10:47:10 CST LOG: checkpoints are occurring too frequently (7 seconds apart) > 2010-02-09 10:47:10 CST HINT: Consider increasing the configuration parameter "checkpoint_segments". > 2010-02-09 10:47:17 CST LOG: SSL renegotiation failure > 2010-02-09 10:47:17 CST LOG: SSL renegotiation failure > 2010-02-09 10:47:17 CST LOG: SSL error: internal error > 2010-02-09 10:47:17 CST LOG: could not send data to client: Connection reset by peer > 2010-02-09 10:47:17 CST LOG: SSL error: internal error > 2010-02-09 10:47:17 CST LOG: could not receive data from client: Connection reset by peer > 2010-02-09 10:47:17 CST LOG: unexpected EOF on client connection > ---<--------------------cut here---------------end--------------------->--- Try the advice here: http://archives.postgresql.org/message-id/41EE6009-A3E0-4C3A-8A83-BB39D934B461@mac.com -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On Tue, Feb 9, 2010 at 10:03 AM, Seb <spluque@gmail.com> wrote: > Hi, > > While copying a large'ish file (~ 7.6 million rows) into a table with: > > \copy [tablename] ([columns]) FROM '/path/to/large/file' NULL AS '' > > The command stopped running after a few minutes with the message: > > ---<--------------------cut here---------------start------------------->--- > server closed the connection unexpectedly > This probably means the server terminated abnormally > before or while processing the request. > ---<--------------------cut here---------------end--------------------->--- > > Checking /var/log/postgresql/postgresql-8.4-main.log, there's a long > series of checkpoint_segments (only the last 2 shown) and an SSL error: > > ---<--------------------cut here---------------start------------------->--- > 2010-02-09 10:47:03 CST LOG: checkpoints are occurring too frequently (6 seconds apart) > 2010-02-09 10:47:03 CST HINT: Consider increasing the configuration parameter "checkpoint_segments". > 2010-02-09 10:47:10 CST LOG: checkpoints are occurring too frequently (7 seconds apart) > 2010-02-09 10:47:10 CST HINT: Consider increasing the configuration parameter "checkpoint_segments". > 2010-02-09 10:47:17 CST LOG: SSL renegotiation failure > 2010-02-09 10:47:17 CST LOG: SSL renegotiation failure > 2010-02-09 10:47:17 CST LOG: SSL error: internal error > 2010-02-09 10:47:17 CST LOG: could not send data to client: Connection reset by peer > 2010-02-09 10:47:17 CST LOG: SSL error: internal error > 2010-02-09 10:47:17 CST LOG: could not receive data from client: Connection reset by peer > 2010-02-09 10:47:17 CST LOG: unexpected EOF on client connection > ---<--------------------cut here---------------end--------------------->--- > > Any pointers appreciated. Thanks. Are there any PANICs in the log before this? Doesn't seem likely from your description, but just wanted to be sure.
On Tue, 9 Feb 2010 10:28:50 -0700, Scott Marlowe <scott.marlowe@gmail.com> wrote: > Are there any PANICs in the log before this? Doesn't seem likely from > your description, but just wanted to be sure. No, there's no such lines in the log. AFAICS, the data did make into the table, after psql re-established connection with the server. Reading through the thread Alvaro suggested, I cannot see what could be done about this. Any clarification would be appreciated. Cheers, -- Seb
Seb escribió: > On Tue, 9 Feb 2010 10:28:50 -0700, > Scott Marlowe <scott.marlowe@gmail.com> wrote: > > > Are there any PANICs in the log before this? Doesn't seem likely from > > your description, but just wanted to be sure. > > No, there's no such lines in the log. > > AFAICS, the data did make into the table, after psql re-established > connection with the server. > > Reading through the thread Alvaro suggested, I cannot see what could be > done about this. Any clarification would be appreciated. Err, remove the renegotiation code from the server. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Tue, 9 Feb 2010 14:44:23 -0300, Alvaro Herrera <alvherre@commandprompt.com> wrote: [...] > Err, remove the renegotiation code from the server. Oh I meant something from a user's point of view, not hacking the source code. If there's not a user option, I don't mind the connection loss as long as the data do get copied to the table. Thanks. -- Seb
Seb escribió: > On Tue, 9 Feb 2010 14:44:23 -0300, > Alvaro Herrera <alvherre@commandprompt.com> wrote: > > [...] > > > Err, remove the renegotiation code from the server. > > Oh I meant something from a user's point of view, not hacking the source > code. If there's not a user option, I don't mind the connection loss as > long as the data do get copied to the table. Thanks. I guess there's always the option of using a connection that doesn't go over SSL ... -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Feb 9, 2010, at 9:55 AM, Seb wrote: > On Tue, 9 Feb 2010 14:44:23 -0300, > Alvaro Herrera <alvherre@commandprompt.com> wrote: > > [...] > >> Err, remove the renegotiation code from the server. > > Oh I meant something from a user's point of view, not hacking the source > code. If there's not a user option, I don't mind the connection loss as > long as the data do get copied to the table. Thanks. Your only real option that doesn't require recompilation right now is to avoid using SSL. Your operating system provider is shipping openssl libraries that are (intentionally) broken. If you want to use the versions of openssl and postgresql they provide they won't work with long running SSL connections until they've fixed one or the other. Cheers, Steve