---

title: 'PostgreSQL Data Connector Deployment Guide' sidebar_label: 'Deployment Guide' description: 'Operating guide for the PostgreSQL data connector in production: authentication, connection pooling, TLS, metrics, and observability.' sidebar_position: 10 pagination_prev: null pagination_next: null tags:

• data-connectors
• postgres
• observability

---

Production operating guide for the PostgreSQL data connector covering authentication, connection pool sizing, TLS, metrics, and observability.

Authentication & Secrets

The connector uses the native PostgreSQL wire protocol with username/password authentication.

Parameter	Description
pg_host	PostgreSQL server hostname.
pg_port	TCP port (default 5432).
pg_db	Database name.
pg_user	Database user.
pg_pass	Password. Use ${secrets:...} to resolve from a configured secret store.
pg_connection_string	Alternative to the individual parameters.

Passwords must be sourced from a secret store in production. See Secret Stores (opens in a new tab) for configuration options (environment variables, file, Kubernetes, AWS Secrets Manager, HashiCorp Vault).

TLS

TLS is controlled via pg_sslmode:

Value	Behavior
disable	No TLS.
prefer	Try TLS, fall back to plaintext. Not recommended for production.
require	Require TLS; no server certificate verification.
verify-ca	Require TLS and verify the CA chain.
verify-full	(default) Require TLS, verify CA chain, and verify server hostname.

For production, use verify-full with pg_sslrootcert pointing to the CA bundle file path.

Resilience Controls

Connection Pool Sizing

The connector maintains a per-dataset connection pool:

Parameter	Default	Description
pg_connection_pool_min_idle	1	Minimum idle connections held by the pool.
connection_pool_size	5	Maximum connections the pool will open.

When pg_connection_pool_min_idle exceeds connection_pool_size, the pool silently caps idle connections at the pool size.

Size the pool to match concurrent query and refresh load for the dataset. The server's max_connections (default 100) is a shared budget across Spice datasets, other clients, and server-side background workers — plan accordingly, or front Postgres with PgBouncer.

Application Name

The connector automatically sets application_name to the Spice.ai version string, which surfaces in pg_stat_activity.application_name. This value is not configurable.

Retry Behavior

Transient query failures are not automatically retried at the connector layer. Dataset refresh retries are controlled by the acceleration refresh policy (see Data Refresh (opens in a new tab)).

Capacity & Sizing

• Network: Postgres traffic is TCP. Sum connection_pool_size across all Spice datasets sharing the server when sizing max_connections.
• Memory: Result sets are streamed in record batches; memory footprint for federated reads is bounded by DataFusion's batch size (8192 rows default).
• Connection setup cost: TLS handshake and authentication add latency to cold connections. connection_pool_min_idle keeps a warm pool to absorb burst traffic.

Metrics

The PostgreSQL connector exposes observable metrics for its replication pipeline. Most are auto-registered — no configuration is required to export them. replication_confirmed_flush_lsn, replication_server_wal_end_lsn, replication_truncates_total, and replication_bootstrap_rows_total are opt-in and must be listed in the dataset's metrics section. To turn an auto-registered metric off for a dataset, set enabled: false there. See Component Metrics (opens in a new tab) for general configuration.

Metric Name	Type	Description
replication_lag_ms	ObservableGauge	Milliseconds between now and the Postgres commit timestamp of the most recently replicated transaction. Primary freshness signal.
replication_lag_bytes	ObservableGauge	WAL bytes between the server's latest reported position and the last confirmed flush LSN.
replication_confirmed_flush_lsn	ObservableGauge	Most recent LSN acknowledged to Postgres. Matches pg_replication_slots.confirmed_flush_lsn. Opt-in.
replication_server_wal_end_lsn	ObservableGauge	Most recent WAL end LSN reported by the Postgres server. Opt-in.
replication_transactions_total	ObservableCounter	Transactions committed and applied to the accelerator.
replication_inserts_total	ObservableCounter	INSERT operations received from WAL.
replication_updates_total	ObservableCounter	UPDATE operations received from WAL.
replication_deletes_total	ObservableCounter	DELETE operations received from WAL.
replication_truncates_total	ObservableCounter	TRUNCATE operations received from WAL and applied. Opt-in.
replication_bootstrap_rows_total	ObservableCounter	Rows loaded during the initial-snapshot bootstrap. Opt-in.
replication_bootstrap_complete	ObservableGauge	1 once the initial snapshot finished (or was skipped on resume); 0 while it is still running.
replication_decode_errors_total	ObservableCounter	pgoutput decoding errors encountered while parsing WAL events.
replication_schema_mismatch_errors_total	ObservableCounter	Errors where the source relation no longer matches the declared accelerator schema.
replication_recv_errors_total	ObservableCounter	Transport-level errors while receiving from the replication connection.
replication_reconnects_total	ObservableCounter	Times the stream reconnected after a transient failure. Non-zero with no user-visible error just means it recovered.

Metric instruments are exposed with the prefix dataset_postgres_. Each instrument carries a name attribute set to the dataset name.

Task History

PostgreSQL operations participate in Spice task history (opens in a new tab) via the shared SQL data-connector spans. Queries executed against Postgres are captured as child spans of the enclosing sql_query or acceleration_refresh task.

Known Limitations

• Only TCP connections are supported. Unix sockets are not exposed through Spice configuration.
• pg_sslmode: prefer silently downgrades to plaintext and is not recommended for production.
• LISTEN/NOTIFY is not exposed. CDC is supported natively via logical replication (WAL streaming) — see the replication parameters (opens in a new tab) in the connector docs.
• Server-side cursors are used for federated reads; long-running queries hold a backend for their duration.

Troubleshooting

Symptom	Likely cause	Resolution
FATAL: password authentication failed	Incorrect credentials.	Verify credentials via the secret store; test with psql using the same credentials.
FATAL: too many clients already	Pool size + other clients exceeds server max_connections.	Reduce connection_pool_size or raise max_connections / front the server with PgBouncer.
Idle connections never exceed connection_pool_size despite a higher pg_connection_pool_min_idle	The pool silently caps min_idle at the pool size.	Set pg_connection_pool_min_idle to connection_pool_size or lower for clarity.
Sustained active_wait_requests > 0	Pool saturation.	Increase connection_pool_size or reduce concurrent refreshes.
certificate verify failed	pg_sslmode: verify-ca / verify-full with wrong CA or hostname.	Verify pg_sslrootcert matches the server's issuing CA; with verify-full ensure hostname matches SAN.
Sessions lingering with the default app name	Multiple Spice instances share the same version-based name.	The application_name is auto-set to the Spice.ai version and is not currently configurable.