Google Cloud SQL PostgreSQL Setup Guide

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

Prerequisites

To connect your PostgreSQL database to Fivetran, you need:

PostgreSQL version 7.3 or above
IP (e.g. 1.2.3.4) or host (your.server.com)
Port (usually 5432)

Connection options

Google Cloud SQL products require client certificates to connect to a database that is configured to require SSL. Fivetran only supports Server Certificate authentication.

To connect Fivetran to your Google Cloud SQL product, you must perform either of these two options:

Select the Allow Unsecured Connections option from the Connections tab of your Cloud SQL instance.
Create a Google Cloud Virtual Machine to act as a proxy to connect using one of these options:
- SSH tunneling with client certificates configured
- Google Cloud SQL Proxy
- Certificate forwarding using stunnel

Allow access to read replica

If you already have a read replica, you can skip in the instructions to Enable Access.

Creating a read replica is optional but it allows Fivetran to integrate your data without putting unnecessary load on or interrupting the queries running on your Master server.

Click on the name of your Master Instance:

Postgres-gcs-select-master

Select "Create Read Replica":

Postgres-gcs-create-replica

Enable Access

The Fivetran data processing servers will need access to your read replica.

Make sure the replica is accessible to Fivetran:

Postgres-gcs-pauthorize-replica

What you enter for "Network" depends on whether you're connecting directly or via an SSH tunnel: for a direct connection, enter Fivetran's IP. For a connection via an SSH tunnel enter {your-ssh-tunnel-server-ip-address}/32

Once you have finished configuring your replica it will take a few minutes for the read replica to be created or updated with the new settings.

Select your replica's "Overview":

Postgres-gcs-select-overview

Scroll down to find your replica's host IP address, write this down for later use in the connection wizard:

Postgres-gcs-get-host

Create Fivetran user in master database

Open a connection to your Postgres database and execute this SQL command to create a fivetran user:

CREATE USER fivetran PASSWORD 'some-password';

replacing some-password with a password of your choice.

Grant read-only access to Fivetran

Then grant the fivetran user read-only access to all tables:

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;

The last command makes sure that any future tables will be accessible to fivetran. To grant access to schemas other than the Postgres' default public schema, simply replace public with your other schema name. If you have several schemas and you wish to grant access to all of them, you will need to repeat these three commands for each schema.

Restricted permissions (optional)

You can limit Fivetran's access to any schemas, tables, or columns of your choice.

How to allow only a subset of tables

To grant access only to some tables in a schema, first make sure fivetran has access to the schema itself:

GRANT USAGE ON SCHEMA "some_schema" TO fivetran;

Make sure you have removed any previously given 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;

Then repeat this command for each table you wish to include:

GRANT SELECT ON "some_schema"."some_table" TO fivetran;

Any tables created in the future will be excluded from access by default. To include them, run:

ALTER DEFAULT PRIVILEGES IN SCHEMA "some_schema" GRANT SELECT ON TABLES TO fivetran;

To grant access to all tables except a few, unfortunately there is no way to do that directly in Postgres. You will just have to positively include all the tables you do want. Note that it is not possible to achieve exclusion by granting access to all tables and then revoking access for a subset of tables.

How to allow only a subset of schemas or columns

To grant access only to some columns in a table, first make sure you have removed any previously given permission to read all columns in the table:

REVOKE SELECT ON "some_schema"."some_table" FROM fivetran;

Then give permission to the specific columns, e.g. some_column and other_column:

GRANT SELECT (xmin, "some_column", "other_column") ON "some_schema"."some_table" TO fivetran;

Access to the hidden system column xmin is required for incremental updates. Any new columns added to the table in the future will not be accessible to fivetran unless you rerun this command with the new column included.

To grant access to all columns except one, you would have to positively grant access to all the other columns.

Incremental update Configuration

Note - Logical Replication is not supported by Google Cloud SQL PostgreSQL so we use XMIN system column for incremental updates. As a result, our Google Cloud SQL PostgreSQL Postgres connection does NOT support replicating deleted data. If you would like this feature to be supported, please reach out to Google Cloud Support to let them know.

Choose schema prefix

This is the last step of the integration. Each schema from the source database will be mapped to a schema in the destination by adding a prefix to the original schema name. For example, if your original database contains schemas "foo" and "bar" and if you choose the prefix "pre", then you will get schemas "pre_foo" and "pre_bar" in the output.

Documentation