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.

Configure publication

Snowflake Connector for PostgreSQL requires Publication to be created and configured.

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

CREATE PUBLICATION <publication name>;
Copy

Then define tables that the Snowflake Connector for PostgreSQL agent will be able to see using:

ALTER PUBLICATION <publication name> ADD TABLE <table name>;
Copy

Attention

For Postgres v15 and later

In case of publications created for subset of table’s columns, please add tables for replication using ADD_TABLE_WITH_COLUMNS procedure, specifying exactly the same set of columns.

If ADD_TABLES will be used, the connector will work, but following non-obvious side effects will occur:

  • in the destination database, columns that are not included in filter will be suffixed with _DELETED. All data replicated during snapshot phase will still be there.

  • in case of adding more columns to the publication, table will result in Permanently Failed state, requiring restarting the replication.

For more information see ALTER PUBLICATION documentation.

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.

Language: English