2

I have multiple applications that run using Node.js and pg (node-postgres).

The issue i have is that every app is getting the error "Connection terminated unexpectedly" every hour. Here is the error :

> node ./dist/app.js

App Started
events.js:174
      throw er; // Unhandled 'error' event
      ^

Error: Connection terminated unexpectedly
    at Connection.con.once (/app/node_modules/pg/lib/client.js:255:9)
    at Object.onceWrapper (events.js:286:20)
    at Connection.emit (events.js:198:13)
    at Socket.<anonymous> (/app/node_modules/pg/lib/connection.js:139:10)
    at Socket.emit (events.js:203:15)
    at endReadableNT (_stream_readable.js:1145:12)
    at process._tickCallback (internal/process/next_tick.js:63:19)
Emitted 'error' event at:
    at connectedErrorHandler (/app/node_modules/pg/lib/client.js:202:10)
    at Connection.con.once (/app/node_modules/pg/lib/client.js:272:9)
    at Object.onceWrapper (events.js:286:20)
    [... lines matching original stack trace ...]
    at process._tickCallback (internal/process/next_tick.js:63:19)

Here is how i connect my Client to the database :

Database.ts:

import { Client, QueryResult } from 'pg';

export default class DatabaseModule {

    private client: Client;

    constructor() {
        this.client = new Client({
            connectionString: process.env.DATABASE_URL
        });
    }

    public init(): Promise<any> {
        return this.client.connect();
    }
}

app.ts:

Promise.all([
    express.init(),
    database.init()
])
.then(() => {
    console.log("App Started");
    [load routes...];
})
.catch((error) => {
    console.error(error);
    process.exit(1);
});

All works fine on local but not on production.

On production we are running every app as micro services in Google Kubernetes Engine. Is there any config in K8s that may cause this connection loss every hour ? (even if the Client is idle or not idle, this error happens)

NAME                                                    READY   STATUS    RESTARTS   AGE
my-service-57c9f99767-wnm47                             2/2     Running   96         4d

As you can see, my app has 96 restarts: 4 days * 24 hours = 96 => error every hour that crashs the pod.

We are using a postgreSQL server hosted by Google Cloud SQL, and every app in K8s has an access to it with a local address.

EDIT:

I just found this in the Google Cloud SQL documentation : WebSockets are always available to your application without any additional setup. Once a WebSockets connection is established, it will time out after one hour.

So the error was generated from the usage of pg.Client with a persistent connection to the SQL server. I will try to use pg.Pool(). Here is the explanation of why i should use pool instead of client : https://stackoverflow.com/a/48751665/12052533

mmoussa
  • 173
  • 1
  • 3
  • 11
  • Have you tried setting `connectionTimeoutMillis` explicitly to like a few days or weeks? That's the only related option i see. You can pass the option to client constructor. – Silvan Bregy Aug 05 '21 at 14:25
  • `connectionTimeoutMillis?: number, // number of milliseconds to wait for connection, default is no timeout` : is this the time to wait for my app to connect ? Because it does connect to the database initially, then it disconnect after 60 minutes... And as you can see, the default is no timeout. – mmoussa Aug 05 '21 at 14:38
  • Look in the database server's log file for the corresponding error message. – jjanes Aug 05 '21 at 17:00
  • @mmoussa, if you fount the solution (or at least an explanation), you should rather post it as an answer to your question than EDIT. It will be much clearer for others. – mario Aug 06 '21 at 21:24

2 Answers2

2

I found the problem :

In the Google Cloud SQL documentation : WebSockets are always available to your application without any additional setup. Once a WebSockets connection is established, it will time out after one hour.

The error was generated by the usage of pg.Client() because i had a persistent connection into my database which is a bad practice. A client shall connect to the database then end its connection after it finished executing a query.

I will use pg.Pool() as it will generate clients and it is better for multiple requests. After the generation of a client i juste have to release all my clients.

I removed the database.init() and modified the database.query() function like the following :

     public query(command: string, args?: Array<any>): Promise<QueryResult> {
        if (args === undefined)
            args = [];
        return this.pool.connect()
            .then((client: Client) => {
                return this.queryClient(client, command, args)
            })
            .then((result: QueryResult) => {
                return result;
            })
            .catch((error) => {
                throw error;
            });
    }

     private queryClient(client: Client, command: string, args?: Array<any>): Promise<QueryResult> {
        return client.query(command, args)
            .then((result: QueryResult) => {
                client.release();
                return result;
            }).catch((error) => {
                client.release();
                throw error;
            })
    }
mmoussa
  • 173
  • 1
  • 3
  • 11
0

What I am seeing wrong in your code is, you didnt closed the connection after making client.connect(). Whenver we connects a client or pool, It means all our request is going throw that connection, but if you are not going to close it after usage, it will keep on pilling up, as after sometime, your database connection is going to crash !

So best practice according to me for 1-2 queries use pool.query() as it open and automatically closes the connection, we do not have to do it manually.

or whenver use have lots of sql query in single api use const client = pool.connect(), and then client.query(), close the connection by client.release();

SAURABH
  • 71
  • 6