TimescaleDB on AWS User Guide

Overview

This image runs TimescaleDB, the open source time-series SQL extension for PostgreSQL. TimescaleDB lets you write standard SQL against billions of rows and get millisecond response times. Hypertables automatically partition time-series data into chunks by time range. Continuous aggregates pre-compute common rollups so analytic queries stay fast regardless of data volume. Native columnar compression can reduce storage by up to 95 percent.

The image ships the Community edition of TimescaleDB (Apache 2.0 licence). The Timescale License (TSL) edition is not included, so there are no cloud-hosting restrictions.

PostgreSQL listens on port 5432. An nginx process listens on port 80 to serve the Marketplace HTTP health check.

The PostgreSQL superuser password is generated on the first boot of every deployed instance. No two instances launched from the same AMI share a password. The password is written to /root/timescaledb-credentials.txt with mode 0600, readable only by root.

Database files are kept on a dedicated EBS data volume mounted at /var/lib/postgresql, separate from the operating system disk, so the data tier can be resized independently.

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 inbound port 5432 from the trusted networks that host the applications which will connect to PostgreSQL

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, 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 and inbound port 5432 from your application layer. Do not open port 5432 to the public internet unless your application requires it — a security group on port 5432 provides the first layer of defence, and pg_hba.conf provides the second.

Select Launch instance. First boot initialisation takes approximately one minute 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. 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 ports 22 and 5432 as described above.

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> \
  --block-device-mappings '[{"DeviceName":"/dev/sda1","Ebs":{"VolumeSize":50,"VolumeType":"gp3"}},{"DeviceName":"/dev/sdb","Ebs":{"VolumeSize":30,"VolumeType":"gp3"}}]' \
  --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=timescaledb-01}]'

Retrieve the public IP address once the instance is running:

aws ec2 describe-instances \
  --instance-ids <instance-id> \
  --query "Reservations[].Instances[].PublicIpAddress" \
  --output text

Step 3: Connect and Retrieve the PostgreSQL Password

Connect over SSH with the key pair you selected. The SSH login user depends on the AMI variant:

AMI variant	SSH login user
TimescaleDB on Ubuntu 22.04	ec2-user

The first boot service runs before the SSH daemon becomes ready, so the credentials file is always in place when you first log in.

ssh ec2-user@<public-ip>
sudo cat /root/timescaledb-credentials.txt

The credentials file contains the instance IP, the PostgreSQL username (postgres), the generated password, the default database (tsdb), and a ready-to-run connect command.

Step 4: Verify the Server is Healthy

Check that PostgreSQL is running and the TimescaleDB extension is loaded:

sudo systemctl status postgresql --no-pager

Connect to the default database and list installed extensions:

PGPASSWORD='<password>' psql -U postgres -h localhost -d tsdb

Inside the psql session:

tsdb=# \dx
                                                List of installed extensions
    Name     | Version |   Schema   |                                      Description
-------------+---------+------------+---------------------------------------------------------------------------------------
 plpgsql     | 1.0     | pg_catalog | PL/pgSQL procedural language
 timescaledb | 2.27.1  | public     | Enables scalable inserts and complex queries for time-series data (Community Edition)
(2 rows)

Confirm the extension version directly:

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

Expected output:

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

Step 5: Create a Hypertable

A hypertable is a TimescaleDB table that automatically partitions data by time. All standard SQL operations work on hypertables exactly as on regular PostgreSQL tables.

Connect to the tsdb database and create a metrics table, then convert it to a hypertable:

PGPASSWORD='<password>' psql -U postgres -h localhost -d tsdb

tsdb=# CREATE TABLE metrics (
    time TIMESTAMPTZ NOT NULL,
    device TEXT,
    value DOUBLE PRECISION
);
CREATE TABLE

tsdb=# SELECT create_hypertable('metrics', by_range('time'));
 create_hypertable
-------------------
 (2,t)
(1 row)

Step 6: Insert and Query Time-Series Data

Insert sample sensor readings and run a time_bucket aggregate:

tsdb=# INSERT INTO metrics VALUES
    (NOW(), 'sensor-1', 21.5),
    (NOW()-INTERVAL '1h', 'sensor-1', 20.3),
    (NOW()-INTERVAL '2h', 'sensor-2', 22.1);
INSERT 0 3

tsdb=# SELECT time_bucket('1 hour', time) AS hour, device, AVG(value)
       FROM metrics GROUP BY hour, device ORDER BY hour DESC;
          hour          |  device  |  avg
------------------------+----------+------
 2026-05-27 22:00:00+00 | sensor-1 | 21.5
 2026-05-27 21:00:00+00 | sensor-1 | 20.3
 2026-05-27 20:00:00+00 | sensor-2 | 22.1
(3 rows)

Step 7: Inspect Chunks

TimescaleDB partitions the hypertable into time-range chunks. List them with show_chunks:

tsdb=# SELECT show_chunks('metrics');
         show_chunks
------------------------------
 _timescaledb_internal._hyper_2_1_chunk
(1 row)

Step 8: Enable Columnar Compression

Compression can reduce storage by 90 percent or more for time-series workloads. Set a compression policy to compress chunks older than 7 days:

tsdb=# ALTER TABLE metrics SET (
    timescaledb.compress,
    timescaledb.compress_orderby = 'time DESC',
    timescaledb.compress_segmentby = 'device'
);
ALTER TABLE

tsdb=# SELECT add_compression_policy('metrics', INTERVAL '7 days');
 add_compression_policy
------------------------
                      1
(1 row)

Step 9: Create a Continuous Aggregate

Continuous aggregates pre-compute rollups and refresh automatically so analytic queries remain fast:

tsdb=# CREATE MATERIALIZED VIEW metrics_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', time) AS hour,
       device,
       AVG(value) AS avg_value,
       MAX(value) AS max_value,
       MIN(value) AS min_value,
       COUNT(*) AS sample_count
FROM metrics
GROUP BY hour, device
WITH NO DATA;
CREATE MATERIALIZED VIEW

tsdb=# SELECT add_continuous_aggregate_policy('metrics_hourly',
    start_offset => INTERVAL '3 days',
    end_offset   => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour');
 add_continuous_aggregate_policy
----------------------------------
                                2
(1 row)

Query the materialized view just like any PostgreSQL view:

tsdb=# SELECT * FROM metrics_hourly ORDER BY hour DESC LIMIT 5;

Step 10: Remote Connections

By default the PostgreSQL listener is bound to all addresses (listen_addresses = '*'). To connect from a remote host, ensure port 5432 is open in the security group and use:

psql -h <public-ip> -U postgres -d tsdb

Restrict the security group ingress rule to your application subnet CIDR rather than 0.0.0.0/0 in production environments.

Step 11: Rotate the PostgreSQL Password

To change the password of the postgres superuser:

sudo -u postgres psql -c "ALTER USER postgres PASSWORD '<new-password>';"

Update /root/timescaledb-credentials.txt to match so the credentials file stays accurate:

POSTGRES_PASSWORD=<new-password>

Step 12: Backup and Restore

Dump a specific database with pg_dump:

PGPASSWORD='<password>' pg_dump -U postgres -h localhost -d tsdb -Fc -f /tmp/tsdb.dump

Restore with pg_restore:

PGPASSWORD='<password>' pg_restore -U postgres -h localhost -d tsdb /tmp/tsdb.dump

For continuous backups and point-in-time recovery, configure PostgreSQL WAL archiving to S3 using pgBackRest or wal-g.

Step 13: Monitor TimescaleDB

View TimescaleDB job statistics:

tsdb=# SELECT * FROM timescaledb_information.jobs;
tsdb=# SELECT * FROM timescaledb_information.job_stats;

Check hypertable sizes:

tsdb=# SELECT hypertable_name, pg_size_pretty(hypertable_size(format('%I', hypertable_name)::regclass))
       FROM timescaledb_information.hypertables;

Security Notes

• Restrict port 5432 in your security group to known application subnet CIDRs.
• The /root/timescaledb-credentials.txt file is mode 0600 and readable only by root. Retrieve it over SSH and store it in a secrets manager such as AWS Secrets Manager or HashiCorp Vault.
• Consider creating application-specific PostgreSQL users with minimum required privileges rather than using the postgres superuser for application connections.

---

Screenshots

psql version and the installed timescaledb extension confirmed via the \dx meta-command inside the tsdb database.

systemctl status showing PostgreSQL active and running, with the timescaledb extension version confirmed via pg_extension.

Creating a hypertable from a metrics table and running a time_bucket aggregate query to group sensor readings by hour.

---

Support

This image is maintained by cloudimg. For assistance with deployment, hypertable design, continuous aggregates, compression policies, replication or upgrades, contact support at support@cloudimg.co.uk.