Upgrading a live TimescaleDB from Postgres 15 to 18 without taking reads offline

Every measurement a device sends to Datacake ends up in TimescaleDB, hosted on Tiger Cloud (the company formerly known as Timescale). Dashboards read from it, the rules engine evaluates history against it, exports and reports stream out of it. It is the one database we cannot take away from customers for twenty minutes and call it maintenance.

In September we received the final reminder that Tiger Cloud would upgrade every remaining PostgreSQL 15 service automatically on or around the 21st. TimescaleDB 2.29 had already dropped PG15, so staying meant no more extension updates. Fair enough; PG15 had a good run. The problem was how the upgrade works.

What the vendor offers

Tiger Cloud performs major PostgreSQL upgrades in place. The documentation says the service is unavailable for “up to 20 minutes” and “can take longer if you have a large or complex service”. Ours is large. There is no upgrade-via-replica path, HA replicas have to be deleted before the upgrade, and if you miss the deadline the upgrade happens in your maintenance window whether you are watching or not.

We looked at the migration tooling too. Livesync, Tiger’s logical-replication migration tool, is documented for external sources only, not for moving between two Tiger Cloud services, and it copies compressed chunks uncompressed. Hand-rolling logical replication against hypertables means dealing with chunks as individual tables. Neither felt like something to bet a production cutover on.

The candid summary: a managed time-series database, in 2026, with no zero-downtime major upgrade path. Ingestion we could have buffered; our insert workers already run on their own Celery queue and would simply have queued up. But reads would have gone dark, and history-based rule conditions would have failed closed for the duration. We decided to build our way around it.

The idea: fork, upgrade the fork, mirror, backfill, swap

Tiger Cloud can fork a service at a point in time. A fork is a full copy with its own connection string, and you can upgrade the fork to PG18 while production keeps running on PG15. That gives you a PG18 database that is correct up to the fork point and frozen after it.

From there the plan writes itself:

1. Fork production and upgrade the fork.
2. Make the API write every change to both instances.
3. Copy whatever production received between the fork and the start of mirroring.
4. Verify, then swap which instance is primary.
5. Keep mirroring in the other direction for a few days as a rollback path, then retire the old instance.

Steps 2 and 3 are the interesting ones.

One seam for every write

Our first instinct was a sidecar that replays writes from a Redis stream we already publish for MQTT fan-out. It would have needed no application changes. It also would not have worked: that stream carries a topic and a stringified value, not the timestamp, the target table or the field type, and it knows nothing about deletes. A replication log has to be built as one.

So we did it inside the application, and we did it at a single point. Every statement that changes time-series data, whether the hot-path batch insert, device and field deletions, timeframe deletions or the INSERT ... SELECT that moves a device between plan tables, now goes through one function:

def timescale_write(sql: str, params: Sequence, many: bool = False) -> None:
    if _shadow_only.get():
        _execute(SHADOW_ALIAS, sql, params, many)
        return

    _execute(PRIMARY_ALIAS, sql, params, many)
    _mirror(sql, params, many)   # never raises

The shadow leg is best-effort by design. If it fails, we log once and skip the shadow for a 30 second cooldown, so an unreachable second database costs one connection timeout per worker per half minute instead of one per task. Gaps are repaired later, not retried inline. A test greps the codebase and fails if any module outside this helper opens a cursor on the primary connection, which is how we make sure the sixth write path, whenever it appears, cannot forget to mirror.

With no shadow connection configured, the whole thing is a no-op. We merged and deployed it days before we needed it.

Backfilling the gap without an index

Between the fork point and the moment the last old pod stopped, production received roughly a day of writes that the fork never saw. We needed to copy exactly those rows and nothing else.

Our hypertables have a created timestamptz DEFAULT now() column next to the measurement’s own time. That distinction matters: devices can and do report historical timestamps, so time is not monotonic, but created is the insert time and identifies rows that arrived after the fork regardless of what they claim to measure.

The catch is that created has no index and is not the partitioning column. Our first attempt at a plain SELECT max(created) to pin the fork point scanned an entire hypertable and had to be cancelled. Every backfill statement therefore bounds time as well, which lets TimescaleDB skip chunks:

WHERE created > :t0 AND created <= :t1 AND time > :t0 - interval '7 days'

Live data lands in the newest chunks and copies in minutes. Rows imported during the gap with a time older than a week are a separate, explicitly requested pass that scans whole tables and runs off-peak. The two passes partition the window exactly.

The copy itself is COPY ... TO STDOUT on the primary streamed straight into COPY ... FROM STDIN on the shadow, in binary format, keeping the original created. Before copying, the command deletes the same window on the shadow. The tables have no primary key, so this delete-then-copy is what makes the operation idempotent: any window can be re-run, and a run that dies halfway is resumed per table.

One more change fell out of this. We now set created explicitly from the worker’s clock rather than relying on the column default, so a row and its mirrored twin carry the identical value on both instances. That makes the window boundary exact on both sides. Choosing the end of the window “a bit late” then simply re-copies rows that were already mirrored, and enabling mirroring became a normal rolling restart instead of a carefully timed ingest pause.

Re-running the tasks that are not inserts

Inserts are the bulk, but not the whole story. In the same gap, devices were deleted, fields were removed from products, and devices changed plans, which moves their history between tables. None of that is visible in a copy of newly inserted rows.

Those operations are Celery tasks, and they store their results in our result backend, arguments included. The backfill command lists the successful runs in the window and calls the very same task functions again, inside a context that routes the write seam to the shadow alone:

with shadow_only():
    celery_app.tasks[result.task_name](*args)

The tasks keep their own guards, so the replay does exactly what the primary run did, against the other database. There is no second implementation of “what does deleting a device mean” to drift out of sync.

Two things we learned here. First, order matters: replay has to run before the copy. A replayed deletion after the copy would remove rows the copy had just restored, and a replayed plan migration would duplicate the window portion of the moved history. Second, Celery records task arguments as a Python repr, which cannot be rebuilt when an argument is a datetime. One of our tasks takes two. Those runs get listed for manual replay instead; they are rare.

The result

From fork to swap took two days, most of it building and reviewing the code. The cutover itself was a rolling restart with two connection strings exchanged: the fork became the primary, the old PG15 instance became the shadow. During the overlap, every pod wrote to both databases and only disagreed about which one was primary, so no row was missed on either side. Dashboards followed the primary alias as pods restarted. Nobody outside the team noticed.

Rollback, for as long as we keep the old instance, is swapping the two variables back. There is no data to move because both databases have been receiving every write.

The side effect we intend to keep

Here is the part worth dwelling on. Nothing in this mechanism is specific to Tiger Cloud. The write seam speaks plain SQL over the Postgres wire protocol. The backfill is COPY from one Postgres to another. The task replay runs our own code. The only thing the target needs is the same tables and, for our aggregate queries, the TimescaleDB extension, which is open source and runs anywhere Postgres does.

We built this to survive a vendor’s upgrade procedure. What we ended up with is a general way to stand up a second time-series database, bring it to parity while production keeps running, verify it row for row, and swap. The next PostgreSQL major version is a settings change and a runbook. So is a different region, a different provider, or a self-hosted cluster.

We are happy on Tiger Cloud today, and the forking feature was genuinely what made this approach possible. But a managed database that cannot upgrade itself without downtime has, in effect, asked its customers to build their own migration path. Having built ours, we would rather not have to depend on the vendor’s roadmap for the next one.

Takeaways

• Put every write behind one function before you need it. Mirroring, auditing and migration all become a flag.
• Keep an insert-time column on append-only tables, and set it from the application so replicas agree on it.
• Never filter a hypertable by a column that is neither indexed nor the partition key without also bounding the time dimension.
• Make backfills idempotent by construction. Delete the window, then copy it. You will be interrupted.
• Store task arguments somewhere you can replay them from. Then replay the real task, not a re-implementation of it.