#PostgreSQL
The PostgreSQL cube owns the server, its cluster and the databases the platform creates. It renders postgresql.conf, pg_hba.conf and pg_ident.conf (all mode 0600) under postgresql.config_dir (/aegis/config/postgresql), keeps the cluster in postgresql.data_dir (/aegis/data/postgresql), the socket and pid in postgresql.run_dir and the server log in postgresql.log_dir. configure runs initdb once, when the data directory is empty, then creates the platform superuser from aegis.user / aegis.password.
The server runs as the keeper service aegis-postgresql under the postgresql.user account. Local connections use peer authentication, TCP uses scram-sha-256; every administrative statement runs through psql as the OS user over the socket.
#Common commands
Cube operations share one wrapper: ✔ postgresql: <detail> on success, ✖ <message> on failure (exit 1), and {"ok": true, "cube": "postgresql", "result": "<detail>"} with global --json.
expose is the only verb that prints a block instead of that line.
| Verb | Effect |
|---|---|
configure | create the service account, initialise the cluster if empty, render the three files, start, ensure the platform role |
validate | run pg_isready against the socket and port |
reload | send SIGHUP to the postmaster: settings reload, connections stay |
status | read data/postmaster.pid: running (pid N) or stopped |
start | start the server through the keeper |
stop | SIGTERM, then SIGKILL after 15 seconds |
restart | stop, then start |
expose | print paths, port and socket, then publish the client tools |
help | print the command list with examples |
#configure
aegis cubes postgresql configureaegis --json cubes postgresql configureRefuses a non-empty data directory that is not a cluster (data directory is not empty: <path>), so a mount point is never overwritten.
✔ postgresql: configuration written, server accepts connections{"ok": true, "cube": "postgresql", "result": "configuration written, server accepts connections"}#validate
aegis cubes postgresql validateaegis --json cubes postgresql validatepg_isready only proves the server accepts connections; it does not authenticate.
✔ postgresql: server accepts connections{"ok": true, "cube": "postgresql", "result": "server accepts connections"}#reload
aegis cubes postgresql reloadaegis --json cubes postgresql reloadSends SIGHUP; a stopped server fails with postgresql is not running.
✔ postgresql: configuration reloaded{"ok": true, "cube": "postgresql", "result": "configuration reloaded"}#status
aegis cubes postgresql statusaegis --json cubes postgresql status✔ postgresql: running (pid 1601){"ok": true, "cube": "postgresql", "result": "running (pid 1601)"}#start
aegis cubes postgresql startaegis --json cubes postgresql start✔ postgresql: started (pid 1601){"ok": true, "cube": "postgresql", "result": "started (pid 1601)"}#stop
aegis cubes postgresql stopaegis --json cubes postgresql stopAn immediate stop is not requested: the postmaster is asked to shut down and is killed only if it ignores the signal for fifteen seconds.
✔ postgresql: stopped (pid 1601){"ok": true, "cube": "postgresql", "result": "stopped (pid 1601)"}#restart
aegis cubes postgresql restartaegis --json cubes postgresql restart✔ postgresql: stopped (pid 1601), started (pid 1633){"ok": true, "cube": "postgresql", "result": "stopped (pid 1601), started (pid 1633)"}#expose
aegis cubes postgresql exposeaegis --json cubes postgresql exposecube postgresqlinstalled /aegis/apps/data/postgresql/18.7configuration /aegis/config/postgresqlstate running (pid 1601)pid 1601log /aegis/logs/postgresql/postgresql.logconfig /aegis/config/postgresql/postgresql.confdata /aegis/data/postgresqlport 5432socket /aegis/run/postgresqlpublished /aegis/bin/psql, /aegis/bin/pg_dump, /aegis/bin/pg_restore, /aegis/bin/createdb, /aegis/bin/dropdb, /aegis/bin/pg_isready, /aegis/bin/initdb{"ok": true, "cube": "postgresql", "installed": "/aegis/apps/data/postgresql/18.7", "configuration": "/aegis/config/postgresql", "state": "running (pid 1601)", "pid": "1601", "log": "/aegis/logs/postgresql/postgresql.log", "config": "/aegis/config/postgresql/postgresql.conf", "data": "/aegis/data/postgresql", "port": "5432", "socket": "/aegis/run/postgresql", "published": "/aegis/bin/psql, /aegis/bin/pg_dump, /aegis/bin/pg_restore, /aegis/bin/createdb, /aegis/bin/dropdb, /aegis/bin/pg_isready, /aegis/bin/initdb"}#help
aegis cubes postgresql helpaegis cubes help postgresqlℹ Postgresql — PostgreSQL server, its cluster and databasesCommands validate Check the configuration reload Reload the running daemon configure Prepare the cluster and write config start Start the server stop Stop the server status Show whether the server runs restart Restart the server expose Show where it is installed and how it runs databases Manage databasesThe list continues with users, tables, sequences and help, then the examples.
#Databases
#databases list
aegis cubes postgresql databases listaegis --json cubes postgresql databases listLists every database whose datistemplate is false. The two templates are therefore absent; postgres is not a template and appears in the list.
✔ postgresql: aegis, postgres, shop{"ok": true, "cube": "postgresql", "result": "aegis, postgres, shop"}#databases create
aegis cubes postgresql databases create shopaegis --json cubes postgresql databases create analytics| Argument | Meaning |
|---|---|
name | database name; quotes and semicolons are refused |
The owner is aegis.user, the platform superuser created by configure.
✔ postgresql: created shop (owner aegis){"ok": true, "cube": "postgresql", "result": "created analytics (owner aegis)"}#databases drop
Destructive. Drops the database and everything in it.
postgres,template0andtemplate1are refused explicitly.
aegis cubes postgresql databases drop shopaegis --json cubes postgresql databases drop analytics| Argument | Meaning |
|---|---|
name | the database to drop |
✔ postgresql: dropped shop{"ok": true, "cube": "postgresql", "result": "dropped analytics"}#Users
A role is cluster-wide; a grant is per database. users grant gives the role the database plus full rights on the tables, sequences and functions in its public schema, including default privileges for objects created later.
#users list
aegis cubes postgresql users listaegis --json cubes postgresql users listLogin roles only, excluding pg_* internals and postgres.
✔ postgresql: aegis, app{"ok": true, "cube": "postgresql", "result": "aegis, app"}#users add
aegis cubes postgresql users add app --database shopaegis cubes postgresql users add reporting| Option | Meaning |
|---|---|
name | role name; letters, digits, _, $, - |
--password | password; generated and printed when omitted |
--database | optional database to grant immediately |
✔ postgresql: user app created; app granted on shop{"ok": true, "cube": "postgresql", "result": "user reporting created; password: 3Jc9xQ2mR7tLk0pW"}#users remove
Destructive. Drops the role. A role that still owns objects is refused with the count, so
DROP OWNED BYnever deletes data behind your back; only ACL entries and default privileges are cleared first.
aegis cubes postgresql users remove appaegis --json cubes postgresql users remove reporting| Argument | Meaning |
|---|---|
name | the role to drop |
✔ postgresql: user app dropped{"ok": true, "cube": "postgresql", "result": "user reporting dropped"}#users password
aegis cubes postgresql users password app --password 'new-secret'aegis --json cubes postgresql users password app --password 'new-secret'| Option | Meaning |
|---|---|
name | the role |
--password | required new password |
✔ postgresql: password changed for app{"ok": true, "cube": "postgresql", "result": "password changed for app"}#users grant
aegis cubes postgresql users grant app shopaegis --json cubes postgresql users grant reporting analytics| Argument | Meaning |
|---|---|
name | the role |
database | the database to grant |
✔ postgresql: app granted on shop{"ok": true, "cube": "postgresql", "result": "reporting granted on analytics"}#users revoke
Destructive. Revokes the database grant and the schema privileges. It does not drop objects the role owns.
aegis cubes postgresql users revoke app shopaegis --json cubes postgresql users revoke reporting analytics✔ postgresql: app revoked on shop{"ok": true, "cube": "postgresql", "result": "reporting revoked on analytics"}#Tables
#tables list
aegis cubes postgresql tables list shopaegis --json cubes postgresql tables list shop| Argument | Meaning |
|---|---|
database | the database to inspect |
Live row estimates from pg_stat_user_tables and total relation size.
✔ postgresql: orders (1204 rows, 8192 kB); sessions (12 rows, 48 kB){"ok": true, "cube": "postgresql", "result": "orders (1204 rows, 8192 kB); sessions (12 rows, 48 kB)"}#tables vacuum
aegis cubes postgresql tables vacuum shopaegis cubes postgresql tables vacuum shop --full| Option | Meaning |
|---|---|
database | the database |
--full | rewrite the tables instead of only reclaiming dead rows |
VACUUM (ANALYZE) without --full; VACUUM (FULL, ANALYZE) with it. A full rewrite takes an exclusive lock per table and needs free disk equal to the table size. The client timeout is one hour.
✔ postgresql: vacuum (analyze) done on shop{"ok": true, "cube": "postgresql", "result": "vacuum (full, analyze) done on shop"}#tables analyze
aegis cubes postgresql tables analyze shopaegis --json cubes postgresql tables analyze shopRefreshes planner statistics without rewriting anything.
✔ postgresql: analyze done on shop{"ok": true, "cube": "postgresql", "result": "analyze done on shop"}#tables reindex
aegis cubes postgresql tables reindex shopaegis --json cubes postgresql tables reindex shopRuns REINDEX DATABASE, outside a transaction, with the one-hour timeout.
✔ postgresql: reindexed shop{"ok": true, "cube": "postgresql", "result": "reindexed shop"}#Sequences
Rows restored from a dump keep their ids while the sequence behind the column starts again at 1, so the next insert fails on a duplicate key. These commands find those sequences and move them past MAX(column).
#sequences list
aegis cubes postgresql sequences list shopaegis cubes postgresql sequences list shop --schema public --table orders| Option | Meaning |
|---|---|
database | the database |
--schema | only sequences in this schema |
--table | only sequences owned by this table |
Report only: it never calls setval. It prints ok lines as well, so an empty answer means there are no owned sequences at all.
✔ postgresql: public.orders.id seq=41 max=57 behind{"ok": true, "cube": "postgresql", "result": "public.orders.id seq=41 max=57 behind"}#sequences sync
aegis cubes postgresql sequences sync shopaegis cubes postgresql sequences sync shop --dry-run| Option | Meaning |
|---|---|
database | the database |
--schema | restrict to one schema |
--table | restrict to one table |
--dry-run | report what would change, write nothing |
--all | also list sequences that are already in sync |
Only sequences that are behind are moved; each is set to the current maximum, so the next insert gets the following value.
✔ postgresql: fixed 1 of 3 sequencespublic.orders.id seq=41 max=57 -> setval(57)✔ postgresql: would fix 1 of 3 sequencespublic.orders.id seq=41 max=57 behind