CYBERTEC Logo

PostgreSQL GitHub Actions - Continuous Integration

03.2021 / Category: / Tags: | |

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

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

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

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.

0 0 votes
Article Rating
Subscribe
Notify of
guest
4 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Zoltán Lehóczky
Zoltán Lehóczky
1 year ago

Would you consider publishing this as a composite action in the GitHub Marketplace?

Pavlo Golub
Pavlo Golub
1 year ago

Do you think it worth it?

Zoltán Lehóczky
Zoltán Lehóczky
1 year ago
Reply to  Pavlo Golub

I think it is. There are actions with a similar goal, but your implementation seems to be the most complete.

cao7113
cao7113
1 year ago

great post

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
R├Âmerstra├če 19
2752 W├Âllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ┬ę
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    4
    0
    Would love your thoughts, please comment.x
    ()
    x
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram