Upgrading external PostgreSQL databases
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
ANALYZE
.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
pg_upgrade
Always ANALYZE
your database after a major version upgrade
It is mandatory to run the ANALYZE
operation
to refresh the pg_statistic
table after a major version upgrade, because optimizer statistics
are not transferred by pg_upgrade
.
This should be done for all databases on the upgraded PostgreSQL service/instance/cluster.
To speed up the ANALYZE
operation, use the
vacuumdb
utility,
with --analyze-only --jobs=njobs
to execute the ANALYZE
command in parallel by
running njobs
commands simultaneously.
Docs
Edit this page to fix an error or add an improvement in a merge request.
Create an issue to suggest an improvement to this page.
Product
Create an issue if there's something you don't like about this feature.
Propose functionality by submitting a feature request.
Feature availability and product trials
View pricing to see all GitLab tiers and features, or to upgrade.
Try GitLab for free with access to all features for 30 days.
Get help
If you didn't find what you were looking for, search the docs.
If you want help with something specific and could use community support, post on the GitLab forum.
For problems setting up or using this feature (depending on your GitLab subscription).
Request support