PostgreSQL on Google Cloud

Share on facebook
Share on twitter
Share on linkedin

Snowflake Cost Saving

We Automate SnowflakeDB Data Cloud Cost Saving. Sign Our Free 7 Days No Risk Trail Now

Data Connector Description:

Data Connector Type: Database

Data Connector Documentation:

[Google Cloud SQL PostgreSQL Setup Guide, Follow these instructions to replicate your Google Cloud SQL PostgreSQL database to your destination using 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), Choose a connection method, Google Cloud SQL products require client certificates to connect to databases that are configured to require SSL. Fivetran only supports , Server Certificate authentication, ., To connect Fivetran to your Google Cloud SQL database, do , one, of the following steps:, 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, Create a read replica (optional), “We recommend that you create a read replica to connect to Fivetran, but its not mandatory. Using a read replica allows Fivetran to integrate your data without putting unnecessary load on or interrupting the queries running on your master server.”, If you already have a read replica or want to connect Fivetran to your master database, skip to the , Enable access section, ., Click on the name of your master instance., Select , Create Read Replica, ., Enable access, “Fivetrans data processing servers need access to your read replica.”, Go to your replica in the GCP dashboard., Use the , Authorize networks, section to grant Fivetran access to your read replica. What you enter in the , Network, ” field depends on whether youre connecting directly or using an SSH tunnel.”, “If youre connecting directly, enter “, “Fivetrans IP”, ., “If youre connecting using an SSH tunnel, enter “, {your-ssh-tunnel-server-ip-address}/32, ., It will take a few minutes for the read replica to be created or updated with the new settings., “Select your replicas “, Overview, ., “Scroll down to find your replicas host IP address and make a note of it. You will need it to configure Fivetran.”, Create Fivetran user , Open a connection to your master PostgreSQL database., Create a user for Fivetran by executing the following SQL command. Replace , some-password, with a password of your choice., “CREATE USER fivetran PASSWORD some-password;n”, Grant read-only access, Grant the , fivetran, ” user read-only access to all tables by running the following commands. To grant access to a schema other than PostgreSQLs default “, public, schema, replace , public, with the schema name., GRANT USAGE ON SCHEMA “public” TO fivetran;nGRANT SELECT ON ALL TABLES IN SCHEMA “public” TO fivetran;nALTER DEFAULT PRIVILEGES IN SCHEMA “public” GRANT SELECT ON TABLES TO fivetran;n, 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 Fivetrans access to your data, grant the “, fivetran, user access to only the tables that you would like to sync. You need to individually grant access for each table that you want to sync. It is not possible to achieve exclusion by granting access to all tables and then revoking 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;n, Revoke any previously granted permission to all tables in that schema., ALTER DEFAULT PRIVILEGES IN SCHEMA “some_schema” REVOKE SELECT ON TABLES FROM fivetran;nREVOKE SELECT ON ALL TABLES IN SCHEMA “some_schema” FROM fivetran;n, Repeat the following command for each table you want Fivetran to sync., GRANT SELECT ON “some_schema”.”some_table” TO fivetran;n, By default, any tables that you create in the future will be excluded from the , fivetran, ” users access. To grant access to new tables, run the following command.”, ALTER DEFAULT PRIVILEGES IN SCHEMA “some_schema” GRANT SELECT ON TABLES TO fivetran;n, Restrict access to columns (optional), You can also grant the , fivetran, user access to only certain columns within a table. You need to individually grant access for each column that you want to sync., NOTE: We need access to the hidden system column , xmin, for incremental updates., Ensure that you have revoked any previously granted permission to read all columns in the table., REVOKE SELECT ON “some_schema”.”some_table” FROM fivetran;n, Grant permission to the specific columns you want to sync (for example some_column, and , other_column, )., GRANT SELECT (xmin, “some_column”, “other_column”) ON “some_schema”.”some_table” TO fivetran;n, 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., Configure incremental updates, Our Google Cloud SQL PostgreSQL connector does , not, support replicating deleted data. Google Cloud SQL PostgreSQL does not support , logical replication so we must use the , XMIN system column, for incremental updates. If you would like Google Cloud SQL PostgreSQL to support logical replication, 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., Related articles, description, Connector Overview, , account_tree, Schema Information, , assignment, Release Notes, , settings, API Connector Configuration, , home, Documentation Home]

Share on facebook
Share on twitter
Share on linkedin

Join The Club

Every week, we'll be sending you curated materials handpicked by professionals. Plus, you'll be the first to know about our latest data!