#Cube: postgresql
PostgreSQL 18 with its own data directory, socket directory and role for the platform. Nothing is taken from the distribution except the client libraries the build links against.
In the panel: Database → PostgreSQL. Every write there is a task.
#Configuration
[postgresql]version = "18"port = 5432user = "postgres" # operating-system accountconfig_dir = "/aegis/config/postgresql"data_dir = "/aegis/data/postgresql"run_dir = "/aegis/run/postgresql"log_dir = "/aegis/logs/postgresql"listen = "127.0.0.1"remote_rules = "" # extra pg_hba lines, one per linemax_connections = 200shared_buffers = "512MB"effective_cache_size = "1536MB"work_mem = "16MB"maintenance_work_mem = "128MB"| Key | Default | Meaning |
|---|---|---|
version | 18 | informational, matches the installed series |
port | 5432 | TCP port, loopback only unless listen changes |
user | postgres | system account the server runs as |
config_dir | /aegis/config/postgresql | the three configuration files |
data_dir | /aegis/data/postgresql | cluster created by initdb |
run_dir | /aegis/run/postgresql | Unix socket directory |
log_dir | /aegis/logs/postgresql | postgresql.log |
listen | 127.0.0.1 | listen_addresses |
remote_rules | empty | appended verbatim to pg_hba.conf |
max_connections | 200 | server ceiling |
shared_buffers | 512MB | main cache |
effective_cache_size | 1536MB | planner hint, not an allocation |
work_mem | 16MB | per sort/hash operation |
maintenance_work_mem | 128MB | VACUUM and index builds |
The platform role comes from [aegis] user / [aegis] password in config/aegis.toml; it is created as a login superuser by configure.
#Files
<postgresql.config_dir>/├── postgresql.conf main configuration├── pg_hba.conf client authentication└── pg_ident.conf user name maps<postgresql.data_dir>/ cluster (initdb), postmaster.pid lives here<postgresql.run_dir>/ Unix socket, .s.PGSQL.5432.lock<postgresql.log_dir>/└── postgresql.logpostgresql.conf is rendered from storage/data/apps/postgresql/templates/aegis-postgresql.conf; the other two files come from its siblings in the same directory.
#Commands
aegis cubes postgresql configure # initdb on first run, config, start, roleaegis cubes postgresql validate # pg_isready over the socketaegis cubes postgresql reload # SIGHUP: configuration, no dropped clientsaegis cubes postgresql start|stop|restart|statusaegis cubes postgresql databases list|create|dropaegis cubes postgresql users list|add|remove|password|grant|revokeaegis cubes postgresql tables <db> list|vacuum --full|analyze|reindexaegis cubes postgresql sequences <db> list|sync --schema --table --dry-run --allaegis cubes postgresql expose#Users
A role is cluster-wide; granting a database adds the schema privileges as well (GRANT ALL ON ALL TABLES/SEQUENCES/FUNCTIONS IN SCHEMA public plus the matching ALTER DEFAULT PRIVILEGES), otherwise a table created later would be invisible to the role.
aegis cubes postgresql users add shop --database shop_db # generated passwordaegis cubes postgresql users add shop --password 'secret' --database shop_dbaegis cubes postgresql users grant shop shop_dbaegis cubes postgresql users revoke shop shop_dbaegis cubes postgresql users password shop --password 'new-secret'aegis cubes postgresql users remove shopremove drops the role; a role that owns objects fails with PostgreSQL's own message (cannot be dropped because some objects depend on it).
#Tables and maintenance
aegis cubes postgresql tables shop_db listaegis cubes postgresql tables shop_db vacuumaegis cubes postgresql tables shop_db vacuum --fullaegis cubes postgresql tables shop_db analyzeaegis cubes postgresql tables shop_db reindex| Action | Statement | Use |
|---|---|---|
vacuum | VACUUM (ANALYZE) | reclaims dead rows and refreshes statistics |
vacuum --full | VACUUM (FULL, ANALYZE) | rewrites the tables, returns disk to the OS (locks) |
analyze | ANALYZE | statistics only |
reindex | REINDEX DATABASE | rebuilds every index of the database |
#Sequences
A restore moves rows but not sequence positions, so the next insert fails on a duplicate key. sequences finds the sequences owned by a table column and moves them past MAX(column):
# what would changeaegis cubes postgresql sequences shop_db sync --dry-run# fix everything, then show the resultaegis cubes postgresql sequences shop_db sync# one schema or one table, and include the sequences already in syncaegis cubes postgresql sequences shop_db sync --schema public --table orders --allaegis cubes postgresql sequences shop_db listOutput is one line per sequence:
5/2 fixed sequencespublic.orders.id seq=1042 max=1187 -> setval(1187)public.invoices.id seq=900 max=902 -> setval(902)An empty table is reset with setval(seq, 1, false), so the first row gets 1 rather than 2. list is the same report with --dry-run --all.
#configure
postgres operating-system group and user when missing;initdb once, when the data directory is still empty, with--auth-local=peer --auth-host=scram-sha-256;
0600;#The platform role
[aegis] user and [aegis] password from aegis.toml are used to create (or update) a login superuser. That role is what the panel and the other cubes connect with over TCP:
psql -h 127.0.0.1 -U aegis -d postgres# password from [aegis] passwordLocal administration uses peer authentication as the postgres system user, so psql over the socket needs no password.
#databases
aegis cubes postgresql databases listaegis cubes postgresql databases create shopaegis cubes postgresql databases drop shopA database is owned by the platform role. Names containing " or ; are rejected rather than quoted.
#Process
[services.postgresql]command = <prefix>/bin/postgres -D <data_dir> -c config_file=pid_file = <data_dir>/postmaster.piduser = postgresThe keeper starts the server as the postgres system user and reads the first line of postmaster.pid: PostgreSQL writes seven lines there (pid, data directory, start time, port, socket directory, listen address, shared memory), so a full-file parse would fail.
#expose
aegis cubes postgresql exposecube postgresqlinstalled /aegis/apps/data/postgresql/18.7configuration /aegis/config/postgresqlstate running pid 740766pid 740766log /aegis/logs/postgresql/postgresql.logconfig /aegis/config/postgresql/postgresql.confdata /aegis/data/postgresqlport 5432socket /aegis/run/postgresqlpublished /aegis/bin/psql /aegis/bin/pg_dump Published commands are wrapper scripts exporting LD_LIBRARY_PATH; see cubes/php.md for why a symlink is not enough.
#Notes
-lpq against the vendored OpenSSL; the published wrapperscarry the library path.
initdb refuses to run on a non-empty data directory: the cube reports thepath instead of guessing.
initdb leaves the directory empty, so a retry is safe.