regarding statistics retaining with 18 Upgrade - Mailing list pgsql-hackers

From Rambabu V
Subject regarding statistics retaining with 18 Upgrade
Date
Msg-id CADtiZxrUzRRX6edyN2y-7U5HA8KSXttee7K=EFTLXjwG1SCE4A@mail.gmail.com
Whole thread Raw
Responses Re: regarding statistics retaining with 18 Upgrade
List 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@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@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



pgsql-hackers by date:

Previous
From: Peter Eisentraut
Date:
Subject: Re: Document NULL
Next
From: Alexander Korotkov
Date:
Subject: Re: Implement waiting for wal lsn replay: reloaded