Migrating Data from PostgreSQL¶

This page covers PostgreSQL-specific setup for Data migration. For workflow and Worker field definitions, see Data migration configuration reference.

Prerequisites¶

Npgsql driver (pure Python) on Workers. You don’t install a PostgreSQL ODBC driver for cloud migration.
TLS configuration: Set ssl_mode in Worker TOML (Disable, Prefer, Require, VerifyCA, VerifyFull). Default Require for managed hosts; use Disable for local Docker or lab instances without TLS.

Connectivity and extraction¶

PostgreSQL uses regular extraction (partitioned SELECT through the Worker to Parquet, then the internal migration stage).

Managed host (TLS required)¶

[connections.source.postgresql]
auth_method = "standard"
username = "scai"
password = "your_password"
database = "analytics"
host = "db.example.com"
port = "5432"
ssl_mode = "Require"
use_copy = true

Local Docker or lab (no TLS)¶

[connections.source.postgresql]
auth_method = "standard"
username = "scai"
password = "scai"
database = "scai_pg"
host = "localhost"
port = "5432"
ssl_mode = "Disable"
use_copy = true

Workflow example:

tables:
  - source:
      databaseName: analytics
      schemaName: public
      tableName: customers
    target:
      databaseName: TARGET_DB
      schemaName: PUBLIC
      tableName: customers
    columnNamesToPartitionBy:
      - customer_id

Data type mappings¶

PostgreSQL type	Snowflake target type	Supported for migration	Notes
smallint, integer, bigint, serial types	NUMBER	Yes
numeric(p,s), decimal	NUMBER	Yes	Default NUMBER(20,10) when unspecified
real, double precision	FLOAT	Yes
boolean	BOOLEAN	Yes
date	DATE	Yes
time without/with time zone	TIME	Yes
timestamp without/with time zone	TIMESTAMP_NTZ / TIMESTAMP_TZ	Yes
character, character varying, text	VARCHAR	Yes
bytea	BINARY	Yes
uuid	VARCHAR(36)	Yes
json, jsonb, xml	VARIANT	Yes
money	VARCHAR	Yes	Stored as a text representation
bit, bit varying	VARCHAR	Yes	Stored as a text representation
interval	VARCHAR	Yes	Stored as a text representation
array	ARRAY	Yes
inet, cidr, macaddr types	VARCHAR	Yes	Stored as a text representation
point, line, lseg, box, path, polygon, circle	VARCHAR	Yes	Native PostgreSQL geometric types stored as text
PostGIS geometry	GEOMETRY	Yes
PostGIS geography	GEOGRAPHY	Yes
pg_lsn, pg_snapshot, txid_snapshot	(unmapped)	No

Platform-specific suggestions¶

use_copy = true: PostgreSQL native COPY protocol is enabled by default for cloud migration and significantly improves extraction speed on large tables.
ssl_mode: Set this to match your environment. Most managed cloud PostgreSQL hosts require Require or stricter.
PostGIS columns: Deploy converted DDL with GEOMETRY or GEOGRAPHY target columns before migrating so Snowflake receives the spatial type shown in the table above. Native point, line, and polygon types always map to VARCHAR.
Anti-locking: No automatic hint is added on PostgreSQL. Set queryModifiers only when you need custom source SQL hints. See Anti-locking and query modifiers.