TimescaleDB Community on AWS User Guide

Overview

This image runs TimescaleDB Community, the open source PostgreSQL extension purpose built for time series data. PostgreSQL 16 is installed from the official PostgreSQL Global Development Group (PGDG) repository and TimescaleDB 2 Community Edition is installed as a shared_preload_libraries extension from the official Timescale package repository. TimescaleDB Community is distributed under the Apache 2.0 licence; this image does not ship the Timescale License (TSL) edition.

The image ships with scram-sha-256 password authentication switched on for both local and remote connections. A dedicated cloudimg application database is created at build time with the TimescaleDB extension already enabled, so hypertables, continuous aggregates and retention policies are available immediately. On the first boot of your instance a one shot service rotates both the postgres superuser password and the cloudimg application role password to fresh random values, unique to that instance, and writes them to /root/timescaledb-credentials.txt, a file that only the root user can read. No shared or default database credentials ship in the image.

PostgreSQL data, including hypertable chunks, lives under /var/lib/postgresql, which is mounted as a dedicated EBS volume separate from the operating system disk. Keeping database files on their own volume means storage can be grown, snapshotted and backed up independently of the root disk.

A small nginx page is served on port 80 so the AWS Marketplace HTTP health check can confirm the instance is reachable. The page does not proxy to PostgreSQL — the database listens directly on port 5432 and is reached with psql or any standard PostgreSQL driver. This is a headless image; you administer it over SSH with the psql shell.

Prerequisites

Before you deploy this image you need:

• An Amazon Web Services account where you can launch EC2 instances
• IAM permissions to launch instances, create security groups, and subscribe to AWS Marketplace products
• An EC2 key pair in the target Region for SSH access to the instance
• A VPC and subnet in the target Region, with a security group allowing inbound port 22 from your management network and, if you intend to connect remotely, port 5432 from your application tier
• The AWS CLI (version 2) installed locally if you plan to deploy from the command line

Recommended instance type: m5.large (2 vCPU, 8 GB RAM) or larger. PostgreSQL with TimescaleDB benefits from generous RAM for the shared buffer cache and from fast attached storage; the included timescaledb-tune utility sizes memory settings for the live host the first time it is run.

Step 1: Launch the Instance from the AWS Marketplace

Sign in to the AWS Management Console, open the EC2 service, and select Launch instance. Under Application and OS Images choose AWS Marketplace AMIs and search for TimescaleDB. Select the cloudimg listing and choose Select, then Continue on the subscription summary.

Pick an instance type of m5.large or larger. Choose your EC2 key pair under Key pair (login). Under Network settings select your VPC and subnet, and either create or select a security group that allows inbound port 22 from your management network. Add port 5432 if you plan to connect from outside the instance. Leave the root volume at the default size or larger; the PostgreSQL data volume is attached automatically from the image.

Select Launch instance. First boot initialisation, which rotates the database passwords and re-enables the TimescaleDB extension, takes a minute or two after the instance state becomes Running and the status checks pass.

Step 2: Launch the Instance from the AWS CLI

The following block launches an instance from the cloudimg TimescaleDB Marketplace AMI into an existing subnet and security group. Replace <ami-id> with the AMI ID shown on the Marketplace listing, <key-name> with your EC2 key pair name, <subnet-id> with your subnet ID, and <security-group-id> with a security group that opens inbound port 22.

aws ec2 run-instances \
  --image-id <ami-id> \
  --instance-type m5.large \
  --key-name <key-name> \
  --subnet-id <subnet-id> \
  --security-group-ids <security-group-id> \
  --metadata-options HttpTokens=required \
  --block-device-mappings '[{"DeviceName":"/dev/sda1","Ebs":{"VolumeSize":30,"VolumeType":"gp3"}}]' \
  --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=timescaledb-01}]'

The command prints a JSON document on success. Note the instance ID, then retrieve its public address once it is running with aws ec2 describe-instances --instance-ids <instance-id> --query "Reservations[].Instances[].PublicIpAddress" --output text.

Step 3: Connect over SSH

Connect over SSH with the key pair you selected and the public IP address from step 2. The SSH login user depends on the operating system of the AMI variant you launched:

AMI variant	SSH login user
TimescaleDB Community 2 on Ubuntu 24.04	ubuntu

ssh <login-user>@<public-ip>

Wait until the instance has passed both EC2 status checks before connecting. The first boot service runs before the SSH daemon is fully usable, so the database is initialised by the time you can log in.

Step 4: Retrieve the Generated Passwords

The first boot service generates fresh postgres and cloudimg passwords for this instance and writes them, with sample connection commands, to /root/timescaledb-credentials.txt. The file is readable only by the root user. Display it from your SSH session:

sudo cat /root/timescaledb-credentials.txt

The file looks like this, with unique passwords on your instance:

# TimescaleDB Community on PostgreSQL 16 — generated on first boot by
# timescaledb-firstboot.service. These passwords are unique to this instance.

port=5432
default_database=cloudimg
default_role=cloudimg

postgres.user=postgres
postgres.password=<TIMESCALEDB_POSTGRES_PASSWORD>

cloudimg.user=cloudimg
cloudimg.password=<TIMESCALEDB_APP_PASSWORD>

sample_connect_app=PGPASSWORD='<TIMESCALEDB_APP_PASSWORD>' psql -h 127.0.0.1 -U cloudimg -d cloudimg

The postgres superuser is reserved for database administration; for normal application traffic use the cloudimg role against the cloudimg database.

Step 5: Confirm the Service and the Listener

PostgreSQL runs under systemd as the postgresql service and starts automatically on boot. Confirm it is active:

systemctl is-active postgresql

The command prints active. Confirm PostgreSQL is bound on its default port:

ss -tln | grep 5432

You should see a listening socket on every interface (*:5432).

The nginx health-check page on port 80 should answer 200:

curl -sI http://127.0.0.1/ | head -1

Step 6: Connect with psql and Confirm the TimescaleDB Extension

Open a local interactive psql session as the cloudimg role against the cloudimg database. Replace <password> with the value from /root/timescaledb-credentials.txt:

PGPASSWORD='<password>' psql -h 127.0.0.1 -U cloudimg -d cloudimg

Confirm the TimescaleDB extension is enabled in this database. The screenshot below shows a complete session that opens psql, prints the server version and the TimescaleDB extension version, and exits.

To verify the extension non interactively from your SSH session, query pg_extension:

PGPASSWORD='<password>' psql -h 127.0.0.1 -U cloudimg -d cloudimg \
  -c "SELECT extname, extversion FROM pg_extension WHERE extname = 'timescaledb';"

The query returns the extension name and version:

  extname    | extversion
-------------+------------
 timescaledb | 2.18.2
(1 row)

Step 7: Create a Hypertable

A hypertable is a PostgreSQL table that TimescaleDB partitions automatically across time. The following block, run inside psql as the cloudimg role, creates a metrics table, converts it into a hypertable by time, inserts a handful of rows and counts them. It mirrors the session in the screenshot above and produces the screenshot below.

CREATE TABLE metrics (
  time        TIMESTAMPTZ NOT NULL,
  sensor_id   INT          NOT NULL,
  temperature DOUBLE PRECISION
);

SELECT create_hypertable('metrics', by_range('time'));

INSERT INTO metrics VALUES
  (NOW(),                    1, 23.5),
  (NOW() - INTERVAL '1 hour', 1, 22.8),
  (NOW() - INTERVAL '2 hours', 2, 24.1);

SELECT COUNT(*) FROM metrics;

create_hypertable returns the hypertable identifier and creates an initial chunk per time range. Subsequent inserts continue to land in the appropriate chunk for their time value.

Step 8: Run a Time-Series Query

The signature TimescaleDB function is time_bucket. It groups time-stamped rows into fixed intervals so you can roll metrics up by minute, hour, day or any interval. Run the following query against the metrics table from the previous step.

SELECT
  time_bucket('1 hour', time)         AS bucket,
  COUNT(*)                            AS samples,
  AVG(temperature)::numeric(5,2)      AS avg_temp
FROM metrics
GROUP BY bucket
ORDER BY bucket;

The query returns one row per hour bucket, with the count of samples and the average temperature in that bucket. The time_bucket function operates on any TimescaleDB hypertable or any standard PostgreSQL table with a TIMESTAMPTZ column.

Step 9: Add a Retention Policy

Time-series data ages out quickly. TimescaleDB's add_retention_policy schedules a background job that drops hypertable chunks older than a chosen interval, freeing storage automatically. Add a retention policy that drops chunks older than thirty days:

SELECT add_retention_policy('metrics', INTERVAL '30 days');

List active TimescaleDB jobs to confirm the policy is registered:

SELECT job_id, application_name, schedule_interval, hypertable_name
FROM timescaledb_information.jobs
WHERE proc_name = 'policy_retention';

The query returns a row for the retention policy on the metrics hypertable. Type \q to leave the psql shell.

To drop the retention policy later, call remove_retention_policy('metrics'). To inspect the chunks the policy will act on, query show_chunks('metrics', older_than => INTERVAL '30 days').

Step 10: The PostgreSQL Data Volume

PostgreSQL data, including TimescaleDB chunks, is stored under /var/lib/postgresql, which is a dedicated EBS volume separate from the operating system disk. Confirm the mount:

findmnt /var/lib/postgresql

The output shows /var/lib/postgresql is its own ext4 filesystem on a separate device:

TARGET              SOURCE       FSTYPE OPTIONS
/var/lib/postgresql /dev/nvme1n1 ext4   rw,relatime

Because the data directory is on its own volume you can take an Amazon EBS snapshot of it on its own schedule, and you can grow it independently of the root volume. Check the available space at any time with:

df -h /var/lib/postgresql

Step 11: Managing the PostgreSQL Service

PostgreSQL is managed through systemd. The service starts automatically on boot.

Check the service status:

systemctl status postgresql --no-pager

Stop, start and restart the service when needed:

sudo systemctl stop postgresql
sudo systemctl start postgresql
sudo systemctl restart postgresql

PostgreSQL log files live under /var/log/postgresql. The main cluster log is /var/log/postgresql/postgresql-16-main.log; review it first when diagnosing a startup or runtime problem:

sudo tail -f /var/log/postgresql/postgresql-16-main.log

To re-run the memory and concurrency tuning when you change instance type, invoke timescaledb-tune and restart the service:

sudo timescaledb-tune --quiet --yes
sudo systemctl restart postgresql

Step 12: Backups

The data to back up on a PostgreSQL instance with TimescaleDB is the entire cloudimg database. The pg_dump utility writes a logical backup that any PostgreSQL server with the TimescaleDB extension installed can restore. From an SSH session, take a compressed dump:

PGPASSWORD='<postgres-password>' pg_dump -h 127.0.0.1 -U postgres -F c -f /var/backups/cloudimg-$(date +%F).dump cloudimg

Archive the dump file to durable storage, for example an Amazon S3 bucket. Restore on a fresh instance with:

PGPASSWORD='<postgres-password>' pg_restore -h 127.0.0.1 -U postgres -d cloudimg /var/backups/cloudimg-<date>.dump

Because /var/lib/postgresql is a dedicated EBS volume, you can also take an EBS snapshot of the volume itself for a point-in-time copy of the whole cluster after stopping the PostgreSQL service.

Step 13: Connect from a Remote Application

By default the security group on the instance allows inbound traffic on port 22 for SSH and port 80 for the Marketplace health check. To connect from a remote application, add an inbound rule for TCP port 5432 from your application tier in the AWS console or with:

aws ec2 authorize-security-group-ingress \
  --group-id <security-group-id> \
  --protocol tcp --port 5432 \
  --cidr <application-cidr>

Replace <application-cidr> with the CIDR of the network that will connect to the database. Avoid 0.0.0.0/0 in production. The pg_hba.conf on the image accepts remote scram-sha-256 authenticated connections; use the cloudimg role and password from /root/timescaledb-credentials.txt.

---

Screenshots

Creating a TimescaleDB hypertable from a standard PostgreSQL table with create_hypertable().

Running a time_bucket() aggregate over a TimescaleDB hypertable for time-series rollups.

Adding a TimescaleDB add_retention_policy() to drop hypertable chunks older than a given interval.

---

Support

cloudimg provides 24/7/365 expert technical support for this image. Guaranteed response within 24 hours, one hour average for critical issues. Contact support@cloudimg.co.uk.

For general TimescaleDB and PostgreSQL administration questions consult the official documentation at https://docs.timescale.com/ and https://www.postgresql.org/docs/16/.