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|
|Windows Server 2019||windows-2019|
|Windows Server 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
- create a test database
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:
\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:
brew servicesto start the server;
pg_isreadyprocedure 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
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.
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.