regarding statistics retaining with 18 Upgrade

From: Rambabu V <ram(dot)wissen(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: regarding statistics retaining with 18 Upgrade
Date: 2025-11-15 10:02:53
Message-ID: CADtiZxrUzRRX6edyN2y-7U5HA8KSXttee7K=EFTLXjwG1SCE4A@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi Team,

seems stats are not retained after upgrade. Below are the artifacts , is it
expected? as per the release notes statistics should be retained.

before upgrade:

postgres=# select * from pg_stat_user_tables;

-[ RECORD 1 ]-------+------------------------------

relid | 16384

schemaname | public

relname | emp

n_tup_ins | 1000000

n_tup_upd | 0

n_tup_del | 0

n_tup_hot_upd | 0

n_tup_newpage_upd | 0

n_live_tup | 1000000

n_dead_tup | 0

n_mod_since_analyze | 0

n_ins_since_vacuum | 0

last_vacuum | 2025-11-15 09:40:48.662853+00

last_autovacuum | 2025-11-15 09:40:04.302062+00

last_analyze | 2025-11-15 09:40:44.523538+00

last_autoanalyze | 2025-11-15 09:40:04.455379+00

vacuum_count | 1

autovacuum_count | 1

analyze_count | 1

autoanalyze_count | 1

postgres=# select version();

-[ RECORD 1
]------------------------------------------------------------------------------------------------------

version | PostgreSQL 16.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC)
14.3.1 20250617 (Red Hat 14.3.1-2), 64-bit

after upgrade:

[postgres(at)ip-172-31-35-206 ~]$ /usr/pgsql-18/bin/pg_upgrade -b
/usr/pgsql-16/bin/ -B /usr/pgsql-18/bin/ -d /var/lib/pgsql/16/data/ -D
/var/lib/pgsql/18/data/ -p 5432 -P 5433
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
+++++------------------
Setting locale and encoding for new cluster ok
Analyzing all rows in the new cluster ok

Upgrade Complete
----------------
Some statistics are not transferred by pg_upgrade.
Once you start the new server, consider running these two commands:
/usr/pgsql-18/bin/vacuumdb --all --analyze-in-stages
--missing-stats-only
/usr/pgsql-18/bin/vacuumdb --all --analyze-only
Running this script will delete the old cluster's data files:
./delete_old_cluster.sh

[postgres(at)ip-172-31-35-206 ~]$ psql
psql (18.1)
Type "help" for help.

postgres=# select * from pg_stat_user_tables;
relid | schemaname | relname | seq_scan | last_seq_scan | seq_tup_read |
idx_scan | last_idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd |
n_tup_del | n_tup_hot_upd | n_tup_newpage_upd | n_live_tup | n_dead_tup |
n_mod_since_analyze | n_ins
_since_vacuum | last_vacuum | last_autovacuum | last_analyze |
last_autoanalyze | vacuum_count | autovacuum_count | analyze_count |
autoanalyze_count | total_vacuum_time | total_autovacuum_time |
total_analyze_time | total_autoanalyze_time
-------+------------+---------+----------+---------------+--------------+----------+---------------+---------------+-----------+-----------+-----------+---------------+-------------------+------------+------------+---------------------+------
--------------+-------------+-----------------+--------------+------------------+--------------+------------------+---------------+-------------------+-------------------+-----------------------+--------------------+------------------------
16384 | public | emp | 0 | | 0 |
| | | 0 | 0 | 0
| 0 | 0 | 0 | 0 |
0 |
0 | | | |
| 0 | 0 | 0 | 0
| 0 | 0 | 0 |
0
(1 row)

postgres=# \q

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Alexander Korotkov 2025-11-15 10:29:25 Re: Implement waiting for wal lsn replay: reloaded
Previous Message Peter Eisentraut 2025-11-15 08:06:41 Re: Document NULL