In PostgreSQL, which settings turn on continuous WAL archiving, and what must an archive_command guarantee before it exits with status zero?
answer
- three settings, one needs a restart
- %p is the path, %f the name
- zero exit means safe to recycle
- never overwrite a different existing file
basics
~10 sSet wal_level to replica or higher, archive_mode = on (restart required) and either archive_command or archive_library. The command must exit zero only after the segment is durably stored, because PostgreSQL then recycles it.
solid answer
~40 sArchiving needs `wal_level` at `replica` or `logical` (the default is `replica`), `archive_mode = on`, which only takes effect at server start, and an archiver: either a shell `archive_command` or a loadable `archive_library`, both changeable with a reload and mutually exclusive. In the command, `%p` expands to the segment's path relative to the data directory and `%f` to its file name. The contract is the exit status: zero tells PostgreSQL the segment is safely archived, so it may remove or recycle it from `pg_wal`; non-zero means it retries later. So the command must report success only after the copy is durable, and it should refuse to overwrite an existing file with different contents; the manual's example guards `cp` with `test ! -f`. `archive_mode = always` also archives on a standby.
code
ini · 5 lines# postgresql.conf on the legal-documents primary
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /mnt/wal_archive/%f && cp %p /mnt/wal_archive/%f'
archive_timeout = 60go deeper
Recall the settings involved, that archive_mode needs a restart while the command reloads, and what %p and %f expand to.
Explain the exit-status contract: zero lets PostgreSQL recycle the segment, so the copy must be durable first, and the command must not overwrite a file with different contents.
Show you would test the command against an existing file, monitor pg_stat_archiver after enabling it, and choose deliberately between a shell command, an archive module and a backup manager's archiver.
Weigh what archiving costs and how a gap is detected against shipping WAL by streaming, and decide who is paged when archiving stops.
## What continuous archiving gives you in PostgreSQL PostgreSQL writes every change to the **write-ahead log (WAL)** in `pg_wal/`, cut into **segment files** of 16MB by default (the size is fixed at `initdb`). Without archiving, segments older than the last checkpoint are simply recycled. **Continuous archiving** copies every completed segment somewhere safe before it is recycled. Together with a base backup (taking one is covered with base backups), that unbroken series of segments is what lets a restore replay forward to a chosen moment. One gap in the series ends the road: no backup older than the gap can roll forward past it. ## The settings and when each takes effect | Setting | Values and default | Takes effect | |---|---|---| | `wal_level` | `minimal`, `replica` (default), `logical` | server restart | | `archive_mode` | `off` (default), `on`, `always` | server restart | | `archive_command` | shell command, default empty | reload | | `archive_library` | archive module name, default empty | reload; the archiver process is restarted | | `archive_timeout` | seconds, default `0` (off) | reload | - `archive_mode` cannot be enabled while `wal_level = minimal`, because that level skips WAL for some bulk operations; `wal_level` itself is covered with WAL durability settings. - `archive_mode` is deliberately separate from the command, so you can change *how* you archive with a reload without leaving archiving mode. - `on` archives only while the server runs as a primary. `always` also archives on a standby, every segment it restores or receives by streaming. - If `archive_mode` is on but both the command and the library are empty, archiving is only paused: segments accumulate in `pg_wal/` and the archiver logs a warning that archiving is not configured. ## The archive_command contract In `archive_command`, `%p` is replaced by the segment's path relative to the data directory and `%f` by its file name; `%%` is a literal percent sign. The command runs as the operating-system user that runs the server, so the archive should not be readable by anyone else: it contains effectively the whole database. What PostgreSQL does next depends on how the command ends: 1. **Exit status zero** means the segment is safely archived. PostgreSQL may now remove or recycle it from `pg_wal/`, so the copy must already be durable: written and flushed, not merely queued. 2. **Non-zero exit status** means it was not archived. The archiver retries the same segment later and does not move past it. 3. **Killed by a signal** other than `SIGTERM`, or a shell error such as *command not found*: the archiver process aborts and the postmaster restarts it. Three further rules protect the archive itself: - **Refuse to overwrite.** If two clusters are pointed at one directory by mistake, the second must fail loudly rather than silently replace the first one's history. The manual's example guards the copy with `test ! -f`. - **Accept an identical re-archive.** After a crash PostgreSQL may archive a segment again. If the existing file has identical contents and is durably stored, return zero; if its contents differ, return non-zero. - **Do not trust `cp -i`.** GNU `cp` returns zero with `-i` when the target exists, which is exactly the wrong signal. ## archive_command or archive_library `archive_library` loads an **archive module** written in C instead of forking a shell for every segment. The contrib module `basic_archive` is the reference example: it copies segments into the directory named by `basic_archive.archive_directory`. Modules can be faster and can use server facilities, but writing one is far more work than a shell line, and most teams get one through a backup manager; pgBackRest, Barman and WAL-G are covered with those tools. The two settings are mutually exclusive: if both are set, an error is raised. ## Checking that it works After the restart, force a segment boundary and watch the archiver: ```sql SELECT pg_switch_wal(); SELECT archived_count, last_archived_wal, last_archived_time, failed_count FROM pg_stat_archiver; ``` - `last_archived_wal` should advance within seconds and `failed_count` should stay where it was. - Confirm the file actually exists in the archive and can be read back by the `restore_command` you will use in a recovery. - Remember the archive holds no configuration: `postgresql.conf`, `pg_hba.conf` and `pg_ident.conf` are edited by hand, not through WAL, and need their own backup.
- What happens in PostgreSQL if archive_mode is on but archive_command and archive_library are both empty?Archiving is paused, not off. PostgreSQL keeps every completed segment in `pg_wal` as waiting to be archived and logs a warning that `archive_mode` is enabled yet archiving is not configured. That is the documented way to stop archiving briefly while you fix the destination, but WAL accumulates until a command or library is supplied, so a long pause fills the disk.
- Why can a quiet PostgreSQL database leave recent commits unarchived for hours, and what does archive_timeout change?The archiver only sees completed segments, normally 16MB each, so on a quiet server the current segment can stay open for a long time. `archive_timeout`, in seconds and off by default, forces a switch to a new segment after that interval if there was any activity. Each switched file is still full size, so very short values bloat the archive; the manual suggests about a minute and points to streaming for faster shipping.
- When would you set archive_mode = always instead of on?With `on`, the archiver is idle during recovery and standby mode and starts only after promotion. `always` makes a standby archive every segment it restores or receives by streaming, which lets it keep its own archive. Against a shared archive the command must then return success for an identical existing file and failure for a different one, without races between the two servers.
- archive_mode can be switched on with a reload; no restart is needed
- If archive_command fails, PostgreSQL skips that segment and archives the next one
- cp -i is enough to stop an archive_command overwriting an existing file
- Setting archive_command to /bin/true is a harmless way to pause archiving
- archive_command and archive_library can both be set as a primary and fallback pair
- wal_level = minimal is fine for archiving as long as archive_mode is on