KhaqanWhat is 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.
PostgreSQL has two kinds of replication. Physical (streaming) replication copies the write-ahead log byte for byte, so the standby is an exact copy and must run the same major version. Logical replication, built into the core since PostgreSQL 10, decodes the log into row changes (insert, update, delete) for a chosen set of tables and sends them to a subscriber, which applies them as ordinary SQL. The subscriber can be a different major version, a different operating system, or a database with extra indexes and a different physical layout.
That is what makes it useful for upgrades. The traditional routes are pg_dump and restore, which takes the system down for the whole copy, or pg_upgrade, which is fast but still needs an outage and a rehearsed rollback. With logical replication you build the new server, take an initial copy while the old one keeps running, let the subscriber catch up to within seconds, and then, in a short window, stop writes, confirm the lag is zero, repoint the application and resume. The old server is untouched, so rollback is switching back.
There are rules. Every replicated table needs a primary key or a replica identity for updates and deletes to work. Schema changes are not replicated, so the schema must be created on the subscriber first and frozen during the copy. Sequences are not replicated either, so their current values must be set on the new server before it takes writes; forgetting this produces duplicate key errors on the first insert. Because the feature needs PostgreSQL 10 or newer on the publisher, an older source needs the pglogical extension or a dump and restore instead.
The common mistake is skipping the rehearsal. Run the whole switch-over against a copy, time it and script it, and the real one becomes boring, which is what a major upgrade of an ERP database should be.
Related terms
- 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.
- 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.
- 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.