PostgreSQL Architecture¶

Overview¶

PostgreSQL 16 is the data persistence layer for the entire Astra system. It stores all OpenWebUI application data -- user accounts, conversation histories, system settings, and vector embeddings for RAG (Retrieval-Augmented Generation). PostgreSQL runs bare metal (directly on the host operating system, not inside a container) on each of the three node1 (server) nodes.

Database Configuration¶

Property	Value
PostgreSQL version	16
Database name	`openwebui`
Application user	`openwebui`
Replication user	`replicator`
Data directory	`/var/lib/postgresql/16/main/`
Port	5432
WAL level	`replica`
Max WAL senders	4

Roles and Permissions¶

Two database roles are configured:

openwebui -- The application user. OpenWebUI connects to PostgreSQL using this role. It has full read/write access to the openwebui database, including all tables for user data, conversations, configuration, and vector embeddings.

replicator -- The replication user. Standby nodes connect to the primary using this role to receive streaming replication data. It has the REPLICATION privilege, which allows it to establish replication connections and receive WAL (Write-Ahead Log) data.

Host-Based Authentication¶

The pg_hba.conf file controls which connections are accepted:

# Replication connections from any host (used by standby nodes)
host replication replicator 0.0.0.0/0 md5

# Application connections from any host (used by OpenWebUI pods)
host openwebui openwebui 0.0.0.0/0 md5

Both roles use md5 password authentication. The broad 0.0.0.0/0 source address allows connections from any network interface, supporting both the primary LAN and any management network interfaces.

Security context

The 0.0.0.0/0 network range is acceptable in Astra's deployment because the system operates on a physically isolated LAN with no internet connectivity. All network access is controlled at the physical layer (the network router).

pgvector Extension¶

The pgvector extension is installed and enabled on all three node1s. pgvector adds vector similarity search capabilities to PostgreSQL, allowing it to store and query high-dimensional embedding vectors.

OpenWebUI uses pgvector for RAG functionality:

When a user uploads a document, OpenWebUI generates vector embeddings using a sentence transformer model
The embeddings are stored in PostgreSQL using pgvector's vector column type
When a user asks a question, OpenWebUI generates an embedding for the query and performs a similarity search against stored embeddings
The most relevant document chunks are retrieved and included in the AI model's context

By using pgvector (configured via VECTOR_DB=pgvector in the Helm values), all embedding data is stored in the same PostgreSQL database as all other application data. This means embeddings are automatically replicated to standby nodes via streaming replication, and they survive failover events without any additional synchronization.

Why Bare Metal Instead of Containerized¶

PostgreSQL runs directly on the host OS rather than inside a Kubernetes pod. This design decision was made for three reasons:

1. Direct Replication Control¶

PostgreSQL streaming replication requires precise control over the data directory, WAL files, and recovery configuration. Running PostgreSQL inside a container adds a layer of abstraction (container networking, volume mounts, pod lifecycle management) that complicates replication setup and debugging.

With bare-metal PostgreSQL, the replication configuration files (postgresql.conf, pg_hba.conf, standby.signal) are directly accessible on the filesystem. The pg_basebackup recovery procedure operates directly on the data directory without needing to coordinate with Kubernetes volume management.

2. Simpler Recovery¶

The pg-autoheal system (see Auto-Heal System) rebuilds standby nodes by stopping PostgreSQL, deleting the data directory, and running pg_basebackup. This process interacts directly with the local filesystem and systemd. If PostgreSQL were containerized, the recovery script would need to coordinate with Kubernetes to stop and restart pods, manage persistent volume claims, and handle the container lifecycle -- adding complexity and potential failure modes.

3. Performance¶

Bare-metal PostgreSQL has direct access to the host's storage I/O without the overhead of container filesystem layers (overlay filesystems) and network namespace translation. On Raspberry Pi hardware with microSD storage, minimizing I/O overhead is important for maintaining acceptable database performance.

Replication Configuration¶

PostgreSQL is configured for streaming replication across the three node1s:

Node	Role	Description
k3s-c1-node1	PRIMARY	Writable; accepts all read and write operations
k3s-c2-node1	STANDBY	Read-only; receives replicated data from the primary via the VIP
k3s-c3-node1	STANDBY	Read-only; receives replicated data from the primary via the VIP

Key postgresql.conf settings for replication:

wal_level = replica
max_wal_senders = 4

wal_level = replica -- Enables Write-Ahead Log data sufficient for streaming replication. This is required for standby nodes to receive and replay WAL records from the primary.
max_wal_senders = 4 -- Allows up to 4 concurrent replication connections. With 2 standbys and the possibility of a pg_basebackup running simultaneously, 4 senders provides adequate capacity.

For the full replication architecture, see Cascade Failover.

Standby Node Behavior¶

Standby nodes are in read-only mode. They accept read queries but reject any write operations. This means:

OpenWebUI running on the primary cluster (where PostgreSQL is the PRIMARY) operates normally with full read/write access
OpenWebUI running on standby clusters (where PostgreSQL is a STANDBY) will encounter read-only database errors when attempting to write -- this is expected behavior and not a bug
When a standby is promoted to primary (via Keepalived failover), the read-only restriction is lifted and the node accepts writes

Expected behavior on standby clusters

OpenWebUI on standby clusters (C2, C3) will display errors or crash when it attempts write operations against the read-only database. This is normal operation. The standby clusters become fully functional when promoted to primary during a failover event.

Data Stored in PostgreSQL¶

The openwebui database contains all application state:

Data Category	Description
User accounts	Email, hashed password (bcrypt), role, preferences
Conversations	Full chat history with all messages and metadata
System configuration	Ollama base URLs, API configurations, feature flags
Vector embeddings	Document embeddings for RAG via pgvector
Session data	Active user sessions and authentication tokens

Because all application state is in PostgreSQL, and PostgreSQL is replicated across all three nodes, a failover event preserves the complete application state. Users do not lose conversation history, settings, or uploaded documents during failover.

Operational Commands¶

Check PostgreSQL Status¶

sudo systemctl status postgresql

Check Whether Node Is Primary or Standby¶

sudo -u postgres psql -c "SELECT pg_is_in_recovery();"

Returns f (false) on the primary node and t (true) on standby nodes.

Check Replication Status (Run on Primary)¶

sudo -u postgres psql -c "SELECT client_addr, state, replay_lag FROM pg_stat_replication;"

Expected output: two rows showing streaming state with sub-millisecond replay_lag.

Restart PostgreSQL¶

sudo systemctl restart postgresql

Restarting PostgreSQL on the primary

Restarting PostgreSQL on the primary node will briefly interrupt replication to all standbys. The standbys will automatically reconnect once PostgreSQL restarts. However, the OpenWebUI pod on the primary cluster will also restart to re-establish its database connection.

Deployment Files¶

Path	Purpose
`/var/lib/postgresql/16/main/`	PostgreSQL data directory
`/var/lib/postgresql/16/main/postgresql.conf`	Main configuration file
`/var/lib/postgresql/16/main/pg_hba.conf`	Host-based authentication configuration
`/var/lib/postgresql/16/main/standby.signal`	Present on standby nodes; indicates recovery mode
`/var/log/pg-autoheal.log`	Auto-heal script log file