Certain tasks in organizations are executed on a recurring schedule to ensure business-critical operations are running smoothly. Typically, these job schedules are manual to set up and require time to run and validate. Scheduler platforms enable engineers to automatically execute tasks at particular times. They are highly beneficial tools that eliminate the need for an employee to manually run each iteration of the job.
PostgreSQL is a powerful and popular database system that many organizations use. Many operations in this database require scheduled executions, such as generating a daily report or refreshing inventory count. While traditional methods, such as cron, can help users set up job scheduling, they can be tricky to manage and are sometimes unreliable. Utilizing a scheduling platform, like Airplane, provides users with an easy and reliable way to create tasks that can run PostgreSQL queries and schedule them to run at fixed time intervals.
In this article, we'll walk through what PostgreSQL is, how to build an Airplane Task using PostgreSQL, and how to schedule the task to run at fixed time intervals.
What is PostgreSQL?
PostgreSQL is a powerful database system that uses SQL to house large and complex data workloads. It runs on most operating systems and is easy to set up and start using.
PostgreSQL includes a rich library of features and is free and open-sourced. It's also highly extensible and users can define their data types, write code from other programming languages and build out their own custom functions fit for their specific needs.
Let's now walk through a tutorial that showcases how Airplane makes building and scheduling PostgreSQL-based tasks easy and efficient.
Prerequisites
For this tutorial, ensure you have the following:
- Free Airplane account
- PostgreSQL database to query against. This database should allow incoming network connections. If it doesn’t, ensure that Airplane IP addresses are safelisted. If the database is behind a private network, see Airplane’s guide on private networking.
- Basic understanding of PostgreSQL
We will create an example PostgreSQL query that retrieves our hypothetical organization's daily sales and will build an Airplane Task for it. We will then schedule our new task in Airplane to run at a fixed time interval that returns our daily sales.
To start, let's create a PostgreSQL query to retrieve daily sales.
Creating a PostgreSQL query to retrieve daily sales
For this example, let's use a sales table in the online store database that contains the product, unit_price, and sold_at fields.
To generate a report of the daily sales for each product, execute this query:
The resulting table will show how many products were sold and for what dollar amount. Here’s an example of the generated table:
We've now successfully created the PostgreSQL query. The next step is to create a PostgreSQL resource in Airplane.
Creating a PostgreSQL resource in Airplane
Airplane makes it easy to connect databases, APIs, and messaging platforms to use in tasks. Let's walk through how to add a PostgreSQL resource in Airplane.
Once logged in to your Airplane account, visit the Resources page and select PostgreSQL under the databases section.
Next, enter the following database connection details in the form:
- Name — Human-readable name for the resource
- Host — IP address or DNS name of the database
- Port — The database connection port
- Database — Name of the database
- Username — The username of the database user
- Password — The database connection password
Once done filling out the information above, click Create. You have now created the PostgreSQL Airplane resource.
Next, let's build an Airplane task to run the PostgreSQL query from above using the Airplane command-line interface (CLI).
Building the Airplane Task using the Airplane CLI
An Airplane Task is a function that anyone on a team can execute. The Airplane CLI helps users develop and deploy tasks using the terminal.
To install the Airplane CLI, execute the command below that pertains to your operating system:
MacOs
For macOS, use Homebrew to run the installation script:
Alternatively, you can use the curl command:
Linux
If using Linux, run this installation script in the terminal:
Windows
On Windows, open Powershell and run the following command to install:
Building the task
After completing the installation pertaining to your operating system, log in to your Airplane account by using the following command:
You’ll see this output:
Follow the instructions to log in. Once you’ve logged in, navigate to the folder where you want to create the task and run the following command:
You’ll be prompted to enter a few bits of information:
- Task name — The name should describe what the task does. For this example, we can use the name
get_daily_sales - Type of task — Select
SQL - Location of the script and definition file — Press Enter to accept the suggested file names
Open the get_daily_sales.sql task file and add the PostgreSQL query to finish creating the task:
We also need to add the database resource name to the task definition file. Open the get_daily_sales.task.yaml file and add the slug of the database resource as follows:
Now that we've defined the query and configured the resource, run the airplane deploy command in the terminal to deploy the task to Airplane.
Then, navigate to the Tasks page in Airplane to see the deployed task:
And we have now successfully created an Airplane Task that runs our PostgreSQL query.
Next, let's set up an automated schedule for our new Airplane Task.
Scheduling the Airplane Task
Schedules allow users to run tasks automatically at regular intervals. We can easily add a schedule for the task created above in Airplane using code or the web UI. In this example, let's walk through both options to schedule our task to run daily at midnight.
Scheduling the task using code
To schedule the task using code, add the following to the schedules object of the get_daily_sales.yaml file:
Note that the above code uses cron syntax for scheduling the task. For more information on cron syntax, check out our intro to cron blog post.
The schedule is identified using a unique slug. This example uses generate_sales. The schedule object is also made up of the following:
- Cron — A required cron string of the schedule
- Name — An optional name of the schedule
- Description — An optional short description of the schedule
Once we've created a schedule in the task definition file, let's redeploy the task via the Airplane CLI using the command airplane deploy.
And we have now created a schedule for the daily sales task quickly and easily in Airplane using code.
Scheduling the task using the web UI
Let's now walk through creating the same schedule using Airplane's web interface. To do this, navigate to the Library page and select Create schedule from the dropdown menu by the task name:
We can use cron or simple syntax to schedule a task. For this schedule, let's use the simple syntax for the task to run every day at 0:00 (12:00 am) UTC. Then, name the new schedule and provide a description:
Finally, click Create schedule to finish.
And that's it - we have now easily created a schedule for the daily sales task using the web UI.
Airplane makes it easy to schedule recurring tasks using code or its UI for both engineers and less technical team members. It's a very reliable option and allows full visibility into the organization's schedules through audit logs. If you're looking for a streamlined and reliable way to schedule business-critical operations, then Airplane is a great fit for you.
Conclusion
Many organizations schedule PostgreSQL operations that are critical for business success. Although traditional schedulers, such as cron, can be used to automate job executions, they can be unreliable, hard to use, and have insufficient logs. Airplane provides a valuable scheduling alternative that allows users to create easy-to-use, highly reliable schedules in just a few minutes. If you're looking for more information on scheduling and PostgreSQL, check out our blog for content such as 6+ cron alternatives, choosing the best enterprise scheduling tool, top 5 PostgreSQL GUI tools, and more.
Airplane also offers permissions settings, audit logs, and error notifications, providing users with complete visibility into their job scheduling process. In addition to tasks and scheduling features, Airplane offers Views, which allow users to build custom UIs and dashboards in under 15 minutes using pre-built components and templates.
If you're looking for a reliable job scheduling platform, sign up for a free Airplane account or say hello at [email protected].
