Prerequisites for Snowflake Connector for PostgreSQL datasources

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 (https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-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 or 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 (https://cloud.google.com/sql/docs/postgres/replication/configure-logical-replication#set-up-logical-replication-with-pglogical).

Azure

Set the replication support to Logical. For more information, see Azure documentation (https://learn.microsoft.com/en-us/azure/postgresql/single-server/concepts-logical#set-up-your-server).

Configure publication

Snowflake Connector for PostgreSQL requires Publication (https://www.postgresql.org/docs/current/logical-replication-publication.html#LOGICAL-REPLICATION-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 (https://www.postgresql.org/docs/current/sql-alterpublication.html).

Create replication slot

Snowflake Connector for PostgreSQL will create Replication Slot (https://www.postgresql.org/docs/current/logicaldecoding-explanation.html#LOGICALDECODING-REPLICATION-SLOTS) 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 (https://www.postgresql.org/docs/current/logical-replication-security.html).

Next steps

After completing these procedures, follow the steps in Setting up the Snowflake Connector for PostgreSQL using Snowsight.

Language: English