SSL connection lost after long-lasting copy command

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