Google Cloud PostgreSQL
Source and destination
Polytomic connects to a Google Cloud PostgreSQL instance using a Google Cloud service account key and Google's Cloud SQL connector. The connector authorizes connections with the service account rather than by IP address. Before you start, make sure that:
- The Cloud SQL Admin API is enabled in the instance's Google Cloud project.
- The instance has a public IP address. You don't need to add Polytomic's IPs to the instance's authorized networks.
Add the connection
-
Create a service account key. See Create a service account key.
-
Create a user on your PostgreSQL database. You supply this account's credentials to Polytomic. See Create a PostgreSQL user for Polytomic below.
-
In Polytomic, go to Connections → Add connection → Google Cloud PostgreSQL.
-
Enter your connection details:
- Cloud SQL connection name: your instance's connection name in the form
project:region:instance. See Find your connection name. - Database: the name of the database to connect to.
- Username and Password: the PostgreSQL user you created in step 2. When authenticating with IAM, enter the service account's email address as the Username and leave Password empty.
- Service account key: the JSON key file from step 1.
- Cloud SQL connection name: your instance's connection name in the form
- Click Save.
Create a PostgreSQL user for Polytomic
Create a user on your PostgreSQL database and grant it access to the database and schema you want to sync. You'll supply these credentials to Polytomic.
Connect to your instance as a user with the cloudsqlsuperuser role (for example, the default postgres user) and run:
CREATE USER polytomic_user WITH PASSWORD 'my_password';
GRANT CONNECT ON DATABASE my_database TO polytomic_user;
GRANT USAGE ON SCHEMA my_schema TO polytomic_user;Grant read access to the tables you want to sync, including tables created in the future:
GRANT SELECT ON ALL TABLES IN SCHEMA my_schema TO polytomic_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema GRANT SELECT ON TABLES TO polytomic_user;If you want Polytomic to write to PostgreSQL as well, grant the additional permissions listed in Write permissions.
Using IAM authenticationTo authenticate with IAM instead of a password:
- Set the
cloudsql.iam_authenticationdatabase flag toonfor your instance.- Grant the service account from step 1 the Cloud SQL Instance User role (
roles/cloudsql.instanceUser) in addition to Cloud SQL Client.- Add the service account to the instance as an IAM service account user from the Cloud SQL console's Users tab (or with
gcloud). Cloud SQL names the PostgreSQL user after the service account's email without the.gserviceaccount.comsuffix (for example,[email protected]).- Grant that user the same permissions shown above, quoting its name:
GRANT CONNECT ON DATABASE my_database TO "[email protected]";See Google's documentation for details.
CDC replication
For fast incremental Bulk Syncs from Google Cloud PostgreSQL, Polytomic can read your instance's logical replication log instead of scanning your tables. See CDC replication for Google Cloud PostgreSQL.
Updated 15 days ago