Node js with postgres handling database reconnections

[Unwarped]

Hi there!

I just started using render yesterday after using heroku for a while. The daily cycles were getting pretty annoying for my online websocket game I’m developing interrupting online play. So I want to give render a go!

I made a static site for the client game files, a web service for the node server and a postgreSQL database for the server database.

Everything was looking good yesterday until I logged into my game this morning, which the node server authenticates with the postgres database.

The database had disconnected from the server since my last query about 15 hours earlier.

I tried adding some code such as:

	client.on('error', (err, cli) => {
		console.log('postgres connection error : ' + err)

		client = null;
		
		client = new Client({
			connectionString: process.env.DATABASE_URL
		});
		
		client.connect(err => {
			console.log("RECONNECTING ATTEMPT")
			if (err) {
				console.error('connection error', err.stack)
			} else {
				console.log('connected')
			}
		});
		*/
	})

But it just gets the error “the database system is shutting down”. And I need to restart the server which I’m trying to avoid.

Do the postgres databases restart manually? I noticed in the logs every 10 seconds it gets spammed with a connection/disconnection from a different port while not in use.

Or could it be because the database is on the free tier, which I’m using to test alongside the starter 7$ plan for the server?

I’m trying to have minimum downtime if possible as it’s an online persistent game. Please let me know if you have any help or suggestions. Thanks!

Edit: I just got it again “Error: Connection terminated unexpectedly”

[John_B]

It can depend on the client libraries that you’re using to communicate between your service and the database and whether or not it holds connections open once they’ve been used. We don’t restart databases typically and I’m not seeing any issues with the DB at the moment. How are things looking now for you?

One thing to be mindful of is that our free databases only exist for 90 days, after which they expire (see https://render.com/docs/free#free-postgresql-databases for more details).

You will see frequent logs lines in the Postgres logs as these are the health checks that we use to make sure your database is available.

John B

[Unwarped]

Hi @John_B thank you for your response.

I’m using the pg package pg - npm

It could be coincidence but I enabled keepAlive: true and the database has not disconnected since!

That makes sense about the health checks and the 90 days limit good point.

I really would like to figure out coding it to auto-reconnect in case there is any kind of maintenance or temporary unavailability. Do you have any recommendations or examples/resources of how to handle it correctly?

Thanks again! I’m really enjoying working with render so far.

[John_B]

That’s great to hear.

We do perform maintenance periodically, but where we know about it we would inform you beforehand. Of course, hardware can fail and instances will need to be brought up on new hardware so you should always assume that a database can disappear - I’ve certainly never seen anything that requires you to restart anything as the connection should just be reattempted when it’s next needed.

John B

[Unwarped]

I did some more testing and it will never reconnect on it’s own unfortunately.

But I had some luck moving the reconnect code into the client.on(‘end’ callback

		client.on('end', (err, cli) => {
			console.error('DATABASE CONNECTION ENDED. RETRYING IN 2 SECONDS...');
			setTimeout(connect_stuff, 2000);
		})

Previously I was attempting to reconnect in the on error callback and without the setTimeout.
So now when the connection ends, it tries every few seconds and eventually database functionality is restored (about 10-20 seconds). It works for now and without making a crazy amount of reconnection attempts!

Thanks for your replies @John_B I appreciate your help!