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.

Answer 1

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
}