When upgrading your PostgreSQL database engine, it is important to follow all steps recommended by the PostgreSQL community and your cloud provider. Two kinds of upgrades exist for PostgreSQL databases:
Minor version upgrades: These include only bug and security fixes. They are always backward-compatible with your existing application database model.
The minor version upgrade process consists of replacing the PostgreSQL binaries and restarting the database service. The data directory remains unchanged.
Major version upgrades: These change the internal storage format and the database catalog. As a result, object statistics used by the query optimizer are not transferred to the new version and must be rebuilt with
Not following the documented major version upgrade process often results in poor database performance and high CPU use on the database server.
All major cloud providers support in-place major version upgrades of database
instances, using the
pg_upgrade utility. However you must follow the pre- and
post- upgrade steps to reduce the risk of performance degradation or database disruption.
Read carefully the major version upgrade steps of your external database platform:
- Amazon RDS for PostgreSQL
- Azure Database for PostgreSQL Flexible Server
- Google Cloud SQL for PostgreSQL
- PostgreSQL community
It is mandatory to run the
to refresh the
pg_statistic table after a major version upgrade, because optimizer statistics
are not transferred by
This should be done for all databases on the upgraded PostgreSQL service/instance/cluster.
To speed up the
ANALYZE operation, use the
--analyze-only --jobs=njobs to execute the
ANALYZE command in parallel by
njobs commands simultaneously.