GitHub Action using pre-installed Postgres

Until now I used something like this for Postgres in GitHub Actions:

jobs:
  the_job_that_needs_postgres:
    runs-on: ubuntu-latest

    services:
      postgres:
        image: postgres:latest
        env:
          POSTGRES_PASSWORD: ${{ env.POSTGRES_PASSWORD }}
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        ports:
          - 5432:5432

    steps:
      # your steps

Then I saw that the GitHub's Ubuntu Runner Image actually comes with a pre-installed Postgres version, but the service is not activated by default.

So instead of using the postgres:latest image as a service, we could use that pre-installed Postgres.

In order to make that work, we have to add one step to the CI job that starts the postgresql.service and one step that creates a runner user in the database, so that the workflow's session user can connect to the database.

jobs:
  the_job_that_needs_postgres:
    runs-on: ubuntu-latest

    steps:
      - name: Start PostgreSQL
        run: |
          sudo systemctl start postgresql.service
          pg_isready
          sudo -u postgres createuser -s -d -r -w runner

      # your steps

Here's what the createuser flags do:

• -s : User will be a superuser
• -d : User can CREATE DATABASE
• -r : User can CREATE ROLE
• -w : It won't prompt for a password.

This approach takes only a few seconds (3 - 5 seconds) to startup whereas the former setup takes between 20 - 30 seconds.

On the downside you can't specify the Postgres version, you have to live with the version that comes with the runner image.

At the time of writing ubuntu-22.04 contains Postgres 14.6.

Database config for Rails

For Rails projects this is how a minimal database.yml could look like using the setup above:

test:
  adapter: postgresql
  encoding: unicode
  database: github_actions_ci_db
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username: runner

Behold:

• username is set to runner
• hostname is not required

(It might even not work if you provide the hostname, like localhost or 127.0.0.1.)

Credits

(Cover photo by Nathan Queloz on Unsplash)

Comments

[Tarun]

I tried it using node js but on calling the db it is throwing an error for Start your Database: SequelizeConnectionError: password authentication failed for user "***"