Re: regarding statistics retaining with 18 Upgrade

On Sat, Nov 15, 2025 at 06:02:53PM +0800, Rambabu V wrote:
> 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.

Uh, it is the _optimizer_ statistics that are preserved by pg_upgrade,
not the cumulative statistics system:

https://www.postgresql.org/docs/current/monitoring-stats.html

Yes, they are easily confused.

---------------------------------------------------------------------------

>
> 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
>
>
>

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

Do not let urgent matters crowd out time for investment in the future.