Google Cloud SQL for PostgreSQL Setup Guide

IMPORTANT: You must have TLS enabled on your database to connect directly to Fivetran.

Allow unsecured connections to your database.

1. Go to your Google Cloud Platform SQL dashboard.

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

   NOTE: 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.

Create a Google Cloud Virtual Machine to act as a proxy to connect. Use one of the following options:

• Google Cloud SQL Proxy. To set up your GCS Proxy, set up your VM host and add Fivetran's public SSH key to your authorized_keys file. Install the Google Cloud Proxy on that VM.

• Certificate forwarding using stunnel

• SSH tunneling with client certificates configured

IMPORTANT: You must have a Business Critical plan to use Google Cloud Private Service Connect.

Google Cloud Private Service Connect Beta 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 Service Connect for your database.

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.

   

   IMPORTANT: 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.

If you want to limit Fivetran's access to your tables, grant the Fivetran user access to only the tables that you would like to sync. You must individually grant access to each table that you want to sync. 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 "some_schema" TO <username>;
   

2. Revoke any previously granted permission to all tables in that schema.

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

3. Repeat the following command for each table that you want Fivetran to sync.

   GRANT SELECT ON "some_schema"."some_table" TO <username>;
   

4. By default, any tables that you create in the future will not be accessible to the Fivetran user. To grant access to new tables, run the following command.

   ALTER DEFAULT PRIVILEGES IN SCHEMA "some_schema" GRANT SELECT ON TABLES TO <username>;
   

If you want to limit Fivetran's access to the columns in your tables, grant the Fivetran user access to only certain columns. You must individually grant access for each column that you want to sync.

1. Revoke any previously granted permission to read all columns in that table.

   REVOKE SELECT ON "some_schema"."some_table" FROM <username>;
   

2. Grant permission to the specific columns you want to sync (for example, some_column and other_column).

   IMPORTANT: If you chose XMIN as your incremental sync method, you must grant us access to the hidden system column xmin. While it's not mandatory, you can also grant us access to the hidden system column ctid to make your initial sync faster.

   GRANT SELECT (xmin, ctid, some_column, other_column) ON "some_schema"."some_table" TO <username>;
   

Once you restrict access to columns within a table, the Fivetran user will not have access to any new columns added to that table in the future. To grant access to new columns, you must rerun the command above.

IMPORTANT: You can only enable logical replication if your PostgreSQL version is 10 or later. If you want to use logical replication on a read replica, your PostgreSQL version must be 16 or later.

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

   IMPORTANT: 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.

   NOTE: 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 command.

   IMPORTANT: 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 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 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);
   

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

   IMPORTANT: 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.)

   IMPORTANT: You need to create the replication slot after you have created the publication.

    SELECT pg_create_logical_replication_slot('fivetran_pgoutput_slot', 'pgoutput');
   

   NOTE: 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 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.

The XMIN method relies on the hidden xmin system column that is present in all PostgreSQL tables. With XMIN, Fivetran must scan every table in full to detect updated data. The need for a full table scan can lead to significant processing overhead, especially for large tables. Learn more in our XMIN documentation.

You do not need to do any additional configuration for the XMIN method. However, we recommend that you filter frozen pages from incremental syncs. Filtering makes incremental syncs significantly shorter, which means that we use fewer resources in your source database.

(Optional) To filter frozen pages, run the following commands on your primary database as a superuser:

CREATE EXTENSION pg_visibility;

CREATE SCHEMA fivetran;

CREATE OR REPLACE FUNCTION fivetran.get_all_pages(v_table_name character varying)

RETURNS TABLE (
    pagenumber integer,
    all_visible_yn boolean,
    all_frozen_yn boolean)
LANGUAGE plpgsql
SECURITY definer

AS $function$
declare
begin
    RETURN QUERY
    SELECT blkno::int as pageNumber,
           all_visible as all_visible_yn,
           all_frozen as all_frozen_yn
    FROM pg_visibility_map($1::regclass);
END;
$function$;

GRANT USAGE ON SCHEMA fivetran TO <username>;

GRANT EXECUTE ON FUNCTION fivetran.get_all_pages TO <username>;

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 command 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 Teleport mechanism will later use is automatically created for you.