Generic PostgreSQL Setup Guide

Follow these instructions to replicate your generic PostgreSQL database to your destination using Fivetran.

You can also follow the setup instructions to connect with PostgreSQL-compatible databases, including Neon and Supabase.

More information about the setup form in the Fivetran dashboard

The Fivetran connection setup form is organized into the following tabs: Prerequisites, Connection name, Networking or Hybrid Deployment, Database access, and Incremental sync. In each tab, you need to specify the required parameters to complete the connection configuration successfully. This involves performing the following actions:

Depending on how your destination is configured, the setup form displays either the Networking or Hybrid Deployment tab. If your destination is configured for Hybrid Deployment, the Hybrid Deployment tab is displayed instead of the Networking tab.
In the Networking tab, specify the networking information required to establish the connection between your database and Fivetran. For this, you may need to configure certain networking settings based on the connection method you select. You must have network administrative access or assistance from a network administrator to configure these settings.
In the Hybrid Deployment tab, you can select the Hybrid Deployment Agent.
In the Database access tab, specify the database connection parameters. For this, you may need to create a database user and grant the necessary permissions. You must have database administrative access or assistance from a database administrator to configure these settings.
In the Incremental sync tab, select the incremental sync method. For this, you may need to configure certain database settings based on the incremental sync method you select. You must have database administrative access or assistance from a database administrator to configure these settings.

The Add collaborator button lets you invite a network or database administrator to assist in configuring and providing the necessary information. A collaborator can view and edit a connection's configuration.

The Save for Later button lets you save the information you've entered so far, enabling you to exit the screen and return later to resume the setup process. This feature is helpful if you need to pause to gather additional information or finalize the setup at a later time.

Setup instructions

Prerequisites

Before setting up your connection, ensure you have the following information and access rights to connect your generic PostgreSQL database to Fivetran.
Networking requirements:
- Network administrative access (to configure firewall rules or security groups to allow Fivetran's IP addresses)
- Access to the host machine
- TLS enabled on your database
- Additional requirements depending on the connection method you choose (for example, SSH, Private Link, Proxy Agent)
- (For Supabase with logical replication) IPv4 add-on
Database requirements:
- PostgreSQL version 10 - 18
- Access to the database server
- Database administrative access (to provision a database user for Fivetran)
Click Next to proceed to the Connection name tab.

Connection name

In the Destination schema prefix field, enter a name for the connection. This name is prefixed to each replicated schema on the destination and cannot be changed once your connection is created.
To invite collaborators (for example, network/database administrator), you must save the connection name first.
In the Destination schema names field, choose the naming convention you want Fivetran to use for the schemas, tables, and columns in your destination:
- Source naming: Preserves the original schema, table, and column names from the source system in your destination, and ignores the Destination schema prefix specified in the setup form. However, when multiple connections share the same source schema name (for example, public), Fivetran stores their tables in the same destination schema. Tables with duplicate names may lead to overwrites and data inconsistencies. Be sure to use unique table names across connections that write to the same schema.
- Fivetran naming: Standardizes the schema, table, and column names in your destination according to the Fivetran naming conventions.
If you want to modify your selection, make sure you do it before you start the initial sync.
Depending on your selection, we will either prefix the connection name to each replicated schema or use the source schema names instead.
Click Save and continue to proceed to the Networking or Hybrid Deployment tab.

Networking or Hybrid Deployment

Depending on how your destination is configured, the setup form displays either the Networking or Hybrid Deployment tab. The Hybrid Deployment tab is displayed only if your destination is configured for Hybrid Deployment; otherwise, the Networking tab is displayed in its place.

Expand the section below that applies to your setup and follow the instructions.

Networking

Expand for instructions

Specify the networking-related information required to establish the connection between your database server and Fivetran. You may need to configure certain networking settings based on the connection method you select. You must have network administrative access or assistance from a network administrator to configure these settings.

Choose and configure the connection method

Decide on the connection method you want to use to connect Fivetran to your database, and set up the required network configurations. The connection methods are:

Connect directly

Fivetran connects directly to your PostgreSQL database. This is the simplest connection method to set up, requiring minimal configuration.

To connect directly, you must do the following:

Enable TLS on your PostgreSQL database. Follow PostgreSQL's instructions to enable TLS on your database.
Configure your firewall and/or other access control systems to allow incoming connections to your PostgreSQL host and port (usually 5432) from Fivetran's IPs for your database's region.

To connect to Supabase, use the direct connection method with the IPv4 add-on. Alternatively, you can use Query-Based replication with the session pooler.

Connect using SSH

Fivetran connects to a separate server in your network that provides an SSH tunnel to your PostgreSQL database. You must connect through SSH if your database resides in an inaccessible network.

To connect using an SSH tunnel, configure an SSH tunnel between Fivetran and your PostgreSQL database. For more information, see our SSH connection setup documentation.

The SSH tunnel setup requires adding Fivetran's SSH public key to the authorized_keys file on your SSH tunnel host. Copy the public key from the connector setup form, which is visible when you select Connect via an SSH tunnel in the Connection method drop-down.

Connect using private networking

Private networking enables communication between private networks and services without exposing traffic to the public internet. Private networking is the most secure connection method.

You must have a Business Critical plan to use private networking.

We support the following providers:

AWS PrivateLink – used for VPCs and AWS-hosted or on-premises services. For more information, see our AWS PrivateLink setup documentation.
Azure Private Link – used for Virtual Networks (VNets) and Azure-hosted or on-premises services. For more information, see our Azure PrivateLink setup documentation.
Google Cloud Private Service Connect – used for VPCs and Google-hosted or on-premises services. For more information, see our Google Cloud Private Service Connect setup documentation.

Connect using Proxy Agent

Fivetran connects to your database through the Proxy Agent, providing secure communication between Fivetran processes and your database host. The Proxy Agent is installed in your network and creates an outbound network connection to the Fivetran-managed SaaS.

To learn more about the Proxy Agent, how to install it, and how to configure it, see our Proxy Agent documentation.

Specify Networking parameters

In the Fivetran setup form's Networking tab,

Select your Connection method.
Provide the details required for your selected Connection method.
Connect Directly
1. Copy the Fivetran's IP addresses (or CIDR) that you must safelist in your firewall.
2. In the Host field, enter your database host's IP (for example, 1.2.3.4) or domain (for example, your.server.com).
3. In the Port field, enter your database instance's port number (usually 5432).
Connect via SSH Tunnel
1. In the SSH Host field, enter the hostname of your SSH server (do not use a load balancer's IP address or hostname).
2. In the SSH Port field, enter the port number for the SSH connection.
3. In the SSH User field, enter the username for the SSH connection.
4. The Require TLS through Tunnel toggle is ON by default. Keep it ON if you enabled TLS on your database in the Choose and configure the connection method step above.
5. Copy the Fivetran's IP addresses (or CIDR) that you must safelist in your firewall.
6. In the Host field, enter your database host's IP (for example, 1.2.3.4) or domain (for example, your.server.com).
7. In the Port field, enter your database instance's port number (usually 5432).
Ensure that you have added Fivetran's SSH Public Key to the authorized_keys file on your SSH tunnel host.
Connect via Private Networking
1. Select an existing private networking connection from the drop-down menu or click Configure a new connection to create and use a new connection.
2. If you enabled TLS on your database in the Choose and configure the connection method step above, keep the Require TLS when using Private Networking toggle turned ON.
3. Copy the Fivetran's IP addresses (or CIDR) that you must safelist in your firewall.
4. In the Port field, enter your database instance's port number (usually 5432).
Connect via Proxy Agent
1. Select an existing agent from the Proxy agents drop-down list or click + Configure a new proxy agent to set up a new agent.
2. If you enabled TLS on your database in the Choose and configure the connection method step above, keep the Require TLS when using Proxy Agent toggle turned ON.
3. Copy the Fivetran's IP addresses (or CIDR) that you must safelist in your firewall.
4. In the Host field, enter your database host's IP (for example, 1.2.3.4) or domain (for example, your.server.com).
5. In the Port field, enter your database instance's port number (usually 5432).
Click Next to proceed to the Database access tab.

Hybrid Deployment

Expand for instructions

Specify Hybrid Deployment connection parameters

In the Fivetran setup form's Hybrid Deployment tab,

The Hybrid Deployment Agent associated with your destination is pre-selected. To use a different agent, click Replace agent, and then select the agent of your choice.
In the Host field, enter your database host's IP (for example, 1.2.3.4) or domain (for example, your.server.com).
In the Port field, enter your database instance's port number (usually 5432).
Click Next to proceed to the Database access tab.

Database access

Specify the database-related information required to establish the connection between your database and Fivetran. Also, create a database user and grant the user the required permissions. You must have database administrative access or assistance from a database administrator to configure these settings.

Configure database

In your database, configure the following:

Create a database user for Fivetran's exclusive use.
Expand for instructions
Create database user
1. Open a connection to your PostgreSQL database in a PostgreSQL console (such as a SQL workbench or psql).
2. Create a user for Fivetran by executing the following SQL command. Replace <username> and <password> with a username and password of your choice.
```
CREATE USER <username> PASSWORD <password>;
```

Grant read-only access

Grant the Fivetran user read-only access to all tables by running the following commands. To grant access to a schema other than PostgreSQL's default public schema, replace public with your schema name. If you want to grant access to multiple schemas, run these commands for each schema.

GRANT USAGE ON SCHEMA "public" TO <username>;
GRANT SELECT ON ALL TABLES IN SCHEMA "public" TO <username>;
ALTER DEFAULT PRIVILEGES IN SCHEMA "public" GRANT SELECT ON TABLES TO <username>;

The ALTER DEFAULT PRIVILEGES command ensures that future tables created in the schema are also accessible to the Fivetran user.

Restrict access to tables (optional)

You can limit Fivetran's access to specific tables by granting access only to the tables that you want to sync.

You must grant access individually for each table. You cannot grant access to all tables and then revoke access for a subset of tables.

Ensure that the Fivetran user has access to the schema that contains your table(s):
```
GRANT USAGE ON SCHEMA "your_schema" TO <username>;
```

Revoke previously granted table-level permissions:

ALTER DEFAULT PRIVILEGES IN SCHEMA "your_schema" REVOKE SELECT ON TABLES FROM <username>;
REVOKE SELECT ON ALL TABLES IN SCHEMA "your_schema" FROM <username>;

Grant access to each table:

GRANT SELECT ON "your_schema"."your_table" TO <username>;

By default, new tables created in the schema are not accessible to the Fivetran user. To grant access to new tables, run the following command:
```
ALTER DEFAULT PRIVILEGES IN SCHEMA "your_schema" GRANT SELECT ON TABLES TO <username>;
```

Restrict access to columns (optional)

You can limit access to specific columns within a table by granting permissions only to those columns.

Revoke existing table-level permissions:

REVOKE SELECT ON "your_schema"."your_table" FROM <username>;

Grant access to specific columns:
If you chose Query-Based as your incremental sync method, you must grant us access to the hidden system columns xmin and ctid. This speeds up your initial sync and enables capturing deletes. If you chose Logical replication, granting access to xmin is recommended for new connections to enable faster initial sync and re-import.
```
GRANT SELECT (xmin, ctid, some_column, other_column) ON "your_schema"."your_table" TO <username>;
```

After restricting column access, newly added columns will not be accessible automatically. To grant access to new columns, rerun the command above with the additional columns.

Specify database connection parameters

In the Fivetran setup form's Database access tab,

In the User field, enter the Fivetran-specific database user that you created in the previous step.
In the Password field, enter the password for the Fivetran-specific database user.
In the Database field, enter the name of your database (for example, your_database).
Click Next to proceed to the Incremental sync tab.

Incremental sync

Choose and configure the incremental sync method. This determines how Fivetran detects new or changed rows so we don't have to copy the whole table every time. Learn more in our Updating data documentation.

We recommend using the Logical replication method when possible because it is faster and more efficient than Query-Based. For guidance on choosing the best option for your workload, see Logical replication vs Query-Based documentation.

Configure incremental sync in database

In your database, configure the following:

Logical replication

To enable logical replication, perform the following steps:

Go to your PostgreSQL database.
Ensure that your server has ample free space for the logs. As soon as Fivetran processes a log, we delete it. However, we don't delete logs if the sync is interrupted (for example, if we lose access to your database). In this case, logs may accumulate on your server and consume additional storage. The amount of additional disk space that these logs consume is proportional to the number of changes committed on the server. If we can't resume a lost connection quickly enough and you need more disk space, you can drop the replication slot, which deletes its unconsumed logs.
Dropping and recreating the replication slot resets the Log Sequence Number (LSN). Fivetran cannot resume syncing from where it left off, and requires a historical re-sync. For more information, see Data Integrity Issues After Recreating a Replication Slot With the Same Name.
Ensure that the statement_timeout setting on your server is either 0 (the default value to disable the timeout) or greater than 5 minute.
Set the wal_level parameter in your database configuration to logical. For a standard PostgreSQL database, do this by adding a wal_level = logical line to the postgresql.conf file. Restart the server for this change to take effect.
Ensure that your max_replication_slots value is equal to or higher than the number of PostgreSQL connectors that use logical replication plus the number of other replication slots your database uses.
Set the wal_sender_timeout parameter in your database configuration to 0 to disable the timeout.
For PostgreSQL 18 and later, set the idle_replication_slot_timeout parameter to 0 (disabled) or to at least 24h to prevent the replication slot from being invalidated between syncs.
Add a record to your pg_hba.conf file that allows your database to authenticate Fivetran's connection to the WAL.
Go to the postgresql.conf file and ensure that the max_wal_senders parameter, which specifies the maximum number of concurrent connections to the WAL, is at least twice the total number of logical replication slots. For example, if your database uses 11 replication slots in total, then the max_wal_senders value must be 22 or greater.
Use a PostgreSQL console (such as a SQL workbench or psql) to log in to your primary database as a superuser.
Create a publication for your tables. If you want, you can create a publication for only certain tables so that you add or remove tables from the publication later on. Only changes from tables in the publication are replicated to Fivetran. Each database can have multiple distinct publications. You must have CREATE privileges or above to run this command.
The publication name fivetran_pub quoted throughout this guide is used purely as an example. The actual publication name should be unique for every database and cannot start with a number.
```
CREATE PUBLICATION fivetran_pub FOR TABLE table2, table4, table8;
```
To add or remove a table from a publication, run the following command. You must have ownership rights over the table(s).
```
ALTER PUBLICATION fivetran_pub ADD/DROP TABLE table_name;
```
Alternatively, you can create a publication for all of your tables. However, you cannot remove any table from this publication later on. You must have superuser privileges to run this command.
```
CREATE PUBLICATION fivetran_pub FOR ALL TABLES;
```
(Optional) You can choose which operations to include in the publication. For example, the following publication includes only INSERT and UPDATE operations.
```
CREATE PUBLICATION insert_only_pub FOR TABLE table1 WITH (publish = 'INSERT, UPDATE');
```
To add partitioned tables for PostgreSQL version 13 or later, run the following command to enable publish_via_partition_root.
```
CREATE PUBLICATION fivetran_pub FOR ALL TABLES WITH (publish_via_partition_root=true);
```
Create a logical replication slot for the database you want to sync by running the following command. You must use the standard output plugin pgoutput. Ensure that you are connected to the correct database when you create your replication slot, or your connection will not be able to find the slot.
You must create a unique replication slot for every connection that uses the same PostgreSQL cluster. Replication slot names cannot start with a number. (The replication slot name fivetran_pgoutput_slot quoted throughout this guide is used purely as an example.)
You need to create the replication slot after you have created the publication.
```
SELECT pg_create_logical_replication_slot('fivetran_pgoutput_slot', 'pgoutput');
```
If your PostgreSQL server version is 16 or later and you want to sync from a standby, create the replication slot in the read replica.
Verify that your chosen tables are in the publication.
```
SELECT * FROM pg_publication_tables;
```
Grant the Fivetran user permission to read the replication slot.
```
ALTER ROLE <username> WITH REPLICATION;
```
Log in as the Fivetran user.
Verify that the Fivetran user can read the replication slot by running the following command. Replace fivetran_pgoutput_slot with your replication slot name and fivetran_pub with the publication name.
```
SELECT count(*) FROM pg_logical_slot_peek_binary_changes('fivetran_pgoutput_slot', null, null, 'proto_version', '1', 'publication_names', 'fivetran_pub');
```
If the query succeeds, then permissions are sufficient.

You must periodically tune the checkpoint_timeout and max_wal_size parameters based on your PostgreSQL database operations. If you do not, you may experience replication failures. To learn how to tune, read this tuning checkpoints documentation.

Query-Based

Fivetran runs SQL queries that read PostgreSQL system columns (xmin and, when applicable, ctid) to detect new and changed data during each sync.

Capture Deletes (optional)

Query-Based sync detects deleted rows by default. You can disable this behavior by turning off the Capture Deletes toggle in the connection setup form.

When Capture Deletes is enabled:

Fivetran adds an internal helper column ctid_fivetran_id to each synced table. Fivetran uses this column to track deletes by storing the row's ctid value at the end of each sync.
- Before the initial sync (new connection): Fivetran creates tables with ctid_fivetran_id during the initial sync. No additional re-sync is required.
- After data is already synced (existing connection): Fivetran runs a one-time migration sync of the selected tables to populate ctid_fivetran_id column and capture a snapshot of the source table. This migration sync does not trigger a table re-sync. It may take longer than a regular sync because Fivetran must populate the additional ctid_fivetran_id column.
This setting is permanent for the connection. After you click Save & Test in the connection setup form, it’s locked and can’t be disabled later, whether or not the connection has run its first sync.
We sync partitioned tables using child-to-child sync only.

For details and limitations, see our Capturing deletes documentation.

Query-Based sync requires full table scans to detect updates and may be slower than logical replication, especially for large tables. In high-write databases, PostgreSQL xmin freezing/wraparound can also cause older rows to be re-synced, increasing sync volume and load on your PostgreSQL source database. If possible, use logical replication.

Fivetran Teleport Sync Sunset

We have sunset Teleport Sync, and it is no longer available as an Incremental sync method option in the connection setup form for new connections.

You do not need any additional configuration for the Teleport Sync method. We automatically create the aggregate function (bit_xor) required for the Teleport mechanism.

However, if you are connecting to a standby or read replica, you must manually create this aggregate on the primary database because new objects cannot be created directly in read replicas. Run the following SQL command on your primary database as the Fivetran user:

CREATE AGGREGATE BIT_XOR(IN v bigint) (SFUNC = int8xor, STYPE = bigint);

Creating the aggregate on the primary ensures that it is replicated to the standby or read replica, allowing Teleport Sync to function correctly.

Specify replication parameters

In the Fivetran setup form's Incremental sync tab,

In the Incremental sync method tab, select the incremental sync method that you want to use:
- Logical Replication: Enter the Replication Slot name and Publication Name that you created in the Configure incremental sync method - Logical replication step.
- Query-Based: The Capture Deletes toggle is ON by default. Fivetran uses this to detect deleted rows. Once you save the connection, this setting cannot be changed. Turn the toggle OFF before saving if you don't want to capture deletes. For more information, see Capture deletes.

Click Save & Test. Fivetran tests and validates the connection to your PostgreSQL database. After the tests are completed successfully, follow the prompts in the Fivetran dashboard to begin your initial sync.

Setup tests

Fivetran performs the following tests to ensure that we can connect to your generic PostgreSQL database and that it is properly configured:

The Connecting to SSH Tunnel Test validates the SSH tunnel details you provided in the setup form. It then checks that we can connect to your database using the SSH Tunnel. (We skip this test if you aren't connecting using SSH.)
The Connecting to Host 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 Validating Certificate Test generates a pop-up window where you must choose which certificate you want Fivetran to use. It then validates that certificate and checks that we can connect to your database using TLS. (We skip this test if you selected an indirect connection method and then disabled the Require TLS through Tunnel toggle.)
The Connecting to Database Test checks that we can access your database.
The Connecting to WAL Replication Slot Test confirms that the database associated with the replication slot matches the name you supplied in the setup form. It then verifies that the replication slot uses the pgoutput plugin. Lastly, it makes sure that the Fivetran user has replication privileges. (We skip this test if you selected Query-Based as your incremental sync method)
The Checking Configuration Values Test checks a set of WAL-configured values against the recommended settings and detects if they are below the recommended range. (We skip this test if you selected Query-Based as your incremental sync method.)
The Publication Test verifies that the supplied publication name exists in your database. (We skip this test if you selected Query-Based as your incremental sync method.)

The tests may take a few minutes to finish running.

Connector Overview

Schema Information

Release Notes

API Connector Configuration

Documentation Home