Toolbox

• 1: ZFS Storage
• 2: PGMQ Queues
• 3: SQLite + Litestream
• 4: TimescaleDB

1 - ZFS Storage

pg zfs turns a Linux host into a ZFS storage server: install the packages, inspect disks, create redundancy-aware pools, and manage datasets and snapshots. It talks directly to the kernel — no state is written to pg.yaml, and it does not need a PostgreSQL instance.

The command family is deliberately resource-oriented: pg zfs pool create, pg zfs dataset create, pg zfs snapshot rollback. Anything not wrapped is reachable through pg zfs raw.

Supported systems

pg zfs install detects the distribution from /etc/os-release and installs everything needed, including package repositories:

These are the versions the upstream repositories shipped when the installer was tested; the repositories decide which release you get. pg zfs status always prints the installed userland and kernel-module versions.

Other distributions are detected and given manual instructions rather than installed automatically. Only Linux is supported.

Install

pg zfs install

The command is zero-configuration and idempotent:

1. Verifies privileges (root, or working sudo -n).
2. Enables the required repository (contrib on Debian, universe on Ubuntu, the OpenZFS release repository on Fedora and Rocky).
3. Installs ZFS, kernel headers/module and the ZED daemon.
4. Loads the module and enables the ZFS systemd units for boot.
5. Prints userland and kernel-module versions.

Running it again on a healthy host is a no-op. There are no --version, --backports or --force switches — use the distribution package manager when you need a specific ZFS build, then let pg zfs manage it.

Verify the result at any time:

pg zfs status

OS:            Debian GNU/Linux 12 (bookworm)
Family:        debian
ZFS:           installed
Userland:      zfs-2.1.11-1+deb12u1
Kernel module: 2.1.11-1+deb12u1 (loaded)
Kernel:        6.1.0-54-cloud-amd64
Privilege:     root
Pools:         none
Secure Boot:   disabled

RAID levels and ZFS

ZFS has no standard RAID2/RAID3 levels. The pool types map as follows:

The aliases raid0/raid1/raid2/raid3 are accepted for convenience.

Quick start

# 1. Install ZFS
pg zfs install

# 2. See which disks are usable
pg zfs disk list

# 3. Two whole disks -> mirror automatically
pg zfs pool create tank /dev/vdb /dev/vdc

# 4. Confirm the pool is online
pg zfs pool list

# 5. A dataset for backups
pg zfs dataset create tank/backups

# 6. Snapshot before an upgrade
pg zfs snapshot create tank/backups@before-upgrade

# 7. Roll back if the upgrade went wrong
pg zfs snapshot rollback tank/backups@before-upgrade

The pool is mounted at /tank, the dataset at /tank/backups. Rollback discards every change made after the snapshot and asks for confirmation.

Reusing disks from an old pool

ZFS keeps its vdev partitions and labels on the disks, and export does not remove them, so the safety check refuses such disks. Destroy the old pool and clear the leftovers before creating a new one:

# 1. Import and destroy the old pool if its data is no longer needed
pg zfs pool import fast
pg zfs pool destroy fast --force

# 2. Clear the ZFS labels and the partition table
pg zfs raw zpool labelclear -f /dev/vdd
pg zfs raw zpool labelclear -f /dev/vde
wipefs -a /dev/vdd /dev/vde

# 3. Create the new pool
pg zfs pool create tank /dev/vdd /dev/vde

If labelclear fails on the whole disk, clear the member partitions instead (/dev/vdd1, /dev/vde1). Skip step 1 when the old pool was already destroyed.

Disk safety

pg zfs disk list reports every whole disk with its mountpoints, current ZFS membership and whether pgcli considers it safe to hand to ZFS.

Pool creation refuses:

• the root disk, or a disk with a mounted partition;
• partitions, loop, ROM and CD-ROM devices;
• disks carrying a filesystem (ext4, xfs, swap, LVM, LUKS, mdraid, …);
• disks with an existing ZFS label;
• read-only disks;
• duplicate devices, including duplicates reached through different paths;
• disks already used by another pool.

--force skips only the confirmation prompt. It is never passed to ZFS as -f, and it does not bypass any safety check.

Creating pools

Automatic layouts

Positional devices select the layout:

pg zfs pool create tank /dev/vdb /dev/vdc              # mirror
pg zfs pool create data /dev/vdb /dev/vdc /dev/vdd     # raidz1
pg zfs pool create bulk /dev/vdb /dev/vdc /dev/vdd /dev/vde   # raidz2

Override with --type:

pg zfs pool create fast /dev/vdb /dev/vdc /dev/vdd /dev/vde --type mirror
pg zfs pool create safe /dev/vdb /dev/vdc /dev/vdd /dev/vde /dev/vdf --type raidz3

Use disks of the same capacity (and similar performance) within a vdev. ZFS does not reject mixed sizes, but a vdev’s usable capacity is limited by its smallest member, so the extra space on larger disks is wasted. Different vdevs in one pool may have different capacities.

Multiple vdevs

Use repeatable --data groups together with an explicit --type. Each group is one vdev; the pool stripes across them:

# Two mirror vdevs (striped mirrors)
pg zfs pool create fast --type mirror \
  --data /dev/vdb,/dev/vdc --data /dev/vdd,/dev/vde

# Two raidz2 vdevs
pg zfs pool create bulk --type raidz2 \
  --data /dev/vdb,/dev/vdc,/dev/vdd,/dev/vde \
  --data /dev/vdf,/dev/vdg,/dev/vdh,/dev/vdi

--type applies inside each --data group, not to the pool as a whole: every group becomes one vdev of that layout, and the pool stripes across the vdevs. All groups share the same --type; pg zfs cannot mix, for example, one mirror vdev and one raidz2 vdev in a single create. Use pg zfs raw zpool create for a mixed topology.

Capacity is the sum of the vdev capacities. Redundancy is per vdev: losing more disks than one vdev can tolerate takes down the whole pool.

SLOG, special, cache and spares

pg zfs pool create tank /dev/vdb /dev/vdc \
  --type mirror \
  --special /dev/vdd,/dev/vde \
  --log /dev/vdf,/dev/vdg \
  --cache /dev/vdh \
  --spare /dev/vdi

--special follows --type (a mirror here). --log with two or more devices is a mirror; a single device is a stripe and is warned about. --cache and --spare take independent device lists.

A device may appear only once across data, special, log, cache and spare.

Add a spare to an existing pool with pool add:

pg zfs pool add tank --class spare /dev/vdi

Default properties

Unless --no-defaults is given:

Override pool properties with --prop/-p (maps to zpool -o) and root dataset properties with --fs-prop/-P (maps to zpool -O):

pg zfs pool create tank /dev/vdb /dev/vdc \
  --prop ashift=13 --fs-prop compression=lz4 --fs-prop atime=on

-o is pgcli’s global output flag, which is why the pool/dataset properties use -p/-P. Inside pg zfs raw the native flags work normally.

Managing pools

pg zfs pool list
pg zfs pool status tank
pg zfs pool status --brief          # only pools with problems
pg zfs pool scrub tank
pg zfs pool scrub tank --stop

Growth and repair:

# Add another mirror vdev
pg zfs pool add tank /dev/vdd /dev/vde --type mirror

# Attach /dev/vdd to the mirror holding /dev/vdc (2-way to 3-way)
pg zfs pool attach tank /dev/vdc /dev/vdd

# Shrink it back
pg zfs pool detach tank /dev/vdd

# Replace the failing /dev/vdc with the new /dev/vde
pg zfs pool replace tank /dev/vdc /dev/vde

# Temporarily take a disk out
pg zfs pool offline tank /dev/vdc
pg zfs pool online tank /dev/vdc

attach targets mirror vdevs. Adding disks to a raidz vdev (raidz expansion) requires OpenZFS 2.3 or newer; Debian 12 (2.1) and Ubuntu 24.04 (2.2) cannot do it. Use pg zfs raw zpool attach on a recent release if you need it.

Unmounting and destroying a pool

pg zfs pool export unmounts every dataset and detaches the pool from the host, but keeps all data on the member disks:

pg zfs pool export tank

An exported pool is not imported at boot. Attach it again by listing the importable pools and importing it:

pg zfs pool import                  # list importable pools
pg zfs pool import tank
pg zfs pool import tank --dir /dev/disk/by-id --no-mount

pg zfs pool destroy deletes the pool, every dataset and all data:

pg zfs pool destroy tank

destroy only works on an imported pool. If the pool was exported, import it first:

pg zfs pool import tank
pg zfs pool destroy tank

Without --force both commands ask for confirmation; EOF and the default answer mean “no”. Non-interactive automation must pass --force. --force only skips the prompt — it is never passed to ZFS as -f and does not bypass safety checks. Export is reversible; destroy is not.

Persistence and boot

Pools and datasets survive reboots without any extra steps:

• pg zfs install enables zfs-load-module, zfs-import-cache, zfs-mount and zfs-zed (when the unit exists).
• zpool create records the pool in /etc/zfs/zpool.cache; on boot zfs-import-cache imports it.
• ZFS does not use /etc/fstab. Each dataset’s mountpoint is a ZFS property, and zfs-mount mounts them at boot. A pool’s root dataset mounts at /<pool>, and child datasets mount under it (for example /fast/data).
• The pool state lives on the member disks, so data survives a crash or power loss even if the host did not shut down cleanly.

Verify the setup:

systemctl is-enabled zfs-load-module zfs-import-cache zfs-mount zfs-zed
pg zfs raw zpool get cachefile fast
pg zfs raw zfs get mountpoint fast

After a reboot pg zfs pool list should list the pool and findmnt /fast should show it mounted.

On real hardware, create pools from stable paths such as /dev/disk/by-id/.... /dev/sdX names can change across boots, and a pool whose devices are missing will not import. Run pg zfs pool export tank before moving disks to another host; the destination can then adopt them with pg zfs pool import tank.

Datasets and snapshots

pg zfs dataset create tank/data
pg zfs dataset create tank/a/b/c --parents
pg zfs dataset create tank/logs --prop quota=50G --prop recordsize=128K

pg zfs dataset list
pg zfs dataset list tank/data --props quota,recordsize,compression

pg zfs dataset set tank/data compression=lz4 quota=100G

pg zfs snapshot create tank/data@snap1
pg zfs snapshot create tank/data@weekly --recursive
pg zfs snapshot list tank/data
pg zfs snapshot rollback tank/data@snap1
pg zfs snapshot destroy tank/data@snap1

pg zfs dataset clone tank/data@snap1 tank/clone --prop compression=off
pg zfs dataset destroy tank/clone
pg zfs dataset destroy tank/a --recursive

A dataset with children requires --recursive to destroy. Rollback discards all changes made after the snapshot.

Rollback

pg zfs snapshot rollback tank/data@snap1 returns the dataset to the state captured by the snapshot; every change made after it is discarded. If newer snapshots exist, ZFS refuses the rollback unless --recursive is given, which deletes those newer snapshots. Clones that depend on the snapshot also block the rollback — remove them first, or use pg zfs raw zfs rollback -R (which destroys the dependent clones). Non-interactive runs need --force to skip the confirmation prompt.

# Snapshot before an upgrade
pg zfs snapshot create tank/data@before-upgrade

# Roll back (asks for confirmation)
pg zfs snapshot rollback tank/data@before-upgrade

# Non-interactive automation
pg zfs snapshot rollback tank/data@before-upgrade --force

# Newer snapshots exist: --recursive also destroys them
pg zfs snapshot rollback tank/data@before-upgrade --recursive --force

# Clones block the rollback: -R also destroys dependent clones
pg zfs raw zfs rollback -R tank/data@before-upgrade

Clone

pg zfs dataset clone tank/data@snap1 tank/clone creates a new, writable dataset from the snapshot. Unchanged blocks are shared copy-on-write, so the clone does not duplicate the full data. Pass --prop to set properties on the new dataset:

# Create a writable copy of a snapshot
pg zfs dataset clone tank/data@snap1 tank/clone

# Set properties on the clone
pg zfs dataset clone tank/data@snap1 tank/clone \
  --prop compression=off --prop mountpoint=/tank/clone

# Detach the clone; the original snapshot can then be destroyed
pg zfs raw zfs promote tank/clone
pg zfs snapshot destroy tank/data@snap1
pg zfs dataset destroy tank/clone --force

While a clone depends on a snapshot, the snapshot cannot be destroyed. Promote the clone (pg zfs raw zfs promote tank/clone) to make it independent of the original dataset before removing the old snapshot.

Raw passthrough

pg zfs raw zpool status -v
pg zfs raw zpool set comment="backup pool" tank
pg zfs raw zfs create -o compression=off tank/scratch
pg zfs raw zpool create -f -O compression=gzip temp /dev/vdb

raw bypasses every safety check and confirmation. It is the escape hatch for options pgcli does not wrap — treat it exactly like running zpool/zfs as root. Binary pipelines work too, for example:

pg zfs snapshot create tank/data@send
pg zfs raw zfs send tank/data@send | pg zfs raw zfs receive tank/recv

Permissions

Every command that invokes zpool or zfs first checks privileges:

• root: runs directly;

• non-root with working sudo -n: all ZFS commands run through sudo -n;

• otherwise: fails immediately with

  ZFS management requires root: run "sudo pg zfs ..." or configure passwordless sudo for this user

pgcli never prompts for a sudo password. pg zfs status and pg zfs disk list still show static information without privileges, marking pool details as unavailable.

Command reference

Troubleshooting

pg zfs install cannot find packages. Check that the archive repository is reachable and that contrib/universe is enabled. The installer edits only distribution archive sources (never third-party repositories) and leaves a *.pgcli.bak next to each modified file.

DKMS build fails. Confirm headers for the running kernel are installed (linux-headers-$(uname -r) on Debian/Ubuntu, kernel-devel-$(uname -r) on Fedora/Rocky). The installer reports the build log location; it never reboots or changes the kernel. On very small VMs, add swap before installing.

Secure Boot is enabled. DKMS modules must be signed. pgcli reports the condition but does not enroll keys; sign the module with your MOK key or disable Secure Boot for the test host.

Module is not loaded. modprobe zfs then pg zfs status. The installer enables zfs-load-module, zfs-import-cache, zfs-mount and zfs-zed so pools import automatically on boot.

A disk is rejected because it has partitions or an old label. If it really is free, clear the leftovers:

pg zfs raw zpool labelclear -f /dev/vdb
wipefs -a /dev/vdb

On OpenZFS 2.2 and newer, clearing the whole disk can fail with failed to clear label for /dev/vdb when the label lives in the ZFS member partition. In that case clear the partition instead, then wipe the table:

pg zfs raw zpool labelclear -f /dev/vdb1
wipefs -a /dev/vdb

Never do this to a disk that belongs to an imported pool — pg zfs disk list shows the owning pool, and the safety check refuses such disks.

SELinux (Fedora/Rocky). ZFS mountpoints use standard file contexts. If a service cannot access a dataset, check ausearch/journalctl for AVC denials instead of disabling SELinux globally.

Pools do not come back after reboot. systemctl status zfs-import-cache and zpool import as root. Pools created from /dev/sdX names follow device renames; use stable paths (/dev/disk/by-id/...) when creating pools on real hardware.

2 - PGMQ Queues

pgmq turns PostgreSQL into a durable message queue: no Redis, RabbitMQ or background worker, just tables and SQL functions in the pgmq schema. Producers send JSON messages, consumers read them with a visibility timeout, and messages stay in the queue until they are explicitly deleted or archived.

Install

pgcli installs the extension package from the Pigsty repository and runs CREATE EXTENSION IF NOT EXISTS pgmq in the instance’s default database:

pg extension install pgmq -i pg01

To use it from another database in the same instance:

pg psql -i pg01 -- -d appdb
CREATE EXTENSION IF NOT EXISTS pgmq;

Check the installed version:

pg exec -i pg01 "SELECT extversion FROM pg_extension WHERE extname = 'pgmq'"

Quick start

-- Create a queue; running it again on an existing queue is a no-op
SELECT pgmq.create('jobs');

-- Send one message; returns its message id
SELECT pgmq.send('jobs', '{"task": "resize", "image_id": 42}');

-- Read up to 10 messages, invisible to other consumers for 60 seconds
SELECT msg_id, read_ct, message
FROM pgmq.read('jobs', 60, 10);

-- Acknowledge one message
SELECT pgmq.delete('jobs', 1);

Run the same statements through pgcli:

pg exec -i pg01 "SELECT pgmq.send('jobs', '{\"task\": \"resize\"}')"
pg psql -i pg01

Queues on disk

Each queue is a table in the pgmq schema:

A live message row contains msg_id, read_ct (number of deliveries), enqueued_at, vt (visible again at) and message jsonb. Recent versions add last_read_at and headers.

Because messages are rows, pgmq.send participates in transactions: it commits or rolls back with the surrounding statement. Queue traffic is also ordinary table traffic — high send/delete rates bloat the table like any other.

Sending messages

SELECT pgmq.send('jobs', '{"task": "resize"}');

-- Visible only after 30 seconds
SELECT pgmq.send('jobs', '{"task": "cleanup"}', 30);

-- Batch send: one statement, one atomic insert
SELECT pgmq.send_batch('jobs', ARRAY[
  '{"task": "a"}',
  '{"task": "b"}',
  '{"task": "c"}'
]::jsonb[]);

send returns the new msg_id; send_batch returns one id per message. Use send_batch instead of looping over send — it is one round trip and the whole batch is atomic.

Payloads must be valid JSON and are stored as jsonb: keys are reordered and duplicates removed. Keep large blobs out of the queue.

Newer versions accept a headers jsonb argument for metadata such as a trace id or a FIFO group key.

Reading and acknowledging

SELECT msg_id, read_ct, enqueued_at, vt, message
FROM pgmq.read('jobs', 60, 10);

read(queue, vt, qty) returns up to qty visible messages and makes them invisible for vt seconds. Reading does not remove anything, so each message must be acknowledged:

SELECT pgmq.delete('jobs', 42);                -- one message
SELECT pgmq.delete('jobs', ARRAY[42, 43, 44]); -- batch

pgmq.pop('jobs') reads and deletes in one call. Use it only when losing an in-flight message on a consumer crash is acceptable.

Avoid a tight polling loop in long-running consumers:

SELECT msg_id, message
FROM pgmq.read_with_poll('jobs', 60, 10, 5, 100);

This waits up to max_poll_seconds (5) for messages, checking every poll_interval_ms (100) milliseconds, and returns as soon as a message arrives.

Visibility timeout and retries

The visibility timeout is PGMQ’s delivery model:

1. read sets vt = now() + timeout and increments read_ct.
2. The message stays invisible to other consumers until vt.
3. delete or archive removes it. If the consumer crashes or does not acknowledge in time, the message becomes visible again after vt and is delivered to another consumer.

Delivery is therefore at-least-once across failures. Choose vt longer than the worst-case processing time, and make consumers idempotent. For long jobs, extend the lease without re-reading:

SELECT pgmq.set_vt('jobs', 42, 300);            -- one message
SELECT pgmq.set_vt('jobs', ARRAY[42, 43], 300); -- batch

read_ct is a ready-made retry counter.

Archive and dead letters

delete is permanent. archive moves a message to pgmq.a_<queue> for retention, audit or replay:

SELECT pgmq.archive('jobs', 42);
SELECT pgmq.archive('jobs', ARRAY[42, 43]);
SELECT * FROM pgmq.a_jobs;

A simple dead-letter pattern is to archive messages that failed too many times:

-- Move messages that have been delivered 5 or more times to the archive
SELECT pgmq.archive('jobs', ARRAY(
  SELECT msg_id FROM pgmq.q_jobs WHERE read_ct >= 5
));

Without a rule like this, a poison message is redelivered forever.

Monitoring

SELECT * FROM pgmq.list_queues();
SELECT * FROM pgmq.metrics('jobs');
SELECT * FROM pgmq.metrics_all();

metrics reports queue_length, queue_visible_length, the age of the newest and oldest messages, and total_messages. Older extension versions return a smaller set of columns.

The queue table can be queried directly:

SELECT count(*) FROM pgmq.q_jobs;

SELECT msg_id, read_ct, enqueued_at, vt, message
FROM pgmq.q_jobs
ORDER BY msg_id DESC
LIMIT 5;

Alert when queue_length keeps growing or oldest_msg_age_sec climbs: either consumers are down or they cannot keep up.

Maintenance

SELECT * FROM pgmq.list_queues();

SELECT pgmq.create_unlogged('scratch');                    -- no WAL, lost on crash
-- Partitioned queues require pg_partman: pg extension install pg_partman -i pg01
SELECT pgmq.create_partitioned('events', '10000', '100000');
SELECT pgmq.purge_queue('jobs');                           -- delete all messages, keep queue
SELECT pgmq.drop_queue('jobs');                            -- queue and archive tables

• create_unlogged trades crash safety for write speed. Use it only for data that can be regenerated.
• create_partitioned requires the pg_partman extension. It keeps a very high-throughput queue from bloating a single table; old partitions are dropped automatically.
• purge_queue empties a queue but keeps it; drop_queue removes it entirely.
• Queue names are limited to 47 characters.

With ordinary queues, heavy send/delete traffic produces dead tuples. Make sure autovacuum keeps up, or archive messages instead of deleting them and consider partitioned queues at high volume.

Permissions

PGMQ functions run with the caller’s privileges. An application role needs access to the schema, the functions and the queue tables:

GRANT USAGE ON SCHEMA pgmq TO app;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA pgmq TO app;
GRANT SELECT, INSERT, UPDATE, DELETE ON pgmq.q_jobs TO app;
GRANT SELECT, INSERT ON pgmq.a_jobs TO app;   -- only when archiving

Run pgmq.create as the queue owner; creating tables requires privileges the application role usually should not have. New queues need their own grants, or you can pre-configure default privileges.

Function reference

Troubleshooting

extension "pgmq" is not available. The extension package is not in the instance image, or the extension has no build for this database. Install it with pg extension install pgmq -i <instance> and create it in the target database.

Messages are redelivered. Processing took longer than vt, or the consumer exited before delete. Increase vt, call set_vt during long jobs, and make processing idempotent.

Queue length never drops. Consumers are failing or too slow. Inspect read_ct, application logs and metrics().oldest_msg_age_sec. PGMQ retries failed messages forever unless they are archived or deleted.

Performance degrades over time. Default queues are ordinary tables. Check dead tuples and autovacuum, batch sends and reads, and consider create_partitioned for sustained high throughput.

permission denied for schema pgmq. Grant USAGE on the schema and EXECUTE on the functions to the application role, plus table privileges on each queue it uses.

3 - SQLite + Litestream

pg sqlite manages SQLite databases directly on a Linux host and can replicate their WAL changes to S3-compatible object storage with Litestream. SQLite runs as a normal host process — there is no container, no Go SQLite driver and no cgo. The sqlite3 CLI executes all SQL; Litestream runs as one detached process per database.

Replication is asynchronous (roughly one second of RPO with the default sync interval). It is continuous backup, not high availability: there is still a single writer, and a failover means restoring a backup somewhere else.

Only Linux is supported.

How it works

• Every managed database has an entry in pg.yaml and a per-name state directory under <base>/sqlite/<name>/.
• pg sqlite create enables journal_mode=WAL, which is persistent in the database header.
• pg sqlite replication enable renders a dedicated litestream.yml (mode 0600) and starts litestream replicate in the background. Litestream tails the WAL and uploads LTX objects to the configured bucket.
• S3 credentials are written only to that litestream.yml. They are never stored in pg.yaml and are never printed by any pg sqlite command.

Install

pg sqlite install

The command is idempotent and installs two host binaries:

1. sqlite3 from the distribution package manager if it is not already on PATH (apt-get, dnf, yum, pacman, zypper or apk). Only this step may need root; pgcli uses passwordless sudo -n and fails with a clear message instead of prompting for a password.
2. Litestream 0.5.17, downloaded from GitHub and verified against the published SHA256 checksum. It goes to /usr/local/bin when writable, otherwise to ~/.local/bin.

Useful flags:

pg sqlite install --install-dir /opt/bin      # choose where Litestream goes
pg sqlite install --force-litestream          # re-download a supported version

Litestream 0.3.x is not compatible with the generated configuration; the installer ignores older binaries on PATH and installs 0.5.x.

Create and manage databases

# Default path: <base>/sqlite/appdb/appdb.db
pg sqlite create appdb
pg sqlite create analytics --path /srv/data/analytics.db

pg sqlite list
pg sqlite info appdb
pg sqlite info appdb --integrity full

pg sqlite exec appdb "CREATE TABLE items (id INTEGER PRIMARY KEY, name TEXT)"
pg sqlite exec appdb "INSERT INTO items (name) VALUES ('first')"

# Read SQL from stdin (no command-line length limit)
pg sqlite exec appdb - <<'SQL'
INSERT INTO items (name) VALUES ('second');
INSERT INTO items (name) VALUES ('third');
SQL

pg sqlite shell appdb
pg sqlite shell appdb -- -header -column

create never truncates an existing file: pointing it at an existing SQLite database adopts the file, enables WAL and leaves the contents untouched. --path must be absolute and only moves the database file — configuration, logs, pid and local Litestream state always stay under <base>/sqlite/<name>/.

info reads journal_mode, page size/count and freelist, then runs PRAGMA quick_check by default. Use --integrity full for a complete integrity_check, or --integrity none to skip it on large databases.

remove unregisters the database and stops replication but leaves the database file in place. Add --clean-data to delete the database, WAL sidecars, local Litestream state and logs (interactive confirmation; --force skips it). Remote S3 backups are never deleted automatically.

Enable S3 replication

The bucket must already exist — Litestream does not create buckets. Create it with mc mb, the cloud console, or another S3 tool first.

Local MinIO for testing

pgcli can host the object store itself with the MinIO addon:

# Install a named MinIO instance; the output prints the S3 API endpoint.
# The default endpoint is http://127.0.0.1:9000 when the port is free.
pg addon install minio --name sqlite-s3

# The root user defaults to "admin"; the password is generated at install.
S3_SECRET=$(pg addon password minio --name sqlite-s3)

# Register the endpoint with the bundled mc wrapper and create the bucket.
pg mc alias set local http://127.0.0.1:9000 admin "$S3_SECRET"
pg mc mb local/sqlite-backups

If port 9000 was already taken, use the endpoint reported by pg addon list instead. Then point replication at the local store:

pg sqlite replication enable appdb \
  --s3-endpoint http://127.0.0.1:9000 \
  --s3-bucket sqlite-backups \
  --s3-access-key admin \
  --s3-secret-key "$S3_SECRET" \
  --s3-uri-style path

Any other MinIO or S3-compatible store uses the same flags with its own endpoint and credentials:

pg sqlite replication enable appdb \
  --s3-endpoint http://127.0.0.1:9000 \
  --s3-bucket sqlite-backups \
  --s3-access-key minioadmin \
  --s3-secret-key minioadmin \
  --s3-uri-style path

AWS S3 with the default credential chain (no keys on the command line):

pg sqlite replication enable appdb \
  --s3-bucket my-org-sqlite-backups \
  --s3-region eu-west-1 \
  --s3-uri-style virtual-host

enable verifies that the database is in WAL mode, resolves a 0.5.x Litestream binary (or uses --litestream-binary PATH), writes litestream.yml with mode 0600, clears stale local Litestream state, starts the process and checks that it survives startup. If it exits immediately, the last log line is printed and the command fails.

Running enable again with identical non-secret settings while replication is running is a no-op. Different settings are rejected unless --force is given; --force stops the running process, rewrites the configuration, resets local state and restarts.

Where the credentials live

Back up or copy litestream.yml with the same care as any credential file. replication disable removes it, so credentials must be supplied again on the next enable.

Operate replication

pg sqlite replication status appdb
pg sqlite replication status              # all databases
pg sqlite replication logs appdb -n 100
pg sqlite replication logs appdb -f
pg sqlite replication stop appdb          # final sync, up to 30s
pg sqlite replication start appdb
pg sqlite replication disable appdb       # stop + remove litestream.yml

status combines the process state with litestream status -json (local transaction id and WAL size) and shows the newest error line from the log. If the status query fails, the command still reports process state and exits successfully.

Check remote backups

pg mc wraps the bundled MinIO client; its flags must come after --. Using the alias created in the Local MinIO example, list what Litestream has uploaded under the configured --s3-path:

pg mc ls local/sqlite-backups/sqlite/appdb -- --recursive

A populated prefix looks like this:

0000/0000000000000001-0000000000000001.ltx
0000/0000000000000002-0000000000000002.ltx
0001/0000000000000001-0000000000000002.ltx
0009/0000000000000001-0000000000000002.ltx

0000/ holds per-transaction WAL frames; higher-numbered levels (0001/, 0009/, …) are merged and compacted files. An empty prefix right after enable only means no write has been synced yet — insert a row, wait at least one --sync-interval (default 1s), then list again. The local transaction id shown by replication status should match the highest txid in these names.

disable keeps the database, local LTX state and logs, but removes the replication block from pg.yaml and deletes litestream.yml. The remote backups remain untouched.

Auto-start on boot

Litestream runs as a detached host process, so it does not survive a reboot on its own. Register the database with the pgcli boot service:

pg autostart enable --sqlite --name appdb
pg autostart status

--name is required (SQLite has no default database name). Replication must already be enabled and its litestream.yml must still exist — the toggle checks both and fails with the usual re-enable hint otherwise. At boot, pg start --autostart restarts Litestream after the object-storage addons, so a same-host MinIO/silo/rustfs/garage/SeaweedFS backend is listening first. The boot path is start-only: it never rewrites litestream.yml and never touches the database file.

pg autostart disable --sqlite --name appdb

pg sqlite replication disable clears this flag automatically. See Auto-start on Boot for the systemd/launchd service details.

Restore

Litestream can restore the latest state or a point in time. Two modes are available:

# Restore a copy to another path; replication keeps running
pg sqlite replication restore appdb --target /tmp/appdb-restored.db

# Point in time by RFC3339 timestamp (UTC with a `Z` suffix)
pg sqlite replication restore appdb --target /tmp/appdb-old.db \
  --timestamp 2026-01-01T12:00:00Z --integrity full

# Point in time by Litestream transaction id (hex; 0x prefix optional)
pg sqlite replication restore appdb --target /tmp/appdb-tx.db --txid 00000000000000ff

# Show what would be restored without writing anything
pg sqlite replication restore appdb --dry-run

--timestamp and --txid are mutually exclusive; omitting both restores the latest state. --timestamp is an absolute instant: write it in UTC with the Z suffix (for example 2026-01-01T12:00:00Z) so the restore point does not depend on the host timezone. --target cannot be combined with --restart, and an existing target requires --force, which first moves the old target and its -wal/-shm files into <target>.pre-restore-<UTC>/.

Point-in-time recovery walkthrough

Capture a transaction boundary before making the change you want to undo:

# Current local transaction id (the `local: ... txid=...` line)
pg sqlite replication status appdb

# Or inspect the uploaded LTX ranges in S3
pg mc ls local/sqlite-backups/sqlite/appdb -- --recursive

LTX object names are <min-txid>-<max-txid>.ltx. A restore by --txid includes the state up to and including that transaction. Record the txid (or an RFC3339 UTC timestamp taken safely before the change), then restore a copy and verify it:

pg sqlite replication restore appdb \
  --target /tmp/appdb-pitr.db \
  --txid 0000000000000004 \
  --integrity full

sqlite3 -readonly /tmp/appdb-pitr.db \
  "SELECT COUNT(*) FROM items; PRAGMA journal_mode;"

The timestamp form follows the same flow:

pg sqlite replication restore appdb \
  --target /tmp/appdb-pitr-ts.db \
  --timestamp 2026-01-01T12:00:00Z \
  --integrity full

Use --dry-run first when unsure: it prints the transaction range that would be used and writes nothing. Restoring to --target does not stop replication, so traffic can continue while you inspect the copy. As a sanity check, restore the latest state (no --timestamp/--txid) and confirm that newer rows are present; only then promote the verified copy with an in-place restore.

In-place restore

Without --target, the configured database is replaced:

pg sqlite replication restore appdb --force

The sequence is deliberately conservative:

1. Litestream is stopped (a final sync runs) and other applications must have been stopped manually first.
2. The current database and its WAL sidecars are moved to <db>.pre-restore-<UTC>/ (confirmation unless --force).
3. The backup is restored into a temporary directory next to the database, so a failed restore never touches the preserved copy.
4. The restored database is checked and switched to WAL if necessary, then moved into place with an atomic rename on the same filesystem.
5. Local Litestream state is reset. If reset fails, the stale state directory is removed.
6. Replication stays stopped by default. --restart starts it again.

Restarting replication into the same --s3-path after a restore can mix the restored timeline with the old remote data. Prefer re-enabling with a new --s3-path, or make sure you understand the old timeline before using --restart.

If the database file is lost entirely, in-place restore works without --force; the per-database litestream.yml must still exist.

WAL and application settings

Only PRAGMA journal_mode=WAL is persisted in the database file by the CLI. Tune the following per connection from the application:

PRAGMA busy_timeout=5000;   -- wait for locks instead of failing immediately
PRAGMA synchronous=NORMAL;  -- recommended with WAL (durability trade-off)
PRAGMA wal_autocheckpoint=1000;

Keep in mind:

• SQLite in WAL mode requires a local filesystem. Do not put the database on NFS or SMB.
• There is still one writer at a time. Long write transactions block other writers; long read transactions prevent WAL checkpointing and can make the WAL grow without bound.
• Litestream replicates the WAL, not an application-consistent snapshot. Use transactions for multi-statement invariants.

Troubleshooting

litestream exited during startup — run pg sqlite replication logs <name> -n 50. The usual causes are a missing bucket, wrong credentials or an endpoint that is not reachable from the host.

no Litestream 0.5.x binary found — run pg sqlite install. A 0.3.x binary on PATH is reported but not used.

TLS errors against MinIO/RustFS — use the full http://host:port endpoint for plain HTTP, or --s3-ca-file / --s3-skip-verify for self-signed HTTPS.

database ... uses journal_mode=delete — the database was not created by pg sqlite create, or another tool changed the mode. Use PRAGMA journal_mode=WAL; with no other connections open, then retry enable.

litestream.yml not found — start, status and restore require the configuration written by enable; re-run enable (credentials are not stored in pg.yaml).

Limits

• Linux only; the macOS host-binary paths are not implemented.
• Litestream is asynchronous backup, not synchronous replication or HA. The default RPO is about one second, and a final sync on stop/disable can take up to 30 seconds.
• One Litestream process and one WAL writer per database.
• --clean-data and in-place restore are destructive; they prompt by default, and the preserved .pre-restore-* directories are left for manual cleanup.

4 - TimescaleDB

TimescaleDB is a PostgreSQL extension for time-series workloads. It turns a regular table into a hypertable: rows are partitioned into time-based chunks automatically, while applications keep using ordinary SQL, indexes and transactions.

pgcli installs the Pigsty TSL build of TimescaleDB 2.31.0. Hypertables, time_bucket, chunk management, columnstore/compression, continuous aggregates and retention policies are available. The TSL permits free internal use but prohibits offering a time-series database as a service.

Use cases

TimescaleDB fits append-heavy data where time is the main access dimension:

• IoT and device telemetry — per-device readings, latest values and time-window aggregates.
• Infrastructure and application monitoring — CPU, memory, latency and request metrics for dashboards and alerting.
• Industrial and energy metering — high-frequency sensor streams with a long retention window.
• Financial market data — ticks, quotes and trades; OHLC-style rollups with time_bucket and first/last.
• Product and event analytics — user events, sessions and API calls aggregated over recent time windows.
• Smart home and building automation — sensor history and state changes.

The typical shape is append-only rows keyed by (entity_id, time): queries filter a recent time range, and old data is expired by dropping whole chunks.

It is a poor fit for OLTP workloads without a time dimension, key-value or document storage, and blob storage. The TSL build includes compression, continuous aggregates and retention policies, but the TSL does not allow offering a time-series database as a service.

Install

pg extension install timescaledb -i pg01 --auto-restart

pgcli builds an extension image with the Pigsty TSL package, puts timescaledb first in shared_preload_libraries, restarts PostgreSQL and runs CREATE EXTENSION IF NOT EXISTS timescaledb in the instance’s default database.

To use it from another database in the same instance:

pg psql -i pg01 -- -d appdb
CREATE EXTENSION IF NOT EXISTS timescaledb;

Check the installed version and license:

pg exec -i pg01 "SELECT extversion FROM pg_extension WHERE extname = 'timescaledb'"
pg exec -i pg01 "SHOW timescaledb.license"

The Pigsty package reports timescale.

Quick start

1. Create an instance

pg create -i tsdb
pg start -i tsdb

pg create writes the configuration and prints the admin password; pg start brings up PostgreSQL and the backup container. The default database is tsdb_db.

2. Install the extension

pg extension install timescaledb -i tsdb --auto-restart

pgcli builds the extension image, preloads timescaledb, restarts PostgreSQL and runs CREATE EXTENSION IF NOT EXISTS timescaledb in tsdb_db.

3. Create a hypertable

Connect to the instance:

pg psql -i tsdb

Create a table and convert it to a hypertable:

CREATE TABLE sensor (
    time      timestamptz NOT NULL,
    sensor_id text NOT NULL,
    value     double precision
);

SELECT create_hypertable('sensor', by_range('time', INTERVAL '1 day'));
CREATE INDEX ON sensor (sensor_id, time DESC);

Or create it in one step; the default chunk interval applies and can be changed afterwards:

CREATE TABLE sensor (
    time      timestamptz NOT NULL,
    sensor_id text NOT NULL,
    value     double precision
) WITH (tsdb.hypertable);

SELECT set_chunk_time_interval('sensor', INTERVAL '1 day');

Choose the chunk interval to fit the query and retention window. Existing chunks keep their interval when you change it later.

4. Write data

INSERT INTO sensor
SELECT now() - (i * INTERVAL '1 minute'),
       'sensor_' || (i % 3 + 1),
       random() * 100
FROM generate_series(1, 1440) AS i;

SELECT time_bucket('1 hour', time) AS bucket,
       sensor_id,
       round(avg(value)::numeric, 2) AS avg_value
FROM sensor
WHERE time > now() - INTERVAL '24 hours'
GROUP BY bucket, sensor_id
ORDER BY bucket DESC, sensor_id;

5. Daily operations

Append new rows, inspect chunks and jobs, and enable automatic maintenance:

INSERT INTO sensor VALUES (now(), 'sensor_1', 42.0);

SELECT show_chunks('sensor');
SELECT * FROM timescaledb_information.jobs;

ALTER TABLE sensor SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'sensor_id',
    timescaledb.compress_orderby = 'time DESC'
);
CALL add_columnstore_policy('sensor', INTERVAL '7 days');
SELECT add_retention_policy('sensor', INTERVAL '365 days');

Operational commands:

pg status -i tsdb               # instance and backup status
pg logs -i tsdb -n 100          # server log tail
pg export -i tsdb -o sensor.dump

See Compression and columnstore, Retention policies, Continuous aggregates and Managing chunks for the full set.

InfluxDB-compatible API: the influx2tsdb addon

If existing tooling speaks InfluxDB rather than SQL, the influx2tsdb addon runs a managed, stateless proxy that serves the InfluxDB 1.x HTTP API (Line Protocol writes and InfluxQL queries) on top of a TimescaleDB database:

• each InfluxDB database becomes a PostgreSQL schema, and each measurement becomes a hypertable with time as the partition column;
• CREATE / ALTER / DROP RETENTION POLICY is translated into TimescaleDB’s own add_retention_policy / add_compression_policy jobs — no pg_cron is needed, and chunk cleanup continues while the proxy is stopped;
• the proxy holds no data, so backup, PITR, replication and HA remain ordinary PostgreSQL operations.

pg addon install influx2tsdb --pg-name tsdb

The trade-off is throughput: every request is translated to SQL, so pure time-series ingest and query load is slower than native InfluxDB. The proxy has no authentication or TLS; front it with nginx or isolate the port. See the addon page for flags, the data model, retention semantics and the test scripts.

Query patterns

First and last value per series:

SELECT sensor_id,
       first(value, time) AS first_value,
       last(value, time) AS last_value
FROM sensor
GROUP BY sensor_id;

Latest row per series:

SELECT DISTINCT ON (sensor_id) sensor_id, time, value
FROM sensor
ORDER BY sensor_id, time DESC;

Daily buckets in a specific timezone:

SELECT time_bucket('1 day', time, 'Asia/Shanghai') AS day,
       count(*)
FROM sensor
GROUP BY day
ORDER BY day DESC;

Fill gaps in a bucket sequence:

SELECT time_bucket_gapfill('1 hour', time) AS bucket,
       avg(value)
FROM sensor
WHERE time BETWEEN now() - INTERVAL '6 hours' AND now()
GROUP BY bucket
ORDER BY bucket;

time_bucket_gapfill needs an explicit start and finish, either as the second and third arguments or in the WHERE clause.

Compression and columnstore

Older chunks can be moved to the columnstore, which compresses them and speeds up analytical scans:

ALTER TABLE sensor SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'sensor_id',
    timescaledb.compress_orderby = 'time DESC'
);

-- Compress chunks older than 7 days; runs as a background job
CALL add_columnstore_policy('sensor', INTERVAL '7 days');

Check the compression status and the space used:

SELECT * FROM hypertable_columnstore_stats('sensor');
SELECT * FROM chunk_columnstore_stats('sensor');

Retention policies

Expire chunks automatically once they fall outside the retention window:

SELECT add_retention_policy('sensor', INTERVAL '365 days');

-- Inspect or remove the policy
SELECT * FROM timescaledb_information.jobs
WHERE proc_name = 'policy_retention';

SELECT remove_retention_policy('sensor');

These jobs run in TimescaleDB’s own background scheduler; pg_cron is not required. The influx2tsdb addon uses the same mechanism: its CREATE / ALTER / DROP RETENTION POLICY statements are translated into these jobs automatically, and cleanup continues while the proxy is stopped.

Continuous aggregates

Pre-aggregate recent data and keep the rollup refreshed in the background:

CREATE MATERIALIZED VIEW sensor_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', time) AS bucket,
       sensor_id,
       avg(value) AS avg_value
FROM sensor
GROUP BY bucket, sensor_id;

SELECT add_continuous_aggregate_policy('sensor_hourly',
    start_offset      => INTERVAL '3 hours',
    end_offset        => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour');

Query the rollup like a normal table:

SELECT * FROM sensor_hourly
ORDER BY bucket DESC
LIMIT 10;

The policy refreshes only the recent window between start_offset and end_offset; refresh older ranges manually after a backfill.

Managing chunks

Chunk intervals

Set the interval at creation, or change it later for new chunks:

SELECT create_hypertable('sensor', by_range('time', INTERVAL '1 day'));

-- Existing chunks keep their ranges; new chunks use 6 hours
SELECT set_chunk_time_interval('sensor', INTERVAL '6 hours');

SELECT time_interval
FROM timescaledb_information.dimensions
WHERE hypertable_name = 'sensor';

For timestamptz columns, chunk boundaries align to UTC. Choose an interval large enough that a chunk fits comfortably in memory; a very small interval creates many chunks and adds planning overhead.

List chunks

SELECT show_chunks('sensor');
SELECT show_chunks('sensor', older_than => INTERVAL '30 days');
SELECT show_chunks('sensor', newer_than => INTERVAL '1 day');

SELECT chunk_name, range_start, range_end
FROM timescaledb_information.chunks
WHERE hypertable_name = 'sensor'
ORDER BY range_start DESC
LIMIT 10;

older_than and newer_than filter by the chunk’s time range, not by its creation time.

Chunk sizes

SELECT chunk_name,
       pg_size_pretty(pg_total_relation_size(
           format('%I.%I', chunk_schema, chunk_name)::regclass)) AS total_size
FROM timescaledb_information.chunks
WHERE hypertable_name = 'sensor'
ORDER BY range_start DESC;

SELECT pg_size_pretty(hypertable_size('sensor'));
SELECT * FROM hypertable_detailed_size('sensor');

Split a chunk

Split one chunk at a timestamp inside its range; take the chunk name from show_chunks('sensor'):

CALL split_chunk('_timescaledb_internal._hyper_1_2_chunk',
                 '2026-10-26 12:00:00+00'::timestamptz);

split_chunk is a procedure; use CALL, not SELECT.

Remove chunks

-- Chunks entirely older than 90 days, or newer than tomorrow
SELECT count(*) FROM drop_chunks('sensor', older_than => INTERVAL '90 days');
SELECT count(*) FROM drop_chunks('sensor', newer_than => now() + INTERVAL '1 day');

drop_chunks removes files instead of producing dead tuples. Avoid deleting large time ranges with DELETE. For automatic expiration prefer a retention policy; pg_cron is only needed for a custom schedule:

pg extension install pg_cron -i pg01 --auto-restart

SELECT cron.schedule('drop-sensor-chunks', '0 3 * * *',
  $$SELECT drop_chunks('sensor', older_than => INTERVAL '90 days')$$);

Manual columnstore conversion

With compression settings configured (see Compression and columnstore), convert a single chunk without waiting for the policy:

CALL convert_to_columnstore('_timescaledb_internal._hyper_1_1_chunk');
SELECT chunk_name, is_compressed
FROM timescaledb_information.chunks
WHERE hypertable_name = 'sensor'
ORDER BY range_start DESC;

CALL convert_to_rowstore('_timescaledb_internal._hyper_1_1_chunk');

Both procedures take a chunk name, not the hypertable name.

Metadata views

SELECT * FROM timescaledb_information.hypertables;
SELECT * FROM timescaledb_information.dimensions;
SELECT * FROM timescaledb_information.chunks;
SELECT * FROM timescaledb_information.jobs;

jobs also lists the columnstore, retention and continuous-aggregate policies created above, alongside the internal job-history task.

Feature availability

Rows marked ✅ Apache follow Apache-2.0. Rows marked ✅ TSL come from the Timescale License: free for internal use, but offering a time-series database as a service is not permitted.

Backup and restore

Logical backups and pg export/pg import work as usual. A physical restore (pgBackRest, used by pg backup/pg restore) also needs TimescaleDB’s restore hooks:

SELECT timescaledb_pre_restore();   -- before replacing the data files, when possible
-- restore the instance
SELECT timescaledb_post_restore();  -- after the restored instance starts

pg restore does not run these automatically, so run timescaledb_post_restore() after the restore completes. Test the procedure before relying on it.

Permissions

CREATE EXTENSION requires a superuser; pg exec connects as the instance admin. The owner of a table can convert it with create_hypertable and manage its chunks. Application roles need only ordinary table privileges.

Troubleshooting

extension "timescaledb" is not available. The package is missing from the instance image, or the extension was not created in the target database. Run pg extension install timescaledb -i <instance> and create it in that database.

timescaledb is not preloaded. The extension is in shared_preload_libraries but PostgreSQL was not restarted. pg extension install handles this; verify with SHOW shared_preload_libraries; and restart the instance.

License errors for a function. The instance image still contains the Apache build (an older pgcli release or a manually installed PGDG package). Reinstall with pg extension install timescaledb -i <instance> --auto-restart to switch to the Pigsty TSL package.

cannot create a unique index without the column "time". Every unique index and primary key on a hypertable must include the partitioning column:

CREATE TABLE sensor (
    time      timestamptz NOT NULL,
    sensor_id text NOT NULL,
    value     double precision,
    PRIMARY KEY (sensor_id, time)
);

Too many chunks. Lower the chunk interval with set_chunk_time_interval; existing chunks are not rewritten.

Old data does not disappear. Add a retention policy with add_retention_policy, or run drop_chunks manually. Check timescaledb_information.jobs for the policy job.

Function reference