Generic PostgreSQL Destination Setup Guide
Follow our setup guide to connect PostgreSQL database as a destination to Fivetran.
Prerequisites
To connect PostgreSQL to Fivetran, you need the following:
- PostgreSQL version 7.3 or above
- Database host's IP (e.g.,
1.2.3.4
) or domain (your.server.com
) - Port (usually
5432
) - A Fivetran role with the Create Destinations or Manage Destinations permissions
Setup instructions
Choose connection method
IMPORTANT: Do not perform this step if you want to use Hybrid Deployment for your data pipeline.
Decide whether to connect to your PostgreSQL database directly, using an SSH tunnel, or using private link. For more information, see our destination connection options documentation.
Connect directly
If you connect directly, you must create a rule in a security group that allows Fivetran access to your database instance and port.
Configure your firewall and/or other access control systems to allow:
- incoming connections to your host and port (usually
5432
) from Fivetran's IPs for your database's region - outgoing connections from all ports (
1024
to65535
) to Fivetran's IPs
Connect using an SSH tunnel
If you connect using an SSH tunnel, Fivetran connects to a separate server in your network that provides an SSH tunnel to your PostgreSQL database. You must then configure your tunnel server's security group to allow Fivetran access and configure the instance's security to allow access from the tunnel.
You must connect through SSH if your database is contained within an inaccessible subnet.
To connect using SSH, do the following:
In the destination setup form, select the Connect via an SSH tunnel option.
Copy Fivetran's public SSH key.
Add the public key to the
authorized_keys
file of your SSH server. The key must be all on one line, so make sure that you don't introduce any line breaks when cutting and pasting.
Connect using AWS PrivateLink
IMPORTANT: You must have a Business Critical plan to use AWS PrivateLink.
AWS PrivateLink allows VPCs and AWS-hosted or on-premises services to communicate with one another without exposing traffic to the public internet. PrivateLink is the most secure connection method. Learn more in AWS’ PrivateLink documentation.
Follow our AWS PrivateLink setup guide to configure PrivateLink for your destination.
Connect using Azure Private Link
IMPORTANT: You must have a Business Critical plan to use Azure Private Link.
Azure Private Link allows Virtual Networks (VNets) and Azure-hosted or on-premises services to communicate with one another without exposing traffic to the public internet. Learn more in Microsoft's Azure Private Link documentation.
Follow our Azure PrivateLink setup guide to configure Private Link for your destination.
Connect using Google Cloud Private Service Connect Beta
Google Cloud Private Service Connect allows VPCs and Google-hosted or on-premises services to communicate with one another without exposing traffic to the public internet. Learn more in Google Cloud's Private Service Connect documentation.
Follow our Google Cloud Private Service Connect setup guide to configure Private Link for your destination.
Create Fivetran user
Connect to your PostgreSQL database and execute the following query to create a user for Fivetran. Choose a memorable username (for example,
fivetran
). Replace<password>
with a password of your choice:CREATE USER fivetran PASSWORD <password>;
Execute the following query to grant the
fivetran
user the following privileges:- CREATE: Allows the user to create new schemas in the database
- TEMPORARY: Allows the user to create temporary tables while using the database
GRANT CREATE, TEMPORARY ON DATABASE <database> TO fivetran;
Complete Fivetran configuration
Log in to your Fivetran account.
Go to the Destinations page and click Add destination.
Enter a Destination name of your choice and then click Add.
Select PostgreSQL as the destination type.
(Optional for Enterprise and Business Critical accounts) If you want to use the Hybrid Deployment model, do the following:
i. Set the Enable hybrid deployment toggle to ON.
ii. Select an existing Hybrid Deployment Agent from the Agent name list or configure a new agent.
NOTE: To select an existing agent, go to the More Options menu for the agent you want to use and select Make agent default.
(Not applicable to Hybrid Deployment) Choose your Connection method:
- Connect directly
- Connect via an SSH
- Connect via PrivateLink
NOTE: The Connect via PrivateLink option is available only for Business Critical accounts.
(Not applicable to Hybrid Deployment) If you choose Connect via an SSH tunnel, enter the following details:
- SSH Host
- SSH Port
- SSH User
- (Optional) Enable the Require TLS through tunnel toggle if you want to use TLS.
Enter the Host name or the IP address of the database server.
Enter the Port number. For example,
5432
.Enter the User name you created in Step 2.
Enter your Password.
Enter the Database name you want to replicate to.
Choose the Data processing location. Depending on the plan you are on and your selected cloud service provider, you may also need to choose a Cloud service provider and cloud region as described in our Destinations documentation.
IMPORTANT: If you are using AWS PrivateLink, Azure Private Link, or Google Cloud Private Connect Service, select the corresponding Cloud service provider.
Choose your Time zone.
(Optional for Business Critical accounts and not applicable to Hybrid Deployment) To enable regional failover, set the Use Failover toggle to ON, and then select your Failover Location and Failover Region. Make a note of the IP addresses of the secondary region and safelist these addresses in your firewall.
Click Save & Test.
Fivetran tests and validates the PostgreSQL destination connection. On successful completion of the setup tests, you can sync your data using Fivetran connectors to the PostgreSQL destination.
In addition, Fivetran automatically configures a Fivetran Platform Connector to transfer the connector logs and account metadata to a schema in this destination. The Fivetran Platform Connector enables you to monitor your connectors, track your usage, and audit changes. The connector sends all these details at the destination level.
IMPORTANT:
- If you are an Account Administrator, you can manually add the Fivetran Platform Connector on an account level so that it syncs all the metadata and logs for all the destinations in your account to a single destination. If an account-level Fivetran Platform Connector is already configured in a destination in your Fivetran account, then we don't add destination-level Fivetran Platform Connectors to the new destinations you create.
- If your PostgreSQL database uses pgbouncer as the connection pooler, make sure it pools the connections in session mode.
Setup tests
Fivetran performs the following PostgreSQL connection tests:
The SSH Tunnel Test validates the SSH tunnel details you provided in the setup form and then checks the connectivity to the instance using the SSH Tunnel if you are connecting using an SSH tunnel.
The Database Host Connection Test validates the database credentials you provided in the setup form. The test verifies that the host is not private and then checks the connectivity to the host.
The Certificate Validation Test generates a pop-up window where you must choose which certificate you want Fivetran to use. The test then validates that certificate and checks that we can connect to your database using TLS. We skip this test if you aren't connecting directly.
The Permission Test checks that we have the correct permissions to create schemas and tables in your database.
NOTE: The tests may take a couple of minutes to finish running.
Related articles
description Destination Overview
settings API Destination Configuration