Postgresql connection timed out in node.js and pg

Question

I am new to node, postgresql, and to the whole web development business. I am currently writing a simple app which connects to a postgres database and display the content of a table in a web view. The app will be hosted in OpenShift.

My main entry is in server.js:

var pg = require('pg');
pg.connect(connection_string, function(err, client) {
    // handle error
    // save client: app.client = client;
});

Now, to handle the GET / request:

function handle_request(req, res){
    app.client.query('...', function(err, result){
        if (err) throw err; // Will handle error later, crash for now
        res.render( ... );  // Render the web view with the result
    });
}

My app seems to work: the table is rendered in the web view correctly, and it works for multiple connections (different web clients from different devices). However, if there is no request for a couple of minutes, then subsequent request will crash the app with time out information. Here is the stack information:

/home/hai/myapp/server.js:98
            if (err) throw err;
                           ^
Error: This socket is closed.
    at Socket._write (net.js:474:19)
    at Socket.write (net.js:466:15)
    at [object Object].query (/home/hai/myapp/node_modules/pg/lib/connection.js:109:15)
    at [object Object].submit (/home/hai/myapp/node_modules/pg/lib/query.js:99:16)
    at [object Object]._pulseQueryQueue (/home/hai/myapp/node_modules/pg/lib/client.js:166:24)
    at [object Object].query (/home/hai/myapp/node_modules/pg/lib/client.js:193:8)
    at /home/hai/myapp/server.js:97:17
    at callbacks (/home/hai/myapp/node_modules/express/lib/router/index.js:160:37)
    at param (/home/hai/myapp/node_modules/express/lib/router/index.js:134:11)
    at pass (/home/hai/myapp/node_modules/express/lib/router/index.js:141:5)

Is there a way to keep the connection from timed out (better)? Or to reconnect on demand (best)? I have tried to redesign my app by not connecting to the database in the beginning, but upon the GET / request. This solution works only for the first request, then crashed on the second. Any insight is appreciated.

Community · Accepted Answer · 2017-05-23 12:30:03Z

3

Have you looked into the postgres keepalive setting values? It sends packets to keep idle connections from timing out. http://www.postgresql.org/docs/9.1/static/runtime-config-connection.html

I also found this similar question: How to use tcp_keepalives settings in Postgresql?

You could also perform really minor queries from the db at a set interval. However, this method is definitely more hacked.

Edit: You could also try initiating the client like this:

var client = new pg.Client(conString);

Before you make your queries, you can check if the client is still connected. I believe you can use:

if(client.connection._events != null)
    client.connect();

edited May 23, 2017 at 12:30

CommunityBot

11 silver badge

answered Dec 30, 2012 at 2:00

sheldonk

2,7043 gold badges24 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hai Vu Over a year ago

I have tried those and it does not seem to make any difference.

sheldonk Over a year ago

I added another method you could try.

Wisu Suntoyo · Accepted Answer · 2013-04-24 13:07:16Z

2

faced the same problem.. telling the client to close connection upon the end event

query.on('end', function() { 
  client.end();
});

did the trick for me...

answered Apr 24, 2013 at 13:07

Wisu Suntoyo

1581 gold badge2 silver badges7 bronze badges

2 Comments

Hai Vu Over a year ago

It took sometime, but I finally learned to do this. Basically, for every request, there must be a new connection, processing, then closing.

Sebastian Over a year ago

Would it also work to simply do query.on('end', client.end)?

Elias Vargas · Accepted Answer · 2022-09-21 13:47:43Z

2

I'm using the parameter keepAlive in true and it works.

This is my configuration and it is solved.

const client_pg = new Client({
    connectionString,
    keepAlive: true,
    keepAliveInitialDelayMillis: 10000
  });

answered Sep 21, 2022 at 13:47

Elias Vargas

1,2101 gold badge21 silver badges38 bronze badges

1 Comment

user11857517 Over a year ago

i tried this to no avail

DeadPassive · Accepted Answer · 2015-06-29 09:11:40Z

0

You can also change the default idle timeout of 30 seconds to whatever value you need. E.g.

pg.defaults.poolIdleTimeout = 600000; // 10 mins

answered Jun 29, 2015 at 9:11

DeadPassive

8873 gold badges9 silver badges22 bronze badges

Collectives™ on Stack Overflow

Postgresql connection timed out in node.js and pg

4 Answers 4

2 Comments

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related