PostgreSQL Integration
In this article, we’ll integrate a PostgreSQL data source with Y42.
PostgreSQL, also known as Postgres, is an open-source relational database management system emphasizing extensibility and SQL compliance
Overview
Authentication: Database Credentials
Settings: None
Schema type: Dynamic
Update Type: Full import and incremental
Authentication
Database Credentials
Your database credentials allow you to log into your database, either locally or remotely. A credential is a record that contains the authentication information. This information is used internally by server hosting the SQL database. The credentials entail the host address, the IP hosting the server/database, the port number, the default port on PostgreSQL is 5432, the user and the password.
You may also use SSH tunneling or use a Y42 IP address [35.198.102.73, 35.234.125.155, 35.198.107.177, 35.242.197.68, 35.241.201.46 ] for whitelisting.
SSH tunneling is a method of transporting arbitrary networking data over an encrypted SSH connection. The SSH connection is used by the application to connect to the application server. With tunneling enabled, the application contacts to a port on the local host that the SSH client listens on. The SSH client then forwards the application over its encrypted tunnel to the server. See the configuration example page for detailed configuration instructions. The SSH command line options and SSH server configuration file pages may also be helpful.
To make the SSH connection work when connecting in Y42, you will need to chain the SSH tunnel configuration and the IP whitelisting as illustrated below:
Meanwhile, whitelisting means taking manual steps to ensure that a certain IP address isn't blocked from accessing your application by some automated security process. Whitelisting IP addresses enables you to control who can (or cannot) access your database. You can add an IP to Whitelist on the security configurations of your database.
Import Settings
This integration has no import settings.
Schema
There is no predefined schema for this integration.
Updating your data
You have the option of scheduling Full imports or Incremental Imports by the month, weeks, days, and even by the hour. With the incremental import you will only add the new rows to that table, while a full import will actually re-import the entire table completely.
Incremental imports significantly increase the import speed, we import your table incrementally based on the last imported state e.g. id - If the table does not have a state, a full import will be triggered - This is done automatically.
We recommend therefore to schedule an incremental import regularly, but also schedule a full import with a certain frequency -- this is because only the full import will guarantee that old rows that have been updated on the data source will also get updated on your y42 integration destination.
PostgreSQL Setup Guide
- On Integrate, click on "Add..." to search for PostgreSQL and select it.
- Name your integration.
- Authorize access with your database credentials. Alternatively, use SSH tunnel(s) to connect or use a y42 IP for whitelisting.
- After authentication, you are good to go and start importing your tables.
- Select the tables you need and click import. You can start accessing the tables once the status is “Ready”.