Skip to main content

PostgreSQL

The PostgreSQL analyzer is available to check version and connection status of a PostgreSQL database. It relies on the data collected by the PostgreSQL collector.

The analyzer's outcome when clause may be used to evaluate the database connection status or a semver range to compare against the running version, and supports standard comparison operators.

Parameters​

checkName: Optional name.

collectorName: (Recommended) Must match the collectorName specified by the postgres collector.

Outcomes​

The when value in an outcome of this analyzer contains the connection or version information.

The conditional in the when value supports the following:

connected: A boolean representing whether the database is connected. Can be compared to a boolean value with the == operator.

version: A string representing the semantic version of the database. Can be compared to a semver string using <, <=, >, >=, ==, !=, with the letter 'x' as a version wildcard (10.x). The 'x' is parsed as '0'.

Example Analyzer Definition​

apiVersion: troubleshoot.sh/v1beta2
kind: Preflight
metadata:
  name: supported-postgres-version
spec:
  collectors:
    - postgres:
        collectorName: postgresql
        uri: 'postgresql://user:password@hostname:5432/dbname?sslmode=require'
  analyzers:
    - postgres:
        checkName: Must be PostgreSQL 10.x or later
        collectorName: postgresql
        outcomes:
          - fail:
              when: connected == false
              message: Cannot connect to PostgreSQL server
          - fail:
              when: version < 10.x
              message: The PostgreSQL server must be at least version 10
          - pass:
              message: The PostgreSQL server is ready

Test PostgreSQL Analyzer locally​

If you want to test it locally, you can spin up a postgres database running the following Docker command. Be sure to specify the image version postgres:<version_tag>. In this case, the version is 11.9:

docker run --rm --name some-postgres -p 5432:5432 -e POSTGRES_PASSWORD=mysecretpassword -e POSTGRES_USER=postgres -d postgres:11.9

You should use the following uri in the collector:

uri: postgresql://postgres:mysecretpassword@localhost:5432/postgres?sslmode=disable

Once it's running, you can run preflight and test the results.