Get the PostgreSQL server version from connection?

Question

Is there anything in the modern PostgreSQL connection protocol that would indicate the server version?

And if not, is there a special low-level request that an endpoint can execute against an open connection to pull the server details that would contain the version?

I'm looking at a possible extension of node-postgres that would automatically provide the server version upon every fresh connection. And I want to know if this is at all possible.

Having to execute SELECT version() upon every new connection and then parsing it is too high-level for the base driver that manages the connection. It should be done on the protocol level.

You don't really need to parse the output from `version()`. You can use `show server_version_num` or `show server_version` to get a more "friendly" number. The underlying Postgres protocol does provide the server version and the connection object can return that without a query: https://www.postgresql.org/docs/current/libpq-status.html#LIBPQ-PQSERVERVERSION but I don't know if that is available from within Node.js — , Nov 06 '19 at 08:22
It was just suggested [elsewhere](https://github.com/brianc/node-postgres/issues/2002) that the [start-up message](https://www.postgresql.org/docs/current/protocol-flow.html#id-1.10.5.7.3) might contain the version. — vitaly-t, Nov 06 '19 at 08:25
Then on the other hand, running `show server_version` after the connection was established, isn't really such a big overhead. — , Nov 06 '19 at 08:27
@a_horse_with_no_name We should not need to execute any query, the server should provide the version automatically, and I believe it does, just need to figure out how exactly. — vitaly-t, Nov 06 '19 at 08:27
I understand that that's the thing you ultimately want, but until node-postgres is changed to expose that, running `show server_version` isn't such a big thing as you think. — , Nov 06 '19 at 08:40
@a_horse_with_no_name [I have found out that it already does](https://github.com/brianc/node-postgres/issues/2002) :) — vitaly-t, Nov 06 '19 at 09:15

vitaly-t · Accepted Answer · 2022-10-31T18:19:59.683

After a bit of research, I found that PostgreSQL does provide server version during connection, within the start-up message.

And specifically within node-postgres driver, we can make Pool provide a custom Client that handles event parameterStatus on the connection, and exposes the server version:

const {Client, Pool} = require('pg');

class MyClient extends Client {
    constructor(config) {
        super(config);
        this.connection.on('parameterStatus', msg => {
            if (msg.parameterName === 'server_version') {
                this.version = msg.parameterValue;
            }
        });
    }
}

const cn = {
    database: 'my-db',
    user: 'postgres',
    password: 'bla-bla',
    Client: MyClient // here's our custom Client type
};

const pool = new Pool(cn);

pool.connect()
    .then(client => {
        console.log('Server Version:', client.version);
        client.release(true);
    })
    .catch(console.error);

On my test PC, I use PostgreSQL v11.2, so this test outputs:

Server Version: 11.2

UPDATE - 1

Library pg-promise has been updated to support the same functionality in TypeScript. And you can find a complete example in this ticket.

UPDATE - 2

See example here:

// tests connection and returns Postgres server version,
// if successful; or else rejects with connection error:
async function testConnection() {
    const c = await db.connect(); // try to connect
    c.done(); // success, release connection
    return c.client.serverVersion; // return server version
}

Get the PostgreSQL server version from connection?

1 Answers1