How to Migrate TimescaleDB to AWS EC2 (Step-by-Step Guide)

“What I cannot create, I do not understand.” — Feynman

I had a perfectly good managed database. Timescale Cloud, 60,000 stocks, daily bars going back to 2010, plus a pile of forecast tables. About 32 GB spread across six hypertables. It just worked. Then I looked at my AWS credit balance, saw a number with a lot of zeros, and decided to move the whole thing onto a bare EC2 instance I run myself.

This is the guide I wish I’d had. If you want to migrate TimescaleDB to AWS EC2, you can follow it top to bottom. I’ve kept every command, and more usefully, every place it blew up in my face, because the failures are where the real lessons hide.

Photo by Adi Goldstein on Unsplash

Fair warning before you start. Self-hosting means backups, patches, crashes, and security are now your job. Managed Timescale Cloud does all of that for you. I moved off it to burn credits that were going to expire anyway, which is a weak reason, and I’ll say so again at the end. If uptime matters more than saving money, staying managed is the smarter call. Still here? Good. Let’s build it.

What you’ll need

• An AWS account.
• Your existing Timescale Cloud (or any Postgres/TimescaleDB) connection URL, with the password.
• Basic comfort with SSH and the Linux command line.

The shape of the data

Six hypertables. One big one, stock_prices, and five forecast tables at different horizons (weekly through multi-yearly). Before touching anything I pulled the real sizes, because in TimescaleDB the parent table always lies to you.

Run a plain \dt+ and every table reports 8192 bytes, which looks like they're empty. They're not. The rows live in chunks under _timescaledb_internal, so the parent looks tiny while thousands of chunks sit underneath. To see the truth, ask the hypertable directly:

SELECT hypertable_name,
       hypertable_size(format('%I.%I', hypertable_schema, hypertable_name)::regclass)
FROM timescaledb_information.hypertables;

My honest sizes:

stock_prices                 ~22.3 GB
stock_forecast_monthly        ~3.6 GB
stock_forecast_multi_yearly   ~1.8 GB
stock_forecast_quarterly      ~0.8 GB
stock_forecast_weekly         ~0.6 GB
stock_forecast_half_yearly    ~0.3 GB

Call it 32 GB. Daily data for 60k stocks is genuinely small (a few million new rows a year), so this was never a performance project. It was a “can I move it without corrupting it” project. Those are different, and the second one is where the pain lives.

Step 1: Launch the EC2 instance

I picked a m7i.large, not the giant r5.4xlarge the tutorials wave around. Daily data with light queries does not need 16 vCPUs sitting idle and burning credits. I went non-burstable on purpose so the CPU stays steady instead of throttling on a t3 box.

For the OS, use an LTS image. I used Ubuntu 26.04 because it was the newest thing on the list, and I’ll be honest, that was a mistake I got away with. A brand new OS means packages may not be built for it yet. For a production database, pick a tested LTS (24.04 was the safe choice at the time) so you’re not gambling on package availability.

Storage. I gave it 100 GB gp3, roughly 3x my data. That leaves headroom, and TimescaleDB compresses old chunks over time so you’ll likely use less. The default 8 GB is a trap for anything real.

Security group. Here is my worst mistake, called out loud so you don’t copy it. I opened both SSH (22) and PostgreSQL (5432) to 0.0.0.0/0, the entire internet, "just for now." That is exactly how databases get found and hacked. Do this instead:

• SSH (port 22): source set to My IP.
• PostgreSQL (port 5432): source set to your app server’s IP, not the world.

Key pair. Create one, download the .pem, and keep it safe. On your machine, tighten its permissions or SSH will refuse it:

bash

chmod 400 your-key.pem

Step 2: Give it a static IP

Attach an Elastic IP to the instance. I learned this the hard way. Without a static IP, the box’s public address changes on every stop/start, and your connection string rots. I watched my IP jump twice mid-setup and spent ten minutes wondering why SSH kept timing out. Allocate an Elastic IP, associate it with the instance, and use that address from now on.

SSH in:

ssh -i "your-key.pem" ubuntu@<elastic-ip>

Step 3: Install TimescaleDB and PostgreSQL

Add the PostgreSQL (PGDG) repo and the TimescaleDB repo, refresh, and install. This is the standard sequence:

sudo apt install gnupg postgresql-common apt-transport-https lsb-release wget
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
echo "deb https://packagecloud.io/timescale/timescaledb/ubuntu/ $(lsb_release -c -s) main" \
  | sudo tee /etc/apt/sources.list.d/timescaledb.list
wget --quiet -O - https://packagecloud.io/timescale/timescaledb/gpgkey \
  | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/timescaledb.gpg
sudo apt update

A note on the codename. lsb_release -c -s prints your Ubuntu codename. On 26.04 that's "resolute." I assumed the repo wouldn't have a build for something that new and braced to force an older codename. Turned out it did have one, so this just worked. Check your own codename resolves before you fight it.

Then install. Match the PostgreSQL major version you want. I used 17:

sudo apt install timescaledb-2-postgresql-17 postgresql-client

This pulled in TimescaleDB 2.28.2 on Postgres 17. Hold onto that version number. It matters a lot in Step 6.

Step 4: Tune and configure PostgreSQL

Let TimescaleDB size the config to your box:

sudo timescaledb-tune --quiet --yes --conf-path /etc/postgresql/17/main/postgresql.conf

Watch this gotcha. The tuner guessed Postgres 18 by default and went looking for a config file that didn’t exist, because I’d installed the 17 build. Passing --conf-path with the real 17 path fixed it. It set shared_preload_libraries = 'timescaledb' plus memory and worker tunings sized to the machine (mine reported 7.6 GB RAM and 2 CPUs).

Now open the config to allow outside connections:

sudo nano /etc/postgresql/17/main/postgresql.conf

Set (and uncomment) this line so Postgres listens beyond localhost:

listen_addresses = '*'

Then edit the host-based auth file so remote logins are allowed:

sudo nano /etc/postgresql/17/main/pg_hba.conf

Add this at the bottom:

host    all    all    0.0.0.0/0    scram-sha-256

That rule technically lets any IP try to log in, which is why your firewall (Step 1) and a strong password (next) have to carry the weight. Restart to apply everything:

sudo systemctl restart postgresql

Confirm TimescaleDB actually loaded:

sudo -u postgres psql -c "SHOW shared_preload_libraries;"

You want to see timescaledb.

Step 5: Create the database and a password

Set a strong password for the postgres user, or nothing outside the box can log in:

sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'your-strong-password';"

Create the target database. Name it to match your old one so your app’s URL changes as little as possible. Mine was tsdb:

sudo -u postgres psql -c "CREATE DATABASE tsdb;"

Enable the extension inside it:

psql "postgresql://postgres:your-password@localhost:5432/tsdb" \
  -c "CREATE EXTENSION IF NOT EXISTS timescaledb;"

Step 6: The migration (four steps, in theory)

TimescaleDB has an official restore path, and it is genuinely just four moves:

1. pg_dump the old database in custom format.
2. On the new box, call timescaledb_pre_restore() to put the extension to sleep.
3. pg_restore the dump.
4. Call timescaledb_post_restore() to wake it back up.

The pre/post functions exist because TimescaleDB’s own catalog machinery fights a normal restore. You pause it, load raw, then unpause. Clean, in theory. Here is everything that happened between me and those four steps.

Set your connection URLs

I ran the whole migration from the server, pulling straight from the cloud so there’s no extra network hop. First, set both URLs as variables. Keep the password out of shell history where you can, but at minimum set them like this:

export OLD_URL="postgres://tsdbadmin:PASSWORD@<cloud-host>:31449/tsdb?sslmode=require"
export NEW_URL="postgresql://postgres:PASSWORD@localhost:5432/tsdb"

Test the old one before trusting it:

psql "$OLD_URL" -c "\dt"

Run the dump inside screen

A dump of 32 GB takes a while, and if your SSH drops mid-way, the dump dies with it. Use screen so the work survives a disconnect:

screen -S migrate

Inside the session, dump in custom format:

pg_dump -Fc -f old_db.bak "$OLD_URL"

Detach with Ctrl+A then D. Come back later with screen -r migrate. When it's done, check the file is real:

ls -lh old_db.bak

My 32 GB of data compressed to a 2.1 GB .bak. That ratio felt right and was the first sign things were working.

Bite #1: the 2.1 GB dump that was 0 bytes

The first time, I set $OLD_URL inside one screen session, then ran the dump in a different shell where the variable did not exist. pg_dump happily dumped nothing and left me a 0-byte file with no error loud enough to notice.

The red herring was a warning about “circular foreign-key constraints on continuous_agg.” I thought that was fatal. It isn’t. That warning is normal and harmless. The real bug was the empty variable.

Lesson: run echo $OLD_URL in every new shell before you trust it, and run the dump inside the same screen where the variable lives. Variables do not follow you across sessions.

The four-step restore

Once the dump was real, prep the new database:

psql "$NEW_URL" -c "SELECT timescaledb_pre_restore();"

It returns t. Then restore:

pg_restore -d "$NEW_URL" --no-owner --no-privileges old_db.bak

Then wake TimescaleDB back up:

psql "$NEW_URL" -c "SELECT timescaledb_post_restore();"

That’s the happy path. Mine did not take the happy path on the first try.

Bite #2: “how different can a minor version be”

My old server ran TimescaleDB 2.27.2. My fresh install was 2.28.2. I looked at that gap, shrugged, and said “it’s one minor version, how different can it be.” It was different enough. The restore died like this:

pg_restore: error: COPY failed for table "dimension_slice":
  null value in column "chunk_id" violates not-null constraint
pg_restore: error: COPY failed for table "chunk_constraint":
  cannot copy to view "chunk_constraint"

Between 2.27 and 2.28, TimescaleDB reworked internal catalog objects. chunk_constraint went from a table you can COPY into to a view you can't. My 2.27-shaped dump was pouring old-shaped catalog rows into new-shaped plumbing, and the plumbing said no. None of this touched my stock data. It was purely internal bookkeeping. But a failed restore leaves a half-built database, so the fix wasn't "retry," it was "start clean and match versions."

Wipe the mess and recreate:

sudo -u postgres psql -c "DROP DATABASE tsdb;"
sudo -u postgres psql -c "CREATE DATABASE tsdb;"

Find the exact 2.27.2 package available to you:

apt-cache madison timescaledb-2-postgresql-17

Downgrade the extension to match the source, then restart:

sudo apt install timescaledb-2-postgresql-17=2.27.2~ubuntu26.04-1710
sudo systemctl restart postgresql

The safe rule I should have started with: match the source version exactly for the restore, then upgrade afterward if you want the newer one. Restoring into a newer version sounds safe, but “usually fine” is doing heavy lifting when the catalog layout changed underneath you.

Bite #3: the downgrade left a ghost

After downgrading, creating the extension failed with:

ERROR: extension "timescaledb" has no installation script
       nor update path for version "2.28.2"

The database still had 2.28.2 wired in as its default target even though the files on disk were now 2.27.2. A file reinstall didn’t shake it loose. The fix was to stop letting it pick a default and name the version outright:

CREATE EXTENSION timescaledb VERSION '2.27.2';

That worked at once. With versions finally matched, the four-step dance ran start to finish with no drama, which after all of the above felt almost anticlimactic.

Step 7: Verify (do not skip this)

All six hypertables restored. The sizes came out a bit smaller than the source:

stock_prices    old ~22.3 GB  ->  new ~17.8 GB

That looked alarming for about ten seconds until it made sense. A fresh restore packs chunks densely, with no bloat from a live table’s churn and no dead tuples waiting on vacuum. Smaller-after-restore is the normal, healthy outcome, not missing data.

But byte counts are not proof. Row counts are. Run this on both sides and confirm they match before you declare victory:

psql "$OLD_URL" -c "SELECT count(*) FROM stock_prices;"
psql "$NEW_URL" -c "SELECT count(*) FROM stock_prices;"

I’ll be honest: this is the step I was most tempted to skip, and it’s the one step you actually can’t. If the counts don’t match, your migration isn’t done, no matter how good the sizes look.

Step 8: Switch the connection URL

This is the whole point of the exercise, and it’s the easy part. Your old app URL looked like:

postgres://tsdbadmin:****@<cloud-host>:31449/tsdb?sslmode=require

The new one points at your box:

postgres://postgres:****@<elastic-ip>:5432/tsdb

Same URL shape. Four fields change: host, port, user, and database name. One line in your app config. Swap it, restart your app, and you’re running on EC2.

Reflections

What I’d do differently, in rough order of how much grief each would have saved:

• Match the TimescaleDB version from the very first install. This was 90% of the total pain, and it was self-inflicted by a shrug.
• Use an LTS OS. 26.04 worked out, but I gambled on package availability for no reason on a production box.
• Never open the firewall to the world, not even “for now.” Lock it to your IP at creation.
• Echo every env var in every new shell. The 0-byte dump cost me a full round trip because I trusted a variable that had quietly evaporated across a screen boundary.

And the uncomfortable meta-question: was moving off managed even worth it? I traded a database that handled its own backups, failover, and patching for one where all of that is now a task on my list. I saved money that was going to expire anyway. If I’m honest, the strongest thing this migration produced was a better understanding of how TimescaleDB’s catalog actually works, which I only earned because I broke it three times. What I cannot create, apparently, I do not understand, and what I cannot restore I understand even better.

If you’re doing this yourself: match versions, verify row counts, lock your firewall, and treat every “just for now” shortcut as a future incident report. Then run pg_restore and watch six hypertables come back to life, which, I'll admit, is a pretty satisfying thing to watch after the third try.