PostgreSQL
Overview
Section titled “Overview”PostgreSQL is a powerful, open-source relational database.
Key features:
- ACID compliance
- Advanced indexing
- Full-text search
- JSON support
Installation
Section titled “Installation”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
Configuration
Section titled “Configuration”PostgreSQL provides a socket for other apps:
shc install postgres -t <tenant> --stack dbshc install nextcloud -t <tenant> --stack cloud -i postgres=db --expose cloud.example.comThe socket exports:
POSTGRES_HOST- Database hostnamePOSTGRES_PORT- Port (default: 5432)POSTGRES_USER- UsernamePOSTGRES_PASSWORD- Password (from vault)POSTGRES_DB- Database name
Extensions
Section titled “Extensions”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_librarieswhich 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.
Backup
Section titled “Backup”PostgreSQL uses pg_dump converter for consistent backups:
shc backup create --stack dbThe backup includes:
- All databases (via pg_dump)
- User roles and permissions
- Configuration files
Restore with pg_restore:
shc backup restore --file abc123.bak --stack dbAccess Database
Section titled “Access Database”# Interactive shell in stack db. The server runs as member postgres-1.shc shell db --service postgres-1
# Inside containerpsql -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).
Troubleshooting
Section titled “Troubleshooting”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