Timescale-DB Postgres-Stack

Docker-Compose stack consisting of:

• PostgreSQL
• PostgREST
• SwaggerUI
• pgAdmin
• Keycloak

classDiagram
    PostgresDB --|> PostrestAPI : generates
    PostrestAPI --|> SwaggerUI : generates
    PostgresDB  <|-- PgAdminUI : configurates
    PythonClient --|> PostrestAPI : connects to
    PythonClient <|-- Keycloak : provides token
    Keycloak <|-- PostrestAPI : validates token
    SQLClient --|> PostgresDB : connects to

As reverse proxy, caddy is recommended. To use it, create the following docker compose within the same or a separate stack:

services:
  caddy:
    environment:
    - CADDY_INGRESS_NETWORKS=caddy
    image: lucaslorentz/caddy-docker-proxy:ci-alpine
    networks:
    - caddy
    ports:
    - 80:80
    - 443:443
    restart: unless-stopped
    volumes:
    - /var/run/docker.sock:/var/run/docker.sock
    - caddy_data:/data

volumes:
  caddy_data: {}
networks:
  caddy:
    external: true

In docker-compose.example.override.yml you can find the necessary definitions to expose the different containers via caddy.

cp .env.example .env

Set ENV values in .env

docker compose up

docker compose down -v
sudo rm -r postgres/data && sudo rm -r pgadmin/data
sudo mkdir pgadmin/data && sudo chown -R 5050:5050 pgadmin/data
mkdir pgadmin/config

see: https://postgrest.org/en/v12/tutorials/tut1.html#step-2-make-a-secret

echo "jwt-secret = \"$(LC_ALL=C tr -dc 'A-Za-z0-9' </dev/urandom | head -c32)\""

goto https://jwt.io/ and sign token with header {"alg": "HS256", "typ": "JWT"} and payload {"role": "api_user"}

Example token: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoiYXBpX3VzZXIifQ.<signature>

set the jwt-secret in your .env file:

PGRST_JWT_SECRET=aAdsdasd...
PGRST_ROLE_CLAIM_KEY='role'

Go to https://auth.<domain>/admin/master/console/#/master/realm-settings/keys

Copy public key for RSA RS256 algorithm.

Wrap it in header/footer line and convert it via https://8gwifi.org/jwkconvertfunctions.jsp to a JWT. Example:

-----BEGIN PUBLIC KEY-----
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA2WGSqwsD/8VS6CEPF7Bwknzk6u9SgdLoUtRYnyWlvAE4jDmx92ql4YEcGug+DXZy33EnpoL9mjSXrghuiKb1pNAI9sHcc863pkuBWm2S7/l/esJkTD8J1sUETfy4OH4IutjTmtwyHGhfi1rlI81a1E6vCcMNyh5vTCizjerHfP34jjXvnMHIDU4F51JmN9FVpwpKlk/2JXRyCesedTNiiPaHZXQDRltVQGputXClugyEs8o7y46RoieGlc6/FLPU1JJGlM7F52fOYmIjhDWzO54/PHlzVCGEpW5c8kxeLlBBfjaYyiSvLH5ScssmrjtD5+aqV8A9iKViZuu4zOQs1wIDAQAB
-----END PUBLIC KEY-----

{"kty":"RSA","e":"AQAB","kid":"15f3d607-103c-49ec-9041-df9e0f9fa848","n":"2WGSqwsD_8VS6CEPF7Bwknzk6u9SgdLoUtRYnyWlvAE4jDmx92ql4YEcGug-DXZy33EnpoL9mjSXrghuiKb1pNAI9sHcc863pkuBWm2S7_l_esJkTD8J1sUETfy4OH4IutjTmtwyHGhfi1rlI81a1E6vCcMNyh5vTCizjerHfP34jjXvnMHIDU4F51JmN9FVpwpKlk_2JXRyCesedTNiiPaHZXQDRltVQGputXClugyEs8o7y46RoieGlc6_FLPU1JJGlM7F52fOYmIjhDWzO54_PHlzVCGEpW5c8kxeLlBBfjaYyiSvLH5ScssmrjtD5-aqV8A9iKViZuu4zOQs1w"}

Store the JWT in your .env file

PGRST_JWT_SECRET={"kty":"RSA","e":"AQAB","kid": "abc..."}
PGRST_ROLE_CLAIM_KEY='.resource_access.postgrest.roles[0]'

Go to "clients" and create a new one with name postgrest. Choose access type "public", and define the redirecturi e.g. to https://login.<domain> for now.

Set the following settings:

Type: OpenID Connect
Name: postgrest
Authentication: Off
Flow: Implicite flow + Device Auth Grant

Next define the role to access the POSTGREST-API and the corresponding user group:

1. Create Client role "api_user"
2. Create Group "api_users"
3. Assign role "api_user" to Group "api_users"

For testing, create the following ressources

1. Create user "testuser"
2. Set password "testpassword"
3. Let "testuser" join group "api_users"

Generated access token: see clients/postgrest/client_scopes/evaluate with testuser

curl  -X POST \
  'https://<KEYCLOAK_SERVER>/realms/master/protocol/openid-connect/token' \
  --header 'Accept: */*' \
  --header 'Content-Type: application/x-www-form-urlencoded' \
  --data-urlencode 'grant_type=password' \
  --data-urlencode 'client_id=postgrest' \
  --data-urlencode 'username=testuser' \
  --data-urlencode 'password=testpassword'

Optionally you can provide a minimal login page to allow users to request a token by replace to placeholders in keycloak/config/login-page/index.template.html and provide it via webserver at the configured redirect url.

If you use Caddy, you can configure the caddy docker-compose.yml as follows:

    # custom static files
    - <your_path>/keycloak/config/login-page:/var/www/html/login-page
    labels:
      caddy: <CLIENT_REDIRECT_URL>
      caddy.file_server: /*
      caddy.file_server.root: "/var/www/html/login-page"

sudo chown 1000:1000 ./postgres/data
docker compose up

api.downsample_tool_channel(osw_tool, ch_id, ts_start, ts_end, max_points, bin_size, method, edge_anchors) buckets a tool channel with time_bucket() and reduces each bucket with one of three strategies:

• sample (default): one real row nearest each bucket center, schema-agnostic.
• average: structure-preserving deep average of the numeric JSONB leaves, timestamped at the bucket center.
• minmax: the real argmin/argmax row of every numeric leaf per bucket, so spikes and the signal envelope survive. Returns up to two rows per bucket.

average and minmax fall back to sample for channels without a numeric leaf. With edge_anchors the first and last returned rows are the window's first/last real datapoints.

The dataset-independent benefit is the payload reduction. Absolute query times are planner- and hardware-dependent: the same query varies 2-3x (and minmax more) across PostgreSQL versions, work_mem and available parallel workers, so treat any millisecond figure as indicative and measure on your own database. Payload measured with benchmarks/bench_downsample.py from opensemantic.base-python, 100000 points per channel, max_points=1000. scalar stores {"value": n}, composite a nested dict of two measurements.

Reading this:

• Every strategy shrinks the payload ~50-100x, which is the point of downsampling: a dashboard transfers ~0.1 MB instead of ~10-15 MB.
• sample is the cheapest by far and is the right default for line plots. average and minmax walk every numeric leaf of every row in the window, so their cost scales with the rows scanned, not with max_points; minmax (argmin/argmax of each leaf per bucket) is the most expensive.
• Downsampling only pays off when it replaces reading the whole series. A client that already caps its read (e.g. limit=10000) may find a capped full-resolution read cheaper than minmax over a large window.
• Cost tracks the rows scanned, so a narrow, realistic time range matters more than a small max_points. Over a window far wider than the stored data most buckets are empty and you get far fewer points than requested.

Schema details this relies on, all in postgres/config/optional/100_init_tsdb_schema.sql:

• a (ch, ts DESC) index per tool table, so a channel-filtered read does not scan every channel in the time range (the hypertable itself only indexes ts);
• GRANT EXECUTE on time_bucket to api_user, since the RPC is SECURITY INVOKER. Without it the RPC fails with permission denied for function time_bucket and clients silently fall back to full-resolution reads;
• a ROWS 1 estimate on api._jsonb_numeric_leaves. A set-returning plpgsql function defaults to an estimate of 1000 rows per call, so in the minmax LATERAL join the planner expected ~1000x the real leaf count and chose a serial big-sort. ROWS 1 matches scalar data (one leaf per row) and lets it pick the parallel plan; it helps up to a few numeric leaves per row and mildly regresses past ~10, so raise it for genuinely many-leaf channels.

Tuning minmax: its windowing plans either as a parallel scan+sort or a single serial big-sort, and which the planner picks is sensitive to the ROWS estimate above, to work_mem (a larger work_mem can disable the parallel plan and make it slower), and to max_parallel_workers_per_gather. If minmax is slow, EXPLAIN ANALYZE it and check whether it ran parallel. For very large ranges the structural fix is TimescaleDB continuous aggregates (pre-computed rollups maintained by background workers), which make the cost proportional to the points returned rather than to the rows scanned.

The SQL under postgres/config/* (e.g. postgres/config/optional/100_init_tsdb_schema.sql, which defines the tool endpoints and the api.downsample_tool_channel RPC) is mounted into /docker-entrypoint-initdb.d/. The Postgres entrypoint runs those scripts only when it initializes an empty data directory (first start). On an already-initialized container it logs ... Skipping initialization and never sources them, so:

• docker compose up / docker restart does not re-run the init SQL.
• Editing a mounted .sql file has no effect on the running DB.

To pick up new or changed schema / endpoints without wiping data, apply the SQL manually and reload PostgREST's schema cache. The init SQL is written to be idempotent (CREATE EXTENSION/TABLE IF NOT EXISTS, and a DROP ... IF EXISTS before every CREATE OR REPLACE function / aggregate / view), so it is safe to re-run on a live database:

# 1. Apply the (idempotent) schema, incl. any new endpoints, to the live DB.
#    Run as the superuser so object ownership and the GRANTs re-apply.
docker exec -i postgres_container sh -c \
  'psql -v ON_ERROR_STOP=1 -U "$POSTGRES_USER" -d "$POSTGRES_DB"' \
  < postgres/config/optional/100_init_tsdb_schema.sql

# 2. Make PostgREST expose the changes (reload its schema cache).
docker exec -i postgres_container sh -c \
  'psql -U "$POSTGRES_USER" -d "$POSTGRES_DB" -c "NOTIFY pgrst, '\''reload schema'\'';"'
#    alternative: docker restart postgrest_container

Verify a function is present in the exposed schema, e.g.:

docker exec postgres_container sh -c \
  'psql -U "$POSTGRES_USER" -d "$POSTGRES_DB" -c "\df api.<function_name>"'

Until step 2 runs, PostgREST keeps returning "function not found" for a new RPC; clients that call it should fall back to a full-resolution read in the meantime.

Up to and including commit 3166ac8, 100_init_tsdb_schema.sql ran SET search_path TO api before CREATE EXTENSION timescaledb. An extension created without a SCHEMA clause lands in the first schema of search_path, so wherever the extension had not already been created, it was created inside the PostgREST-exposed api schema. Symptoms:

• create_tool fails with function public.create_hypertable(unknown, unknown) does not exist
• the TimescaleDB functions sit in the exposed schema, where PostgREST turns those a role may execute into /rpc endpoints

Affected: databases initialized from dcc8198 (2025-10-10, the first version of the file) through 3166ac8 (2026-08-11), and on a stack where the extension was not already created before that script ran. Fixed in 9271766, which creates the extension with an explicit SCHEMA public and refuses to initialize a drifted database.

Whether the extension already exists is not decided by the image alone. The timescale/timescaledb-ha image ships /docker-entrypoint-initdb.d/000_install_timescaledb.sh, which runs CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE with the default search_path and sorts before the mounted 100_init_tsdb_schema.sql. On such a stack the extension is already in public and the CREATE EXTENSION in the init script is a no-op.

A deployment that shadows that installer, a common workaround for extension "timescaledb" has already been loaded with another version:

- ./postgres/config/optional/empty:/docker-entrypoint-initdb.d/000_install_timescaledb.sh

removes that step, so 100_init_tsdb_schema.sql becomes the first creator of the extension and it lands in whichever schema search_path names first.

docker-compose.example-tsdb.override.yml in this repository does not shadow that installer, it only skips the toolkit; the deployed test and production overrides do shadow it. Two stacks on the same image and the same commit therefore ended up different, and the drift never showed locally. Deployment overrides are not part of this repository, so check the running stack rather than the example: which compose files were used, and whether the installer is disabled:

docker inspect postgres_container   --format '{{index .Config.Labels "com.docker.compose.project.config_files"}}'
docker exec postgres_container   sh -c 'wc -c < /docker-entrypoint-initdb.d/000_install_timescaledb.sh'
docker exec -i postgres_container   sh -c 'psql -U "$POSTGRES_USER" -d template1 -c "\dx timescaledb"'

A size of 0 means the installer is shadowed by empty. If template1 lists the extension, every new database inherits it in public.

Both the image and those shadowing mounts come from the override files passed to compose, not from docker-compose.yml alone:

docker compose   -f docker-compose.yml   -f docker-compose.example-tsdb.override.yml   -f docker-compose.test.override.yml up

Since this depends on how a stack was deployed rather than on the commit alone, let the preflight decide. It is read-only:

docker exec -i postgres_container sh -c 'psql -U "$POSTGRES_USER" -d "$POSTGRES_DB"'   < postgres/migrations/000_preflight_check.sql

timescaledb schema = api means drifted, public means there is nothing to do. To normalize (catalog-only, no data is copied, but take a backup first):

docker stop postgrest_container

docker exec -i postgres_container sh -c   'psql -v ON_ERROR_STOP=1 -U "$POSTGRES_USER" -d "$POSTGRES_DB"'   < postgres/migrations/001_normalize_timescaledb_schema.sql

docker exec -i postgres_container sh -c   'psql -v ON_ERROR_STOP=1 -U "$POSTGRES_USER" -d "$POSTGRES_DB"'   < postgres/config/optional/100_init_tsdb_schema.sql

docker start postgrest_container

Re-run the preflight afterwards: the extension must be in public, the exposed schema must contain no extension functions and the hypertable count must be unchanged.

Validate the endpoints end to end. The script creates a tool, writes a row to its table endpoint, reads it back and deletes the tool again; because it writes immediately after the create, it also proves the schema cache reload works:

scripts/validate_api.sh                                 # local stack
scripts/validate_api.sh https://db.test.terravac.cloud  # remote

docker compose down -v
sudo rm -R postgres/data/*

Take a full dump in the custom format. It is compressed (about the same size as plain SQL piped through gzip, since the format uses zlib itself), and unlike a compressed SQL stream it can be listed without restoring, restored in parallel with pg_restore -j, and restored selectively with -t / -n.

Do not redirect stderr to /dev/null: a dump that fails or truncates half way still leaves a plausible looking file, so the warnings and errors are the only signal that it did not complete.

cd <path-to-tsdb-docker-compose-folder>
mkdir -p backup
docker exec postgres_container sh -c   'pg_dump -U "$POSTGRES_USER" -Fc -d "$POSTGRES_DB"'   > backup/backup_$(date +"%Y%m%d_%H%M%S").dump

pg_dump prints, for hypertable, chunk and continuous_agg:

warning: there are circular foreign-key constraints on this table
hint: ... Consider using a full dump instead of a --data-only dump ...

Those are TimescaleDB's own catalog tables, which reference each other; the warning appears on every TimescaleDB dump and the dump is still complete. The hint does not apply to the command above, which already is a full dump.

The dump is written on the host, not inside the container, so verify it by piping it back in via stdin (a few hundred TOC entries is normal):

docker exec -i postgres_container pg_restore --list < backup/backup_<date>.dump | wc -l

Always take a backup before applying anything under postgres/migrations/.

For a dump that can be read without restoring it, for example to grep the DDL, use plain SQL instead. It restores by piping into psql rather than pg_restore, still wrapped in the two TimescaleDB calls shown below:

docker exec postgres_container sh -c   'pg_dump -U "$POSTGRES_USER" -F p -d "$POSTGRES_DB" | gzip'   > backup/backup_$(date +"%Y%m%d_%H%M%S").sql.gz

zcat backup/backup_<date>.sql.gz | less

A plain pg_restore is not sufficient for TimescaleDB: its catalog and the chunk triggers have to be put into restore mode first, otherwise the restore fails or leaves the hypertables inconsistent. Wrap it in timescaledb_pre_restore() / timescaledb_post_restore(). The target database must already have the same TimescaleDB version as the source.

cd <path-to-tsdb-docker-compose-folder>

docker exec -i postgres_container sh -c   'psql -v ON_ERROR_STOP=1 -U "$POSTGRES_USER" -d "$POSTGRES_DB" -c "SELECT timescaledb_pre_restore();"'

docker exec -i postgres_container sh -c   'pg_restore -U "$POSTGRES_USER" -d "$POSTGRES_DB"' < backup/backup_<date>.dump

docker exec -i postgres_container sh -c   'psql -v ON_ERROR_STOP=1 -U "$POSTGRES_USER" -d "$POSTGRES_DB" -c "SELECT timescaledb_post_restore();"'

Verify the restored database: the hypertables, their chunks and new writes.

docker exec postgres_container sh -c 'psql -U "$POSTGRES_USER" -d "$POSTGRES_DB" -c   "SELECT hypertable_schema, hypertable_name FROM timescaledb_information.hypertables;"'
docker exec postgres_container sh -c 'psql -U "$POSTGRES_USER" -d "$POSTGRES_DB" -c   "SELECT hypertable_name, count(*) FROM timescaledb_information.chunks GROUP BY 1;"'

Inserting a row with a timestamp outside the existing range must create a new chunk; if it does, the hypertable machinery survived the restore intact.

A plain SQL dump is restored the same way, piped into psql instead of pg_restore and wrapped in the same two calls:

docker exec -i postgres_container sh -c   'psql -v ON_ERROR_STOP=1 -U "$POSTGRES_USER" -d "$POSTGRES_DB" -c "SELECT timescaledb_pre_restore();"'

zcat backup/backup_<date>.sql.gz | docker exec -i postgres_container sh -c   'psql -U "$POSTGRES_USER" -d "$POSTGRES_DB"'

docker exec -i postgres_container sh -c   'psql -v ON_ERROR_STOP=1 -U "$POSTGRES_USER" -d "$POSTGRES_DB" -c "SELECT timescaledb_post_restore();"'

A dump records the schema the extension lived in when it was taken, as CREATE EXTENSION IF NOT EXISTS timescaledb WITH SCHEMA .... A backup taken before the normalization below therefore carries WITH SCHEMA api: restoring it into an empty database recreates the drift and migration 001 has to be run again. Restoring it into a database that already has the extension in public is unaffected, because the statement is then a no-op.