TimescaleDB: Use Docker Compose for TimescaleDB
This time, I'm showing how to use Docker Compose to run TimescaleDB
TimescaleDB is "an open-source time-series database optimized for fast ingest and complex queries. Engineered up from PostgreSQL, packaged as an extension."
In a previous blog post, I wrote about how to use InfluxDB in Docker.
But why should I use TimescaleDB instead of InfluxDB? From my view as a RDBMS DBA, I prefer SQL over other languages - not because I'm good at it, but because I like standards.
Here's a statement in TimescaleDB looking for used memory in the last hour:
SELECT time, (memUsed / procTotal / 1000000) as value
FROM measurements
WHERE time > now() - '1 hour';Using the Flux language, it would look quite different:
// Memory used (in bytes)
memUsed = from(bucket: "telegraf/autogen")
|> range(start: -1h)
|> filter(fn: (r) =>
r._measurement == "mem" and
r._field == "used"
)Installation
This is my docker-compose.yml:
services:
db:
image: timescale/timescaledb:latest-pg17
hostname: timescaledb
environment:
- POSTGRES_PASSWORD=XxXxXxXxIf you want to access the database without a load balancer or something else, would have to also expose the ports, like
ports:
- 5432:5432Let's pull the image and start the container:
# docker compose pull
# docker compose up -ddb-1 | The files belonging to this database system will be owned by user "postgres".
db-1 | This user must also own the server process.
db-1 |
db-1 | The database cluster will be initialized with locale "en_US.utf8".
db-1 | The default database encoding has accordingly been set to "UTF8".
db-1 | The default text search configuration will be set to "english".
db-1 |
db-1 | Data page checksums are disabled.
db-1 |
db-1 | fixing permissions on existing directory /var/lib/postgresql/data ... ok
db-1 | creating subdirectories ... ok
db-1 | selecting dynamic shared memory implementation ... posix
db-1 | selecting default "max_connections" ... 100
db-1 | selecting default "shared_buffers" ... 128MB
db-1 | selecting default time zone ... UTC
db-1 | creating configuration files ... ok
db-1 | running bootstrap script ... ok
db-1 | sh: locale: not found
db-1 | 2025-01-26 11:58:25.857 UTC [35] WARNING: no usable system locales were found
db-1 | performing post-bootstrap initialization ... ok
db-1 | initdb: warning: enabling "trust" authentication for local connections
db-1 | initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
db-1 | syncing data to disk ... ok
db-1 |
db-1 |
db-1 | Success. You can now start the database server using:
db-1 |
db-1 | pg_ctl -D /var/lib/postgresql/data -l logfile start
db-1 |
db-1 | waiting for server to start....2025-01-26 11:58:30.696 UTC [41] LOG: starting PostgreSQL 17.2 on x86_64-pc-linux-musl, compiled by gcc (Alpine 14.2.0) 14.2.0, 64-bit
db-1 | 2025-01-26 11:58:30.699 UTC [41] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
db-1 | 2025-01-26 11:58:30.710 UTC [44] LOG: database system was shut down at 2025-01-26 11:58:26 UTC
db-1 | 2025-01-26 11:58:30.719 UTC [41] LOG: database system is ready to accept connections
db-1 | 2025-01-26 11:58:30.721 UTC [47] LOG: TimescaleDB background worker launcher connected to shared catalogs
db-1 | done
db-1 | server started
db-1 |
db-1 | /usr/local/bin/docker-entrypoint.sh: sourcing /docker-entrypoint-initdb.d/000_install_timescaledb.sh
db-1 | CREATE EXTENSION
db-1 | CREATE EXTENSION
db-1 | 2025-01-26 11:58:31.348 UTC [62] ERROR: TimescaleDB background worker connected to template database, exiting
db-1 |
db-1 | /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/001_timescaledb_tune.sh
db-1 | 2025-01-26 11:58:31.352 UTC [41] LOG: background worker "TimescaleDB Background Worker Scheduler" (PID 62) exited with exit code 1
db-1 | Using postgresql.conf at this path:
db-1 | /var/lib/postgresql/data/postgresql.conf
db-1 |
db-1 | Writing backup to:
db-1 | /tmp/timescaledb_tune.backup202501261158
db-1 |
db-1 | shared_buffers = 975406kB
db-1 | effective_cache_size = 2857MB
db-1 | maintenance_work_mem = 487703kB
db-1 | work_mem = 2438kB
db-1 | timescaledb.max_background_workers = 16
db-1 | max_worker_processes = 27
db-1 | max_parallel_workers_per_gather = 4
db-1 | max_parallel_workers = 8
db-1 | wal_buffers = 16MB
db-1 | min_wal_size = 512MB
db-1 | default_statistics_target = 100
db-1 | random_page_cost = 1.1
db-1 | checkpoint_completion_target = 0.9
db-1 | max_connections = 50
db-1 | max_locks_per_transaction = 128
db-1 | autovacuum_max_workers = 10
db-1 | autovacuum_naptime = 10
db-1 | Recommendations based on 3.72 GB of available memory and 8 CPUs for PostgreSQL 17
db-1 | default_toast_compression = lz4
db-1 | jit = off
db-1 | effective_io_concurrency = 256
db-1 | timescaledb.last_tuned = '2025-01-26T11:58:31Z'
db-1 | timescaledb.last_tuned_version = '0.18.0'
db-1 | Saving changes to: /var/lib/postgresql/data/postgresql.conf
db-1 |
db-1 | waiting for server to shut down...2025-01-26 11:58:31.374 UTC [41] LOG: received fast shutdown request
db-1 | .2025-01-26 11:58:31.377 UTC [41] LOG: aborting any active transactions
db-1 | 2025-01-26 11:58:31.377 UTC [47] FATAL: terminating background worker "TimescaleDB Background Worker Launcher" due to administrator command
db-1 | 2025-01-26 11:58:31.377 UTC [57] FATAL: terminating background worker "TimescaleDB Background Worker Scheduler" due to administrator command
db-1 | 2025-01-26 11:58:31.380 UTC [41] LOG: background worker "logical replication launcher" (PID 48) exited with exit code 1
db-1 | 2025-01-26 11:58:31.380 UTC [41] LOG: background worker "TimescaleDB Background Worker Launcher" (PID 47) exited with exit code 1
db-1 | 2025-01-26 11:58:31.381 UTC [41] LOG: background worker "TimescaleDB Background Worker Scheduler" (PID 57) exited with exit code 1
db-1 | 2025-01-26 11:58:31.381 UTC [42] LOG: shutting down
db-1 | 2025-01-26 11:58:31.385 UTC [42] LOG: checkpoint starting: shutdown immediate
db-1 | .2025-01-26 11:58:33.339 UTC [42] LOG: checkpoint complete: wrote 735 buffers (4.5%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.026 s, sync=1.905 s, total=1.958 s; sync files=412, longest=0.020 s, average=0.005 s; distance=4406 kB, estimate=4406 kB; lsn=0/1938570, redo lsn=0/1938570
db-1 | 2025-01-26 11:58:33.347 UTC [41] LOG: database system is shut down
db-1 | done
db-1 | server stopped
db-1 |
db-1 | PostgreSQL init process complete; ready for start up.
db-1 |
db-1 | 2025-01-26 11:58:33.432 UTC [1] LOG: starting PostgreSQL 17.2 on x86_64-pc-linux-musl, compiled by gcc (Alpine 14.2.0) 14.2.0, 64-bit
db-1 | 2025-01-26 11:58:33.432 UTC [1] LOG: listening on IPv4 address "0.0.0.0", port 5432
db-1 | 2025-01-26 11:58:33.432 UTC [1] LOG: listening on IPv6 address "::", port 5432
db-1 | 2025-01-26 11:58:33.458 UTC [1] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
db-1 | 2025-01-26 11:58:33.471 UTC [74] LOG: database system was shut down at 2025-01-26 11:58:33 UTC
db-1 | 2025-01-26 11:58:33.499 UTC [1] LOG: database system is ready to accept connections
db-1 | 2025-01-26 11:58:33.500 UTC [77] LOG: TimescaleDB background worker launcher connected to shared catalogsWe can see that this container just behaves like a normal PostgreSQL one. But we can also see that TimescaleDB is implemented and running.
Inside the database, we can see that the TimescaleDB extension is loaded:
$ psql
psql (17.2)
Type "help" for help.
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
-------------+---------+------------+---------------------------------------------------------------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
timescaledb | 2.18.0 | public | Enables scalable inserts and complex queries for time-series data (Community Edition)
(2 rows)Usage
But how to use my TimescaleDB?
When you look at the TImescaleDB documentation, everything's just like PostgreSQL. On top, you can use features like SkipScan, advanced analytic queries. Besides writing data into tables (like in standard PostgreSQL), you can use also hypertables.