skip to content

In PostgreSQL 18, how do CREATE DATABASE's WAL_LOG and FILE_COPY strategies differ, and what does setting file_copy_method to clone change?

level: seniorimportance: nice to knowfreq 17%

answer

  1. WAL volume versus checkpoints
  2. small templates favour the default
  3. two forced checkpoints
  4. kernel-level block sharing
  5. new parameter in 18

basics

~20 s

In PostgreSQL 18, WAL_LOG (the default) copies the template block by block, WAL-logging each block. FILE_COPY copies directories with one WAL record per tablespace but forces two checkpoints; file_copy_method = clone lets that copy share disk blocks.

solid answer

~40 s

`CREATE DATABASE ... STRATEGY` chooses how the template is copied. **`WAL_LOG`**, the default, copies the template **block by block and writes each block to the WAL**: cheap for a small template, but a large one produces WAL roughly its own size, which standbys must replay and the archive must store. **`FILE_COPY`**, the older method, copies each tablespace's database directory at the file-system level and writes **one small WAL record per tablespace**, but it **forces a checkpoint before and after** the copy, an I/O spike for the whole server. PostgreSQL 18 adds **`file_copy_method`** (default `copy`): set to `clone`, it uses `copy_file_range()` on Linux and FreeBSD or `copyfile` on macOS, so a capable file system can share blocks instead of copying them. It affects `FILE_COPY` and `ALTER DATABASE ... SET TABLESPACE` only.

code

sql · 8 lines
sql
-- clone a large reference database for a new lab project, outside peak hours
SET file_copy_method = clone;   -- PostgreSQL 18; needs a cloning-capable file system
CREATE DATABASE genome_proj_42
    TEMPLATE genome_ref_template
    STRATEGY = FILE_COPY;

-- a small template: the default WAL_LOG strategy is the right choice
CREATE DATABASE neuro_lab TEMPLATE lab_template;

go deeper

for a junior

Know that CREATE DATABASE copies a template and that the copy method can be chosen.

for a middle

Explain that WAL_LOG logs every block while FILE_COPY logs one record per tablespace but forces checkpoints.

for a senior

Pick the strategy for a large template on a replicated, busy cluster, weighing WAL volume and standby lag against two forced checkpoints, and use clone where the file system supports it.

for a principal

Ask whether cloning large databases should be a database operation at all, or a storage-level or snapshot-based provisioning step with clearer cost and isolation.

## Two ways to copy a template `CREATE DATABASE` always builds the new database as a copy of a template (`template1` unless `TEMPLATE` names another). The `STRATEGY` option, also available as `createdb -S` or `--strategy`, chooses **how** the files get copied. The choice trades WAL volume against checkpoints. It matters little for an empty template and a lot for a large one, such as a 150 GB reference-genome database that a research cluster clones for every new lab project. | | `WAL_LOG` (default) | `FILE_COPY` | |---|---|---| | How the copy is made | block by block, each block written to the WAL | whole database directories copied at the file-system level | | WAL generated | about the size of the template | one small record per tablespace copied | | Checkpoints | none forced | forced immediately **before and after** the copy | | Best for | small templates (the usual case) | large templates, where WAL volume would hurt | | Affected by `file_copy_method` (18) | no | yes | ## WAL_LOG With `WAL_LOG` each block of the template is copied and written to the write-ahead log. Standbys and point-in-time recovery see an ordinary stream of page images, so nothing special happens elsewhere. The cost is volume: cloning 150 GB writes about 150 GB of WAL, which swells `pg_wal`, stretches the WAL archive and the backups built from it, and makes streaming standbys lag while they replay it. For the small templates most sites use, this is the cheapest option, which is why it is the default. ## FILE_COPY With `FILE_COPY` PostgreSQL copies the template's directory in each tablespace as files and logs only one record per tablespace saying the copy happened. To make that safe it forces two checkpoints: 1. **Before the copy**, so every dirty buffer is flushed and the files on disk are a consistent image of the template. 2. **After the copy**, so ordinary crash recovery never has to replay the copy record and recopy a template that may have changed since; the record is still written for standbys and point-in-time recovery. Both are immediate checkpoints that flush dirty buffers across the whole server, so on a busy cluster they cause a visible I/O spike. A standby repeats the directory copy itself when it replays the record. Checkpoint mechanics belong to the WAL-durability leaf. ## file_copy_method in PostgreSQL 18 PostgreSQL 18 added the `file_copy_method` parameter, a user-settable enum: - `copy` (default): read and write the file contents, as before. - `clone`: use `copy_file_range()` (Linux, FreeBSD) or `copyfile` (macOS), giving the kernel the chance to **share disk blocks** or push the work down to the storage layer. On a file system with copy-on-write cloning, a large `FILE_COPY` clone can finish far faster and use little extra space until the copies diverge. The value exists only if the server was built on a platform with one of those calls. It affects exactly two commands: `CREATE DATABASE ... STRATEGY = FILE_COPY` and `ALTER DATABASE ... SET TABLESPACE`. It does nothing for `WAL_LOG`, and nothing for `ALTER TABLE ... SET TABLESPACE`. Before 18 there was no such setting, and `FILE_COPY` always copied bytes. ## What does not change with either strategy - **The source must be idle.** `CREATE DATABASE` fails if any other session is connected to the template, and new connections to it are locked out during the copy. - **No transaction block.** `CREATE DATABASE` cannot run inside `BEGIN ... COMMIT`. - **The result is identical.** Both strategies produce the same database; only the WAL, checkpoint and I/O profile differ. - **The same privileges apply.** You need superuser or the CREATEDB attribute, and a source not marked `IS_TEMPLATE` can be cloned only by its owner or a superuser. ## Choosing for the research cluster - Measure first: compare `pg_database_size('genome_ref_template')` with the WAL burst your archive and standbys can absorb. - Small lab template: keep `WAL_LOG`. - Large reference template, streaming standbys, busy primary: `WAL_LOG` costs WAL and standby lag; `FILE_COPY` costs two checkpoints. Run `FILE_COPY` in a quiet window, and on a cloning-capable file system set `file_copy_method = clone` for that session to make the copy itself cheap.

  • Why does WAL_LOG make streaming standbys lag when cloning a large template?
    Every copied block becomes a WAL record, so the primary emits WAL roughly the size of the template in a short burst. Each standby must receive and replay all of it, and the WAL archive stores it too. With `FILE_COPY` the WAL record is tiny and the standby performs its own directory copy during replay.
  • Does file_copy_method = clone speed up ALTER TABLE ... SET TABLESPACE?
    No. In PostgreSQL 18 it affects only `CREATE DATABASE ... STRATEGY = FILE_COPY` and `ALTER DATABASE ... SET TABLESPACE`. Moving a single table copies its blocks through the normal relation copy path under an `ACCESS EXCLUSIVE` lock, whatever `file_copy_method` says.

  • FILE_COPY is the default strategy for CREATE DATABASE
  • WAL_LOG forces a checkpoint before and after the copy
  • CLONE is a third value of the STRATEGY option
  • file_copy_method also changes how ALTER TABLE SET TABLESPACE copies
  • FILE_COPY writes every copied block to the WAL

Assumes PostgreSQL 18; file_copy_method is new in 18, so on 17 and earlier FILE_COPY always copies file contents.