@speles7172/backup-client

Setting this up end to end — tables, infrastructure, schedule, querying, restore — is docs/BACKUP.md.

Back up Postgres and files to S3, in a layout Athena can query — and put either back.

The point of the layout is that a backup stops being a sealed blob. Last Tuesday's invoices is a table you can SELECT from, next to today's, without a restore and without a database.

npm install @speles7172/backup-client

There is no pg dependency here and no dependency on any other client: whatever your project already uses to reach the database can drive this, through the same (sql, params) => Promise<{ rows }> executor @speles7172/audit-client takes.

What a run produces

One run is one recoverable point in time — tables and files together, under one run id.

<root>/manifests/<store>/run_id=<run>/manifest.json
<root>/database/<table>/snapshot_ts=<run>/part-00000.parquet
<root>/files/snapshot_ts=<run>/<the file's own path>
<root>/file-index/snapshot_ts=<run>/part-00000.parquet

A run id is 2026-08-19T14-30-00Z — ISO 8601 with the punctuation S3 and Hive dislike swapped out. It sorts lexicographically in time order, so listing runs is free, and it is the snapshot_ts partition value, so the two cannot drift apart.

Database runs and file runs are separate runs under separate manifest namespaces, which is why <store> is in the manifest key: they are scheduled apart, take wildly different times and fail for unrelated reasons, so one run holding both would let a slow file copy mark a good database dump as failed.

The data objects are Parquet, not JSON. That is a correctness decision before it is a cost one — see Types — though the cost follows: Athena bills by bytes scanned, and a columnar file lets it read the two columns a query names instead of every byte of every row. There is no .gz: Parquet compresses each column chunk itself, with Snappy by default.

Manifests sit outside every table location on purpose: Athena reads everything beneath a LOCATION, and a manifest under one would be read as a row.

Backing up

import {
  createBackupRunner,
  createS3ObjectStore,
  createDirectoryFileSource,
} from '@speles7172/backup-client';

const store = createS3ObjectStore({ bucket: 'peles-backups', region: 'us-east-1' });

const runner = createBackupRunner({
  execute: pool.query.bind(pool),
  pool,                              // see "One snapshot" below — required by default
  store,
  root: 'main',
  tables: [
    { table: 'invoices', watermarkColumn: 'updated_at' },
    { table: 'billing.notes', keyColumn: 'note_id' },
    { table: 'users', exclude: ['password_hash'] },
  ],
  files: createDirectoryFileSource('/var/app/uploads'),
});

const manifest = await runner.backup({ kind: 'full' });

| | | |---|---| | keyColumn | what the dump pages on. Defaults to id. Keyset, not OFFSET — OFFSET 900000 makes Postgres walk and discard 900,000 rows. It must be NOT NULL and covered by a single-column unique index that is valid, ready and live, and that is checked against the catalog before the dump starts: a duplicated key would silently truncate the backup, since a page boundary inside a run of equal keys makes the next page ask for key > <that value> and skip the rest. >= is no better — it re-reads the whole run and doubles those rows on restore — so a key that is not unique is refused rather than paged badly. A failed CREATE UNIQUE INDEX CONCURRENTLY leaves an index behind that is marked invalid — quite possibly because the column holds duplicates — so that one does not count either. | | watermarkColumn | a column that advances on every write. Supplying it makes the table eligible for incremental runs; without it the table is dumped in full every time. | | exclude | columns to leave out entirely. |

Columns are discovered from information_schema on every run, not from a list here — so a column added by last week's migration is in tonight's backup, and a stale list can never silently stop copying one.

Each table gets storage of its own, named by schema: invoices is stored under invoices, and billing.invoices under billing_invoices. Two tables that would still land on one prefix are refused when the runner is built rather than discovered during a restore.

One snapshot

pool is a pg.Pool — anything with connect() — and the dump runs the whole run on one connection inside BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ READ ONLY. Every table and every page then reads one instant, and Postgres enforces that the backup cannot write.

Without it, a backup can silently miss rows. The dump pages with a keyset, so page two asks for id > <last id of page one>. A transaction that took id 7 before the dump began and commits after page one read ids 1–10 is invisible to every page that follows — and the run is still marked complete. Rows written across two tables in one transaction can likewise be caught half-and-half.

So a run without a pool is refused. If a transaction is genuinely impossible — a remote-pg bridge invokes a Lambda per statement, and nothing can span that — say so:

await runner.backup({ kind: 'full', allowInconsistentSnapshot: true });

When to back up

The schedule is a weekday-by-hour grid, not an interval: "Sunday and Wednesday at 02:00 and 14:00" is a thing an operator picks in a UI, and "every 12 hours" is not. It is evaluated in a named timezone, so the 02:00 slot stays at 02:00 across a DST change rather than drifting an hour twice a year.

import { isDue, assertSchedule } from '@speles7172/backup-client/core';
import { createRunLog } from '@speles7172/backup-client';

const schedule = assertSchedule({
  enabled: true,
  timezone: 'America/New_York',
  hours: { sun: [2], wed: [2, 14] },
  retentionDays: 90,
});

const log = createRunLog(pool.query.bind(pool));

// The last run that *worked*. The newest run of any status is the obvious
// thing to ask for and the wrong one — see below.
const last = await log.lastSuccessfulRun('db');
const decision = isDue(schedule, new Date(), last ? new Date(last.started_at) : undefined);
// { due: true, slot: '2026-08-19T02', reason: '…' }

if (decision.due) {
  const manifest = await runner.backup({ kind: 'full', store: 'db' });
  await catalog.apply(manifest);
  await runner.purge(schedule.retentionDays);
}

Three guarantees are the part worth keeping:

• A slot is served once. isDue compares the local hour of the last run against the current one, so an hourly tick inside the 02:00 slot backs up on the first tick and not the next four.
• Only successful runs count. Hence lastSuccessfulRun rather than list({ limit: 1 }): a run that is still going or that failed occupies the slot, so handing its timestamp to isDue reports the hour as already served — and a failing backup then stops retrying, silently, for as long as it keeps failing.
• purge will not delete a base something it is keeping still needs. It closes over the whole chain, never expires an in-flight run, and never deletes the newest success of either store whatever the retention says.

Reading the schedule is lenient and saving it is strict, deliberately: one malformed weekday in stored configuration must not stop every backup, but it must not be saved either. parseSchedule drops what it cannot read; assertSchedule refuses it.

Call isDue from whatever you already have — an EventBridge rule, a cron line, a Lambda on a schedule. This package deliberately provisions nothing.

The run log

The S3 manifests are authoritative — they are what a restore reads, and a backup index that dies with the database it backs up is not one. backup_runs and backup_restores are the queryable layer on top, so a console can sort, filter and page the history without one GET per run, and so a restore is recorded at all. Losing these two tables costs the history, not the ability to restore.

import { BACKUP_TABLES_SQL, createRunLog } from '@speles7172/backup-client';

// Paste BACKUP_TABLES_SQL into a migration. `ensureBackupTables(execute)` is
// there for projects with no migration runner.
const log = createRunLog(execute);

const row = await log.start({ runId, store: 'db', trigger: 'manual', startedBy: userId });
try {
  const manifest = await runner.backup({ kind: 'full', store: 'db', runId });
  await log.finish(row.id, { status: 'success', tableCount: manifest.tables.length });
} catch (error) {
  await log.finish(row.id, { status: 'error', errorMessage: String(error) });
}

// The reaper, for runs whose process was killed rather than failed — a timeout,
// an OOM kill, exhausted ephemeral storage — where the error handler above
// never ran. It never overwrites an error the runner did manage to record.
await log.failStale(2 * 60 * 60 * 1000);

Requires PostgreSQL 13 or newer, for gen_random_uuid(). It moved into core in 13; on 12 and older run CREATE EXTENSION IF NOT EXISTS pgcrypto first — which needs privileges this package does not assume it has, which is why it is a documented prerequisite rather than a statement the DDL emits.

Runs are listed and picked by started_at, not by run_id. The two are the same value in the ordinary case — the run id is the snapshot timestamp — and they come apart the moment a run id is supplied rather than generated. A backfill of last Tuesday's snapshot, taken today, sorts into last Tuesday: it would vanish from the top of a list of recent activity, and the scheduler would not see that it had just served the current slot.

started_by is text with no foreign key, on purpose: a run outlives the account that asked for it, and a history a DELETE FROM users can cascade away is not a history.

Querying the backup

import { createBackupCatalog } from '@speles7172/backup-client';

const catalog = createBackupCatalog({
  database: 'peles_backups',
  bucket: 'peles-backups',
  root: 'main',
  region: 'us-east-1',
  outputLocation: 's3://peles-athena-results/',
  execute: (sql, params) => client.query('athena-admin', sql, { params }),
});

await catalog.apply(manifest);   // CREATE DATABASE + one table per backed-up table

Then register it with @speles7172/sql-client and it is a database in @speles7172/sql-console like any other:

registerDatabase(catalog.registration());   // { name: 'backups', engine: 'athena', … }

SELECT * FROM "peles_backups"."backup_invoices"
 WHERE run_id = '2026-08-19T14-30-00-123Z'
   AND dt = DATE '2026-08-19'

catalog.sampleQuery(manifest, 'invoices') writes that for you.

Three things about the generated DDL that will come up:

Each table is dropped and rebuilt every run, then every surviving partition is re-registered in one ALTER. That sounds wasteful and is the cheap option: the data is in S3 and never touched, and the alternative — CREATE TABLE IF NOT EXISTS — freezes the schema at whatever the first run saw, so a column added by a later migration is one the table has never heard of. Rebuilding also means a column whose type changed just works, instead of needing a reconciliation pass that has to choose between two wrong answers.

Partitions are registered, not projected. Projection's only form that needs no per-run DDL is an injected partition, and an injected partition makes Athena reject any query that does not filter on it — so SELECT * FROM invoices LIMIT 100, the first thing anyone types, fails. Registering them costs one ALTER per table per run, which the run is already doing.

Which partitions get registered is read from the bucket, not from the run history. The two disagree after a retention prune, and a partition pointing at a prefix that no longer exists makes every query against the table fail outright rather than return fewer rows.

<database>_latest holds tables, not views. The obvious version is a view filtering on max(snapshot_ts); Athena rejects that shape at CREATE VIEW with "Queries of this type are not supported", and even where such a view is accepted every query scans the partition list first just to find the maximum. A plain external table pointed at the newest run's prefix costs one DROP/CREATE per run — which the run is already doing — and reads exactly one snapshot.

Because a query against the full-history database is allowed to omit the partition filter, SELECT count(*) FROM invoices there will scan every backup ever taken. That is what backupPreviewQuery in /core exists to prevent for the console's table-click; for hand-written queries, use the _latest database or name a snapshot_ts.

Types

Athena is told what the column actually is, wherever Parquet can carry it:

| Postgres | Athena | |---|---| | bool | boolean | | int2 / int4 | smallint / int | | int8 | bigint | | float4 / float8 | float / double | | date | date | | timestamp / timestamptz | timestamp, to microsecond precision | | numeric(p,s), p ≤ 15 | decimal(p,s) | | everything else | string |

Three of those were string in the first version of this package, and each for a JSON limitation rather than a Postgres one: JSON has no integer past 2^53, no decimal, and no timestamp Athena will parse. Parquet has all three, so the DDL can stop understating the data.

What is still string, and why it is not a compromise:

| | why | |---|---| | jsonb, arrays, bytea, uuid | no Athena equivalent, or a nested shape a reader would have to guess at | | bare numeric | it declares no precision, which means unbounded — picking one would be this package deciding how much of your ledger to keep | | numeric(p,s) with p > 15 | Parquet stores a decimal as its unscaled integer and the writer takes that as a JavaScript number, so exactness stops at 2^53 |

The rule is unchanged and is one sentence: the backup never guesses. A string always round-trips; a wrong type loses data quietly. A value that cannot be written as its declared type throws rather than becoming null, because a backup with a silent hole passes every check and fails only on the day it is restored.

Two details that look like implementation and are not:

• Timestamps are read as text and stored as microseconds. node-postgres parses a timestamp into a JavaScript Date, which holds milliseconds while Postgres holds microseconds — taking the driver's value would truncate every timestamp in the backup by up to 999µs, invisibly. The dump asks for col::text and converts.
• The restore reads with the conversions switched off. hyparquet turns a stored timestamp back into a Date by default, which would undo the point above at the last moment.

Under the hood, every column that lands as a string is selected as col::text, so Postgres produces the representation. That is what makes an array arrive as {a,b} rather than ["a","b"] — and it is why a restore is a single uniform cast back.

Restoring

import { createRestorer, createDirectoryFileTarget } from '@speles7172/backup-client';

const restorer = createRestorer({ execute: pool.query.bind(pool), pool, store, root: 'main' });

await restorer.restoreTables({ runId, mode: 'replace', tables: ['invoices'] });
await restorer.restoreFiles({ runId, target: createDirectoryFileTarget('/var/app/uploads') });

mode has no default, deliberately. append as a default silently doubles every row when someone re-runs a restore; replace as a default destroys a table when someone meant to add to it. Typing one of them is the only version where the destructive case is a decision.

Restoring a chain is two steps, and the package will not let you get it the wrong way round:

await restorer.restoreTables({ runId: fullRunId,        mode: 'replace' });
await restorer.restoreTables({ runId: incrementalRunId, mode: 'append'  });

replace truncates before it inserts, so it runs inside a transaction and takes every table in the call with it: the restore either lands whole or leaves the database untouched. Without a pool there is no transaction to roll back — a failed object read or a rejected insert would report a failure over a table it had already emptied — so replace is refused unless you pass allowNonTransactional: true. append destroys nothing and needs neither.

It also refuses a run whose manifest does not say complete — those objects are a prefix of the table rather than the table — stops if the row count does not match what the manifest recorded, and verifies every restored file against the SHA-256 taken when it was written.

Entry points

| | | |---|---| | @speles7172/backup-client | Node. The runner, the restorer, the catalog, the S3 store. | | @speles7172/backup-client/core | dependency-free. The key layout, the manifest, the schedule and retention rules, the type map. Safe in a browser bundle, and CI proves it on every run. |

Both are shipped as ESM and CommonJS.

What this package does not do

• Provision anything. No bucket, no Glue database, no schedule, no IAM. This repository ships libraries; where the backup lives and who may read it is the consuming application's decision. catalog.statements(manifest) hands you the DDL if you would rather put it in a migration than run it.
• Replace physical backups. This is a logical, per-table export. It gives you a queryable history and a selective restore; it does not give you point-in-time recovery. Keep your managed snapshots — the two answer different questions, and the one you want at 3am depends on what broke.
• Apply access control. Anyone who can query the Athena database can read every column that was backed up. Use exclude for the columns that must not leave the database, and scope the Glue database accordingly.