PostgreSQL Setup
Prerequisites
Before you begin, ensure you have:
- Supported PostgreSQL Version: PostgreSQL 9.4 or higher.
- Database Admin Access: Sufficient privileges to create users and grant permissions.
- Network Connectivity: Ensure that Supermetal's services can reach your PostgreSQL database. You may need to configure security groups.
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;Password Security
Replace 'strong-password' with a secure, unique password for the supermetal_user. Store this password securely, as you'll need it when configuring the target in Supermetal.
Script Variables
Replace the placeholders in the script with your specific values:
target_database: The name of your PostgreSQL database
Foreign Key Constraints (Optional)
If your destination schema has foreign key constraints, tables loaded in parallel may temporarily violate them until all referenced rows arrive. To 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 Load Performance (Optional)
For large snapshots, optimize checkpoint frequency to maximize COPY throughput and avoid I/O storms. Checkpoints trigger when either limit is reached first.
max_wal_size: Maximum WAL size before checkpoint. COPY generates WAL rapidly, and small values (default: 1GB) cause frequent checkpoints that throttle performance. Increase to 16GB - 64GB to allow bulk loads to complete with fewer I/O interruptions.checkpoint_timeout: Maximum time between checkpoints. Default (5min) triggers checkpoints before large snapshots complete. Increase to 30min - 1h to prevent premature interruptions during extended loads.
Last updated on