Aegis documentation

#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

toml
[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"
KeyDefaultMeaning
version18informational, matches the installed series
port5432TCP port, loopback only unless listen changes
userpostgressystem account the server runs as
config_dir/aegis/config/postgresqlthe three configuration files
data_dir/aegis/data/postgresqlcluster created by initdb
run_dir/aegis/run/postgresqlUnix socket directory
log_dir/aegis/logs/postgresqlpostgresql.log
listen127.0.0.1listen_addresses
remote_rulesemptyappended verbatim to pg_hba.conf
max_connections200server ceiling
shared_buffers512MBmain cache
effective_cache_size1536MBplanner hint, not an allocation
work_mem16MBper sort/hash operation
maintenance_work_mem128MBVACUUM 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

text
<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.log

postgresql.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

bash
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.

bash
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 shop

remove 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

bash
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
ActionStatementUse
vacuumVACUUM (ANALYZE)reclaims dead rows and refreshes statistics
vacuum --fullVACUUM (FULL, ANALYZE)rewrites the tables, returns disk to the OS (locks)
analyzeANALYZEstatistics only
reindexREINDEX DATABASErebuilds 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):

bash
# 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 list

Output is one line per sequence:

text
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

  • creates the postgres operating-system group and user when missing;
  • runs initdb once, when the data directory is still empty, with
  • --auth-local=peer --auth-host=scram-sha-256;

  • renders the three configuration files and sets mode 0600;
  • starts the server through the keeper;
  • makes sure the platform role exists.
  • #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:

    bash
    psql -h 127.0.0.1 -U aegis -d postgres# password from [aegis] password

    Local administration uses peer authentication as the postgres system user, so psql over the socket needs no password.

    #databases

    bash
    aegis cubes postgresql databases listaegis cubes postgresql databases create shopaegis cubes postgresql databases drop shop

    A database is owned by the platform role. Names containing " or ; are rejected rather than quoted.

    #Process

    toml
    [services.postgresql]command  = <prefix>/bin/postgres -D <data_dir> -c config_file=pid_file = <data_dir>/postmaster.piduser     = postgres

    The 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

    bash
    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

  • The build links -lpq against the vendored OpenSSL; the published wrappers
  • carry the library path.

  • initdb refuses to run on a non-empty data directory: the cube reports the
  • path instead of guessing.

  • A failed initdb leaves the directory empty, so a retry is safe.