PostgreSQL 18支持pg_upgrade升级大版本时迁移统计信息

PostgreSQL 18支持pg_upgrade升级大版本时迁移统计信息

2021 年的吐槽, 在18版本上要被终结了:

• 《DB吐槽大会,第73期 - PG 统计信息无法迁移》
• 《PostgreSQL 18 preview - PG 18支持重置(pg_clear_attribute_stats)和设置(pg_set_attribute_stats)指定对象的列级统计信息》
• 《PostgreSQL 18 preview - PG 18支持重置(pg_clear_relation_stats)和设置(pg_set_relation_stats)指定对象的统计信息》
• 《PostgreSQL 18 preview - 支持统计信息导出导入, 将来用pg_upgrade大版本升级后不需要analyze了》

PostgreSQL 18 支持pg_upgrade升级大版本时迁移统计信息, 也就是说大版本升级后, 不需要再通过analyze重新生成统计信息了(目前还不支持自定义扩展统计信息, 例如多列多条件统计信息).

Transfer statistics during pg_upgrade.  

author Jeff Davis <[email protected]>   
Thu, 20 Feb 2025 09:29:06 +0000 (01:29 -0800)  
committer Jeff Davis <[email protected]>   
Thu, 20 Feb 2025 09:29:06 +0000 (01:29 -0800)  
commit 1fd1bd871012732e3c6c482667d2f2c56f1a9395  
tree 232adb265bbbfdb2c2aa89fe9d2ec8f6c14839c2 tree  
parent 7da344b9f84f0c63590a34136f3fa5d0ab128657 commit | diff  
Transfer statistics during pg_upgrade.  

Add support to pg_dump for dumping stats, and use that during  
pg_upgrade so that statistics are transferred during upgrade. In most  
cases this removes the need for a costly re-analyze after upgrade.  

Some statistics are not transferred, such as extended statistics or  
statistics with a custom stakind.  

Now pg_dump accepts the options --schema-only, --no-schema,  
--data-only, --no-data, --statistics-only, and --no-statistics; which
allow all combinations of schema, data, and/or stats. The options are  
named this way to preserve compatibility with the previous   
--schema-only and --data-only options.   

Statistics are in SECTION_DATA, unless the object itself is in
SECTION_POST_DATA.  

The stats are represented as calls to pg_restore_relation_stats() and  
pg_restore_attribute_stats().   

Author: Corey Huinker, Jeff Davis  
Reviewed-by: Jian He  
Discussion: https://postgr.es/m/CADkLM=fzX7QX6r78fShWDjNN3Vcr4PVAnvXxQ4DiGy6V=0bCUA@mail.gmail.com  
Discussion: https://postgr.es/m/CADkLM%3DcB0rF3p_FuWRTMSV0983ihTRpsH%2BOCpNyiqE7Wk0vUWA%40mail.gmail.com