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

To connect directly, you must do the following:

1. Enable TLS on your Google Cloud SQL for PostgreSQL instance. Follow Google Cloud's instructions to enable SSL/TLS on your instance.

2. Configure your Cloud SQL instance to allow connections from Fivetran. In your instance's security settings, ensure that connections without client certificates are permitted:

   1. Go to your Google Cloud Platform SQL dashboard.

   2. Click on the name of the database that you want to connect to Fivetran.

      If you plan to create a read replica to connect to Fivetran, then edit the primary database you plan to replicate. The primary database's settings will be propagated to the replica.

   3. In the left menu, go to the Connections tab.

   4. Go to the Security tab.

      • If you see Only secured connections are allowed to connect to this instance, click Allow unsecured connections.
      • If you see Unsecured connections are allowed to connect to this instance, you do not need to change anything.

Fivetran connects to a separate server in your network that provides an SSH tunnel to your Google Cloud SQL for 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 Google Cloud SQL for PostgreSQL database. For more information, see our SSH connection setup documentation.

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. Private networking is the most secure connection method.

For more information, see our Google Cloud Private Service Connect setup documentation.

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.

1. Follow the instructions in the Google Cloud Platform SQL service to create a client certificate.

2. Download the client-key.pem and client-cert.pem files to a secure location. You will need them to configure Fivetran.

   

   The client-key.pem and client-cert.pem files are only available to download during the client certificate creation.

3. In the left menu of your Google Cloud SQL dashboard, go to the Connections tab.

4. Click Security in the Connections section.

5. Select Require trusted client certificates under the Manage SSL mode section.

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.

1. Ensure that the Fivetran user has access to the schema that contains your table(s):

   GRANT USAGE ON SCHEMA "your_schema" TO <username>;
   

2. 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>;
   

3. Grant access to each table:

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

4. 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>;
   

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

1. Revoke existing table-level permissions:

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

2. 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.

Logical replication relies on the logical decoding of the PostgreSQL write-ahead log (WAL). Fivetran reads the WAL using the pgoutput plugin to detect any new or changed data. This plugin replicates data from your custom publication without needing additional libraries. Learn more in our logical replication documentation.

To enable logical replication, follow these steps:

1. Ensure that your server has ample free space for the logs. Logs that Fivetran has already processed are released. However, logs are not released if replication stops (for example, if we lose access). In this case, logs may accumulate on your server and consume additional storage. The amount of additional disk space consumed by these logs is proportional to the amount of changes committed on the server. If a lost connection can't be resumed quickly enough, you can drop the replication slot, which releases the storage of unconsumed logs. You would then need to do a full re-sync of your connection to reset the cursor in the replication slot.

2. In your Google Cloud platform SQL dashboard, set the cloudsql.logical_decoding flag to on.

3. Click Done to save the change.

4. Go into your primary Cloud PostgreSQL database as a cloudsqlsuperuser (postgres by default in Google Cloud SQL).

   If your superuser doesn't have permission to create a replication slot, run the alter user postgres with replication; command before you do the following steps.

5. Ensure that the statement_timeout setting on your server is either 0 (the default value to disable the timeout) or greater than 5 minute.

   Do not change the wal_sender_timeout parameter in your database. If you change the wal_sender_timeout, it will automatically reset to the default value when your session ends.

6. 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 SQL statement.

   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 SQL statement. You must have the 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 cloudsqlsuperuser privileges to run this SQL statement.

    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 SQL statement to enable publish_via_partition_root.

   CREATE PUBLICATION fivetran_pub FOR ALL TABLES WITH (publish_via_partition_root=true);
   

7. Create a logical replication slot for the database you want to sync by running the following SQL statement. 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.

8. Verify that your chosen tables are in the publication.

   SELECT * FROM pg_publication_tables;
   

9. Grant the Fivetran user permission to read the replication slot.

   ALTER ROLE fivetran WITH REPLICATION;
   

10. Log in as the Fivetran user.

11. Verify that the Fivetran user can read the replication slot by running the following SQL statement. 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.

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.

Fivetran Teleport Sync is a proprietary incremental sync method that can capture deletes without requiring additional setup beyond a read-only SQL connection. Updates are captured using the xmin system column. Learn more in our Fivetran Teleport Sync documentation.

If you are trying to connect with a standby or read replica, run the following SQL statement on your primary database as the Fivetran user:

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

If you are not connecting with a read replica, you do not need to do any additional configuration. The aggregate that the Fivetran Teleport Sync mechanism will later use is automatically created for you.