intermittent "connection reset by peer" sql postgres

Question

After a period of inactivity, my go web service is getting a net.OpError with message read tcp x.x.x.x:52086->x.x.x.x:24414: read: connection reset by peer when executing the first postgres sql query. After the error, the subsequent requests will work fine.

The postgres database is hosted with compose.com which has haproxy in front of the postgres db. My go web app is using standard sql and sqlx.

I've tried running a ticker invoking db.Ping() every 15 minutes, but this hasn't fixed the issue.

Why is the go standard sql lib not handling these connection drops?

Deme

What are you hoping for here? If the connection was reset, you need to reconnect. – Andromada

I'm using the go standard lib sql.Open stuff which is meant to handle connecting pooling, so I can't just simply reconnect. Also as I mentioned, after the error, the subsequent queries go through fine. I'll update the question for clarity. – Deme

Yes, the way to handle that with the Go standard library is to detect a connection failure, and retry the operation. – Andromada

Hmm, ok. Last paragraph on here appears to suggest the opposite. go-database-sql.org/errors.html – Deme

Wild guess: the connection to haproxy is still alive, but the connection to the backend times out and haproxy doesn't close the other end. I've seen similar issues with redis. What happens if you don't use haproxy? Regular pings won't help much because there is no guarantee that all connections in either pool see some traffic before they time out. – Surrebuttal

Yeah, I think I may have just come to a similar conclusion. I'm thinking of setting db.SetConnMaxLifetime(time.Minute * 15) which I think should flush out the dead connections. – Deme

Hi @Peter, looks like that did the trick. If you want to comment that as the answer, I'll give you the points. – Deme

Does this answer your question? HAProxy closes long living TCP connections ignoring TCP keepalive – Phoney

Answer 1

Since no one wrote that explicity. The solution to this problem is setting db.SetConnMaxLifetime(time.Minute). I tried it and it works. Connection reset occurs often on AWS where there is inactivity limit set to 350 seconds, after that TCP RST is returned.

Gloom