# PostgreSQL Data Connector Deployment Guide

Operating guide for the PostgreSQL data connector in production: authentication, connection pooling, TLS, metrics, and 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](https://spiceai.org/docs/components/secret-stores) 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](https://spiceai.org/docs/features/data-acceleration/data-refresh)).

## 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. Every metric below is auto-registered (no configuration is required to export it) **except** `replication_truncates_total` and `replication_bootstrap_rows_total`, which 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](https://spiceai.org/docs/features/observability/component_metrics) 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`. |
| `replication_server_wal_end_lsn` | ObservableGauge | Most recent WAL end LSN reported by the Postgres server. |
| `replication_reader_input_wait_micros_total` | ObservableCounter | Microseconds the reader spent blocked awaiting the next source event. High relative to the processing counter ⇒ source-bound. |
| `replication_reader_processing_micros_total` | ObservableCounter | Microseconds the reader spent decoding WAL and building change batches. |
| `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_rows_expected` | ObservableGauge | Estimated bootstrap row count from schema inference. Absent when no estimate exists; `0` means a known-empty source table. |
| `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. |
| `replication_disconnected_ms_total` | ObservableCounter | Cumulative milliseconds the stream was disconnected across all reconnects, including backoff. |
| `replication_member_send_stalled_seconds_total` | ObservableCounter | Seconds the shared-slot pump spent blocked delivering changes into this dataset's channel. Shared slots only. |
| `replication_member_send_wait_micros_total` | ObservableCounter | Microseconds the shared-slot pump spent awaiting this dataset's delivery channel. Dedicated-slot datasets export `0`. |
| `replication_member_attached` | ObservableGauge | `1` while this dataset is an attached member of its shared slot, `0` once detached. Shared slots only. |
| `replication_member_envelopes_delivered_total` | ObservableCounter | Change envelopes delivered to this dataset as distinct units of work. `replication_transactions_total` divided by this is the coalescing factor the apply loop sees. Shared slots only. |
| `replication_member_envelope_eager_merges_total` | ObservableCounter | Committed transactions folded into an envelope the shared-slot pump was still holding back, before delivery. Shared slots only. |
| `replication_member_envelope_mailbox_merges_total` | ObservableCounter | Committed transactions folded into an envelope already sitting unclaimed in this dataset's delivery buffer: the back-pressure-driven half of coalescing. Shared slots only. |
| `replication_member_mailbox_coalesce_limited_total` | ObservableCounter | Times a committed transaction could not be folded into the unclaimed buffer tail because a configured bound refused it. `0` means the bounds never bind. Shared slots only. |

Metric instruments are exposed with the prefix `dataset_postgres_`. Each instrument carries a `name` attribute set to the dataset name; `replication_member_attached` also carries a `slot` attribute for grouping shared-slot members.

## Task History

PostgreSQL operations participate in Spice [task history](https://spiceai.org/docs/reference/task_history) 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](https://spice.ai/docs/building-blocks/data-connectors/postgres#replication-parameters) 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. |