PostgreSQL image
Andromeda needs one PostgreSQL server with the PostGIS and TimescaleDB extensions, at the versions and with the settings PostgreSQL lists. You don't have to assemble that server yourself. A PostgreSQL image built for the broker is published beside the broker image, for linux/amd64 and linux/arm64.
ghcr.io/mskvarc/postgresql:18.6-timescaledb2.31.0-postgis3.6.4-tsl
It works with every way of installing the broker, in the same Compose file or next to a broker built from source. Nothing in the broker depends on it, so a server of your own works just as well.
Why it exists
The extensions are only part of what the broker needs from its database. The image also provides the following.
Running it
This runs the image on its own, for a broker on the same machine or on the network.
export POSTGRES_PASSWORD=$(openssl rand -hex 24)
docker run -d --name postgresql \
--restart unless-stopped \
--memory 4g --cpus 2 --shm-size 1g \
-e POSTGRES_USER=andromeda \
-e POSTGRES_PASSWORD \
-e POSTGRES_DB=andromeda \
-v postgresql-data:/var/lib/postgresql \
-p 127.0.0.1:5432:5432 \
ghcr.io/mskvarc/postgresql:18.6-timescaledb2.31.0-postgis3.6.4-tsl
The broker's database.connection is then postgres://andromeda:<password>@localhost:5432/andromeda. The role and the database explains what the server holds after its first start. The Docker page runs the image and the broker together in one Compose file.
The role and the database
With no variables set, both the superuser and the initial database are called andromeda. On its first start the image renames the postgres database that initdb creates, so a new cluster holds only andromeda, template0 and template1, and there is no postgres database at all. The broker doesn't need one. It creates and drops tenant databases over a connection to the database database.connection names. The operating-system user inside the container is still postgres, and it owns the data directory.
The official image's variables change this, and only on the first start, while the data directory is empty.
Scripts in /docker-entrypoint-initdb.d run on that first start, against POSTGRES_DB. Once initialisation is done, none of these variables stays in the server's environment.
To open psql in the server, run docker exec -it postgresql psql -U andromeda.
PostgreSQL's own client tools connect to postgres first when they need a database to start from. Without one, they fall back to template1, and the server logs FATAL: database "postgres" does not exist once. Give them --maintenance-db=andromeda and the log stays clean. createdb, dropdb, vacuumdb --all, reindexdb --all and clusterdb --all all take that option, and pg_dumpall takes -l andromeda instead. A backup tool that connects to a database by name may need -d andromeda or its own equivalent.
Tags and versions
The image has a version of its own, independent of the broker's. It follows PostgreSQL, TimescaleDB and PostGIS, so the full tag says what is inside.
18.6-timescaledb2.31.0-postgis3.6.4-tsl
That is PostgreSQL 18.6, TimescaleDB 2.31.0 and PostGIS 3.6.4, in the tsl edition. A new version is published when one of the three moves, on its own schedule rather than with broker releases. On top of that, every edition is rebuilt each Monday so that it picks up security fixes in the base image and the packages, which means a tag can point at a different image from one week to the next even though the versions in its name stay the same. Broker 1.0.0 needs TimescaleDB 2.31.0 at least and refuses a tenant on a server below it. It also updates the extensions inside each database it serves, so a database made on an older image catches up the first time a broker opens it on a newer one.
| Tag | Moves | Example |
|---|---|---|
| Dated | Never | 18.6-timescaledb2.31.0-postgis3.6.4-tsl-20261105.42 |
| Full | Every week | 18.6-timescaledb2.31.0-postgis3.6.4-tsl |
| PostgreSQL major | Every week, within that major | 18-tsl |
| Edition | Every week, any major | tsl |
| Latest | The same as tsl | latest |
The dated tag is the full tag followed by the build date and the number of the build that made it. It is the only tag that never moves. Pin it, or the image's digest, in anything where a restart must not change the image. docker buildx imagetools inspect <tag> prints a tag's digest, and an image can be pulled by digest as postgresql@sha256:<digest>. Moving to a newer dated tag then brings in the weekly fixes when you choose.
Editions
The edition is the last part of every tag. Both work with the broker, and PostgreSQL explains how the broker treats each.
| Edition | TimescaleDB | Old history | Toolkit | latest tag |
|---|---|---|---|---|
tsl, the default | Community | Compressed into the columnstore | Included | Yes |
apache | Apache-2.0 | Kept uncompressed | Left out | No |
A deployment that keeps years of temporal data is much smaller on disk with tsl. Choose apache when its licence matters more to you than disk space.
Both editions come from the same Dockerfile, and its TIMESCALEDB_EDITION build argument picks one. The apache build removes the TSL module and adds 05-edition.conf, which sets timescaledb.license = 'apache'. The TimescaleDB packages come from Timescale's repository, and the build checks that repository's signing key against a pinned fingerprint. PostGIS and pg_repack come from the PostgreSQL project's repository and its archive, so a pinned version still installs after a newer release has replaced it.
What the image adds
The image is the official postgres image (Debian 13 "trixie", PostgreSQL 18) plus what the broker needs. So that image's documentation still applies, from POSTGRES_USER, POSTGRES_PASSWORD and POSTGRES_DB to POSTGRES_PASSWORD_FILE for Docker secrets and scripts in /docker-entrypoint-initdb.d.
Extensions
PostGIS and TimescaleDB are installed, and timescaledb and pg_stat_statements are already in shared_preload_libraries. The broker creates the extensions in each database it makes. The image also carries pg_repack, and the TSL edition adds the TimescaleDB Toolkit.
| Component | Version |
|---|---|
| PostgreSQL | 18.6 |
| TimescaleDB | 2.31.0 |
| PostGIS | 3.6.4 |
| pg_repack | 1.5.3 |
timescaledb-tune | 0.19.0 |
| TimescaleDB Toolkit | 1.27.0, tsl only |
Tuning to the container
On every start, the entrypoint reads the memory and CPU limits of the container, not of the host, and runs timescaledb-tune with them. The tuner sets shared_buffers to a quarter of memory and effective_cache_size to three quarters, and sizes the memory, worker, connection and WAL settings to the hardware. Then the entrypoint derives what the tuner leaves alone or gets wrong for the broker.
| Setting | Value |
|---|---|
autovacuum_work_mem | 5 percent of memory shared over the autovacuum workers, between 32MB and 1GB each |
io_workers | Half the CPUs, rounded up, between 3 and 32 |
temp_file_limit | 5 percent of the data volume |
wal_buffers | A 32nd of shared_buffers, between 32MB and 64MB, never below the tuner's value |
max_wal_size, min_wal_size | The tuner's values, capped at 16GB and 2GB |
work_mem | The tuner's value, lowered until the memory ceilings fit |
The temp_file_limit means one query that spills to disk fails on its own instead of filling the volume. Planner costs for SSD storage come from the fixed policy below.
With a single CPU the tuner writes no worker settings at all, so the entrypoint writes them. max_worker_processes becomes the background workers plus 4, max_parallel_workers becomes 1 and max_parallel_workers_per_gather becomes 0. Raising TS_TUNE_MAX_BG_WORKERS raises max_worker_processes with it, on any number of CPUs.
If the memory ceilings add up to more than the container has, the entrypoint prints a warning at startup. How the settings are chosen explains the WAL, memory and shared memory figures, and PostgreSQL tuning shows the settings and the warnings for any limits you give it.
A fixed server policy
The settings that are the same on every machine come after the tuner's, in a file the entrypoint never rewrites.
ALTER SYSTEM overrides any of it.
Health check
The image declares a health check, so docker ps shows when the server is ready. A Compose service can wait for it with depends_on and condition: service_healthy, without a healthcheck of its own. The check probes over TCP, which the temporary server that initialises a new data directory never listens on, so it passes only once the real server accepts connections.
Environment variables
All are optional.
Tune for this much memory instead, for example 8GB.
Tune for this many CPUs instead.
The max_connections to tune for. A -c max_connections= argument wins over it.
Background workers. TimescaleDB runs a scheduler in every database that has the extension, one per tenant, and each takes a worker, so raise it above the number of tenant databases.
The WAL disk size to tune for. When pg_wal is on a volume of its own, the default is that whole volume.
ALTER SYSTEM or -cThe entrypoint rewrites the tuner's values on every start. Change a setting for good with ALTER SYSTEM, or with a -c flag after the image name, never by editing postgresql.conf.
The server reads its settings in layers, and a later layer wins over an earlier one.
postgresql.conf, whichtimescaledb-tunerewrites from the container's limits on every start- The fixed policy in
/etc/postgresql/andromeda.conf.d/, the same on every machine andromeda-derived.confin the data directory, which holds the values the entrypoint derives and is also rewritten on every startpostgresql.auto.conf, whichALTER SYSTEMwrites and the entrypoint never touches-c name=valueon the server's command line
So ALTER SYSTEM SET work_mem = '64MB' lasts across restarts, while an edit to postgresql.conf or to andromeda-derived.conf is gone after the next one. The entrypoint tunes only when the container runs the server. A container started with another command, such as a shell, leaves the configuration as it found it.
Connections
The broker's connection pools together must fit inside max_connections. The image allows 200 by default, which fits the broker's default pools of 152 connections at most, and PostgreSQL tuning shows what it sets for any other number. For more tenants, raise max_connections and the memory limit with it, since each connection can use work_mem, or put PgBouncer in front. PostgreSQL explains how the broker counts its connections, and Choosing a setup walks through the choices.
How the settings are chosen
Here is the reasoning behind the values the image picks, so you can tell when your own load calls for different ones.
WAL
The tuner gives max_wal_size about 60 percent of the disk it is told about, and min_wal_size half of that, as if pg_wal had a disk to itself. By default pg_wal sits on the data volume, so the entrypoint tells the tuner a quarter of that volume. WAL then takes about 15 percent of the volume and the data keeps the rest. On a 20GB volume that works out at max_wal_size = 3GB and min_wal_size = 1536MB.
To give WAL a volume of its own, mount one and set POSTGRES_INITDB_WALDIR to its path before the server's first start. In Compose, add these to the postgresql service and declare postgresql-wal under the top-level volumes.
environment:
POSTGRES_INITDB_WALDIR: /var/lib/postgresql/wal
volumes:
- postgresql-data:/var/lib/postgresql
- postgresql-wal:/var/lib/postgresql/wal
The entrypoint notices that pg_wal is on another filesystem and tells the tuner the whole of it. TS_TUNE_WAL_DISK_SIZE overrides the size in either case.
However large the disk, max_wal_size stops at 16GB. After a crash, PostgreSQL replays up to that much WAL before it accepts connections, and replay was measured at about 6 seconds per GB. min_wal_size stops at 2GB, because all it does is keep pg_wal from shrinking below that size after a quiet spell, which saves creating segments when writes pick up again.
Memory
After tuning, the entrypoint adds up the server's memory ceilings, which are shared_buffers, max_connections × work_mem × hash_mem_multiplier, autovacuum_max_workers × autovacuum_work_mem, and maintenance_work_mem. When the sum is more than the container has, it warns at startup.
warning: memory ceilings of 2688MB exceed the 2048MB available: shared_buffers + max_connections x work_mem x hash_mem_multiplier + autovacuum_max_workers x autovacuum_work_mem + maintenance_work_mem
The tuner sizes work_mem without counting hash_mem_multiplier or autovacuum's memory. At 200 connections in 2GB, its own values would add up to the 2688MB in that line. The entrypoint therefore lowers work_mem to whatever memory is left once shared_buffers, every autovacuum worker and maintenance_work_mem are counted, spread over max_connections hash tables. That gives 2457kB at 2GB, while at 8GB the tuner's 8MB already fits. With the image's own values the warning never fires. You see it only after an override raises the ceilings past memory, such as ALTER SYSTEM SET work_mem or a larger max_connections on a small machine.
The sum covers ceilings only. Each backend also has overhead of its own that the sum leaves out, like its catalog and relation caches, which grow with the tables of the tenant database it serves, its temporary buffers and its parallel workers. Leave memory beyond the sum for them.
Shared memory
PostgreSQL keeps its dynamic shared memory in /dev/shm, which Docker sizes at 64MB. The cumulative statistics kept there grow with the tables of every tenant database, and parallel hash joins allocate there too. When /dev/shm is full, the statement that needed the space fails. The entrypoint warns at startup when /dev/shm holds less than 1GB.
warning: /dev/shm holds 64MB, below the 1024MB recommended: ...
--shm-size 1g with docker run, or shm_size: 1gb in Compose, gives it enough. On Kubernetes, mount a memory-backed emptyDir at /dev/shm.
volumes:
- name: shm
emptyDir:
medium: Memory
sizeLimit: 1Gi
containers:
- name: postgresql
volumeMounts:
- name: shm
mountPath: /dev/shm
What the volume uses counts against the container's memory limit.
TimescaleDB background workers
TimescaleDB runs a scheduler in every database that has the extension, which means the default tenant's database and one for each named tenant. Each scheduler, and each job a scheduler starts, takes one of timescaledb.max_background_workers, 64 by default. The broker installs no TimescaleDB job, but TimescaleDB adds one of its own to every such database. That job, number 3, deletes old entries from TimescaleDB's job history.
Past about 63 tenant databases the schedulers hold every worker, and the server logs this line now and then for each database.
failed to launch job 3 "Job History Log Retention Policy [3]": out of background workers
The line is harmless. Nothing the broker relies on is lost, and TimescaleDB's job history only grows a little longer. To stop it, raise TS_TUNE_MAX_BG_WORKERS above the number of tenant databases. Every worker is also a process slot in max_worker_processes, which the image raises along with it.
pg_stat_statements
pg_stat_statements keeps one entry for each statement, role and database. With a database per tenant, every tenant database runs the broker's statements under entries of its own, so the count multiplies with the tenants. The image sets pg_stat_statements.max to 20000, which takes about 8MB of shared memory.
The figure comes from a run of the ETSI, NGSI-LD and Annex C test suites. Afterwards a tenant database held 33 distinct statements at the median and 263 at most, and the default tenant's database held 701. Sixteen loaded tenants plus the default tenant therefore need about 4,900 entries, roughly a quarter of the limit. The entries of a tenant that is no longer loaded stay until they are evicted, though, so a server that has served hundreds of tenants fills the table anyway. That run left 368 tenant databases behind, with 19,410 entries and 81 evictions.
When the table overflows, PostgreSQL evicts the least used entries, and pg_stat_statements_info.dealloc counts the evictions. If dealloc keeps growing, raise the limit with ALTER SYSTEM SET pg_stat_statements.max and restart the server.
Autovacuum nap time
The autovacuum launcher starts a worker in each database once per autovacuum_naptime, so with a database per tenant its cost grows with the number of tenants. The tuner recommends 10 seconds, and the image sets 1 minute instead. A worker that finds nothing to do exits straight away, and the tables the broker writes hardest are vacuumed by their own thresholds as soon as the launcher reaches their database. A longer nap mostly saves the visits to idle tenants.
The value was chosen by measuring both sides on one host, at 10 seconds, 30 seconds and 1 minute. For the idle cost, the broker created 300 tenant databases and was then stopped, and the server's CPU use and process starts were counted over five minutes. At 10 and 30 seconds the launcher started about 10 workers a second without a break, and the server used 5 to 8 percent of a CPU. At 1 minute it started about 6 a second and used 3.3 percent.
The other side is how fast the notification queue drains under load. Three Subscriptions on one tenant each received 363 notifications a second for eight minutes, twice for each value. The 99th percentile from write to arrival was 0.14 s and 0.18 s at 10 seconds, and 0.13 s in both runs at 1 minute. One run at 30 seconds had a 13.65 s outlier that its repeat didn't show. The queue table, notification_deliveries with its indexes, peaked at 166MB at 10 seconds. It was 3.7 to 3.9 percent larger at 30 seconds and 7.7 to 9.5 percent larger at 1 minute, with about six times the dead rows at its peak.
1 minute is the longest nap whose latency and peak queue size stay within 10 percent of the 10-second figures. If a tenant writes far harder than this test did, or there are only a few tenants, bring back the tuner's value with ALTER SYSTEM SET autovacuum_naptime = '10s' and a reload.
Backups
The data volume holds everything. Back it up with a physical backup tool and WAL archiving against the running server, because a copy of the volume taken while the server runs can hold files that don't agree with each other. PostgreSQL names the tools.
Verifying the image
The image is signed with Sigstore cosign, so check the signature before you run it.
cosign verify ghcr.io/mskvarc/postgresql:18.6-timescaledb2.31.0-postgis3.6.4-tsl \
--certificate-identity-regexp 'https://github.com/mskvarc/andromeda/' \
--certificate-oidc-issuer https://token.actions.githubusercontent.com
Upgrading
Moving to a newer image of the same PostgreSQL major version is a change of tag. Stop the container and start a new one on the same volume, and the broker updates the extensions inside each database as it opens it. Restart the brokers together with the server. A broker that keeps serving across the restart can fail one statement per connection after the update.
A data directory made by PostgreSQL 18 won't start under another major version. The tsl and latest tags follow the newest major version, so a pull can bring in a new one. Use the dated tag, or at least the full tag or 18-tsl, which stay on PostgreSQL 18. A major upgrade is a migration you plan, with pg_upgrade or a dump and restore, not a change of tag.
Major upgrades with pg_upgrade
pg_upgrade restores template1 over a connection to the new cluster's postgres database, so the new cluster needs one for the duration of the upgrade. The renamed andromeda database keeps the identifier postgres had in the old cluster, and a fresh postgres in the new cluster must not hold it. Initialise the new cluster with initdb -U andromeda, recreate its postgres database, and then upgrade.
psql -U andromeda -d template1 -c 'DROP DATABASE postgres' -c 'CREATE DATABASE postgres'
pg_upgrade -U andromeda -d <old data directory> -D <new data directory> -b <old bindir> -B <new bindir>
Once the upgrade is done, drop the new cluster's postgres database again with psql -U andromeda -d andromeda -c 'DROP DATABASE postgres'. pg_upgrade prints two vacuumdb commands at the end, so run them with --maintenance-db=andromeda added. pg_upgrade --check passes without the new postgres database, since only the upgrade itself needs it.