A PostgreSQL minor update is meant to be a binary swap and restart; what can its release notes still require, with 18.x examples?
answer
- the Migration to Version section
- every skipped minor counts
- fixed code, stale index contents
- a new setting with a strict default
basics
~20 sA PostgreSQL minor's Migration section can still ask you to reindex indexes the old code built wrongly, run ANALYZE, clean affected data or change configuration; 18.6 added output_plugin_libraries, which blocks third-party logical decoding plugins until they are listed.
solid answer
~40 sEvery minor's notes open with "Migration to Version 18.x": no dump/restore, but a list of post-update actions, and you must read it for every minor you skip; 18.6's points anyone coming from before 18.2 to the 18.2 notes. **Reindex** when a fix changes how index entries compare, because the new code does not repair entries already written: 18.2 asks it for indexes on `ltree` columns, 18.6 for `btree_gist` indexes on float columns that may hold NaN or on bit columns. **ANALYZE** when a stored statistic was wrong: parallel GIN builds could leave a bogus `pg_class.reltuples` that does not self-heal. **Configuration**: 18.6, with 17.11, 16.15, 15.19 and 14.24, added `output_plugin_libraries`, default `'pgoutput, test_decoding'`, so slots using other decoding plugins fail until you list them. **Data cleanup**: pgcrypto data encrypted under a silent cipher failure.
code
sql · 9 lines-- Before updating to 18.6: which logical decoding plugins do existing slots use?
SELECT DISTINCT plugin FROM pg_replication_slots WHERE plugin IS NOT NULL;
-- After updating: tables with GIN indexes; check that reltuples looks sane
SELECT DISTINCT t.oid::regclass, t.reltuples
FROM pg_class t
JOIN pg_index i ON t.oid = i.indrelid
JOIN pg_class ic ON i.indexrelid = ic.oid
WHERE t.relhasindex AND ic.relam = 2742;go deeper
Know that each minor's release notes have a Migration section and that it can ask for work after the restart.
Explain why fixed code leaves stale index entries or statistics behind, and read the notes of every skipped minor, not only the newest.
Turn the notes into conditions you can query, such as extensions, index definitions and slot plugins, so each server gets exactly the actions that apply.
Make release-note review a standing step with an owner, so post-update actions are tracked to completion across environments rather than remembered.
## Where the obligations are written Every PostgreSQL minor release has release notes, and each one opens with a section called **"Migration to Version 18.x"**. Its first sentence is nearly always the same: a dump/restore is not required for those running 18.X. The sentences after it are the part to read. They list what an administrator must do **after** installing the new binaries, beyond the restart. Two rules make this reliable: - **Read the notes of every release you skip.** Minors are cumulative in their code, but each post-update action is written down only once, in the release that introduced it. The 18.6 notes say so outright: anyone upgrading from a version earlier than 18.2 must also see the 18.2 notes. - **There is no 18.5.** It was withdrawn after a regression found post-wrap, so from 18.1 to 18.6 the notes to read are 18.2, 18.3, 18.4 and 18.6. ## Why a fixed binary can leave broken data behind A minor release changes code, never the storage format. That is exactly why some fixes need follow-up work: if the old code wrote something wrong, the new code fixes future writes but cannot rewrite what is already on disk. - An **index** built with a buggy comparison stores entries in an order the corrected comparison disagrees with. Searches through it can then miss rows. Only rebuilding the index fixes it. - A **statistic** stored with a wrong value stays wrong until something recomputes it, and if the wrong value stops autovacuum from ever looking at the table, nothing does. - **Data** written by buggy code stays as written. ## The four kinds of post-update action, with 18.x examples | Kind | 18.x example | What you do | |---|---|---| | Reindex | 18.2: indexes on `ltree` columns, because index routines case-folded differently from the operators; 18.6: `btree_gist` indexes on float columns that may contain NaN, and on bit/varbit columns; B-tree indexes holding `ltree` values with more than about 14,653 labels | Find the affected indexes and rebuild them, ideally with `REINDEX CONCURRENTLY` | | Re-analyze | 18.6: parallel GIN index builds could store a bogus `pg_class.reltuples`, even NaN or Infinity, which can stop autovacuum and autoanalyze from ever processing the table | Check tables with GIN indexes and run `ANALYZE` (or build another index) where the value is wrong | | Configuration | 18.6, 17.11, 16.15, 15.19 and 14.24: new `output_plugin_libraries`, default `'pgoutput, test_decoding'`; slots and connectors using other logical decoding plugins are refused until listed | Before updating, list the plugins in use and add the trusted ones after the update | | Data cleanup | 18.6: pgcrypto PGP encryption had silently fallen back to no real encryption when OpenSSL rejected the cipher; decrypting such messages now fails | Use the new `ignore-cipher-failure` option to recover them and re-encrypt with a supported cipher | Not every step applies to every server. The `ltree` and `btree_gist` items matter only where those extensions are installed and indexed; the GIN item only on 18; the plugin item only where logical decoding uses something other than `pgoutput` or `test_decoding`. ## A routine for every minor update 1. Note the current minor of each server and list every release between it and the target. 2. Read each of those releases' Migration sections and write down every action with the condition that triggers it. 3. Run queries that tell you which servers meet each condition: installed extensions, index definitions, the plugins of existing replication slots. 4. Apply prerequisites that must be in place first, such as noting the slot plugins before the restricting setting arrives. 5. Install, restart, and confirm the running version. 6. Run the post-update actions: reindexing, `ANALYZE`, configuration changes, data cleanup. ## What a minor still never asks - No `initdb`, no `pg_upgrade`, no dump and restore: the storage format of the major does not change. - No application rewrite for new features, since minors add no features to adopt; a security fix may add a parameter or an option, as `output_plugin_libraries` and pgcrypto's `ignore-cipher-failure` show, and the notes say who is affected. - No change to the SQL your applications send, apart from rare fixes to behaviour that was itself a bug, which the notes describe. The point interviewers look for is that "binary swap and restart" is the mechanism, while the release notes are the checklist.
- Why can't the new PostgreSQL binary simply repair an index that an older minor built incorrectly?Because a minor release changes code, not stored data. The index pages already hold entries placed by the old comparison, and the corrected comparison expects a different order, so lookups can miss rows. Nothing rewrites those pages on startup; only rebuilding the index does, which is why the notes say which indexes to rebuild.
- How do you find out whether PostgreSQL 18.6's output_plugin_libraries change will affect a server?Before updating, run `SELECT DISTINCT plugin FROM pg_replication_slots WHERE plugin IS NOT NULL;`. Anything other than `pgoutput` or `test_decoding` will be refused after the update until it is added to `output_plugin_libraries`, which takes a reload. Refused requests log an error naming the library and the setting.
- A minor release never needs anything beyond swapping binaries and restarting.
- Reading the newest minor's notes is enough because minors are cumulative.
- Restarting on the fixed binary automatically repairs indexes built by the buggy code.
- Autovacuum will correct a bogus reltuples value on its own after the update.
- Minor releases cannot add configuration parameters.
Assumes PostgreSQL 18.6, the current 18.x minor; the examples come from the 18.2 and 18.6 notes, and output_plugin_libraries arrived in 18.6, 17.11, 16.15, 15.19 and 14.24.