Persistent connection to Postgres database

The node.js application establishes a persistent connection to Postgres for subscribing to Postgres notifications events. It works fine but every 30 minutes Connection terminated unexpectedly error occurs.
My question is, are there any platform time limits for persistent connection to Postgres after which the connection is closed?

The haproxy instance running with postgres has a 30 min idle timeout: postgres-ha/haproxy.cfg at main · fly-apps/postgres-ha · GitHub

Which Node driver are you using? Most of them have pool/reconnect settings you can use to handle connections dropping. It’s probably good practice to do anyway since timeouts are only one possible reason for a drop.

If you really want to avoid the proxy, you can connect to port 5433. But if you’re running multiple Postgres instances, you’ll need to teach your app how to connect to all of them and detect which one is writable. The haproxy handles that for you.

1 Like

I use the node-postgres client which is based on the libpq library. As I mentioned, it works fine because reconnection functionality was initially laid down. What was not clear to me is the reason for connection termination, but I get it now. Thank you for your exhaustive response.

1 Like

I’m also using Node with PG events.

I’m using this driver which I like a lot better than the usual node-postgres:

The only hiccup I had was getting connection closed error. Here are the details.

This is how I solved it:

export const sql = postgres(process.env.DATABASE_URL, {
	idle_timeout: 20
});

The author of the driver suggested to use 120 seconds for the idle_timeout. That seemed excessive so I played a bit with that and 20 seconds has been working flawlessly with PG on Fly for weeks.

Thank you for the link. There is good advice in the discussion - ping the listener to prevent a connection from idle.

1 Like