KhaqanWhat is point-in-time recovery (PITR)?
Point-in-time recovery (PITR) is a database backup method, standard in PostgreSQL, that combines a base backup with a continuous archive of write-ahead log (WAL) files, so the database can be restored to any chosen moment, not only to the time of the last backup.
PostgreSQL writes every change to the write-ahead log before applying it to the data files, and PITR relies on that. You take a base backup of the data directory (with pg_basebackup or a tool such as pgBackRest or Barman) and configure archive_command, or a streaming receiver, so that every completed WAL segment is copied to separate storage as it is produced. To recover, you restore the base backup, point the server at the WAL archive, set a recovery target (a timestamp, a transaction ID, a named restore point or a log position) and start it; the server replays the log up to that target and stops.
It matters because the common failure is not a dead disk but a bad command. A nightly dump gets you back to last night; PITR gets you back to the minute before someone ran an update without a WHERE clause, or before a migration script deleted the wrong records, with a recovery point measured in seconds rather than a day. For an ERP, that is the difference between re-keying a day's transactions across every site and losing almost nothing.
An example: an accountant discovers in the afternoon that a bulk import at half past two corrupted hundreds of invoice lines. With PITR you restore to 14:29 on a spare server, extract the correct rows and repair the live database, or fail over to the restored copy if the damage is wide.
The common mistake is archiving WAL to the same disk or server as the database, which turns a hardware failure into total loss, closely followed by never performing a restore. An archive that has never been replayed is a hope, not a backup. Test the full restore on a schedule and time it, so the recovery time is a known number rather than a guess.
Related terms
- logical replication (PostgreSQL major upgrade): Logical replication is a PostgreSQL feature that streams row-level changes from a publisher database to a subscriber, and because it works across major versions it allows an upgrade with minimal downtime: replicate to the new version, let it catch up, then switch over.
- ERP health check: An ERP health check is a structured review of an ERP system that tests whether its data, customisations, integrations, backups, performance and user access are sound, and produces a prioritised list of risks and fixes before they turn into outages or wrong numbers.
- Odoo: Odoo is a modular, open-source ERP and business application suite from Belgium, written in Python on PostgreSQL, that covers accounting, inventory, manufacturing, sales, CRM, HR, websites and more, available as a free Community edition and a paid Enterprise edition.