Обсуждение: regarding statistics retaining with 18 Upgrade

Поиск
Список
Период
Сортировка

regarding statistics retaining with 18 Upgrade

От
Rambabu V
Дата:
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



Re: regarding statistics retaining with 18 Upgrade

От
Bruce Momjian
Дата:
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@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
> 
> 
> 

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

Re: regarding statistics retaining with 18 Upgrade

От
Greg Sabino Mullane
Дата:
On Sat, Nov 15, 2025 at 9:32 AM Bruce Momjian <bruce@momjian.us> wrote:
Uh, it is the _optimizer_ statistics that are preserved by pg_upgrade,

Specifically, check out the pg_stats view. Try this on your new cluster:

select count(distinct tablename) from pg_stats where tablename !~ 'pg_|sql_';

Cheers,
Greg

--
Enterprise Postgres Software Products & Tech Support

Re: regarding statistics retaining with 18 Upgrade

От
Robert Treat
Дата:
On Sat, Nov 15, 2025 at 9:32 AM Bruce Momjian <bruce@momjian.us> wrote:
> 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.
>

I wonder if it might be worth trying to clarify this a little bit
more... perhaps something like the attached?

Robert Treat
https://xzilla.net
Вложения

Re: regarding statistics retaining with 18 Upgrade

От
Dilip Kumar
Дата:
On Sun, Nov 16, 2025 at 5:22 AM Robert Treat <rob@xzilla.net> wrote:
>
> On Sat, Nov 15, 2025 at 9:32 AM Bruce Momjian <bruce@momjian.us> wrote:
> > 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.
> >
>
> I wonder if it might be worth trying to clarify this a little bit
> more... perhaps something like the attached?

IMHO it makes sense to update the doc as you suggested to avoid any confusion.

--
Regards,
Dilip Kumar
Google

Re: regarding statistics retaining with 18 Upgrade

От
Rambabu V
Дата:
Thanks all for the proactive response and clarification.

people will be confused by seeing the release notes, it should be a little bit clear or need to update.

i have one more question : 

with every version of package installation , PG 17 version is getting by default installed, is it a hard dependency or bug?

[root@db1 ~]# dnf install -y postgresql16-server postgresql16-contrib -y
Updating Subscription Management repositories.
Unable to read consumer identity

This system is not registered with an entitlement server. You can use "rhc" or "subscription-manager" to register.

Last metadata expiration check: 0:05:28 ago on Sun Nov 16 04:56:13 2025.
Dependencies resolved.
====================================================================================================================================================================================================================
 Package                                              Architecture                           Version                                              Repository                                                   Size
====================================================================================================================================================================================================================
Installing:
 postgresql16-contrib                                 x86_64                                 16.11-1PGDG.rhel10                                   pgdg16                                                      730 k
 postgresql16-server                                  x86_64                                 16.11-1PGDG.rhel10                                   pgdg16                                                      6.8 M
Installing dependencies:
 libicu                                               x86_64                                 74.2-5.el10_0                                        rhel-10-baseos-rhui-rpms                                     10 M
 libxslt                                              x86_64                                 1.1.39-8.el10_0                                      rhel-10-appstream-rhui-rpms                                 190 k
 postgresql16                                         x86_64                                 16.11-1PGDG.rhel10                                   pgdg16                                                      1.8 M
 postgresql16-libs                                    x86_64                                 16.11-1PGDG.rhel10                                   pgdg16                                                      339 k
 postgresql17                                         x86_64                                 17.7-1PGDG.rhel10                                    pgdg17                                                      1.9 M
 postgresql17-libs                                    x86_64                                 17.7-1PGDG.rhel10                                    pgdg17                                                      346 k
Installing weak dependencies:
 postgresql17-server                                  x86_64                                 17.7-1PGDG.rhel10                                    pgdg17                                                      7.0 M

Transaction Summary
====================================================================================================================================================================================================================
Install  9 Packages

Total download size: 30 M
Installed size: 120 M
Downloading Packages:
(1/9): postgresql17-libs-17.7-1PGDG.rhel10.x86_64.rpm                                                                                                                                11 MB/s | 346 kB     00:00
(2/9): postgresql17-17.7-1PGDG.rhel10.x86_64.rpm                                                                                                                                     42 MB/s | 1.9 MB     00:00
(3/9): postgresql16-16.11-1PGDG.rhel10.x86_64.rpm                                                                                                                                    56 MB/s | 1.8 MB     00:00
(4/9): postgresql16-contrib-16.11-1PGDG.rhel10.x86_64.rpm                                                                                                                            25 MB/s | 730 kB     00:00
(5/9): postgresql16-libs-16.11-1PGDG.rhel10.x86_64.rpm                                                                                                                               20 MB/s | 339 kB     00:00
(6/9): libxslt-1.1.39-8.el10_0.x86_64.rpm                                                                                                                                           6.6 MB/s | 190 kB     00:00
(7/9): libicu-74.2-5.el10_0.x86_64.rpm                                                                                                                                              104 MB/s |  10 MB     00:00
(8/9): postgresql17-server-17.7-1PGDG.rhel10.x86_64.rpm                                                                                                                              28 MB/s | 7.0 MB     00:00
(9/9): postgresql16-server-16.11-1PGDG.rhel10.x86_64.rpm                                                                                                                             33 MB/s | 6.8 MB     00:00
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                                                                                92 MB/s |  30 MB     00:00
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                                                                                                            1/1
  Installing       : postgresql17-libs-17.7-1PGDG.rhel10.x86_64                                                                                                                                                 1/9
  Running scriptlet: postgresql17-libs-17.7-1PGDG.rhel10.x86_64                                                                                                                                                 1/9
  Installing       : libicu-74.2-5.el10_0.x86_64                                                                                                                                                                2/9
  Installing       : postgresql16-libs-16.11-1PGDG.rhel10.x86_64                                                                                                                                                3/9
  Running scriptlet: postgresql16-libs-16.11-1PGDG.rhel10.x86_64                                                                                                                                                3/9
  Installing       : postgresql16-16.11-1PGDG.rhel10.x86_64                                                                                                                                                     4/9
  Running scriptlet: postgresql16-16.11-1PGDG.rhel10.x86_64                                                                                                                                                     4/9
  Installing       : postgresql17-17.7-1PGDG.rhel10.x86_64                                                                                                                                                      5/9
  Running scriptlet: postgresql17-17.7-1PGDG.rhel10.x86_64                                                                                                                                                      5/9
  Running scriptlet: postgresql17-server-17.7-1PGDG.rhel10.x86_64                                                                                                                                               6/9
  Installing       : postgresql17-server-17.7-1PGDG.rhel10.x86_64                                                                                                                                               6/9
  Running scriptlet: postgresql17-server-17.7-1PGDG.rhel10.x86_64                                                                                                                                               6/9
  Running scriptlet: postgresql16-server-16.11-1PGDG.rhel10.x86_64                                                                                                                                              7/9
  Installing       : postgresql16-server-16.11-1PGDG.rhel10.x86_64                                                                                                                                              7/9
  Running scriptlet: postgresql16-server-16.11-1PGDG.rhel10.x86_64                                                                                                                                              7/9
  Installing       : libxslt-1.1.39-8.el10_0.x86_64                                                                                                                                                             8/9
  Installing       : postgresql16-contrib-16.11-1PGDG.rhel10.x86_64                                                                                                                                             9/9
  Running scriptlet: postgresql16-contrib-16.11-1PGDG.rhel10.x86_64                                                                                                                                             9/9
Installed products updated.

Installed:
  libicu-74.2-5.el10_0.x86_64                          libxslt-1.1.39-8.el10_0.x86_64                        postgresql16-16.11-1PGDG.rhel10.x86_64         postgresql16-contrib-16.11-1PGDG.rhel10.x86_64
  postgresql16-libs-16.11-1PGDG.rhel10.x86_64          postgresql16-server-16.11-1PGDG.rhel10.x86_64         postgresql17-17.7-1PGDG.rhel10.x86_64          postgresql17-libs-17.7-1PGDG.rhel10.x86_64
  postgresql17-server-17.7-1PGDG.rhel10.x86_64

Complete!
[root@db1 ~]# rpm -qa|grep postgres
postgresql17-libs-17.7-1PGDG.rhel10.x86_64
postgresql16-libs-16.11-1PGDG.rhel10.x86_64
postgresql16-16.11-1PGDG.rhel10.x86_64
postgresql17-17.7-1PGDG.rhel10.x86_64
postgresql17-server-17.7-1PGDG.rhel10.x86_64
postgresql16-server-16.11-1PGDG.rhel10.x86_64
postgresql16-contrib-16.11-1PGDG.rhel10.x86_64







On Sun, Nov 16, 2025 at 12:43 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:
On Sun, Nov 16, 2025 at 5:22 AM Robert Treat <rob@xzilla.net> wrote:
>
> On Sat, Nov 15, 2025 at 9:32 AM Bruce Momjian <bruce@momjian.us> wrote:
> > 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.
> >
>
> I wonder if it might be worth trying to clarify this a little bit
> more... perhaps something like the attached?

IMHO it makes sense to update the doc as you suggested to avoid any confusion.

--
Regards,
Dilip Kumar
Google

Чтобы сделать работу с сайтом удобнее, мы используем cookie и аналитический сервис «Яндекс.Метрика». Продолжая пользоваться сайтом, вы соглашаетесь с их использованием.