[PATCH] Doc: clarify that pg_upgrade statistics transfer works cross-version
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t77955psql -h localhost -U postgresBuilt from patchset v1 (message #1), July 27, 2026 at 02:58 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t77955_1 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t77955_1 && git checkout t77955_1Patchset v1 (message #1) is on t77955_1
Hi hackers,
I initially assumed that PG18's statistics transfer during pg_upgrade
would only work when upgrading from PG18 to a future version. I hear
this misconception from others fairly often too.
In reality, it works when upgrading *to* PG18 from any older supported
version (PG14, 15, 16, 17), because pg_upgrade uses the new cluster's
pg_dump, which reads from standard catalog views (pg_class and
pg_stats) that exist in all PostgreSQL versions. The pg_dump docs
already note that "pg_dump can also dump from PostgreSQL servers older
than its own version" — so no changes needed there. The confusion
seems specific to the major upgrade context.
The attached patch adds a brief clarification to pgupgrade.sgml to address this.
I tested pg_dump --statistics-only from PG18 against PG16 and PG14 —
both work as expected, stats are transferred and restored correctly.
--
Nik
On Mon, 2026-03-23 at 22:30 -0500, nik@postgres.ai wrote:
I initially assumed that PG18's statistics transfer during pg_upgrade
would only work when upgrading from PG18 to a future version. I hear
this misconception from others fairly often too.In reality, it works when upgrading *to* PG18 from any older supported
version (PG14, 15, 16, 17), because pg_upgrade uses the new cluster's
pg_dump, which reads from standard catalog views (pg_class and
pg_stats) that exist in all PostgreSQL versions. The pg_dump docs
already note that "pg_dump can also dump from PostgreSQL servers older
than its own version" — so no changes needed there. The confusion
seems specific to the major upgrade context.The attached patch adds a brief clarification to pgupgrade.sgml to address this.
I tested pg_dump --statistics-only from PG18 against PG16 and PG14 —
both work as expected, stats are transferred and restored correctly.
I agree that it would be a good idea to explicitly mention that
statistics are transferred when upgrading from older PostgreSQL
versions. However, I think that your patch offers too much detail,
like from which relations the statistics are extracted. Anybody
who wants to know that level of detail probably has to read the
source code anyway.
How about adding a simple remark, like
--- a/doc/src/sgml/ref/pgupgrade.sgml
+++ b/doc/src/sgml/ref/pgupgrade.sgml
@@ -837,6 +837,8 @@ psql --username=postgres --file=script.sql postgres
all statistics, such as those created explicitly with
<xref linkend="sql-createstatistics"/>, custom statistics added by
an extension, or statistics collected by the cumulative statistics system.
+ Transferring optimizer statistics works regardless of the old cluster's
+ major version.
</para><para>
Yours,
Laurenz Albe
On Mon, Mar 23, 2026 at 23:59 Laurenz Albe <laurenz.albe@cybertec.at> wrote:
...
I think that your patch offers too much detail,
like from which relations the statistics are extracted. Anybody
who wants to know that level of detail probably has to read the
source code anyway.How about adding a simple remark, like
--- a/doc/src/sgml/ref/pgupgrade.sgml +++ b/doc/src/sgml/ref/pgupgrade.sgml @@ -837,6 +837,8 @@ psql --username=postgres --file=script.sql postgres all statistics, such as those created explicitly with <xref linkend="sql-createstatistics"/>, custom statistics added by an extension, or statistics collected by the cumulative statistics system. + Transferring optimizer statistics works regardless of the old cluster's + major version. </para><para>
That would work too -- my goal was just to resolve confusion.
Nik
Show quoted text
On Tue, Mar 24, 2026 at 05:36:42AM -0700, Nikolay Samokhvalov wrote:
On Mon, Mar 23, 2026 at 23:59 Laurenz Albe <laurenz.albe@cybertec.at> wrote:
...I think that your patch offers too much detail,
like from which relations the statistics are extracted. Anybody
who wants to know that level of detail probably has to read the
source code anyway.How about adding a simple remark, like
--- a/doc/src/sgml/ref/pgupgrade.sgml +++ b/doc/src/sgml/ref/pgupgrade.sgml @@ -837,6 +837,8 @@ psql --username=postgres --file=script.sql postgres all statistics, such as those created explicitly with <xref linkend="sql-createstatistics"/>, custom statistics added by an extension, or statistics collected by the cumulative statistics system. + Transferring optimizer statistics works regardless of the old cluster's + major version. </para><para>
That would work too -- my goal was just to resolve confusion.
I was under the same impression --- that it only worked with upgrades
_from_ PG 18 and later. However, I can't find any documentation on this
limitation, so why did so many think this was true? I don't think
adding anything to the docs is the right approach.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Do not let urgent matters crowd out time for investment in the future.
On Tue, 2026-03-24 at 11:04 -0400, Bruce Momjian wrote:
On Tue, Mar 24, 2026 at 05:36:42AM -0700, Nikolay Samokhvalov wrote:
On Mon, Mar 23, 2026 at 23:59 Laurenz Albe <laurenz.albe@cybertec.at> wrote:
How about adding a simple remark, like--- a/doc/src/sgml/ref/pgupgrade.sgml +++ b/doc/src/sgml/ref/pgupgrade.sgml + Transferring optimizer statistics works regardless of the old cluster's + major version.That would work too -- my goal was just to resolve confusion.
I was under the same impression --- that it only worked with upgrades
_from_ PG 18 and later. However, I can't find any documentation on this
limitation, so why did so many think this was true? I don't think
adding anything to the docs is the right approach.
Bruce, you are confusing me. Your first sentence suggests that you
(erroneously) thought that upgrading statistics only works when upgrading
*from* v18 or better. But your last sentence suggests that you'd rather
not add anything to the documentation that could dispel that misconception.
Why?
Yours,
Laurenz Albe
On Tue, Mar 24, 2026 at 05:53:27PM +0100, Laurenz Albe wrote:
On Tue, 2026-03-24 at 11:04 -0400, Bruce Momjian wrote:
On Tue, Mar 24, 2026 at 05:36:42AM -0700, Nikolay Samokhvalov wrote:
On Mon, Mar 23, 2026 at 23:59 Laurenz Albe <laurenz.albe@cybertec.at> wrote:
How about adding a simple remark, likeI was under the same impression --- that it only worked with upgrades
_from_ PG 18 and later. However, I can't find any documentation on this
limitation, so why did so many think this was true? I don't think
adding anything to the docs is the right approach.Bruce, you are confusing me. Your first sentence suggests that you
(erroneously) thought that upgrading statistics only works when upgrading
Right.
*from* v18 or better. But your last sentence suggests that you'd rather
not add anything to the documentation that could dispel that misconception.
So, we don't normally document cases where a limitation does not exist.
I think the logical place to document this is in the PG 18 release
notes. I am still confused why people, like myself, got this wrong.
What is the source of the confusion? Just unclear release notes?
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Do not let urgent matters crowd out time for investment in the future.