Prerequisites for Snowflake Connector for PostgreSQL datasources

Important

Thank you for your interest in the Snowflake Connector for PostgreSQL. Note that we’re now focused on a next-generation solution that will offer a significantly improved experience. Hence, moving this connector to the general availability status is currently not on our product roadmap. You may continue to use this connector as a preview feature, but please note that support for future bug fixes and improvements are not guaranteed. The new solution is available as Openflow Connector for PostgreSQL and includes better performance, customizability, and enhanced deployment options.

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

配置关联的数据源

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:

配置 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.

根据 PostgreSQL 服务器的托管位置，可以通过不同的方式执行此操作

On premise

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

ALTER SYSTEM SET wal_level = logical;
RDS

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

您还需要执行以下操作：

  • 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 AuroraSet the rds.logical_replication static parameter to 1.
GCP

设置以下标记：

  • 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).

AzureSet 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).

配置发布

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>;

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

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

Attention

对于 Postgres v15 及更高版本

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).

创建复制槽

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.

如果不再使用连接器，则必须移除复制槽，以免在 PostgreSQL 服务器中累积数据。

select pg_drop_replication_slot(<slot_name>)

创建所需的用户

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).

后续步骤

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