Create a Postgres connection in Airflow

Postgres is a free and open source relational database system. Integrating Postgres with Airflow allows you to interact with your Postgres database, run queries, ans load or export data from an Airflow DAG.

This guide provides the basic setup for creating a Postgres connection.

Prerequisites

• The Astro CLI.
• A locally running Astro project.
• A Postgres database running in the cloud or on-premises.
• Permission to access your Postgres database from your local Airflow environment.

Get connection details

A connection from Airflow to Postgres requires the following information:

• Host (also known as the endpoint URL, server name, or instance ID based on your cloud provider)
• Port (default is 5432)
• Username
• Password
• Schema (default is public)

The method to retrieve these values will vary based which cloud provider you use to host Microsoft SQL Server. Refer to the following documents to for more information about retrieveing these values:

• AWS: Connect to Postgres running on RDS
• GCP: Connect to Postgres running on Cloud SQL
• Azure: Connect to Postgres running on an Azure database

For example, if you're running Postgres in a Relational Data Store (RDS) in AWS, complete the following steps to retrieve these values:

1. In your AWS console, select your region, then go to the RDS service and select your Postgres database.
2. Open the Connectivity & security tab and copy the Endpoint and Port.
3. Follow the AWS instructions to create a user and grant a role to the user that Airflow will use to connect to Postgres. Copy the username and password.
4. (Optional) To use a specific schema, copy the name of the schema. If you skip this, the default schema public will be used.

Create your connection

1. Open your Astro project and add the following line to your requirements.txt file:

   apache-airflow-providers-postgres

   This will install the Postgres provider package, which makes the Postgres connection type available in Airflow.

2. Run astro dev restart to restart your local Airflow environment and apply your changes in requirements.txt.

3. In the Airflow UI for your local Airflow environment, go to Admin > Connections. Click + to add a new connection, then choose Postgres as the connection type.

4. Fill out the following connection fields using the information you retrieved from Get connection details:

   • Connection Id: Enter a name for the connection.
   • Host: Enter your Postgres server's host/ endpoint URL/ server name/ instance ID.
   • Schema: Enter your schema name.
   • Login: Enter your username.
   • Password: Enter your password.
   • Port: Enter your Postgres server's Port.

5. Click Test. After the connection test succeeds, click Save.

   

How it works

Airflow uses the psycopg2 python library to connect to Postgres through the PostgresHook. You can also directly use the PostgresHook to create your own custom operators.

See also

• Apache Airflow Postgres provider package documentation
• Postgres modules and example DAGs in the Astronomer Registry
• Import and export Airflow connections using Astro CLI