:books: Today I Learned

How to run PostgreSQL 13 on Travis CI

programming Travis CI travis-ci PostgreSQL postgresql testing

Travis CI is a continuous integration platform for open source. I use it for all my open source projects that have an automated test suite (here’s an example). Read about the core concepts and the tutorial if you want to know more.

It supports PostgreSQL as a database for your tests. Unfortunately, according to the documentation at the time of writing, the default integration is with PostgreSQL 9.2, a version that is no longer under active development since November 2017. Versions 10 and later are also not as well supported in that they require additional configuration to avoid this error:

$ travis_setup_postgresql 13
Starting PostgreSQL v13
Assertion failed on job for postgresql@13-main.service.
sudo systemctl start postgresql@13-main

Let’s see about fixing that.

The default PostgreSQL integration is enabled by adding the postgresql service to your .travis-ci.yml file:

services:
  - postgresql

The documentation indicates what to do if you want to use a different version:

  • Specify the version of the PostgreSQL addon.
  • Install the appropriate APT package.
  • Set the PGPORT environment variable to use a different port.

Here’s the configuration fragment for PostgreSQL 13:

addons:
  postgresql: 13
  apt:
    packages:
      - postgresql-13
env:
  global:
    - PGPORT=5433

The documentation states that you can use the postgres user with a blank password to access the PostgreSQL database. However when you’re not using the default PostgreSQL version, that’s a lie (like the cake). You might get the following error:

psql: error: FATAL:
Peer authentication failed for user "postgres"

You have to update the configuration yourself. For example, you can modify the pg_hba.conf file to use the trust authentication method for local connections. That way you won’t have to supply a password at all:

before_install:
  # Use trust instead of peer authentication:
  - >-
    sudo sed -i
    -e '/local.*peer/s/postgres/all/'
    -e 's/peer\|md5/trust/g'
    /etc/postgresql/13/main/pg_hba.conf
  # Restart the PostgreSQL service:
  - sudo service postgresql@13-main restart

Finally, you can create your test database like you would with the default PostgreSQL integration. You just have to specify the same custom port as the PGPORT environment variable:

before_script:
  - sudo psql -p 5433 -U postgres -c 'create database my-app;'

Here’s a full sample configuration for a hypothetical Node.js application:

language: node_js
node_js:
  - '10'
  - '12'
  - '14'

addons:
  # Use a different PostgreSQL version than the default:
  postgresql: 13
  apt:
    update: true
    packages:
      - postgresql-13
      - postgresql-13-postgis-3

services:
  - postgresql

env:
  global:
    # A different port must be used when not using the
    # default PostgreSQL:
    - PGPORT=5433
  jobs:
    - NODE_ENV=test DB_PORT=5433 DB_USERNAME=postgres

before_install:
  # Use trust instead of peer authentication:
  - >-
    sudo sed -i
    -e '/local.*peer/s/postgres/all/'
    -e 's/peer\|md5/trust/g'
    /etc/postgresql/13/main/pg_hba.conf
  # Restart the PostgreSQL service:
  - sudo service postgresql@13-main restart

before_script:
  # Create the test database:
  - sudo psql -p 5433 -U postgres -c 'create database my-app;'
Travis CI 2021-02-11
PostgreSQL 13.x