Google Cloud SQL for PostgreSQL Setup Guide link

Updated November 16, 2023

Follow these instructions to replicate your Google Cloud SQL for PostgreSQL database to your destination using Fivetran.

Prerequisiteslink

To connect your Google Cloud SQL for PostgreSQL database to Fivetran, you need:

PostgreSQL version 9.6 - 14
Your database's port (usually 5432)
TLS enabled on your database, if you choose to connect directly to Fivetran. Follow Google Cloud's TLS setup instructions to enable TLS on your database.

Setup instructionslink

Choose connection methodlink

Google Cloud SQL products require client certificates to connect to databases that are configured to require SSL. However, Fivetran only supports server certificate authentication.

To connect Fivetran to your Google Cloud SQL for PostgreSQL database, do one of the following:

Connect directlylink

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

Allow unsecured connections to your database.

Go to your Google Cloud Platform SQL dashboard.
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 master database you plan to replicate. The master database's settings will be propagated to the replica.
In the left menu, go to the Connections tab.
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.

Connect using Google Cloud Virtual Machinelink

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

Connect using Google Cloud Private Service Connect Betalink

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

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 Service Connect for your database.

Create read replica (optional)link

If you'd like, create a read replica for Fivetran's exclusive use. Using a read replica allows Fivetran to integrate your data without putting unnecessary load on or interrupting the queries running on your master server. We recommend that you connect a read replica to Fivetran, but it's not required.

If you already have a read replica or want to connect Fivetran to your master database, skip ahead to Step 3.

Go to your Google Cloud Platform SQL dashboard.
Click on the name of your master database.
In the left menu, go to the Replicas tab.
Click Create Read Replica.
On the Create read replica page, enter an Instance ID for the replica.
Click Done.
Click Create. It will take a few minutes for the read replica to be created.

Enable database accesslink

Grant Fivetran's data processing servers access to your database.

In your Google Cloud Platform SQL dashboard, click on your master database or read replica.
In the left menu, go to the Connections tab.
In the Connectivity section, click Add network.
In the New network window, create a network for Fivetran. What you enter in the Network field depends on whether you're connecting directly or using an SSH tunnel.
- If you're connecting directly, enter Fivetran's IPs for your database's region.
- If you're connecting using an SSH tunnel, enter {your-ssh-tunnel-server-ip-address}/32.
Click Save. It will take a few minutes for the database to be updated with the new settings.
In the left menu, go to the Overview tab.
In the Connect to this instance section, find your database's public IP address and make a note of it. You will need it to configure Fivetran.

Create user link

Create a database user for Fivetran's exclusive use.

Open a connection to your PostgreSQL master database in a PostgreSQL console (such as a SQL workbench or psql).
Create a user for Fivetran by executing the following SQL command. Replace <username> and some-password with a username and password of your choice.
```
CREATE USER <username> PASSWORD 'some-password';
content_copy
```

Grant read-only accesslink

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 the schema name.

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>;
content_copy

NOTE: The last command makes sure that any future tables will be accessible to Fivetran.

If you want to grant access to multiple schemas, you must run these three commands for each schema.

Restrict access to tables (optional)link

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.

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

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>;
content_copy

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

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

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>;
content_copy
```

Restrict access to columns (optional)link

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.

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

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

Grant permission to the specific columns you want to sync (for example, some_column and other_column).
IMPORTANT: You must grant us access to the hidden system column ctid, which we need to perform initial syncs. Also, if you chose XMIN as your incremental update mechanism, you must grant us access to the hidden system column xmin.
```
GRANT SELECT (xmin, ctid, some_column, other_column) ON "some_schema"."some_table" TO <username>;
content_copy
```

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.

Choose incremental sync mechanismlink

To keep your data up to date after the initial sync, we use one of the following incremental sync methods:

The first three methods keep a record of recent data changes, which allows Fivetran to update only the data that has changed since our last sync. Fivetran Teleport Sync instead takes snapshots of tables to calculate differences.

TIP: We recommend using logical replication as your incremental update mechanism because it is faster than XMIN replication and allows Fivetran to detect deleted rows for tables with primary keys. Learn more in our Updating data documentation.

Choose logical replication with the pgoutput plugin, logical replication with the test_decoding plugin, XMIN or Fivetran Teleport Sync as your incremental update mechanism.

To enable logical replication with the pgoutput plugin, proceed to the pgoutput section.
To enable logical replication with the test_decoding plugin, proceed to the test_decoding section. SUNSET
To enable XMIN, proceed to the XMIN section.
To enable Fivetran Teleport Sync, proceed to the Fivetran Teleport Sync section.

Logical replication with the `pgoutput` pluginlink

IMPORTANT: You can only enable logical replication with the pgoutput plugin if your Google Cloud PostgreSQL version is 10 or later.

To enable logical replication with the pgoutput plugin, follow these steps:

Connect to your master database. You cannot enable logical replication on a read replica.
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 connector to reset the cursor in the replication slot.
In your Google Cloud platform SQL dashboard, set the cloudsql.logical_decoding flag to on.
Click Done to save the change.
Go into your 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.
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.
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;
content_copy
```
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;
content_copy
```
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;
content_copy
```
(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');
content_copy
```
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);
content_copy
```
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 connector will not be able to find the slot.
IMPORTANT: You must create a unique replication slot for every connector 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');
    ```

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

    ```
    SELECT * FROM pg_publication_tables;
    ```

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

    ```
    ALTER ROLE fivetran WITH REPLICATION;
    ```

1. Log in as the Fivetran user.

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

#### Logical replication with the `test_decoding` plugin [object Object]

> IMPORTANT: You can only enable logical replication with the `test_decoding` plugin if your Google Cloud PostgreSQL version is 9.6 or later.

To enable logical replication with the `test_decoding` plugin, follow these steps:

1. Connect to your master database. You cannot enable logical replication on a read replica.


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 connector to reset the cursor in the replication slot.

1. In your Google Cloud platform SQL dashboard, set the `cloudsql.logical_decoding` flag to `on`.
   ![gcs-click-resource](docsMedia1160869883)

1. Click **Done** to save the change.

1. Go into your 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.

1. Set the `statement_timeout` parameter in your database to either `0` 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`, the database will automatically reset to the default value when your session ends.

1. Create a logical replication slot for the database you want to sync by running the following command. You _must_ use the output plugin `test_decoding` supplied in the `postgresql-contrib` subpackage. Ensure that you are connected to the correct database when you create your replication slot, or your connector will not be able to find the slot.

   > IMPORTANT: The replication slot name `fivetran_replication_slot` quoted throughout this guide is used purely as an example. The actual [replication slot name](https://www.postgresql.org/docs/current/logicaldecoding-explanation.html#LOGICALDECODING-REPLICATION-SLOTS) should be unique for every connector using the same [PostgreSQL cluster](https://www.postgresql.org/docs/current/creating-cluster.html). Replication slot names cannot start with a number.

    ```
    SELECT pg_create_logical_replication_slot('fivetran_replication_slot', 'test_decoding');
    ```

1. Grant permission to the Fivetran user for reading the replication slot.

    ```
    ALTER ROLE fivetran WITH REPLICATION;
    ```

1. Log in as the Fivetran user.

1. Verify that the Fivteran user can read the replication slot by running the following command.

    ```
    SELECT count(*) FROM pg_logical_slot_peek_changes('fivetran_replication_slot', null, null);
    ```

If the query succeeds, then permissions are sufficient.

#### XMIN
You do not need to do any additional configuration for the XMIN method.

#### Fivetran Teleport Sync

If you are trying to connect with a standby or read replica, run the following SQL commands:

content_copy

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.


### <span class="step-item">Finish Fivetran configuration</span>

1. In your [connector setup form](/docs/using-fivetran/fivetran-dashboard/connectors#addanewconnector), enter a destination schema prefix. This prefix applies to each replicated schema and cannot be changed once your connector is created.

2. In the **Host** field, enter your database's IP that you found in [Step 3](/docs/databases/postgresql/gcs-setup-guide#enabledatabaseaccess) (for example, `1.2.3.4`). Alternately, you can also enter your database host's domain (for example, `your.server.com`).

3. Enter your database instance's **port** number. The port will be `5432`, unless you changed the default.

4. Enter the Fivetran-specific **user** that you created in [Step 4](/docs/databases/postgresql/gcs-setup-guide#createuser).

5. Enter the **password** for the Fivetran-specific user that you created in [Step 4](/docs/databases/postgresql/gcs-setup-guide#createuser).

6. Enter the name of your **database** (for example, `your_database`).

7. Choose your **connection method**. If you selected **Connect via an SSH tunnel**, provide the following information:
    - SSH hostname (do not use a load balancer's IP address/hostname)
    - SSH port
    - SSH user
    > IMPORTANT: If you connected through a Google Cloud SQL Proxy, use `127.0.0.1` as the host name and the Google Cloud Proxy's external IP as the SSH host. You must also disable TLS encryption.

8. Click **Save & Test**. Fivetran tests and validates our connection to your Google Cloud SQL for PostgreSQL database. Upon successful completion of the setup tests, you can sync your data using Fivetran.

-----------

### Setup tests
Fivetran performs the following tests to ensure that we can connect to your Google Cloud 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 aren't connecting directly.)
- 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` if you selected WAL with pgoutput update method, or the `test_decoding` plugin if you selected WAL with test_decoding update method. Lastly, it makes sure that the Fivetran user has replication privileges. (We skip this test if you selected XMIN as your incremental update mechanism.)
- 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 XMIN as your incremental update mechanism.)
- The Publication Test verifies that the supplied publication name exists in your database. (We skip this test if you selected XMIN or WAL with test_decoding plugin as your incremental update mechanism.)
- The Validating Speed Setup test validates Fivetran can fetch data from your source database quickly enough. During this test, we measure our ability to download sample data from your source database to Fivetran, but we do _not_ perform a full sync. We start a timer, then download the sample data in memory. We then calculate the connector speed based on how much data we downloaded and how long it took to download. The test shows a warning if the download speed is less than 5MB/sec.
- The XMIN Extensions test checks that the correct extensions are enabled for XMIN. (This test is skipped if you did not select XMIN as your incremental update mechanism.)
> NOTE: The tests may take a few minutes to finish running.

-----
## Related articles

[<i aria-hidden="true" class="material-icons">description</i> Connector Overview](/docs/databases/postgresql)

<b> </b>

[<i aria-hidden="true" class="material-icons">account_tree</i> Schema Information](/docs/databases/postgresql#schemainformation)

<b> </b>

[<i aria-hidden="true" class="material-icons">assignment</i> Release Notes](/docs/databases/postgresql/gcs-setup-guide/changelog)

<b> </b>

[<i aria-hidden="true" class="material-icons">settings</i> API Connector Configuration](/docs/rest-api/connectors/config#postgres)

<b> </b>

[<i aria-hidden="true" class="material-icons">home</i> Documentation Home](/docs/getting-started)
content_copy

Documentation