PostgreSQL

Hevo supports the following variations of PostgreSQL as a Source:

Click each variant above to know the prerequisites and pre-configuration setups specific to it. Once you complete the setups, follow the steps mentioned below to configure it as a Source in your Hevo Pipeline and start ingesting data.


Select the Source Type

To select PostgreSQL as the Source:

  1. Click PIPELINES in the Asset Palette.

  2. Click + CREATE in the Pipelines List View.

  3. In the Select Source Type page, select the PostgreSQL variant.


Select the Pipeline Mode

Select how you want Hevo to read your data from the PostgreSQL Source. This can be Custom SQL, Logical Replication, or Table.

Read Pipeline Modes.

  • For Pipeline mode as Table, refer to Object Settings below to configure the objects to be replicated.

  • For Pipeline mode as Logical Replication, follow the steps provided in each PostgreSQL variant document to set up logical replication.


Specify PostgreSQL Connection Settings

In the Configure your PostgreSQL Source page, specify the following:

PostgreSQL settings

  • Pipeline Name: A unique name for your Pipeline.

  • Database Host: The PostgreSQL host’s IP address or DNS.

    The following table lists few examples of PostgreSQL host:

    Variant Host
    Amazon RDS PostgreSQL postgresql-rds-1.xxxxx.rds.amazonaws.com
    Azure PostgreSQL postgres.database.azure.com
    Generic PostgreSQL 10.123.10.001 or postgresql.westeros.inc
    Google Cloud PostgreSQL 35.220.150.0

    Note: For URL-based hostnames, exclude the http:// or https:// part. For example, if the hostname URL is https://postgres.database.azure.com, enter postgres.database.azure.com.

  • Database Port: The port on which your PostgreSQL server is listening for connections. Default value: 5432.

  • Database User: The read-only user that can read the tables in your database.

  • Database Password: Password for the read-only user.

  • Database Name: The database that you wish to replicate.

  • Schema Name: The name of the Schema in the database.

  • Connection Settings:

    • Connect through SSH: Enable this option to connect to Hevo using an SSH tunnel, instead of directly connecting your PostgreSQL database host to Hevo. This provides an additional level of security to your database by not exposing your PostgreSQL setup to the public. Read Connecting Through SSH.

      If this option is disabled, you must whitelist Hevo’s IP addresses. Refer to the content for your PostgreSQL variant for steps to do this.

    • Use SSL: Enable it to use SSL encrypted connection. You should also enable this if you are using Heroku PostgreSQL databases. To enable this, specify the following:

      • CA File: The file containing the SSL server certificate authority (CA).

      • Client Certificate: The client public key certificate file.

      • Client Key: The client private key file.

  • Advanced Settings (for Pipelines with Logical Replication mode)

    • Load Historical Data: If this option is enabled, the entire table data is fetched during the first run of the Pipeline. If disabled, Hevo loads only the data that was written in your database after the time of creation of the Pipeline.

    • Merge Tables: If this option is enabled, Hevo merges tables with the same name from different databases while loading the data to the warehouse. Hevo loads the Database Name field with each record. If disabled, the database name is prefixed to each table name. Read How does the Merge Tables feature work?.

    • Include New Tables in the Pipeline: Applicable for all Pipeline modes except Custom SQL. If enabled, Hevo automatically ingests data from tables created after the Pipeline has been built. If disabled, the new tables are listed in the Pipeline Detailed View in Skipped state, and you can manually include the ones you want and load their historical data.

      You can change this setting later.

Click TEST & CONTINUE. Proceed to configuring the data ingestion and setting up the Destination.


Object Settings

Object settings must be configured if the Pipeline mode is Table. To do this:

  • After specifying the Source settings above, in the SELECT OBJECTS YOU WANT TO REPLICATE page, select the objects to be replicated.

    Note: Each object represents a table in your database.

  • In the CONFIGURE SOURCE OBJECTS page, specify the query mode to be used for each selected object.



Revision History

Refer to the following table for the list of key updates made to this page:

Date Release Description of Change
Jul-26-2021 1.68 Added a note for the Database Host field.
Jul-12-2021 1.67 Added the field Include New Tables in the Pipeline under Source configuration settings.
Last updated on 20 Jul 2021