PostgreSQL is one of the most well-known systems for managing relational databases. The data integration from PostgreSQL to the Data Warehouse created by Kondado allows you to access PostgreSQL data in your analytical cloud.
Adding the data source
To automate the ETL from PostgreSQL with Kondado to your database, follow the steps below:
- Whitelist the Kondado IPs on the database server (and on your VPC) for the port that will be used to connect with PostgreSQL
- Have the address, port, database, user and password data to access your bank at hand
- On the Kondado platform, go to the page to add data sources and select the PostgreSQL data source
- Give a name to your data source and enter the information from step (2)
The value provided in the "schema" parameter will only serve as a reference. We will consider all existing schemas when listing and integrating the tables and views of the data source.
Now just save the data source and start integrating your PostgreSQL data into the Data Lake or Data Warehouse.
Tables and Views
With our integration, you will be able to integrate tables, views, and mat views
If your table has a datetime/timestamp type column that marks when a record was changed/inserted, your integration can be incremental
It will be necessary to define the primary key, which can be defined by several columns and refers to the column (or set of columns) that define a record as being unique
Column names
Columns whose names contain special characters will be ignored
The table created will have a similar format to the one below:
| Field | Type | |
|---|---|---|
| col_x | text | |
| col_y | int | |
| col_z | float |
Create a PostgreSQL data source on Kondado
Set up a PostgreSQL connection on Kondado to integrate your relational database into a cloud Data Warehouse or Data Lake for analytics.
Whitelist Kondado IPs
Configure your database server and VPC firewall to allow connections from Kondado's IP addresses on the PostgreSQL port. This secure network step is essential before any data integration can begin.
Gather connection credentials
Collect your PostgreSQL host address, port, database name, username, and password. You'll need these handy to authenticate when adding the source on the Kondado platform.
Add the PostgreSQL source on Kondado
Navigate to the data sources page on the Kondado platform, select PostgreSQL, give your source a descriptive name, and enter the credentials from the previous step.
Configure schema and save
Note that the schema parameter is for reference only—Kondado will discover all schemas, tables, views, and materialized views. Save the source to start integrating your data into your destination.
Set up incremental sync (optional)
For tables with a datetime/timestamp column tracking changes, enable incremental integration by defining the primary key (single or composite columns) to uniquely identify records.
