PostgreSQL Setup
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).
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.
Last updated on