Skip to content
SHC Docs

PostgreSQL

PostgreSQL is a powerful, open-source relational database.

Key features:

  • ACID compliance
  • Advanced indexing
  • Full-text search
  • JSON support
Terminal window
shc install postgres -t <tenant> --stack db

<tenant> is default on a cluster made through the portal, or one you added (Tenants). An install that names no tenant is refused with X400976TNTNON.

Common variables:

  • postgres.username - Superuser name (default: postgres)
  • postgres.password - Superuser password (auto-generated)
  • postgres.database - Initial database name

PostgreSQL provides a socket for other apps:

Terminal window
shc install postgres -t <tenant> --stack db
shc install nextcloud -t <tenant> --stack cloud -i postgres=db --expose cloud.example.com

The socket exports:

  • POSTGRES_HOST - Database hostname
  • POSTGRES_PORT - Port (default: 5432)
  • POSTGRES_USER - Username
  • POSTGRES_PASSWORD - Password (from vault)
  • POSTGRES_DB - Database name

Every database the socket creates gets uuid-ossp, pgcrypto, vector, btree_gin and, on a server that can serve it, pg_stat_statements. A plug can ask for more with extensions.

pg_stat_statements is the one that needs its library preloaded at server start, which is a postmaster setting no session can turn on. SHC’s own postgres therefore starts with shared_preload_libraries=pg_stat_statements, and the socket only creates the extension when the target server reports the library loaded.

That check matters for an external postgres (external-postgres, a managed instance, anything you point the socket at), because SHC does not own its postgresql.conf. Creating the extension there would succeed and then error on every read:

ERROR: pg_stat_statements must be loaded via shared_preload_libraries

which is worse than not having it: the catalogue says the feature is installed. The job prints a skipping pg_stat_statements line instead. To get it on an external server, preload the library there and re-run the integration.

PMM’s query analytics (-i pmm=) reads pg_stat_statements and nothing else, so this is what makes QAN work for a postgres stack.

PostgreSQL uses pg_dump converter for consistent backups:

Terminal window
shc backup create --stack db

The backup includes:

  • All databases (via pg_dump)
  • User roles and permissions
  • Configuration files

Restore with pg_restore:

Terminal window
shc backup restore --file abc123.bak --stack db
Terminal window
# Interactive shell in stack db. The server runs as member postgres-1.
shc shell db --service postgres-1
# Inside container
psql -U postgres
# Run SQL in stack db. -s is the root stack flag, for this command only.
shc -s db exec --service postgres-1 -- \
psql -U postgres -c "SELECT version();"
# Scaled to three (shc update -s db --scale postgres=3), the group runs
# postgres-1, postgres-2 and postgres-3. Enter one member by its name.
shc -s db exec --service postgres-2 -- \
psql -U postgres -c "SELECT pg_is_in_recovery();"

--service postgres names the group, not a member, and is refused with the members’ names. After a failover any member can lead; apps keep reaching the leader at postgres and <environment>.postgres.<stack> (see HA databases).

Common issues:

Problem: Connection refused Solution: Check if the service is healthy: shc status db

Problem: Authentication failed Solution: Verify password: shc vault get password -s db