Backup and restore

A puddle catalog lives in two places, and a backup needs both:

The metastore (SQLite or PostgreSQL) holds the namespaces with their properties, and for each table and view one pointer: the location of its current metadata file.
The warehouse (object storage, or a directory for file://) holds everything else: each table’s metadata files (metadata.json), manifest lists, manifests and data files.

A pointer is only useful if the files it leads to exist. Every commit writes a new metadata file and then swaps the pointer to it; metadata files are never changed after they are written, and the older ones stay in the warehouse (listed in the table’s metadata log) unless the table’s write.metadata.delete-after-commit.enabled property says otherwise.

The warehouse also holds a small copy of the catalog: see Catalog sidecars.

Order: metastore first, then the warehouse

Back up the metastore first and the warehouse after it. Every pointer in the metastore backup then names a metadata file that the warehouse backup also has, because the file was written before the pointer. Files written after the metastore backup are in the warehouse backup too, but nothing points to them; that’s harmless.

What can still break a backup is a file deleted between the two copies: a table dropped with purge, a snapshot expiration or orphan-file cleanup run by an engine, or metadata files trimmed by delete-after-commit. Keep the window short, pause table maintenance jobs while it runs, or better, use storage that can go back in time (S3 versioning or bucket replication with a retention period, file system or volume snapshots) and restore the warehouse as of the end of the metastore backup.

Backing up the metastore

PostgreSQL: puddle’s tables are in one schema (postgresql.schema, default puddle). pg_dump takes a consistent snapshot while puddle runs:

$ pg_dump --format=custom --schema=puddle \
    --file=puddle-$(date +%F).dump "$PUDDLE_PG_URL"

Managed Postgres point-in-time recovery works just as well; restore it to a time before the warehouse backup (or snapshot) was taken.

SQLite: puddle runs SQLite in WAL mode, so copying the database file alone while puddle runs can miss recent commits or produce a corrupt copy. Either stop puddle and copy puddle.db (with puddle.db-wal if it’s there), or use SQLite’s online backup, which is safe while puddle runs. The image has no shell, so run sqlite3 in another container that mounts the same volume (on an SELinux host, mount the backup directory with :z):

$ mkdir -p backup
$ docker run --rm \
    -v puddle-data:/var/lib/puddle \
    -v "$PWD/backup:/backup" \
    alpine sh -c 'apk add -q sqlite && sqlite3 /var/lib/puddle/puddle.db ".backup /backup/puddle.db"'

Backing up the warehouse

Copy or snapshot the warehouse’s bucket or prefix (or directory) with your storage’s usual tools. For a file:// warehouse in the same volume as SQLite, back the database up first, as above, and then the warehouse directory.

Never delete metadata files by hand or with a storage lifecycle rule (for example an S3 rule that expires objects after 30 days). A table whose current metadata file is gone can’t be loaded or committed to, and one whose older metadata files are gone loses its history and can’t be rolled back. Remove old data with the engines' table maintenance instead (snapshot expiration, orphan-file removal, write.metadata.previous-versions-max with write.metadata.delete-after-commit.enabled), which update the metadata as they go.

Catalog sidecars

For every table and view, puddle also writes a small JSON file, its catalog sidecar, into the warehouse: <warehouse location>/_puddle/catalog/<uuid>.json, named after the table’s (or view’s) UUID. It records what the metastore says about it:

{
  "format-version": 1,
  "kind": "table",
  "warehouse": "analytics",
  "identifier": {"namespace": ["sales"], "name": "orders"},
  "uuid": "4f1c3e0a-9d2b-4c55-8a51-0e6f3b7d9a12",
  "location": "s3://my-warehouse/4f1c3e0a-9d2b-4c55-8a51-0e6f3b7d9a12",
  "metadata-location": "s3://my-warehouse/4f1c3e0a-9d2b-4c55-8a51-0e6f3b7d9a12/metadata/00007-1b9e0c2d-….metadata.json",
  "state": "active",
  "updated-at-ms": 1790000000000
}

Puddle rewrites it after every metastore write for the table or view: create, commit (also in a multi-table transaction), rename and register. Dropping without purge sets state to dropped; a purge deletes it before the table’s files. When a commit’s outcome is unknown (500 CommitStateUnknownException), the sidecar keeps the metadata file the commit started from and lists the one it wrote under unconfirmed-metadata-locations.

Sidecars exist so that the catalog can be rebuilt from the warehouse when the metastore is lost (puddle recover). They are written after the metastore write, and best effort: a sidecar that can’t be written is logged (failed to write catalog sidecar, with the request id) and the request still succeeds. So a sidecar can be a commit behind, and recovery allows for that. The metastore stays the source of truth while puddle runs; nothing reads the sidecars then, and engines never do.

The sidecars live outside the tables’ own directories on purpose: engines’ orphan-file cleanup (Trino’s remove_orphan_files, for example) deletes every file under a table’s location that its metadata doesn’t reference, and vended credentials only reach the table’s own location. No table, view or namespace may use a location inside _puddle. Include _puddle/ when you back up or replicate the warehouse, and don’t expire it with lifecycle rules.

Restoring

Restore both halves, then start puddle:

Stop puddle (every replica).
Restore the warehouse.

Restore the metastore into an empty database or file:

PostgreSQL: create the database (or drop the puddle schema) and pg_restore --dbname="$PUDDLE_PG_URL" puddle-2026-09-24.dump.

SQLite: remove puddle.db, puddle.db-wal and puddle.db-shm, copy the backup to puddle.db, and make it writable by uid 65532:

$ docker run --rm \
    -v puddle-data:/var/lib/puddle \
    -v "$PWD/backup:/backup:ro" \
    alpine sh -c 'rm -f /var/lib/puddle/puddle.db-wal /var/lib/puddle/puddle.db-shm &&
                  cp /backup/puddle.db /var/lib/puddle/puddle.db &&
                  chown 65532:65532 /var/lib/puddle/puddle.db'

Start puddle with the same build as the backup (see Upgrades) and the same warehouse locations in its config.

Restore the warehouse to the same bucket and paths it was backed up from. Pointers, metadata files and manifests all name files by absolute URL, so an Iceberg table can’t be moved by copying its files.

After a restore, the catalog is as it was at the metastore backup. A table committed to after that is back at an older metadata file than engines may have seen, and a table created after it is missing from the catalog, although its files may be in the warehouse. Bring such a table back by registering its newest metadata file (register-table, for example CALL system.register_table(...) in Spark or catalog.register_table(...) in PyIceberg), or register all of them at once with puddle recover --allow-nonempty (see below), which skips what the restored metastore has; for a table that is in the catalog but behind, drop it without purge first. Engines that cache table metadata should be restarted or have their caches cleared.

A restore that ends up with a pointer to a metadata file that isn’t in the warehouse makes that table fail to load (404 NotFoundException, Failed to open input stream for file: … naming the missing file). Restoring the warehouse from a later point in time fixes it; so does dropping the table without purge and registering a metadata file that exists.

Recovering without a metastore backup

Restore the metastore from a backup when you have one: it is the only complete copy of the catalog. When there is none, or it is too old, puddle recover rebuilds the catalog from the warehouse’s catalog sidecars:

$ puddle recover -c puddle.yaml [--warehouse <name>] [--dry-run] [--allow-nonempty]

It uses the config’s metastore and warehouses, recovers every warehouse in the config (or the one named with --warehouse), prints a report per warehouse to standard output (log lines go to standard error), and exits non-zero if anything couldn’t be recovered. For each warehouse it lists the storage, reads the sidecars, and:

recreates the namespaces named in the identifiers of active tables and views, nested ones level by level, without properties;
registers each active table and view under its identifier, through the same path as register-table, so the location rules are checked and its location is recorded as on a normal create;
reports tables and views whose sidecar says dropped, and does not bring them back;
lists directories of metadata files that no sidecar names (a table whose sidecar was never written or was deleted) with their UUID, location and newest metadata file, for registering by hand.

It refuses a warehouse whose metastore already has namespaces, tables or views. With --allow-nonempty it adds what is missing and skips identifiers that exist. --dry-run prints the same report without changing anything.

Stop puddle first (every replica), so nothing commits while the catalog is rebuilt, and point the config at an empty metastore: for SQLite a new file, for PostgreSQL an empty database or schema (recover creates puddle’s tables, as puddle does on start). With the container image, the same volumes and config as the server:

$ docker run --rm \
    -e PUDDLE_TOKEN \
    -v puddle-data:/var/lib/puddle \
    -v "$PWD/puddle.yaml:/etc/puddle/puddle.yaml:ro" \
    ghcr.io/ragnard/puddle:latest recover -c /etc/puddle/puddle.yaml --dry-run

Then run it again without --dry-run, and start puddle. Engines that cache table metadata see the same metadata files as before, as long as the sidecars were current.

Which metadata file a table gets. A sidecar is written after the metastore write, best effort, so it can be behind: the last commit landed, but the sidecar write didn’t. Recovery therefore looks at the metadata files next to the one the sidecar names (call it S):

A file is a descendant of S when it belongs to the same table and lists S in its metadata log: it was written by a commit that started from S, or from a later file.
Descendants that the sidecar lists as unconfirmed (written by a commit whose outcome was unknown) are not used.
With no descendants, the table gets S. If the descendants form one chain, it gets the newest, and the report notes how many commits the sidecar was behind. If they branch, which one table’s history can’t produce, it gets S and the report warns.
A newer file that isn’t a descendant (another table’s, or one from a commit that lost a race) is never used; the report lists it.

A descendant written by a commit that never landed looks the same as a commit whose sidecar write was lost, unless puddle recorded it as unconfirmed. That is left to two rare cases, puddle stopping between writing the file and the metastore write, and a failed commit whose file couldn’t be deleted either, and such a file is adopted. Views have no metadata log: a view always gets S, and newer view metadata files are reported, not used.

Not recovered:

Namespace properties, including a namespace’s location (tables and views keep their own locations; set the property again so new tables go there too), and namespaces without tables or views.
Tables and views without a sidecar (listed for registering by hand), dropped ones, and purged ones (their files are gone).
A table or view that register-table would refuse, for example one whose metadata file isn’t under its location; the report shows the error.

Recovery lists every object in the warehouse’s storage, so on a large bucket it takes a while; the only files it reads are the sidecars and metadata files.

Security hardening Upgrades