Prerequisites for Snowflake Connector for PostgreSQL datasources

Note

The Snowflake Connector for PostgreSQL is subject to the Connector Terms.

Before installing the Snowflake Connector for PostgreSQL, prepare the associated datasource by performing the following tasks:

Configure associated datasource

Ensure that you have a PostgreSQL version 11 or higher server that includes data you want to synchronize with Snowflake. Before installing the Snowflake Connector for PostgreSQL, perform the following in your PostgreSQL environment:

Configure wal_level

Snowflake Connector for PostgreSQL requires wal_level set to logical.

Depending on where PostgreSQL server is hosted it can be done in different ways

On premise

Execute following query with superuser or user with ALTER SYSTEM privilege:

ALTER SYSTEM SET wal_level = logical;
Copy

RDS

User used by the agent needs to have the rds_superuser and rds_replication roles assigned.

You also need to set:

  • rds.logical_replication static parameter to 1.

  • max_replication_slots, max_connections and max_wal_senders parameters according to your database and replication setup.

AWS Aurora

Set the rds.logical_replication static parameter to 1.

GCP

Set the following flags:

  • cloudsql.logical_decoding=on.

  • cloudsql.enable_pglogical=on.

For more information, see Google Cloud documentation.

Azure

Set the replication support to Logical. For more information, see Azure documentation.

Create publication

Snowflake Connector for PostgreSQL requires Publication to be created for all tables and events.

Login as user with CREATE privilege in the database and execute following query:

CREATE PUBLICATION <publication name> FOR ALL TABLES;
Copy

Create replication slot

Snowflake Connector for PostgreSQL will create Replication Slot in PostgreSQL server with name having pattern sf_db_conn_rs_kbmd_<DATASOURCE NAME>, where <DATASOURCE NAME> is the one specified in ADD_DATA_SOURCE procedure.

If the connector is not used anymore, Replication Slot must be removed to not accumulate data in PostgreSQL server.

select pg_drop_replication_slot(<slot_name>)
Copy

Create required user

Create user for Snowflake Connector for PostgreSQL with the REPLICATION attribute. For more information on replication security, see PostgreSQL documentation.

Next steps

After completing these procedures, follow the steps in Opening required ports for Snowflake Connector for PostgreSQL.