Nodejs application using "node postgres" having a "Connection terminated unexpectedly" every 60min in GKE

Question

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

Answer 1

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;
            })
    }