doc/user/content/ingest-data/postgres/connection-closed.md
This guide helps you troubleshoot and resolve the "connection closed" error that can occur with PostgreSQL sources in Materialize.
When you see an error like:
postgres: connection closed
This means the network connection between Materialize and your PostgreSQL database was unexpectedly terminated. The connection that Materialize uses to replicate data from PostgreSQL was closed, interrupting the replication process.
{{< note >}} This error is known to occur during Materialize maintenance windows and can be safely ignored if that is the case. Sources will automatically reconnect after maintenance is complete. {{</ note >}}
Verify your PostgreSQL connection configuration in Materialize:
SELECT name, connection_type
FROM mz_connections
WHERE type = 'postgres';
Test basic connectivity from Materialize to your PostgreSQL host. Verify that:
Review your PostgreSQL logs for connection-related messages:
SELECT * FROM pg_stat_activity
WHERE state_change < NOW() - INTERVAL '60 minutes';
Look for log entries indicating:
Verify you haven't exceeded connection limits:
SELECT
max_conn,
used,
res_for_super,
max_conn - used - res_for_super AS remaining
FROM
(SELECT count(*) AS used FROM pg_stat_activity) t1,
(SELECT setting::int AS res_for_super FROM pg_settings WHERE name = 'superuser_reserved_connections') t2,
(SELECT setting::int AS max_conn FROM pg_settings WHERE name = 'max_connections') t3;
Check timeout settings that might close connections:
SHOW tcp_keepalives_idle;
SHOW tcp_keepalives_interval;
SHOW tcp_keepalives_count;
SHOW statement_timeout;
In most cases, Materialize will automatically attempt to reconnect to PostgreSQL when a connection is closed. Monitor your source to see if it recovers on its own.
You can check the source status with:
SELECT *
FROM mz_internal.mz_source_statuses
WHERE id = 'your_source_id';
1. Increase connection keepalive settings
Configure PostgreSQL to keep connections alive longer by adjusting TCP keepalive
settings in postgresql.conf:
tcp_keepalives_idle = 60
tcp_keepalives_interval = 10
tcp_keepalives_count = 5
Then reload the configuration:
SELECT pg_reload_conf();
2. Configure connection timeout on network devices
If using load balancers or proxies, ensure their idle timeout settings are appropriate for long-lived replication connections:
3. Increase PostgreSQL connection limits
If hitting connection limits, increase max_connections in postgresql.conf:
max_connections = 200
{{< note >}}
Increasing max_connections may require more shared memory. You may also need to
adjust shared_buffers and other memory settings.
{{</ note >}}
4. Review and update SSL certificates
If using SSL, verify certificate validity:
SELECT ssl,
sslversion,
sslcipher
FROM pg_stat_ssl
JOIN pg_stat_activity ON pg_stat_ssl.pid = pg_stat_activity.pid;
Ensure certificates are renewed before expiration.
5. Implement network stability improvements
Best practices to avoid this error:
max_connections).RDS may terminate idle connections after a period of inactivity. Ensure:
Cloud SQL has connection limits based on instance size:
Azure databases have connection limits and idle timeout policies:
You have full control over connection settings: