PostgreSQL GitHub Actions - Continuous Integration

03.2021 / Category: How To / Tags: psql | ubuntu | windows

Intro

GitHub Actions (GHA) are altogether a piece of excellent machinery for continuous integration or other automated tasks on your repo. I started to use them from the release day on as a replacement for CircleCI. Not that I think CircleCI is a bad product; I love to have everything in one place if possible. However, using a young product is a challenge. Even now, there is no easy way to debug actions.

Table of Contents

I came up with many solutions: Docker-based actions, actions downloading binaries, etc. But this post will cover using the latest GitHub Actions Virtual Environments, which have PostgreSQL installed by default. Handy, huh? 🙂

Available GitHub Actions Virtual Environments

Here is the table listing all available GitHub Actions Virtual Environments for a moment:

Environment	YAML Label	Included Software
Ubuntu 20.04	`ubuntu-20.04`	ubuntu-20.04
Ubuntu 18.04	`ubuntu-latest` or `ubuntu-18.04`	ubuntu-18.04
Ubuntu 16.04	`ubuntu-16.04`	ubuntu-16.04
macOS 11.0	`macos-11.0`	macOS-11.0
macOS 10.15	`macos-latest` or `macos-10.15`	macOS-10.15
Windows Server 2019	`windows-latest` or `windows-2019`	windows-2019
Windows Server 2016	`windows-2016`	windows-2016

In this post, I will use three of them: windows-latest, ubuntu-latest, and macos-latest. However, you may use any of the environments available. These actions were first written for pg_timetable testing, but now they are used as a template for all Cybertec PostgreSQL-related actions.

Each of the actions below will:

start PostgreSQL and check if it's running;
create a special user scheduler;
create a test database timetable.

Of course, you may want to add more steps in real life, e.g., import test data, checkout, build, test, gather coverage, release, etc.

PostgreSQL GitHub Action for Ubuntu

  setup-postgresql-ubuntu:
    if: true # false to skip job during debug
    name: Setup PostgreSQL on Ubuntu
    runs-on: ubuntu-latest
    steps:
    - name: Start PostgreSQL on Ubuntu
      run: |
        sudo systemctl start postgresql.service
        pg_isready
    - name: Create scheduler user
      run: |
        sudo -u postgres psql --command='CREATE USER scheduler PASSWORD 'somestrong'' --command='du'
    - name: Create timetable database
      run: |
        sudo -u postgres createdb --owner=scheduler timetable
        PGPASSWORD=somestrong psql --username=scheduler --host=localhost --list timetable

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

setup-postgresql-ubuntu:

if: true # false to skip job during debug

runs-on: ubuntu-latest

steps:

- name: Start PostgreSQL on Ubuntu

run: |

sudo systemctl start postgresql.service

pg_isready

- name: Create scheduler user

run: |

sudo -u postgres psql --command='CREATE USER scheduler PASSWORD 'somestrong'' --command='du'

- name: Create timetable database

run: |

sudo -u postgres createdb --owner=scheduler timetable

PGPASSWORD=somestrong psql --username=scheduler --host=localhost --list timetable

Nothing unusual here for Ubuntu users. We use systemctl to start PostgreSQL and the pg_isready utility to check if the server is running.

To create a scheduler user, we use a psql client in non-interactive mode. We send two commands to it:

CREATE USER ...;
du — list users.

First, we create the user. Second, we output the list of users for control.

💡 To remember psql commands, try to decode them. For example, dt - describe tables, du - describe users, etc.

To create a timetable database, we use the createdb utility. Pay attention to the fact that sudo -u postgres allows us to not specify connection credentials, because a system user is allowed to connect locally without any restrictions. Then, just like in the previous step, list the databases with psql for control.

PostgreSQL GitHub Action for MacOS

  setup-postgresql-macos:
    if: true # false to skip job during debug
    name: Setup PostgreSQL on MacOS
    runs-on: macos-latest
    steps:
    - name: Start PostgreSQL on MacOS
      run: |
        brew services start postgresql
        echo 'Check PostgreSQL service is running'
        i=10
        COMMAND='pg_isready'
        while [ $i -gt 0 ]; do
            echo 'Check PostgreSQL service status'
            eval $COMMAND && break
            ((i--))
            if [ $i == 0 ]; then
                echo 'PostgreSQL service not ready, all attempts exhausted'
                exit 1
            fi
            echo 'PostgreSQL service not ready, wait 10 more sec, attempts left: $i'
            sleep 10
        done
    # Homebrew creates an account with the same name as the installing user, but no password
    - name: Create scheduler user
      run: |
        psql --command='CREATE USER scheduler PASSWORD 'somestrong'' --command='du' postgres
    - name: Create timetable database
      run: |
        createdb --owner=scheduler timetable
        PGPASSWORD=somestrong psql --username=scheduler --host=localhost --list timetable

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

setup-postgresql-macos:

if: true # false to skip job during debug

runs-on: macos-latest

steps:

- name: Start PostgreSQL on MacOS

run: |

brew services start postgresql

echo 'Check PostgreSQL service is running'

i=10

COMMAND='pg_isready'

while [ $i -gt 0 ]; do

echo 'Check PostgreSQL service status'

eval $COMMAND && break

((i--))

if [ $i == 0 ]; then

echo 'PostgreSQL service not ready, all attempts exhausted'

exit 1

fi

echo 'PostgreSQL service not ready, wait 10 more sec, attempts left: $i'

sleep 10

done

# Homebrew creates an account with the same name as the installing user, but no password

- name: Create scheduler user

run: |

psql --command='CREATE USER scheduler PASSWORD 'somestrong'' --command='du' postgres

- name: Create timetable database

run: |

createdb --owner=scheduler timetable

PGPASSWORD=somestrong psql --username=scheduler --host=localhost --list timetable

There are not so many differences from Ubuntu for MacOS:

use brew services to start the server;
the pg_isready procedure is more complicated.

As you probably noticed, we may skip sudo -u postgres prefixes since the current user has all the rights needed in this environment.

PostgreSQL GitHub Action for Windows

  setup-postgresql-windows:
    if: true # false to skip job during debug
    name: Setup PostgreSQL on Windows
    runs-on: windows-latest
    steps:
    - name: Start PostgreSQL on Windows
      run: |
        $pgService = Get-Service -Name postgresql*
        Set-Service -InputObject $pgService -Status running -StartupType automatic
        Start-Process -FilePath '$env:PGBINpg_isready' -Wait -PassThru
    - name: Create scheduler user on Windows
      run: |
        & $env:PGBINpsql --command='CREATE USER scheduler PASSWORD 'somestrong'' --command='du'
    - name: Create timetable database
      run: |
        & $env:PGBINcreatedb --owner=scheduler timetable
        $env:PGPASSWORD = 'somestrong'
        & $env:PGBINpsql --username=scheduler --host=localhost --list timetable

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

setup-postgresql-windows:

if: true # false to skip job during debug

runs-on: windows-latest

steps:

- name: Start PostgreSQL on Windows

run: |

$pgService = Get-Service -Name postgresql*

Set-Service -InputObject $pgService -Status running -StartupType automatic

Start-Process -FilePath '$env:PGBINpg_isready' -Wait -PassThru

- name: Create scheduler user on Windows

run: |

& $env:PGBINpsql --command='CREATE USER scheduler PASSWORD 'somestrong'' --command='du'

- name: Create timetable database

run: |

& $env:PGBINcreatedb --owner=scheduler timetable

$env:PGPASSWORD = 'somestrong'

& $env:PGBINpsql --username=scheduler --host=localhost --list timetable

With Windows, everything is different, but not as scary as haters usually paint it to be:

the Windows environment uses PowerShell (pwsh) by default;
Windows administrates services.

I think the code is straightforward. The only thing I want to highlight is the ampersand sign (& aka call operator) before some commands.

The call operator (&) allows you to execute a command, script, or function. Since the PostgreSQL utilities psql and createdb are not in the PATH, we need to specify the full path to them. We use the environmental variable PGBIN for that. But after the concatenation operation, we have a regular string. Therefore, a call operator allows us to execute a resulting command.

💡 PowerShell (pwsh) is available for all environments in GitHub Actions including Ubuntu and macOS. That's a pretty powerful shell, I must say! Try to give it a chance and you will be surprised.

Conclusion

There are also other ways to run PostgreSQL within GitHub Actions. Let me know if you are interested in this topic, and I will write again with even more examples.

In conclusion, I wish you all the best! ♥️
Please, stay safe! So we can meet in person at one of the conferences, meetups, or training sessions.

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.