PostgreSQL Support¶

Bambuddy supports an optional external PostgreSQL database as an alternative to the built-in SQLite. SQLite remains the default and requires zero configuration.

When to Use PostgreSQL¶

SQLite works great for most users. Consider PostgreSQL if you:

Run a large print farm (10+ printers) with high write concurrency
Want a dedicated database server shared across services
Need point-in-time recovery or streaming replication
Already have PostgreSQL infrastructure (e.g., for other services)

SQLite is Fine for Most Users

SQLite with WAL mode handles concurrent reads + single writer well. Bambuddy tunes busy_timeout and synchronous mode automatically. If you're not hitting performance issues, there's no reason to switch.

Setup¶

1. Install PostgreSQL¶

Skip this step if you already have a PostgreSQL server running.

Docker (Recommended) Debian / Ubuntu Fedora / RHEL Existing Server

Create a docker-compose.yml for PostgreSQL (or add to an existing one):

services:
  postgres:
    image: postgres:16-alpine
    container_name: bambuddy-db
    restart: unless-stopped
    environment:
      POSTGRES_USER: bambuddy
      POSTGRES_PASSWORD: your-secure-password
      POSTGRES_DB: bambuddy
    volumes:
      - pgdata:/var/lib/postgresql/data
    ports:
      - "5432:5432"

volumes:
  pgdata:

Start it:

docker compose up -d

That's it — the database and user are created automatically from the environment variables.

# Install PostgreSQL
sudo apt update && sudo apt install -y postgresql postgresql-client

# Create user and database
sudo -u postgres psql -c "CREATE USER bambuddy WITH PASSWORD 'your-secure-password';"
sudo -u postgres psql -c "CREATE DATABASE bambuddy OWNER bambuddy;"

PostgreSQL starts automatically after installation. Verify it's running:

sudo systemctl status postgresql

Remote Access

By default, PostgreSQL only accepts local connections. To allow connections from another host (e.g., Bambuddy on a different machine), edit /etc/postgresql/*/main/pg_hba.conf and add:

host    bambuddy    bambuddy    192.168.0.0/16    scram-sha-256

And in /etc/postgresql/*/main/postgresql.conf, set:

listen_addresses = '*'

Then restart: sudo systemctl restart postgresql

# Install PostgreSQL
sudo dnf install -y postgresql-server postgresql
sudo postgresql-setup --initdb
sudo systemctl enable --now postgresql

# Create user and database
sudo -u postgres psql -c "CREATE USER bambuddy WITH PASSWORD 'your-secure-password';"
sudo -u postgres psql -c "CREATE DATABASE bambuddy OWNER bambuddy;"

If you already have a PostgreSQL server, create the user and database:

psql -U postgres -c "CREATE USER bambuddy WITH PASSWORD 'your-secure-password';"
psql -U postgres -c "CREATE DATABASE bambuddy OWNER bambuddy;"

Or from inside a Docker container:

docker exec -it your-postgres-container psql -U postgres -d postgres \
  -c "CREATE USER bambuddy WITH PASSWORD 'your-secure-password';"
docker exec -it your-postgres-container psql -U postgres -d postgres \
  -c "CREATE DATABASE bambuddy OWNER bambuddy;"

2. Configure Bambuddy¶

Set the DATABASE_URL environment variable:

DATABASE_URL=postgresql+asyncpg://bambuddy:your-secure-password@db-host:5432/bambuddy

Docker Systemd

Add to docker-compose.override.yml (create if it doesn't exist):

services:
  bambuddy:
    environment:
      - DATABASE_URL=postgresql+asyncpg://bambuddy:your-password@db-host:5432/bambuddy

Then restart:

docker compose up -d

Add to your .env file:

DATABASE_URL=postgresql+asyncpg://bambuddy:your-password@db-host:5432/bambuddy

Then restart:

sudo systemctl restart bambuddy

3. Start Bambuddy¶

Bambuddy automatically creates all tables on first startup. No manual schema setup needed.

Migrating from SQLite¶

To migrate your existing data from SQLite to PostgreSQL:

Create a backup from your current SQLite install (Settings > Backup & Restore > Create Backup)
Configure the DATABASE_URL as described above
Restart Bambuddy (it creates empty tables on the new database)
Restore the backup ZIP through Settings > Backup & Restore > Restore Backup

Bambuddy automatically handles the cross-database import:

Converts SQLite integer booleans (0/1) to PostgreSQL native booleans
Parses SQLite datetime strings into proper datetime objects
Fills default values for columns added after the backup was created
Handles foreign key constraints and duplicate detection
Resets PostgreSQL sequences to match imported data

Portable Backups¶

Backups are always in portable SQLite format, regardless of which database backend you use. This means:

A backup from a PostgreSQL install can be restored on a SQLite install
A backup from a SQLite install can be restored on a PostgreSQL install
Backups work across different Bambuddy versions (forward-compatible)

Health Diagnostics¶

The support bundle (Settings > System > Support Bundle) automatically detects the database backend and reports:

Metric	SQLite	PostgreSQL
Backend type	`sqlite`	`postgresql`
Version	Journal mode	PostgreSQL version
Database size	File size + WAL size	`pg_database_size()`
Integrity check	`PRAGMA quick_check`	Connection test

Full-Text Search¶

Archive search works on both backends with the best available engine:

Backend	Technology	Features
SQLite	FTS5 virtual table	Prefix matching, ranking by relevance
PostgreSQL	tsvector + GIN index	Prefix matching, language-aware stemming

The search experience is identical from the user's perspective.

Connection Settings¶

Setting	SQLite	PostgreSQL
Pool size	20 connections	20 connections
Max overflow	200	80
Pre-ping / recycle	N/A	Enabled / 1800s
WAL mode	Enabled	N/A
Busy timeout	15 seconds	N/A (uses pool)

The defaults suit most installs — no configuration needed. pool_size + max_overflow is the ceiling on concurrent database connections (per app worker process).

Tuning for large printer farms¶

On large farms (many dozens of printers), the default 100-connection ceiling can be raised. Each of these is optional and overrides the default when set:

Variable	Default (PostgreSQL)	Purpose
`DB_POOL_SIZE`	`20`	Base connections kept open
`DB_MAX_OVERFLOW`	`80`	Extra connections opened on demand
`DB_POOL_TIMEOUT`	`30`	Seconds a request waits for a free connection before erroring
`DB_POOL_RECYCLE`	`1800`	Seconds before a pooled connection is recycled
`DB_POOL_USE_LIFO`	`true`	Reuse the most-recently-returned connection so a bursty farm keeps a small hot set busy and lets excess overflow connections age out via `DB_POOL_RECYCLE` instead of churning the whole pool. Set `false` for strict round-robin (FIFO)

# Example for a ~100-printer farm
DB_POOL_SIZE=20
DB_MAX_OVERFLOW=180   # 200-connection ceiling

Size PostgreSQL max_connections to match

PostgreSQL must allow at least DB_POOL_SIZE + DB_MAX_OVERFLOW connections per app worker process, plus headroom for admin/backup tools. Raise max_connections in postgresql.conf accordingly (and note each connection costs memory server-side).

This applies to the defaults too, not just to raised values. The default ceiling is 20 + 80 = 100, while a stock PostgreSQL ships max_connections = 100 and reserves 3 of those for superusers — leaving 97. So a default Bambuddy against a default PostgreSQL is already over budget. Either lower the overflow:

DB_MAX_OVERFLOW=60   # 80-connection ceiling, fits under 97

or raise the server:

# postgresql.conf
max_connections = 200

Bambuddy checks this at startup and logs a warning naming both numbers when the pool could ask for more than the server allows.

Which error means what

The two failure modes look similar and have opposite fixes:

Error	Meaning	Fix
`asyncpg ... TooManyConnectionsError`	The pool's ceiling is above what the server allows, so it never queues — it asks the server, which refuses	Lower `DB_MAX_OVERFLOW` or raise `max_connections`
`QueuePool limit ... connection timed out`	The pool itself is the bottleneck: too much concurrency, or connections held too long	Raise `DB_POOL_SIZE` / `DB_MAX_OVERFLOW`, or investigate what is holding connections

TooManyConnectionsError surfaces at whatever needed a connection next, which can be far from the real cause — a queue dispatch, a notification, a web request. GET /api/v1/system/db-pool reports both sides under server_limits, and so does the support bundle.

Watch the live pool

GET /api/v1/system/db-pool reports the resolved configuration plus live checked_out / checked_in / overflow gauges (requires the system:read permission). If checked_out sits pinned at the ceiling, connections are being held across slow work — raising the pool only buys time.

FAQ¶

Can I switch back to SQLite?: Yes. Remove the DATABASE_URL environment variable and restart. Bambuddy falls back to the built-in SQLite database. Your PostgreSQL data remains untouched — create a backup first if you want to keep it.
Do I need pg_dump installed on the Bambuddy host?: No. Bambuddy's backup system exports data using pure Python (SQLAlchemy + sqlite3), so no external PostgreSQL tools are required on the Bambuddy server.
What PostgreSQL version is supported?: PostgreSQL 14 or newer is recommended. The asyncpg driver supports PostgreSQL 9.5+, but Bambuddy uses features like GIN indexes that work best on modern versions.
Can I share a PostgreSQL server with other applications?: Yes. Create a separate database and user for Bambuddy. It won't interfere with other databases on the same server.
What happens if the PostgreSQL server goes down?: Bambuddy will show connection errors until the server is back. No data is lost — PostgreSQL is ACID-compliant. Bambuddy automatically reconnects when the server recovers.