pg-upgrade-internalslisted

PostgreSQL's `pg_upgrade` — the tool that upgrades a data directory in-place from one major version to another without a dump-restore. Covers `src/bin/pg_upgrade/` architecture, the pre-upgrade checks, catalog dump-restore, relfilenode preservation (or rewriting), the two allocation strategies (link vs copy vs clone), and the "check for known upgrade issues" list. Loads when the user asks about pg_upgrade internals, common upgrade failures ("could not connect to source cluster", ERROR at pg_dump extraction, relfilenumber mismatch), --link vs --clone vs --copy tradeoffs, upgrade-blocking features (unlogged tables, sequences, extensions), or extension upgrade paths. Skip when the ask is about major-version release notes semantics (see `wiki-distilled` corpus) or about pg_dump alone.
matejformanek/postgres-claude · ★ 0 · AI & Automation · score 70

Install: claude install-skill matejformanek/postgres-claude

# pg-upgrade-internals — the in-place major-version upgrader `pg_upgrade` avoids the classic dump-restore round trip for major version upgrades (13→14, 14→15, etc.). Instead: 1. Extract catalog schema from OLD cluster via pg_dump. 2. Load that schema into NEW cluster (which is initdb'd fresh). 3. Copy / link / clone the DATA files (heap + index) from OLD to NEW. 4. Fix up sequences, set relfilenumbers, invalidate catalog caches. 5. Old cluster is left intact but not used; can be deleted. The wins: minutes instead of hours for TB-scale databases. The costs: OLD and NEW must be compatible (same architecture, same tablespaces, no on-disk-format-breaking changes). ## The file map Under `src/bin/pg_upgrade/`: | File | Role | |---|---| | `pg_upgrade.c` | Main driver — orchestrates the sequence. | | `check.c` | Pre-upgrade checks: OLD/NEW cluster state, blocking features, extension versions. | | `controldata.c` | Reads `pg_control` from both clusters — critical for catalog-version alignment. | | `dump.c` | Calls `pg_dumpall --binary-upgrade` on OLD cluster. Special dump mode that preserves OIDs. | | `exec.c` | Utility for running external commands (pg_dumpall, pg_dump, psql). | | `file.c` | The data-file relocation — implements `--link`, `--copy`, `--clone` (aka `--copy-file-range` on Linux). | | `function.c` | Extension function checking. | | `info.c` | Enumerates relations + their storage paths. | | `option.c` | Command-line + config file processing. | | `parallel.c` | Paral