DEV Community

Cover image for GitHub Action using pre-installed Postgres
Mario
Mario

Posted on

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)

Top comments (1)

Collapse
 
tarunsankhla21 profile image
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 "***"