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? 🙂
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:
scheduler
;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.
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 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 |
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.
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 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 |
There are not so many differences from Ubuntu for MacOS:
brew services
to start the server;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.
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 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 |
With Windows, everything is different, but not as scary as haters usually paint it to be:
pwsh
) by default;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.
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.
great post
Would you consider publishing this as a composite action in the GitHub Marketplace?
Do you think it worth it?
I think it is. There are actions with a similar goal, but your implementation seems to be the most complete.