Backup and Restore Strategies for Time-Series Data

What is different about backing up time-series data

This guide is for whoever owns recovery of a telemetry store: a building-analytics platform holding BMS trends, a CMMS that keeps meter readings next to work orders, an energy dashboard. It assumes you know the basic tools; for pg_dump formats, options and pg_restore usage see the pg_dump and pg_restore guide, and for MySQL the mysqldump guide.

Four properties of telemetry change the design:

• Volume grows every day, so a full logical dump takes longer each month and eventually stops fitting in the backup window.
• Old data rarely changes. Once a time partition is past the period in which late readings still arrive, it is effectively read-only and only needs to be backed up once.
• Data classes have different value. Metadata (sites, assets, points, units, tag mappings) is small and irreplaceable; without it raw readings are just numbers against IDs. Roll-ups are expensive to rebuild. Raw readings may be re-sendable from upstream for a short time.
• Upstream buffers exist. A Kafka topic keeps events for its configured retention period and consumers can re-read them, which can shorten the gap a database restore has to cover. An MQTT broker is not a long-term buffer. The Kafka IoT pipeline article covers that design.

Set recovery targets per data class

Write down a recovery point objective (RPO, how much data you can lose) and a recovery time objective (RTO, how long recovery may take) for each class. The table is an illustrative example for a stated scenario, not a set of targets or recommendations: a building-analytics service on one PostgreSQL cluster with monthly-partitioned raw readings, ingestion from a Kafka topic with seven days of retention, and no contractual recovery terms assumed. Your own values depend on contracts and service levels, safety and alarm duties, reporting and regulatory obligations, how far upstream buffers can replay, and what faster recovery would cost.

PostgreSQL: three methods and where each fits

The PostgreSQL documentation describes three fundamentally different approaches: SQL dump, file system level backup, and continuous archiving with point-in-time recovery (PITR). For a telemetry server they play different roles:

# ILLUSTRATIVE ONLY, NOT RUN HERE: not a ready-to-run backup procedure
# postgresql.conf: archive every completed WAL segment
# (the docs call this archive_command "an example, not a recommendation")
#   wal_level = replica
#   archive_mode = on
#   archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
#   summarize_wal = on        (required before incremental backups; default off)

# full base backup; a backup manifest is generated unless --no-manifest is given
pg_basebackup -D /backups/base/2026-10-25 -X stream --checkpoint=fast

# incremental backup (PostgreSQL 17 and later), here referencing the full backup's manifest;
# a later incremental would reference the manifest of the backup it follows
pg_basebackup -D /backups/incr/2026-10-26 --incremental=/backups/base/2026-10-25/backup_manifest

# at restore time: rebuild a full backup from the chain, oldest first
pg_combinebackup -o /restore/data /backups/base/2026-10-25 /backups/incr/2026-10-26

The settings and flags used above, as documented

Each setting and flag in the commands above, with the PostgreSQL 18 documentation text that describes it. The backup frequency (weekly full, daily incremental) is an illustrative choice, not a documented recommendation. The commands show how the documented pieces fit together; they are not a complete or ready-to-run backup procedure. A working setup also needs archive monitoring, retention and deletion of old backups and WAL, verification (for example with pg_verifybackup) and a rehearsed restore, all checked against the documentation for your PostgreSQL version. The documentation itself describes the example archive_command as an example, not a recommendation, that might not work on all platforms.

Why PITR suits telemetry, and what it costs

PITR lets you stop replay just before a mistake, for example a retention job that dropped the wrong month, which a nightly dump cannot do. The cost is storage: every WAL segment from the oldest base backup you keep onwards must be retained, and an ingest-heavy server writes a lot of WAL. The storage sizing article recorded more WAL than table bytes per reading during a load: an illustrative measurement on a small PGlite sample (PostgreSQL 18.6 compiled to 32-bit WebAssembly); not a production benchmark and not representative of your server, so measure WAL volume on your own. PITR also restores only a whole cluster, not one table; to recover one partition you restore the cluster elsewhere and copy the table across. The documentation advises setting up and testing WAL archiving before taking the first base backup.

A streaming replica is not a substitute for backups. This is our reasoning rather than a sentence from the documentation: a physical standby replays the primary's WAL, so a mistaken DROP TABLE reaches it like any other change; it protects against losing a server, not against losing data through an operation. The documentation of recovery_min_apply_delay supports the point indirectly: it says that by default a standby restores WAL records from the sending server as soon as possible, and that a time-delayed copy offers opportunities to correct data loss errors. A delayed standby narrows the gap but is still not a backup history.

Partition-aware archiving for closed months

PITR keeps growing with the WAL it has to retain. Time partitions give you a cheaper layer for old data: once a partition has passed its late-data grace period, archive it once, verify it, and record what you archived. The PostgreSQL partitioning documentation names the moment after DETACH PARTITION as a useful time to back up the data with COPY, pg_dump or similar tools. The workflow:

1. Wait until the month is past the grace period for late data (gateways reconnecting after an outage still write into it). The late-arriving data article covers choosing that period.
2. Record an inventory and a fingerprint of the partition (below).
3. Dump the partition with pg_dump -Fc -t, or export it to a columnar file for the data lake.
4. Restore the dump into a scratch database and compare its fingerprint with the original.
5. Only then let the retention job detach and drop it, if raw retention has expired.

SET TimeZone = 'UTC';
CREATE TABLE reading (
    point_id  integer NOT NULL,
    ts        timestamptz NOT NULL,
    value     double precision,
    quality   smallint NOT NULL DEFAULT 0,
    PRIMARY KEY (point_id, ts)
) PARTITION BY RANGE (ts);
CREATE TABLE reading_2026_08 PARTITION OF reading FOR VALUES FROM ('2026-08-01 00:00+00') TO ('2026-09-01 00:00+00');
CREATE TABLE reading_2026_09 PARTITION OF reading FOR VALUES FROM ('2026-09-01 00:00+00') TO ('2026-10-01 00:00+00');
CREATE TABLE reading_2026_10 PARTITION OF reading FOR VALUES FROM ('2026-10-01 00:00+00') TO ('2026-11-01 00:00+00');
INSERT INTO reading (point_id, ts, value)
SELECT p, ts, 20 + p + extract(hour FROM ts) / 10.0
FROM generate_series(1, 3) AS p
CROSS JOIN generate_series('2026-08-01 00:00+00'::timestamptz, '2026-10-27 23:00+00'::timestamptz, interval '1 hour') AS ts;

SELECT c.relname AS partition_name,
       pg_get_expr(c.relpartbound, c.oid) AS bounds,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS size
FROM pg_inherits i
JOIN pg_class c ON c.oid = i.inhrelid
WHERE i.inhparent = 'reading'::regclass
ORDER BY c.relname;

 partition_name  |                                  bounds                                  |  size
-----------------+---------------------------------------------------------------------------+--------
 reading_2026_08 | FOR VALUES FROM ('2026-08-01 00:00:00+00') TO ('2026-09-01 00:00:00+00') | 248 kB
 reading_2026_09 | FOR VALUES FROM ('2026-09-01 00:00:00+00') TO ('2026-10-01 00:00:00+00') | 240 kB
 reading_2026_10 | FOR VALUES FROM ('2026-10-01 00:00:00+00') TO ('2026-11-01 00:00:00+00') | 224 kB

SELECT tableoid::regclass::text AS partition_name,
       count(*)      AS row_count,
       min(ts)::text AS first_ts,
       max(ts)::text AS last_ts,
       md5(string_agg(point_id || ',' || ts::text || ',' || value::text || ',' || quality,
                      ';' ORDER BY point_id, ts)) AS content_md5
FROM reading
GROUP BY tableoid
ORDER BY 1;

 partition_name  | row_count |        first_ts        |        last_ts         |           content_md5
-----------------+-----------+------------------------+------------------------+----------------------------------
 reading_2026_08 |      2232 | 2026-08-01 00:00:00+00 | 2026-08-31 23:00:00+00 | cd31d723399ff5e49a319cbef229f751
 reading_2026_09 |      2160 | 2026-09-01 00:00:00+00 | 2026-09-30 23:00:00+00 | a4591919291bf97b5ced3451d09be1c7
 reading_2026_10 |      1944 | 2026-10-01 00:00:00+00 | 2026-10-27 23:00:00+00 | a49ea830a335ae6f93be90e92777cc46

# ILLUSTRATIVE ONLY, NOT RUN HERE
# one closed month, custom format
pg_dump -Fc -t 'public.reading_2026_08' -f reading_2026_08.dump telemetry

# a partitioned table and every partition of it
pg_dump -Fc --table-and-children='public.reading' -f reading_all.dump telemetry

# rows re-routed through the root on restore (useful if partition bounds differ on the target)
pg_dump -Fc --load-via-partition-root -t 'public.reading*' -f reading_via_root.dump telemetry

Fingerprint caveats

• On a large partition, string_agg over every row is expensive and memory-hungry. Run it on a replica or off-peak, or checksum per day or per point and compare those smaller results.
• The checksum covers the text form of each value, so the fingerprint is only portable when both sides produce identical text. That is our inference, not a documented guarantee: the text of a timestamptz depends on the session time zone and date style (the run above used UTC), and the text of a double precision value depends on the server version and the extra_float_digits setting. Compute both fingerprints with the same PostgreSQL major version and the same session settings.
• Row count and time range alone catch most failed or partial restores; the checksum adds protection against silent changes.

Restoring a partition into the live table

When an auditor asks for a month that has already been dropped, restore the dump into a standalone table, check it, and attach it as a partition. The PostgreSQL documentation explains that ATTACH PARTITION scans the table to validate the partition constraint unless a matching CHECK constraint already exists, and recommends dropping that CHECK constraint after the attach. The run below detaches August, renames it to stand for the restored copy, and attaches it back that way:

ALTER TABLE reading DETACH PARTITION reading_2026_08;

SELECT count(*) AS rows_still_in_parent_after_detach FROM reading WHERE ts < '2026-09-01';
--  rows_still_in_parent_after_detach
-- -----------------------------------
--                                  0

SELECT count(*) AS rows_in_detached_table FROM reading_2026_08;
--  rows_in_detached_table
-- ------------------------
--                    2232

-- stands in for the table that pg_restore would create from the archive
ALTER TABLE reading_2026_08 RENAME TO reading_2026_08_restored;

ALTER TABLE reading_2026_08_restored ADD CONSTRAINT reading_2026_08_bounds
    CHECK (ts IS NOT NULL AND ts >= '2026-08-01 00:00+00' AND ts < '2026-09-01 00:00+00');
ALTER TABLE reading ATTACH PARTITION reading_2026_08_restored
    FOR VALUES FROM ('2026-08-01 00:00+00') TO ('2026-09-01 00:00+00');
ALTER TABLE reading_2026_08_restored DROP CONSTRAINT reading_2026_08_bounds;

SELECT tableoid::regclass::text AS partition_name, count(*) AS row_count
FROM reading GROUP BY tableoid ORDER BY 1;
--       partition_name      | row_count
-- --------------------------+-----------
--  reading_2026_08_restored |      2232
--  reading_2026_09          |      2160
--  reading_2026_10          |      1944

CREATE TABLE stray (LIKE reading INCLUDING ALL);
INSERT INTO stray VALUES (1, '2026-07-31 23:00+00', 21.0, 0);
ALTER TABLE reading ATTACH PARTITION stray
    FOR VALUES FROM ('2026-07-01 00:00+00') TO ('2026-07-31 00:00+00');

ERROR:  partition constraint of relation "stray" is violated by some row
SQLSTATE 23514

Locks to plan for

The partitioning documentation notes that plain DETACH PARTITION takes an ACCESS EXCLUSIVE lock on the parent, while DETACH PARTITION ... CONCURRENTLY and ATTACH PARTITION need only SHARE UPDATE EXCLUSIVE on the parent. On a table receiving readings every second, the concurrent detach is the documented form with the weaker lock on the parent, so it is worth considering for retention jobs; check the ALTER TABLE documentation for your version for the restrictions on the concurrent form before using it, and consider running attaches of restored months outside peak ingest.

Product notes: TimescaleDB, InfluxDB and QuestDB

Each engine documents its own rules. These summaries are from the vendors' documentation and were not run here. The commands below are illustrative outlines of the documented steps, not ready-to-run backup procedures: check every option against the documentation for your version and rehearse a restore before relying on them. Where the documentation does not cover a case (for example incremental backups for InfluxDB OSS v2), the table says so rather than filling the gap.

-- ILLUSTRATIVE ONLY, NOT RUN HERE: outline of the documented restore order
-- in the new, empty target database
CREATE EXTENSION IF NOT EXISTS timescaledb;
SELECT timescaledb_pre_restore();
-- then from the shell, without -j:  pg_restore -Fc -d <restoration database> <db_name>.bak
SELECT timescaledb_post_restore();

# ILLUSTRATIVE ONLY, NOT RUN HERE
influx backup /backups/2026-10-27/ -t <ROOT_TOKEN>
influx restore /backups/2026-10-27/ --bucket bms_raw --new-bucket bms_raw_restored

TimescaleDB and partition-aware archiving

Hypertable chunks are managed by the extension, not by your own ATTACH and DETACH statements, so the plain-PostgreSQL detach-and-dump workflow above does not apply as written. The documented route for one hypertable is to dump its schema and export the rows with COPY, then recreate the hypertable on restore. For engine background see TimescaleDB explained, InfluxDB explained and QuestDB explained.

Illustrative plan for an example building-analytics database

Scenario (assumed for illustration): a PostgreSQL 18 cluster holds metadata, monthly-partitioned raw readings (90-day raw retention), 15-minute roll-ups kept for five years, and alarm history. Ingestion reads from a Kafka topic with seven days of retention. Below is one illustrative plan for that scenario: an outline for discussion, not a ready-to-run backup procedure, and none of it was run here. The frequencies, the seven-day grace period, the number of chains kept and the drill interval are assumptions chosen for the example, not recommendations; set yours from the recovery targets you agreed, the storage budget, how late data really arrives, and how long a restore takes when you rehearse it.

• Continuous WAL archiving to storage in a different failure domain, with an alert if archiving falls behind.
• Weekly full base backup and daily incremental with pg_basebackup; keep two full chains.
• Nightly pg_dump of the metadata schema and roll-up tables (small), kept separately from the physical backups, so a metadata-only mistake can be fixed without a full restore.
• Monthly partition archive: on the 8th, after a seven-day grace period, dump last month's raw partition, restore it in a scratch database, compare fingerprints and keep the archive for the period your contracts require.
• Quarterly restore drill: restore the cluster to a named time on separate hardware, replay from Kafka, and time the whole exercise against the RTO.

Security, limitations and when this is not enough

Security. Backups hold everything the database holds: telemetry that reveals occupancy patterns, user tables and, in InfluxDB's case, tokens. Encrypt them at rest and in transit, store them under credentials that the database server cannot use to delete them, and restrict who can restore.

Limitations. PITR and pg_basebackup cover a whole cluster, so a multi-tenant cluster restores all tenants together; see PostgreSQL multi-tenant telemetry for how the tenant model affects this. Logical dumps of large raw tables are slow to take and slower to restore with indexes. Partition archives cover only closed months; the current month depends entirely on PITR and upstream replay.

When this is not enough. If you need recovery in minutes across sites, a standby in another region plus the backups above is a common next step; managed services offer their own snapshot and PITR features, documented per provider. If raw data is kept for years, move closed partitions to a lake or warehouse format and treat that copy as the long-term record. Size the backup set with the method in sizing storage for one-second telemetry.

Frequently asked questions

Sources

• PostgreSQL 18 documentation: Backup and Restore
• PostgreSQL 18 documentation: SQL Dump
• PostgreSQL 18 documentation: pg_dump
• PostgreSQL 18 documentation: pg_basebackup
• PostgreSQL 18 documentation: pg_combinebackup
• PostgreSQL 18 documentation: Continuous Archiving and Point-in-Time Recovery
• PostgreSQL 18 documentation: Table Partitioning
• PostgreSQL 18 documentation: Write Ahead Log settings
• Tiger Data documentation: Logical backup with pg_dump and pg_restore
• Tiger Data documentation: Physical backups
• InfluxDB OSS v2 documentation: Back up data
• InfluxDB OSS v2 documentation: Restore data
• QuestDB documentation: Backup and restore
• InfluxDB OSS v2 documentation: influx backup (CLI reference)
• InfluxDB 3 Core documentation: Back up and restore data
• InfluxDB 3 Enterprise documentation: Back up and restore data
• PostgreSQL 18 documentation: Replication settings (recovery_min_apply_delay)

Checked 27 October 2026.

How we research these articles: our editorial method. Research-based from official documentation; SQL examples are run on a local database where the page says so. CodeWithSQL earns nothing from the vendors mentioned.