Relational database of the platform services with the pgvector extension for embeddings. Single server, or high availability with Patroni: streaming replication, automatic failover and leader election in etcd. A PostgreSQL cluster of the bank can be connected instead.
Generated from catalog/services/postgresql.yaml: do not edit by hand.
Summary
| |
|---|
| Version | 16.15 |
| Use | install and configure |
| Layer | functional (AI catalog) |
| Category | Data platforms |
| Installation methods | podman, package, python |
| External connection | postgresql |
| Sandbox only | no |
Licence
| |
|---|
| SPDX identifier | PostgreSQL |
| Licence policy | licence exception |
| Licence exception | LEX-001 (pending, awaiting the client) |
| Platform component | postgresql |
Packages and artifacts
A package the project of the service publishes (its image, its binary archive, its Python or OS package) carries the licence of the service; a third-party package carries the licence written on its row, read from the official upstream metadata.
| Kind | Package | Version | Licence | Details |
|---|
| container image | docker.io/pgvector/pgvector | 0.8.7-pg16-bookworm | PostgreSQL | image key postgres, used by postgres, digest sha256:0dd3149a06e4579a0d70d8fef9945636cd514005e7237deb27f9b67886f8dc4f |
| Python package | pixeltable-pgserver | 0.7.1 | PostgreSQL | interpreters, in order of preference python3.11, python3.12, python3.10 |
| Python package | patroni[etcd3] | 4.1.5 | PostgreSQL | interpreters, in order of preference python3.11, python3.12, python3.10 |
| Python package | psycopg[binary] | 3.3.2 | PostgreSQL | interpreters, in order of preference python3.11, python3.12, python3.10 |
| OS package | postgresql16-server | version of the configured repository | PostgreSQL | RHEL family (dnf) |
| OS package | postgresql16 | version of the configured repository | PostgreSQL | RHEL family (dnf) |
| OS package | postgresql16-contrib | version of the configured repository | PostgreSQL | RHEL family (dnf) |
| OS package | pgvector_16 | version of the configured repository | PostgreSQL | RHEL family (dnf) |
| OS package | patroni | version of the configured repository | PostgreSQL | RHEL family (dnf) |
| OS package | patroni-etcd | version of the configured repository | PostgreSQL | RHEL family (dnf) |
| OS package | postgresql-16 | version of the configured repository | PostgreSQL | Debian family (apt) |
| OS package | postgresql-client-16 | version of the configured repository | PostgreSQL | Debian family (apt) |
| OS package | postgresql-16-pgvector | version of the configured repository | PostgreSQL | Debian family (apt) |
| OS package | patroni | version of the configured repository | PostgreSQL | Debian family (apt) |
| OS package | python3-etcd | version of the configured repository | PostgreSQL | Debian family (apt) |
Images are pinned by tag in the descriptor, and by digest when the descriptor declares one (the offline bundle then refuses a pulled image that does not carry it); the digest is recorded in the mirror of the Manager when the offline bundle is imported, and every pull or load is checked against it.
Supported systems and architectures
| Method | Operating systems | Architectures |
|---|
podman | RHEL family (dnf), Debian family (apt), Arch Linux (pacman) | x86_64, aarch64 (not restricted by the descriptor) |
package | RHEL family (dnf), Debian family (apt) | x86_64, aarch64 (not restricted by the descriptor) |
python | RHEL family (dnf), Debian family (apt), Arch Linux (pacman) | x86_64, aarch64 (not restricted by the descriptor) |
Certified host systems: RHEL 8/9/10, Rocky Linux 8/9/10, AlmaLinux 8/9/10, Oracle Linux 8/9, CentOS Stream 9/10, Ubuntu 22.04/24.04, Debian 12, Arch Linux (rolling). The host preflight accepts these systems (other releases of a known family give a warning, end of life releases are refused) and the x86_64 and aarch64 architectures.
Prerequisites
Host
- at least 1024 MB of memory (blocking,
memory).
- at least 5120 MB free for
/var/lib/navin (warning, disk).
- a synchronized clock (warning,
time_sync).
Network and ports
| Component | Port name | Port | Opened in the host firewall |
|---|
postgres | sql | 5432/tcp | yes |
postgres | restapi | 8008/tcp (mode ha) | yes |
Services and connections needed
Details and resolution: see Dependencies.
Installation methods and modes
| Method | Default | What the method uses |
|---|
podman | yes | the container images of the descriptor, run by Podman under systemd |
package | - | the OS packages of the descriptor, installed by the package manager of the host |
python | - | a Python virtual environment holding the Python packages of the descriptor |
Components
| Component | Kind | Installation methods | Ports |
|---|
PostgreSQL server (postgres) | master | podman, package, python | 5432/tcp, 8008/tcp (mode ha) |
Single server (standalone) (default)
One PostgreSQL server, without replica.
What the mode deploys:
| Component | Placement |
|---|
postgres | min 1, max 1 |
High availability (Patroni) (ha)
2 to 7 members (3 recommended) managed by Patroni: one leader, the other members are streaming replicas, automatic failover through etcd. Methods package and python.
What the mode deploys:
| Component | Placement |
|---|
postgres | min 2, max 7, recommended 3 |
Install this service
The install wizard of the Navin Manager plans, validates and runs the installation; you need the services:install permission on the cluster.
-
Open the wizard. In Clusters, choose the row action Install services of the target cluster. The wizard opens on its first step, Service.
-
Choose the service. On the Service step, select PostgreSQL. The card shows the licence PostgreSQL and the dependencies the installation needs. Pick the Method among podman, package, python (default podman). To register an existing postgresql service of your organisation instead, turn on Use an existing external service.
-
Choose the hosts and the mode. On the Hosts step, tick the hosts, then choose the Mode: standalone (default), ha. Each mode deploys the components listed in Installation methods and modes.
-
Bind the dependencies. On the Bindings step, choose for each dependency (dcs) Install with the platform or Use an existing connection; an optional dependency can stay on No binding.
-
Configure. On the Configure step, review the parameters (see Configuration parameters). Passwords left empty with a generated default are generated and sealed by the Manager.
-
Review and install. On the Install step the Manager validates the plan and shows the installation order; when it says "The plan is valid. Ready to install.", choose Install. A highly available mode is installed only when its HA checks pass.
-
Verify. The operation ends when the health checks of every instance pass (see Health checks); the service then shows as running in Services. Open the service and choose Run the smoke test in the Checks card: Creates a temporary table on the leader, writes then reads a row; with pgvector, a vector(3) column and a nearest neighbour query.
High availability
Prerequisites
| Prerequisite | Must be highly available | Modes | Description |
|---|
dcs | yes | ha | etcd in mode cluster (3 or 5 members, Raft quorum): Patroni demotes the leader when etcd is unreachable, so a single etcd member would make it the single point of failure. |
Topology per mode
| Modes | Highest catalog level | Distribution | Quorum component | Fault domains | Description |
|---|
standalone | standalone (standalone) | single (single) | - | 1 | One PostgreSQL server, restored from its backups after a loss. |
ha | ha (ha) | active-passive (active-passive) | - | 3 | One read-write leader and 1 to 6 streaming replicas; Patroni promotes the most advanced healthy replica when the leader key expires in etcd, the old leader rejoins as a replica (pg_rewind). |
Notes
- Mode ha: the consumers reach the leader through the libpq list of every member with target_session_attrs=read-write (PostgreSQL 14 or later clients). For clients that only take one address, put HAProxy in front of the members with an HTTP check GET /primary on the Patroni REST API port: only the leader answers 200.
- Asynchronous replication by default: a failover may lose the last transactions not yet received by the promoted replica, bounded by maximum_lag_on_failover; synchronous_mode removes this loss.
- The mode ha runs with the methods package and python; Patroni without a watchdog device relies on the leader key TTL in etcd.
- The bootstrap settings (max_connections, WAL, ttl, loop_wait, retry_timeout, synchronous_mode) are written in etcd at the first start; a later change is applied with the command push_settings.
- A PostgreSQL cluster of the bank that meets its high availability requirements can be connected instead (connection type postgresql).
Configuration parameters
Network
| Parameter | Type | Default | Allowed values | Required | Secret | Restart | Description |
|---|
Port (port) | port | 5432 | - | no | no | no | TCP port of the SQL server. |
Patroni REST API port (restapi_port) | port | 8008 | - | no | no | no | Port of the Patroni REST API of each member (health checks /liveness, /primary, /replica, cluster state, switchover). (ha) |
Listen address (listen_address) | string | * | - | no | no | no | Addresses the server and the Patroni REST API listen on. (advanced) |
Tuning
| Parameter | Type | Default | Allowed values | Required | Secret | Restart | Description |
|---|
Maximum connections (max_connections) | int | 200 | min 20, max 5000 | no | no | yes | Maximum number of client connections. |
Shared buffers (shared_buffers_mb) | int | 512 | min 64, MB | no | no | yes | Memory of the shared buffer cache, about 25 percent of the host memory. (recommended clamp(round(host.memory_mb * 0.25), 128, 16384)) |
WAL kept for replicas (wal_keep_size_mb) | int | 1024 | min 0, max 1048576, MB | no | no | no | WAL kept in pg_wal for the replicas and the backups (wal_keep_size). |
Security
| Parameter | Type | Default | Allowed values | Required | Secret | Restart | Description |
|---|
Superuser password (superuser_password) | password | generated | - | no | yes | no | Password of the postgres role, used by the provisioning commands, the backups and pg_rewind. |
Replication password (replication_password) | password | generated | - | no | yes | no | Password of the replicator role the replicas stream the WAL with. (ha) |
REST API password (restapi_password) | password | generated | - | no | yes | no | Basic authentication password (user navin) of the unsafe endpoints of the Patroni REST API (switchover, restart, reinitialize). (ha) |
TLS (tls_enabled) | bool | false | - | no | no | no | Serve SQL, replication and the Patroni REST API over TLS with the certificate of the manager CA and reject every connection without TLS (hostnossl reject). Methods podman and package only: the PostgreSQL of the python method is built without OpenSSL. Required in the mode ha when etcd uses TLS (client certificate). |
Allowed network (allowed_cidr) | string | 0.0.0.0/0 | - | no | no | no | CIDR allowed to connect with a password (scram-sha-256). |
Extensions
| Parameter | Type | Default | Allowed values | Required | Secret | Restart | Description |
|---|
pgvector (pgvector) | bool | true | - | no | no | no | Create the vector extension in template1 and in each consumer database at provisioning (vector type, HNSW and IVFFlat indexes). |
High availability
| Parameter | Type | Default | Allowed values | Required | Secret | Restart | Description |
|---|
WAL limit of a slot (max_slot_wal_keep_size_mb) | int | 10240 | min 1024, max 1048576, MB | no | no | no | Maximum WAL a replication slot of a stopped member retains (max_slot_wal_keep_size), so a lost replica never fills the disk of the leader; past it the member is reinitialized. (ha) |
Synchronous replication (synchronous_mode) | bool | false | - | no | no | no | Patroni synchronous_mode: a commit waits for one synchronous replica and only that replica may be promoted (no committed transaction lost on failover, higher commit latency). (ha) |
Leader key TTL (ttl) | int | 30 | min 10, max 300, s | no | no | no | Time to live of the leader key in etcd: a leader that cannot renew it for this long is demoted and a replica is promoted. (advanced; ha) |
Loop wait (loop_wait) | int | 10 | min 1, max 60, s | no | no | no | Seconds between two runs of the Patroni high availability loop. (advanced; ha) |
Retry timeout (retry_timeout) | int | 10 | min 1, max 120, s | no | no | no | Timeout of the etcd and PostgreSQL operations retried by Patroni before a demotion. (advanced; ha) |
Maximum lag on failover (maximum_lag_on_failover_mb) | int | 1 | min 0, max 102400, MB | no | no | no | A replica further behind the leader than this is never promoted (maximum_lag_on_failover). (advanced; ha) |
Dependencies
For each active dependency the Manager takes the first of: the binding chosen in the wizard; a service of the same installation that provides it; a service already installed in the cluster; the only connection of the expected type. Otherwise an optional dependency stays unbound, and a required one is added to the installation in its default mode (the column below names the catalog service added).
| Binding | Provided by | Connection type | Endpoint | Optional | When nothing is bound | Description |
|---|
dcs (ha) | etcd | - | client | no | etcd is added to the installation | etcd cluster where Patroni keeps the leader key, the member list and the dynamic configuration (mode ha). |
Endpoints
| Endpoint | Scheme | Component | Port | Path | TLS parameter | Binding parameters | Binding secrets | Provisioning command |
|---|
sql | postgresql | postgres | 5432 (port) | - | tls_enabled | database, username, sslmode | password | provision_consumer |
Client configuration files
Files rendered for the client configuration download:
postgresql.env (client.env.j2)
Commands
| Command | Component | Target | Description |
|---|
Provision a consumer database (provision_consumer) | postgres | first host | Creates or updates the role and the database of one consumer service on the leader, with the vector extension when pgvector is on (idempotent). |
Enable an extension (enable_extension) | postgres | first host | Creates an extension in one database on the leader (idempotent). The python method only bundles vector. |
List databases (list_databases) | postgres | first host | Lists the databases and their owners. |
Show activity (activity) | postgres | first host | Shows the sessions of pg_stat_activity of the leader. |
Cluster status (cluster_status) | postgres | first host | Mode ha: members, role, state, timeline and lag (patronictl list). Single server: role and replication of the server. |
Switchover (switchover) | postgres | first host | Mode ha: moves the leader role to the member given, which must be a healthy streaming replica (patronictl switchover); the sessions of the old leader are cut. (asks for confirmation) |
Reinitialize a member (reinit_member) | postgres | first host | Mode ha: deletes the data directory of one replica and clones it again from the leader (patronictl reinit). Never the leader. (asks for confirmation) |
Push the settings (push_settings) | postgres | first host | Mode ha: writes max_connections, wal_keep_size, the slot WAL limit, synchronous_mode, ttl, loop_wait, retry_timeout and maximum_lag_on_failover into the dynamic configuration in etcd (patronictl edit-config), then restarts the members with a pending restart one by one. (asks for confirmation) |
Command parameters
| Command | Parameter | Type | Default | Required | Description |
|---|
provision_consumer | Database (database) | string | - | yes | Database of the consumer. |
provision_consumer | Role (username) | string | - | yes | Login role of the consumer. |
provision_consumer | Password (password) | password | - | yes | Password of the role (derived per consumer). |
enable_extension | Database (database) | string | - | yes | Database where the extension is created. |
enable_extension | Extension (extension) | enum | vector | yes | vector (pgvector) or a contrib extension of PostgreSQL 16. |
switchover | New leader (candidate) | string | - | yes | Member name of the new leader (host name, see cluster status). |
reinit_member | Member (member) | string | - | yes | Member name of the replica (host name, see cluster status). |
Health checks
Liveness (per instance)
| Component | Check | Details |
|---|
postgres | TCP {{ host.ip or '127.0.0.1' }}:{{ config.port }} | 90 retries; interval 2 s; timeout 3 s |
Readiness (service check)
| Check | Details |
|---|
{{ paths.etc }}/check.sh | 30 retries; interval 4 s; timeout 30 s |
Smoke test
Creates a temporary table on the leader, writes then reads a row; with pgvector, a vector(3) column and a nearest neighbour query. (postgres: write_read, nearest_neighbour)
Preflight checks
| Check | Type | Meaning | Severity | Components |
|---|
memory | memory_mb {"min": 1024} | at least 1024 MB of memory | blocking | every component |
disk | disk_free_mb {"path": "/var/lib/navin", "min": 5120} | at least 5120 MB free for /var/lib/navin | warning | every component |
sql_port_free | port_free {"param": "port"} | port 5432 free on the host | blocking | every component |
time_sync | time_sync {} | a synchronized clock | warning | every component |
Backup and restore
Physical backup of the whole server taken on the leader (pg_basebackup, tar format, WAL streamed, SHA256 manifest); the verify step extracts it and runs pg_verifybackup. Restore: the data directory of the member of the backup is replaced; in the mode ha the cluster state in etcd is reset, that member restarts as the leader and every other member is cloned again from it when it starts.
| |
|---|
| Format | pg-basebackup |
| Component | postgres |
| Files | {{ backup.dir }}/base.tar.gz, {{ backup.dir }}/pg_wal.tar.gz, {{ backup.dir }}/backup_manifest |
| Restore test | yes |
| Instances stopped for the restore | yes |
Uninstall
Paths removed when the data is purged: {{ paths.data }}, {{ paths.log }}.
Upstream documentation