PostgreSQL Setup

Prerequisites

• PostgreSQL 9.4 or higher, self managed or managed (AWS RDS, AWS Aurora, Google Cloud SQL, Azure Database).
• Privileges to create users and grant permissions.
• Network connectivity from the Supermetal agent to the database.

Setup

Log in to your PostgreSQL database and run the following:

-- Create a user for Supermetal
CREATE USER supermetal_user WITH PASSWORD 'strong-password';

-- Grant permissions
-- allows supermetal to connect to the database, create schemas and (temp) tables
GRANT CONNECT, CREATE, TEMPORARY ON DATABASE target_database TO supermetal_user;

Foreign Key Constraints

If the destination schema has foreign key constraints, tables loaded in parallel may temporarily violate them until all referenced rows arrive. To let Supermetal defer constraint checks during writes, grant elevated privileges:

-- AWS RDS
GRANT rds_superuser TO supermetal_user;

-- Standard PostgreSQL (run as superuser)
ALTER USER supermetal_user WITH SUPERUSER;

Snapshot Performance

For large snapshots, tune checkpointing on the target server to keep COPY throughput high and avoid I/O storms. Checkpoints trigger when either limit is reached first.

• max_wal_size. Maximum WAL size before a checkpoint. COPY generates WAL rapidly, and the 1GB default causes frequent checkpoints that throttle loads. Increase to 16GB to 64GB.
• checkpoint_timeout. Maximum time between checkpoints. The 5 minute default triggers checkpoints before large snapshots complete. Increase to 30 minutes to 1 hour.

Lock Timeout

Data operations (COPY, INSERT, MERGE) wait indefinitely when a target table is locked by a long running transaction, DDL, or maintenance. Setting the operation lock timeout makes them fail fast instead, applying a lock_timeout in seconds to each operation.