Google Cloud SQL for PostgreSQL Setup Guidelink

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 7.3 or above
Your database's port (usually 5432)
TLS enabled on your database. Follow Google Cloud's TLS setup instructions to enable TLS on your database.

Setup instructionslink

Choose connection method (TLS required) link

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

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:

Allow unsecured connections

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.
Scroll down to the SSL section.
- 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 Google Cloud Virtual Machine

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

Google Cloud SQL Proxy
Certificate forwarding using stunnel
SSH tunneling with client certificates configured

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. Choose a memorable name (for example, fivetran). Replace some-password with a password of your choice.
```
CREATE USER fivetran PASSWORD 'some-password';
```

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 fivetran;
GRANT SELECT ON ALL TABLES IN SCHEMA "public" TO fivetran;
ALTER DEFAULT PRIVILEGES IN SCHEMA "public" GRANT SELECT ON TABLES TO fivetran;

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)

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

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

ALTER DEFAULT PRIVILEGES IN SCHEMA "some_schema" REVOKE SELECT ON TABLES FROM fivetran;
REVOKE SELECT ON ALL TABLES IN SCHEMA "some_schema" FROM fivetran;

Repeat the following command for each table that you want Fivetran to sync.
```
GRANT SELECT ON "some_schema"."some_table" TO fivetran;
```
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 fivetran;
```

Restrict access to columns (optional)

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 fivetran;
```
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 xmin. We use the xmin column for incremental updates.
```
GRANT SELECT (xmin, "some_column", "other_column") ON "some_schema"."some_table" TO fivetran;
```

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.

Request logical replication support (optional)link

Google Cloud SQL for PostgreSQL does not support logical replication, so we must use the XMIN system column for incremental updates. Therefore, our Google Cloud SQL for PostgreSQL connector does not support replicating deleted data. If you would like Google Cloud SQL for PostgreSQL to support logical replication, please reach out to Google Cloud Support to let them know.

Finish Fivetran configurationlink

In your connector setup form, enter a destination schema prefix. This prefix applies to each replicated schema and cannot be changed once your connector is created.
In the Host field, enter your database's IP that you found in Step 3 (for example, 1.2.3.4). Alternately, you can also enter your database host's domain (for example, your.server.com).
Enter your database instance's port number. The port will be 5432, unless you changed the default.
Enter the Fivetran-specific user that you created in Step 4 (for example, fivetran).
Enter the password for the Fivetran-specific user that you created in Step 4.
Enter the name of your database (for example, your_database).
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
Click Save & Test . Fivetran will take it from here and sync your data from your Google Cloud SQL for PostgreSQL database.

Documentation