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