Instant PostgreSQL clones with copy-on-write

Copying a 200 GB database used to mean copying 200 GB. Copy-on-write (CoW) changes the arithmetic: the filesystem creates a second directory tree that shares the same physical extents, and only pages written later by either side consume new space. The clone appears in seconds and starts at near-zero extra disk. PostgreSQL 18 makes this a first-class operation for single databases, and any reflink-capable filesystem makes it possible for whole instances.

Why instant clones

• One database per pull request. CI runs migrations and integration tests against a full-size copy of real data, then throws it away.
• One sandbox per agent task. An agent can mutate, drop, and retry without touching shared state — a natural fit with agent workflows driven over MCP.
• Migration and upgrade drills. Rehearse the schema migration against the production shape, measure duration, then delete the rehearsal copy.
• Incident debugging. Give engineers a frozen copy of the broken state instead of poking at production.

The common requirement is that the copy be cheap enough to be disposable. pg_dump + restore fails that bar at size: it is minutes to hours, doubles storage for the duration, and rebuilds every index.

The PostgreSQL 18 building blocks

CoW copies rely on reflinks: a file copy where source and destination share extents until either is written. Filesystem support is the hard prerequisite — XFS formatted with reflink=1 (the default on modern distributions), Btrfs, and APFS all support it; OpenZFS added block cloning in 2.3. A one-line check tells you whether your mount qualifies:

cp --reflink=always /srv/pg/somefile /tmp/reflink-test && rm /tmp/reflink-test
# fails with "Operation not supported" on filesystems without reflinks

On top of that, PostgreSQL 18 adds the server setting file_copy_method (copy by default, clone to opt in). With clone, the file copies made by CREATE DATABASE ... STRATEGY = FILE_COPY and by ALTER DATABASE ... SET TABLESPACE use copy_file_range() on Linux or copyfile() on macOS, which become reflinks on capable filesystems and raise an error where cloning is unsupported.

Note the boundary: in PostgreSQL 18, initdb and pg_basebackup still perform plain block-by-block copies — there is no reflink option for them. Instance-level CoW comes from the filesystem (a reflink copy or a snapshot), not from a PostgreSQL flag.

Clone a whole instance with reflinks

The consistent path: stop the source cleanly, copy with reflinks, bring the copy up as a separate instance.

# 1. clean stop — fast mode finishes a checkpoint and waits for clients
pg_ctl -D /srv/pg/18/main stop -m fast

# 2. CoW copy of the entire data directory — seconds, near-zero extra space
cp -a --reflink=always /srv/pg/18/main /srv/pg/18/clone-pr482

# 3. start the source again
pg_ctl -D /srv/pg/18/main start

# 4. adjust what must differ, then start the clone as its own instance
echo 'port = 55432' >> /srv/pg/18/clone-pr482/postgresql.conf
pg_ctl -D /srv/pg/18/clone-pr482 start

What must differ between source and clone: port (and any listen_addresses/socket directory assumptions), data_directory if it is set explicitly, and anything in postgresql.auto.conf that pins resources per instance. If the cluster uses additional tablespaces, those directories live outside the data directory and must be reflink-copied too, with the pg_tblspc symlinks repointed.

If stopping the source is not acceptable, use an atomic filesystem snapshot instead of a plain copy — a Btrfs/ZFS/LVM snapshot taken in one instant yields a crash-consistent image, and PostgreSQL replays WAL on first start exactly as after a power failure. A plain cp (reflink or not) of a running data directory is neither atomic nor consistent: files change while the copy walks the tree, and the result may refuse to start or, worse, start with subtle corruption. Do not treat that as a shortcut.

Clone one database with CREATE DATABASE

Inside one instance, CREATE DATABASE ... TEMPLATE copies a database at the file level. The STRATEGY option (PostgreSQL 15+) plus PostgreSQL 18's file_copy_method = clone turns that into a CoW clone:

SET file_copy_method = 'clone';

CREATE DATABASE app_pr482
  TEMPLATE app
  STRATEGY = FILE_COPY;

Constraints, per the official documentation:

• No other session may be connected to the template database; CREATE DATABASE fails if one is, and new connections to the template are locked out until the copy finishes.
• FILE_COPY forces a checkpoint before and after the copy, which can be noticeable on a busy system.
• Database-level configuration (ALTER DATABASE ... SET) and database-level GRANTs are not copied.
• The default strategy WAL_LOG copies block by block through WAL — slower and full-size, but unaffected by filesystem reflink support.

The result is a normal database on the same instance: same roles, same port, isolated schema and data, sharing unmodified extents with the template until either side writes.

Choosing a method

Reach for pg_dump when the clone must move across versions, platforms, or instances — logical copies are portable in ways file-level copies are not.

Relation to branching platforms

Managed branching platforms productize exactly this idea. Neon implements branches in its storage layer: a branch is a copy-on-write fork of the data at a point in time, created via API in seconds and billed for the delta. The self-hosted techniques on this page give you the same primitive without the platform — you trade the API and the billing model for a few shell commands and the operational boundaries listed below.

Risk checklist

• A clone is not a backup. Source and clone share physical extents; a storage-level corruption hits every clone at once, and deleting the source frees nothing while clones exist. Real, independent backups remain mandatory — see Backup, recovery, and PITR.
• WAL consistency. Only a clean shutdown copy or an atomic snapshot produces a safe clone. A live plain copy can yield a directory that fails crash recovery or starts with torn state.
• Disk accounting lies. df and PostgreSQL's size functions do not reflect shared extents; capacity alerts calibrated on full copies will misreport. Monitor the filesystem's actual allocation.
• Same filesystem only. Reflinks cannot cross mounts or hosts — the clone must live on the same filesystem as the source.
• Sharing erodes with writes. Every first write to a shared extent allocates new blocks on that side. Long-lived, heavily written clones converge back toward full size; design CI lifetimes accordingly.
• Template lock-out. Per-database clones freeze connections to the template database for the duration; on CoW filesystems that duration is seconds, on plain copies it scales with size.

I want every pull request to get its own PostgreSQL database clone in CI.
Context: PostgreSQL (version), filesystem (XFS/btrfs/other), database size (GB), CI system (e.g. GitHub Actions).
Please:
1. Choose between CREATE DATABASE ... TEMPLATE ... STRATEGY = FILE_COPY with file_copy_method = clone and a whole-instance reflink clone, and justify the choice for my context.
2. Write the exact SQL/commands, including how to guarantee no connections to the template database during the clone.
3. Add teardown (DROP DATABASE / remove the clone data directory) plus scheduled cleanup of leaked branches.
4. List the risks specific to copy-on-write clones in CI and how to monitor disk usage correctly.