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.
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
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
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:PGBIN\pg_isready" -Wait -PassThru - name: Create scheduler user on Windows run: | & $env:PGBIN\psql --command="CREATE USER scheduler PASSWORD 'somestrong'" --command="\du" - name: Create timetable database run: | & $env:PGBIN\createdb --owner=scheduler timetable $env:PGPASSWORD = 'somestrong' & $env:PGBIN\psql --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.