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¶

The recommended extract is regular, which uses the PostgreSQL COPY protocol (use_copy = true, the default for cloud migration). It requires psql on the Worker host.

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¶

INTERVAL columns and intervalHandling¶

PostgreSQL’s interval type can store year-month and day-time fields in the same value. Snowflake has no single interval qualifier that spans both families.

AIM DMV exposes that tradeoff through intervalHandling. Neither setting is universally better: choose based on what you need on Snowflake.

Set intervalHandling at the workflow root, on defaultTableConfiguration, or per table. Use the same value later during validation so comparisons match how the data was migrated. For the property reference, see INTERVAL data type handling.

Prompt:

For the EVENTS table, store INTERVAL columns as text instead of native INTERVAL so we keep exact year-month fields

Incremental sync and VACUUM FREEZE¶

PostgreSQL supports incremental sync with both the watermark and checksum strategies. For checksum, PostgreSQL is a special case: instead of hashing column values, the default partition checksum fingerprints each partition from the MVCC system column xmin (the inserting transaction ID of each row). It reads no column data, so change detection stays cheap even on wide tables.

The one caveat comes from VACUUM FREEZE. To protect against transaction-ID wraparound, freezing rewrites each row’s xmin to a fixed “frozen” value. That has two effects on checksum sync:

• A frozen partition may re-migrate once (safe). Freezing changes a partition’s fingerprint even though no data changed, so the next sync run re-extracts that partition one extra time. This costs time but never loses data.
• A change may be missed (rare). Once every row in a partition is frozen, the partition’s fingerprint depends only on its row count. If rows are updated and then frozen again between two consecutive sync runs, the next run can reproduce the previous fingerprint and skip the partition. Reaching this requires roughly vacuum_freeze_min_age transactions (50 million by default) plus a vacuum landing between two runs, so it isn’t reachable at a normal sync cadence.

If a table is subject to aggressive freezing and you need value-based detection, set a value-derived synchronization.checksumExpression (for example MAX(updated_at)) or use the watermark strategy with a monotonic column. See Changes a checksum may not detect.

xmin-based detection reads the physical row header, so it works on ordinary tables and on declarative-partitioned parent tables, but not on views or foreign tables.

Platform-specific considerations¶

• 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. When you set up the Worker connection, confirm both COPY and ssl_mode match your host.

  Prompt:

  Copy code

  Set up PostgreSQL data migration for my project with COPY extraction and the right ssl_mode for my host
  

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

Related content¶

• Data migration
• Validating Data from PostgreSQL
• Manual Migration: Data migration