title: 'PostgreSQL Data Connector'
sidebar_label: 'PostgreSQL Data Connector'
description: 'PostgreSQL Data Connector Documentation'
tags:
- data-connectors
- postgres
- write
PostgreSQL is an advanced open-source relational database management system known for its reliability, extensibility, and support for SQL compliance.
The PostgreSQL Server Data Connector enables federated/accelerated SQL queries on data stored in PostgreSQL databases.
Quickstart
Connect to a local PostgreSQL database and accelerate a table for fast local queries:
Start Spice and query the data:
Configuration
from
The from field takes the form postgres:my_table where my_table is the table identifer in the PostgreSQL server to read from.
The fully-qualified table name (database.schema.table) can also be used in the from field.
:::info
Unquoted identifiers are normalized to lowercase. To reference a table or schema with mixed-case characters, wrap each case-sensitive part in double quotes: postgres:my_schema."MixedCaseTable". See Identifier Case Sensitivity.
:::
name
The dataset name. This will be used as the table name within Spice.
Example:
params
The connection to PostgreSQL can be configured by providing the following params:
| Parameter Name | Description |
|---|
pg_connection_string | Optional. The connection string to use to connect to the PostgreSQL server. This can be used instead of providing individual connection parameters. |
pg_host | The hostname of the PostgreSQL server. |
pg_port | The port of the PostgreSQL server. |
pg_db | The name of the database to connect to. |
pg_user | The username to connect with. |
pg_pass | The password to connect with. Use the secret replacement syntax to load the password from a secret store, e.g. ${secrets:my_pg_pass}. |
pg_sslmode | Optional. Specifies the SSL/TLS behavior for the connection, supported values: verify-full: (default) This mode requires an SSL connection, a valid root certificate, and the server host name to match the one specified in the certificate.verify-ca: This mode requires a TLS connection and a valid root certificate.require: This mode requires a TLS connection.prefer: This mode will try to establish a secure TLS connection if possible, but will connect insecurely if the server does not support TLS.: This mode will not attempt to use a TLS connection, even if the server supports it. |
Replication parameters
The following parameters configure PostgreSQL logical replication (WAL streaming) when using refresh_mode: changes:
| Parameter Name | Description |
|---|
pg_replication_slot | Optional. Name of the replication slot to create/reuse. Defaults to spice_<dataset>_<dataset-hash>_<instance-hash>. Each Spice replica MUST have its own unique slot. |
pg_publication | Optional. Name of the publication to create/reuse. Defaults to spice_<dataset>_<dataset-hash>_pub. Shared across replicas for the same dataset. |
pg_replication_initial_snapshot | Optional. Whether to take an initial snapshot of existing rows before streaming WAL changes. Default: true. |
pg_replication_temporary_slot | Optional. If true, create a temporary replication slot that is dropped when the Spice process disconnects. Default: false (durable slot). |
pg_replication_status_interval | Optional. How often to send StandbyStatusUpdate to Postgres (e.g. 10s). Default: 10s. |
pg_replication_bootstrap_batch_size | Optional. Number of rows per emitted batch during the initial replication snapshot. Default: . Maximum: . |
:::warning[pg_sslmode default differs on the WAL replication transport]
The pg_sslmode default of verify-full documented above applies to the federated read/query path. On the WAL replication transport used by refresh_mode: changes, an unset pg_sslmode defaults to prefer, which negotiates a plaintext connection (no certificate verification). Set pg_sslmode to require, verify-ca, or verify-full to force TLS on the WAL stream — see pg_sslmode for WAL streaming.
:::
Types
The table below shows the PostgreSQL data types supported, along with the type mapping to Apache Arrow types in Spice.
| PostgreSQL Type | Arrow Type |
|---|
int2 | Int16 |
int4 | Int32 |
int8 | Int64 |
money | Int64 |
float4 | Float32 |
float8 | Float64 |
numeric | Decimal128 |
text | Utf8 |
varchar | |
:::info
The Postgres federated queries may result in unexpected result types due to the difference in DataFusion and Postgres size increase rules. Explicitly specify the expected output type of aggregation functions when writing queries involving Postgres tables in Spice. For example, rewrite SUM(int_col) into CAST (SUM(int_col) as BIGINT).
:::
Write Support
The PostgreSQL connector supports writing data to PostgreSQL tables using SQL INSERT INTO, UPDATE, and DELETE FROM statements.
To enable writes, set access: read_write on the dataset:
Write modes with acceleration
When PostgreSQL is used as the federated source for an accelerated dataset, acceleration.write_mode selects how writes propagate between the local accelerator and PostgreSQL:
write_through (default) — writes are sent to PostgreSQL synchronously. The client receives an ACK only after the source commits. The local accelerator is updated via the configured refresh path. Choose this for ACID guarantees.write_back — writes are applied to the local accelerator first (fast ACK), then forwarded asynchronously to PostgreSQL. Choose this for write throughput when eventual consistency at the source is acceptable.
acceleration.refresh_mode: changes is supported for access: read_write datasets: writes go to PostgreSQL and the WAL replication stream applies the resulting changes back to the accelerator.
For more details, see Data Ingestion.
Examples
Connecting using Username/Password
Connect using SSL
Separate dataset/accelerator secrets
Specify different secrets for a PostgreSQL source and acceleration:
Secrets
Spice integrates with multiple secret stores to help manage sensitive data securely. For detailed information on supported secret stores, refer to the secret stores documentation. Additionally, learn how to use referenced secrets in component parameters by visiting the using referenced secrets guide.
Cookbook
