Configuring PostgreSQL server parameters on GitHub Actions

If your project is not fully containerized, but you still want to use PostgreSQL in your GitHub Actions workflow, you can use the services feature of GitHub Actions to easily spin up a PostgreSQL container.

However, the services functionality restricts what you can configure declaratively in the workflow file -- namely you cannot configure the PostgreSQL server parameters that you would usually set up in the postgresql.conf file. Fortunately, most of these can be configured through ALTER SYSTEM commands.

For example, this is how to configure max_locks_per_transaction:

jobs:
  build:
    runs-on: ubuntu-latest

    services:
      postgres:
        image: postgres:17
        env:
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: postgres
          POSTGRES_DB: production
        ports:
          - 5432:5432
        options: >-
          --name pg_container
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5

    steps:
      - name: Set up environment
        run: |
          sudo apt-get update
          sudo apt-get install -y \
            postgresql-client \
            wait-for-it

      - name: Configure PostgreSQL
        env:
          PGUSER: postgres
          PGPASSWORD: postgres
          PGHOST: 127.0.0.1
          PGPORT: 5432
          PGDATABASE: template1
        run: |
          psql -c "SHOW max_locks_per_transaction;"
          psql -c "ALTER SYSTEM set max_locks_per_transaction = 128;"
          docker restart pg_container
          wait-for-it localhost:5432 --timeout=30 --strict -- echo "PostgreSQL is up"
          psql -c "SHOW max_locks_per_transaction;"

# ... the rest of the workflow

Of course, if you need heavy customization, it makes more sense to skip services and run your own container through a docker run step or docker-compose, but for simple use cases, this is a quick way to get started.