Configure PostgreSQL 17 or 18

Use PostgreSQL when you already have a reliable database service or need the database separated from the Jiandu host. Jiandu supports server major 17 and 18 only and still runs one application process per installation.

The repository's Docker Compose installation is the simplest complete example: it generates a database password and private CA, publishes no database port, and mounts a protected connection URL and CA into Jiandu.

Before you begin

You need:

• a fresh PostgreSQL 17.x or 18.x server;
• a dedicated database and login that can own and migrate its schema;
• network reachability from Jiandu without publishing the database broadly;
• a server certificate whose chain Jiandu can verify;
• a DNS hostname that matches the certificate for the recommended verify_full mode; and
• matching-major pg_dump and pg_restore clients for backup and recovery.

PostgreSQL 16 and older, PostgreSQL 19 and newer, plaintext connections, trust-all TLS, Unix-socket URLs, connection URL query parameters, and cross-backend migration are rejected or unsupported.

1. Create a database and role

On a self-managed server, create a dedicated login without placing its password in a command argument:

sudo -u postgres createuser --pwprompt jiandu
sudo -u postgres createdb --owner=jiandu jiandu

The role needs to connect to and own the jiandu database and create/alter/drop objects in its schema so Jiandu can run migrations. It does not need superuser, replication, role creation, or access to another database. Fresh installations do not require a PostgreSQL extension.

On a managed service, use its equivalent database/user provisioning path. Prefer a direct database endpoint. Transaction-mode connection poolers are not a supported substitute: startup migrations use PostgreSQL advisory locks and Jiandu expects normal session semantics.

Restrict inbound database traffic to the Jiandu host, container network, pod identity, or other smallest practical source. Do not expose port 5432 to household clients or the public internet.

2. Require and verify TLS

Jiandu has two TLS modes:

Mode	Certificate chain	Hostname	When to use
verify_full	Verified	Verified	Default and recommended
verify_ca	Verified using an explicit CA file	Not verified	Last resort for a private deployment whose certificate cannot match the connection hostname

There is no disable, prefer, or trust-all mode. In verify_full, omit tls_ca_path when the server certificate chains to the runtime's platform trust store. Supply a CA bundle when using a private CA or a managed-service root that is not installed in that runtime.

The hostname in the connection URL is both the network target and, in verify_full, the certificate identity. Do not replace it with an IP address unless the certificate includes that IP address.

:::caution verify_ca is not a certificate fix verify_ca still encrypts and validates the certificate chain, but it does not prove that the server is the hostname you intended. Prefer issuing the correct certificate or using the provider's documented DNS endpoint. :::

3. Create the protected connection URL file

The file contains exactly one URL:

/run/secrets/jiandu-database-url

postgresql://jiandu:[email protected]:5432/jiandu

Percent-encode reserved characters in the username, password, and database name. Do not add ?sslmode=, CA paths, application options, or any other query parameter; Jiandu owns those settings through its typed configuration.

On Unix, the URL file must be a non-empty regular file with one hard link, no symlink indirection, and mode 0400 or 0600. Create it through a secret manager or an input file that did not expose the password in shell history:

sudo install -d -o jiandu -g jiandu -m 0700 /run/secrets
sudo install -o jiandu -g jiandu -m 0600 \
  /protected/input/jiandu-database-url \
  /run/secrets/jiandu-database-url

If you use a private CA, install its PEM file as a regular, non-symlink file that is not writable by group or others:

sudo install -o jiandu -g jiandu -m 0400 \
  /protected/input/postgresql-ca.pem \
  /run/secrets/postgresql-ca.pem

The URL itself is never accepted in JSON, an environment value, or a CLI argument. Keeping it in a file avoids process-list, configuration-dump, and telemetry disclosure.

4. Configure Jiandu

Use verify_full with an explicit private CA:

/etc/jiandu/jiandu.json

{
  "schema_version": 1,
  "data_directory": "/var/lib/jiandu",
  "database": {
    "backend": "postgres",
    "url_file": "/run/secrets/jiandu-database-url",
    "max_connections": 8,
    "min_connections": 1,
    "acquire_timeout_milliseconds": 10000,
    "connect_timeout_milliseconds": 10000,
    "tls_mode": "verify_full",
    "tls_ca_path": "/run/secrets/postgresql-ca.pem"
  }
}

For a certificate rooted in the platform trust store, remove tls_ca_path. For verify_ca, keep an explicit tls_ca_path—configuration without it is rejected.

Understand the pool settings

Setting	Default	Meaning
max_connections	4	Hard upper bound for Jiandu's pool; accepted range 1–64
min_connections	1	Connections kept ready; must not exceed the maximum
acquire_timeout_milliseconds	10000	How long a request waits for a pool connection
connect_timeout_milliseconds	10000	How long startup waits for a database connection stage

The eight-connection example is appropriate only if the service's connection budget allows it. Start with four for a small installation. Increasing the pool does not enable multiple Jiandu replicas and can make a managed-service connection limit or failover event worse.

Equivalent deployment-owned overrides are:

export JIANDU_DATABASE_BACKEND=postgres
export JIANDU_DATABASE_URL_FILE=/run/secrets/jiandu-database-url
export JIANDU_DATABASE_TLS_MODE=verify_full
export JIANDU_DATABASE_TLS_CA_PATH=/run/secrets/postgresql-ca.pem
export JIANDU_DATABASE_MAX_CONNECTIONS=8

Do not set libpq PGHOST, PGPORT, PGDATABASE, PGUSER, PGPASSWORD, PGPASSFILE, PGSERVICE, PGSSLMODE, PGSSLROOTCERT, PGSSLCERT, PGSSLKEY, or PGOPTIONS in the Jiandu environment. Jiandu rejects driver-level overrides so the typed configuration is the only connection authority.

5. Validate and start

Run validation as the service account, with the real secret mounts:

sudo -u jiandu jiandu --config /etc/jiandu/jiandu.json --check-config

This checks file safety, URL syntax, TLS settings, pool bounds, and conflicting environment values. It intentionally makes no network connection. Start Jiandu, then request readiness through its normal public origin:

curl --fail --silent https://documents.example.net/api/v1/health/ready

At startup Jiandu connects, verifies TLS, checks the server major, and applies pending migrations. Readiness then verifies required database access. Keep the prior process stopped throughout a rollout; do not let two revisions race to own the installation.

Managed PostgreSQL checklist

For RDS, Cloud SQL, Azure Database for PostgreSQL, or another managed service:

1. Select an exact supported server major, not a provider's automatically advancing “latest” track.
2. Use the direct writer endpoint and verify its certificate hostname.
3. Download the provider's current CA bundle only from its authoritative channel and mount it read-only when the platform trust store does not contain it.
4. Disable or constrain public network access; permit only the Jiandu runtime.
5. Give the Jiandu role ownership/migration capability inside its dedicated database, not a provider-wide administrator role.
6. Confirm maintenance does not auto-upgrade the server to an unqualified major.
7. Install pg_dump and pg_restore whose major matches the server and prove the Jiandu recovery workflow.

Provider snapshots are valuable additional protection, but Jiandu recovery archives coordinate database state with blob content. Retain both.

Backup and version caveats

Jiandu records the PostgreSQL server major in a recovery backup. Restore requires a new, empty, unpublished database on that same major and a pg_restore client that matches the archive/server major. Keep the old installation stopped and recoverable until the restored copy passes document verification.

Read the PostgreSQL backup workflow before production use. If the required tools are outside the service PATH, Jiandu accepts the documented absolute offline-tool overrides; they are not runtime connection settings.

Troubleshoot by stage

Failure	Inspect
--check-config rejects the URL file	Mode/link count/symlink, empty file, query string, missing password/database, percent encoding, or a conflicting PG* variable
Certificate issuer error	Platform trust store or tls_ca_path contains the correct current CA chain
Certificate hostname error	URL hostname matches a certificate SAN; avoid IPs and aliases absent from the certificate
Unsupported server version	Server reports major 17 or 18, including through any proxy endpoint
Permission denied during migration	Jiandu role owns the database/schema and can create/alter/drop its objects
Pool timeout	Database health/network latency, long transactions, connection budget, then pool sizing
Backup preflight rejects tools	pg_dump/pg_restore major does not match the supported server/archive major

Do not point an existing SQLite installation at an empty PostgreSQL database expecting migration. The result is a separate installation with none of the original library state.