PostgreSQL
Supermetal replicates from PostgreSQL using logical replication for change data capture and parallel chunked snapshots for initial loads.
Prerequisites
- PostgreSQL 9.4 or higher.
- Privileges to create users, grant permissions, and change configuration parameters. This may require
superuseror a cloud provider role such asrds_superuseron AWS RDS orazure_pg_adminon Azure. - Network connectivity from the Supermetal agent to the database, which may require firewall, security group, or VPC changes.
Setup
Supermetal supports three replication types for PostgreSQL sources.
- Logical replication. Realtime change data capture with an initial snapshot. Recommended when available.
- Query-based. Incremental sync using the xmin system column.
- Snapshot. Full table copies, once or on a schedule.
Partitioned tables
By default, each partition replicates as its own table. The partitions as root option syncs the partitioned table as a single root instead. This option applies only to logical replication and requires a publication created WITH (publish_via_partition_root).
TOAST storage
PostgreSQL stores large values out of line and omits them from the logical replication stream when an update leaves them unchanged. The TOAST mode option controls how Supermetal handles unchanged TOAST values.
| Mode | Behavior |
|---|---|
| Replica identity full | Supermetal sets REPLICA IDENTITY FULL on tables with TOAST columns so every change carries the previous row. The default. |
| Placeholder | Supermetal preserves the previous value on the target. |
Target support
Placeholder mode is currently only supported for Snowflake targets.
Placeholder mode covers a table when it has a primary key, its replica identity is DEFAULT, and every column that PostgreSQL can store out of line is a supported character, JSON, XML, binary, or unconstrained numeric type. Bounded numeric columns stored as decimals require REPLICA IDENTITY FULL when they can be stored out of line. Validation lists each table outside coverage with its reason.
When an update changes a primary key and leaves a TOAST value unchanged, the value under the new key is NULL until a backfill restores it.
Read replicas
Supermetal can replicate from a read replica or physical standby. Query-based and snapshot replication need only a read only connection. Logical replication is supported on PostgreSQL 16 and later.
Recovery conflicts
Snapshots on a standby can conflict with WAL replay if the primary vacuums rows the query still needs. Increase max_standby_streaming_delay, enable hot_standby_feedback, or both on the standby.
Set these up before you point the connector at the standby.
- Create the publication on the primary. A standby is read only and cannot create one.
- Set
REPLICA IDENTITY FULLon the primary for tables that need it, such as tables without a primary key. - Enable
hot_standby_feedbackon the standby. Without it the primary vacuums catalog rows the slot still needs and PostgreSQL invalidates the slot.
Point the connector at the standby and set the publication name to the one you created on the primary.
Slot creation can stall on an idle primary
Supermetal creates the slot on the standby, but PostgreSQL cannot complete it until the primary emits a running xacts record. Against an idle primary the step can appear to hang. Write to the primary, or run SELECT pg_log_standby_snapshot() on the primary to release it.
High availability
Supermetal supports fast failover for clustered PostgreSQL (AWS Aurora, RDS Multi AZ).
- Connect to the cluster writer endpoint (
my-cluster.cluster-xyz.us-east-1.rds.amazonaws.com) rather than an instance address. - During logical replication, Supermetal detects failovers and resumes streaming from the new primary, typically in under 10 seconds.
On PostgreSQL 17 and later, Supermetal creates the slot with failover enabled when it connects to a primary. Where standbys synchronize slots, the slot survives promotion and replication continues on the new primary.
Transient connection errors (network timeouts, server restarts, failovers) retry with backoff for up to 5 minutes, configurable through the retry window option.
Data Types Mapping
| PostgreSQL Type(s) | Apache Arrow DataType | Notes |
|---|---|---|
BOOL | Boolean |
| PostgreSQL Type(s) | Apache Arrow DataType | Notes |
|---|---|---|
INT2 | Int16 | |
INT4 | Int32 | |
INT8, MONEY | Int64 | MONEY maps to a fixed point integer. |
FLOAT4 | Float32 | Single precision. |
FLOAT8 | Float64 | Double precision. |
NUMERIC(p, s) | Decimal128(p, s) / Decimal256(p, s) | Precision and scale carry over directly. |
NUMERIC (unconstrained) | Utf8 | Unconstrained NUMERIC has no defined precision or scale, so it maps to text. |
OID | UInt32 | Object identifier. |
PG_LSN | UInt64 | Log sequence number. |
| PostgreSQL Type(s) | Apache Arrow DataType | Notes |
|---|---|---|
DATE | Date32 | Days since UNIX epoch. |
TIME | Time64(µs) | Microseconds since midnight. |
TIMESTAMP | Timestamp(µs) | Microseconds since UNIX epoch, timezone naive. |
TIMESTAMPTZ | Timestamp(µs, "UTC") | Microseconds since UNIX epoch, in UTC. |
TIMETZ, INTERVAL | Utf8 | Represented as text. |
| PostgreSQL Type(s) | Apache Arrow DataType | Notes |
|---|---|---|
CHAR, VARCHAR, TEXT, BPCHAR, NAME, UNKNOWN | Utf8 | |
citext, ltree, lquery, ltxtquery | Utf8 | |
BIT, VARBIT | Utf8 | Bit strings represented as text. |
UUID | Utf8 | |
ENUM | Utf8 | Represented as text labels. |
INET, CIDR, MACADDR, MACADDR8 | Utf8 | Network address types. |
TSVECTOR | Utf8 | Text search vector. Positions are not preserved. |
| PostgreSQL Type(s) | Apache Arrow DataType | Notes |
|---|---|---|
BYTEA | Binary | Variable length byte array. |
| PostgreSQL Type(s) | Apache Arrow DataType | Notes |
|---|---|---|
JSON, JSONB | Utf8 | Carries the Canonical JSON Extension (arrow.json) metadata, so targets with semi structured types map it accordingly (Snowflake VARIANT, ClickHouse JSON). |
XML | Utf8 |
| PostgreSQL Type(s) | Apache Arrow DataType | Notes |
|---|---|---|
BOOL[] | List<Boolean> | Base types map as per their categories. |
BYTEA[] | List<Binary> | |
INT2[] | List<Int16> | |
INT4[] | List<Int32> | |
INT8[], MONEY[] | List<Int64> | |
OID[] | List<UInt32> | |
PG_LSN[] | List<UInt64> | |
FLOAT4[] | List<Float32> | |
FLOAT8[] | List<Float64> | |
NUMERIC(p, s)[] | List<Decimal(p,s)> | Precision and scale preserved within list elements. |
DATE[] | List<Date32> | |
TIME[] | List<Time64(µs)> | |
TIMESTAMP[] | List<Timestamp(µs)> | |
TIMESTAMPTZ[] | List<Timestamp(µs, "UTC")> | |
CHAR[], VARCHAR[], TEXT[], etc., _citext, _ltree, etc., BIT[], VARBIT[], TIMETZ[], INTERVAL[], XML[], UUID[], INET[], CIDR[], MACADDR8[], MACADDR[], JSON[], JSONB[], NUMERIC[] (unconstrained) | List<Utf8> | Arrays of types that map to Utf8. |
| Multidimensional arrays | Unsupported | Arrays with more than one dimension are not supported. |
| PostgreSQL Type(s) | Apache Arrow DataType | Notes |
|---|---|---|
INT4RANGE, INT8RANGE, NUMRANGE, TSRANGE, TSTZRANGE, DATERANGE | Utf8 | Serialized to JSON with the Canonical JSON Extension (arrow.json). |
| Multirange types | Unsupported |
| PostgreSQL Type(s) | Apache Arrow DataType | Notes |
|---|---|---|
DOMAIN(base_type) | Mapped as base_type | Domains resolve to their underlying base type. |
| Composite types (ROW) | Utf8 | Represented as text (JSON). |
| PostgreSQL Type(s) | Apache Arrow DataType | Notes |
|---|---|---|
POINT, LSEG, PATH, BOX, POLYGON, LINE, CIRCLE | Unsupported |
PostgreSQL to PostgreSQL replication
When replicating from PostgreSQL to PostgreSQL, Supermetal replicates the source data types 1:1, preserving the exact type from source to target when possible.
Changelog
0.1.10
2026-07-02
New xmin query-based replication mode for when logical replication is not available, for example on read replicas running PostgreSQL below 16.
Fixes for logical replication from read replicas.
Export snapshot transactions in snapshot and xmin modes are kept alive during long initial loads and over idle bastion tunnels.
TLS handshake failures on the replication client are now retried.
0.1.7
2026-06-16
SSH tunnel errors now surface during validation.
0.1.2
2026-05-21
Source connections can route through an SSH tunnel to a bastion host.
Last updated on