TimescaleDB

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 SecureAnyCloud listing and choose Select, then choose 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, then 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 rotates the database passwords and re-enables the TimescaleDB extension. It takes a minute or two after the instance state becomes Running and the status checks pass.

Run the following command to launch an instance from the SecureAnyCloud TimescaleDB Marketplace AMI into an existing subnet and security group. Replace <ami-id> with the AMI ID shown on the Marketplace listing and <key-name> with your EC2 key pair name. Replace <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}]'

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

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.

For the TimescaleDB Community 2 on Ubuntu 24.04 variant, the SSH login user is ubuntu.

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

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

The first-boot service generates fresh postgres and SecureAnyCloud passwords for this instance. It writes them, with sample connection commands, to /root/timescaledb-credentials.txt, which only the root user can read. Display the file from your SSH session:

sudo cat /root/timescaledb-credentials.txt

The file looks like this, with passwords unique to 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=sac default_role=sac postgres.user=postgres postgres.password=<TIMESCALEDB_POSTGRES_PASSWORD> sac.user=sac sac.password=<TIMESCALEDB_APP_PASSWORD> sample_connect_app=PGPASSWORD='<TIMESCALEDB_APP_PASSWORD>' psql -h 127.0.0.1 -U sac -d sac

Reserve the postgres superuser for database administration. For normal application traffic, use the SecureAnyCloud role against the SecureAnyCloud database.

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

systemctl is-active postgresql

The command prints active. Next, confirm that PostgreSQL is bound on its default port:

ss -tln | grep 5432

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

Finally, check the nginx health-check page on port 80. It should answer 200:

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

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

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

Confirm that 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 sac -d sac \
  -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)

A hypertable is a PostgreSQL table that TimescaleDB partitions automatically across time. Run the following block inside psql as the SecureAnyCloud role. It 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. Later inserts land in the chunk that matches their time value.

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 sample count and the average temperature for that bucket. The time_bucket function works on any TimescaleDB hypertable and on any standard PostgreSQL table with a TIMESTAMPTZ column.

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

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

List the active TimescaleDB jobs to confirm that 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').

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

findmnt /var/lib/postgresql

The output shows that /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 snapshot it with Amazon EBS on its own schedule and grow it independently of the root volume. Check the available space at any time with:

df -h /var/lib/postgresql

systemd manages PostgreSQL, and 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 you diagnose a startup or runtime problem:

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

When you change instance type, re-run the memory and concurrency tuning by invoking timescaledb-tune, then restart the service:

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

The data to back up on a PostgreSQL instance with TimescaleDB is the entire SecureAnyCloud 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/sac-$(date +%F).dump sac

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

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

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

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, either 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 SecureAnyCloud role and password from /root/timescaledb-credentials.txt.