Connecting to PostgreSQL in Astera

PostgreSQL is an open-source relational database management system that stores data workloads. It helps in managing all kinds of datasets with its extensive range of features.

Astera provides connectivity to PostgreSQL databases through its database objects. You can connect to PostgreSQL databases in Astera using the Database Table Source, Database Table Destination, Database Lookup, SQL Query Lookup, SQL script task in a workflow, and Database Write Strategies.

In this article, we will cover:

1. A use case where PostgreSQL is being used as a database in Astera

2. How to connect to the PostgreSQL database in Astera

3. Different data loading and reading options for PostgreSQL databases in Astera

Use Case

We have sample Customer data stored in a PostgreSQL database. The data contains multiple fields containing customer information. One of these fields is Country, indicating Countries which customers reside in.

We want to replace these country names with their respective country codes. We can then further use these codes to obtain additional insights from this data.

We will be using the Database Table Source object to source data from the PostgreSQL database.

1. To configure the Database Table Source object, right-click on its header and select Properties from the context menu.

This will open a new window where you can configure a connection with the PostgreSQL database.

1. The first step is to select the Data Provider. Select PostgreSQL as your data provider from the drop-down list.

1. Enter the required credentials for your PostgreSQL account:

   • User ID

   • Password

   • Server Name

   • Database

   • Port

   • Schema

1. Test Connection to make sure that your database connection is successful, and click Next to proceed to the next screen.

1. Here, you can Pick Source Table and modify Reading Options for your PostgreSQL database.

Note: In this case, we will select the Customers table.

To learn more about data reading options in a Database Table Source, click here.

1. Now, we will use the List Lookup transformation and provide coded values for all the country records in the data.

To learn more about how the List Lookup transformation object works in Astera, click here.

1. Next, we will pick a table in the PostgreSQL database and write this data to it using the Database Table Destination in Astera.

To configure the Database Table Destination object, right-click on its header and select Properties from the context menu.

This will open a new window on your screen.

Here you can configure the properties for the Database Table Destination object.

The first step is to specify the Database Connection for the destination object.

Select PostgreSQL as your Data Provider from the drop-down list.

1. Enter the required credentials for your PostgreSQL data provider account:

   • User ID

   • Password

   • Server Name

   • Database

   • Port

   • Schema

1. Test Connection to make sure that your database connection is successful, and click Next.

You can choose either Pick Table or Create Table and modify Writing Options for PostgreSQL Database on the Properties screen.

Note: In this case, we will pick the Customers table which is the same as the source table.

To read more on the different data writing options available for a database table, click here.

Once you have set the data reading options, click OK.

We have successfully configured PostgreSQL as our database provider for the Database Table Destination object. The data will now be written to the PostgreSQL Database once the dataflow is run.

Now, If we preview the data in the database table, we can see that the country names have been replaced with country codes and the data has been written into the destination table.

We have successfully connected to the PostgreSQL Database and completed our use case in Astera.