You may consider upgrading PostgreSQL to use its new features or because a version is reaching EOL. In a major version upgrade, compatibility between versions is not maintained because the internal architecture of the system tables and data files changes, so you must perform the upgrade with commands such as
pg_dump, pg_restore, and pg_upgrade. Besides the upgrade itself, you may also need additional work such as configuration changes and VACUUM. This work can take more time and more service downtime than a minor version upgrade.This document explains several ways to perform a major version upgrade from PostgreSQL 9.6 to PostgreSQL 12 on the same server, assuming no DDL or DML activity. pg_upgradecluster, described last, is available only on Debian-based systems (Ubuntu), while the upgrade methods that use PostgreSQL's own applications work regardless of OS platform. The steps follow this order: Install and configure PostgreSQL 12→Dump→Restore→Vacuum→Verify the upgrade.
📢 For a description of the applications, see [PostgreSQL Setup - Version & Utility].
For installing PostgreSQL 12, see [PostgreSQL Setup - Installation].
Major Upgrade Test Data
Before explaining the upgrade methods, we set up the configuration files and create test data on PostgreSQL 9.6, the upgrade source. The source server used in the test runs
PostgreSQL 9.6.24 on CentOS 7.9.postgresql.conf settings
pg_hba.conf settings
Test Data
Database | Table Name | Row Count | Size | Extension |
exemdb_01 | exemtab_01 | 50 million rows | 5,901MB (approx. 6GB) | pg_stat_statements |
exemdb_02 | exemtab_02 | 1 million rows | 125MB | pg_visibility |
Test data creation script
Database Size
Major Upgrade Using pg_dumpall
pg_dumpall is an application that backs up an entire PostgreSQL database cluster. A pg_dumpall dump includes many things such as roles, tablespaces, and databases.
With pg_dumpall you don't run pg_dump yourself, but pg_dump runs internally, so backing up the entire PostgreSQL database cluster requires enough disk space on the server. If the upgrade runs on the same server, first check that you have more free disk space than the size of the existing database cluster. You need space dedicated to the new PostgreSQL database cluster, space for the dump file, and so on.
1. Install and configure PostgreSQL 12
Install PostgreSQL 12
📢 For PostgreSQL installation, see [PostgreSQL Setup - Installation].
PostgreSQL 12 - postgresql.conf settings
Configuration differs between versions and may not be fully compatible. Because postgresql.conf is set to defaults when PostgreSQL 12 is installed, you need to compare it with PostgreSQL 9.6 and change the postgresql.conf settings PostgreSQL 12 needs. We also change the port PostgreSQL 12 uses to avoid a port conflict with the existing PostgreSQL 9.6 installation.
2. PostgreSQL 9.6 Cluster Dump
Create a PostgreSQL 9.6 cluster dump with the PostgreSQL 12 pg_dumpall application. If the PostgreSQL 9.6 cluster is large, pg_dumpall can take a long time.
📢 For pg_dumpall usage and documentation, see [PostgreSQL Setup - Version & Utility].
Execution result
pg_dumpall log
3. PostgreSQL 12 Restore
The PostgreSQL 9.6 cluster dump created above is in text format, so restore it to PostgreSQL 12 with psql.
Execution result
4. Run ANALYZE on PostgreSQL 12
Run ANALYZE on the whole cluster so that statistics for all databases are updated.
Execution result
5. Verify the PostgreSQL 12 upgrade
Verify the PostgreSQL major upgrade
Execution result
Check data in the exemdb_01 database
Execution result
Check data in the exemdb_02 database
Execution result
Major Upgrade Using pg_dump
This section covers upgrading from PostgreSQL 9.6 to PostgreSQL 12 with the
pg_dump and psql applications.As with the pg_dumpall upgrade, backing up the database cluster requires enough disk space on the server. If the upgrade runs on the same server, first check that you have more free disk space than the size of the existing database cluster. You need space dedicated to the new PostgreSQL database cluster, space for the dump file, and so on.
1. Install and configure PostgreSQL 12
Install PostgreSQL 12
📢 For PostgreSQL installation, see [PostgreSQL Setup - Installation].
PostgreSQL 12 - postgresql.conf settings
Configuration differs between versions and may not be fully compatible. Because postgresql.conf is set to defaults when PostgreSQL 12 is installed, you need to compare it with PostgreSQL 9.6 and change the postgresql.conf settings PostgreSQL 12 needs. We also change the port PostgreSQL 12 uses to avoid a port conflict with the existing PostgreSQL 9.6 installation.
2. PostgreSQL 9.6 Cluster Global Object Dump & Restore
Create a PostgreSQL 9.6 cluster global object dump with the PostgreSQL 12 pg_dumpall utility. It only dumps the cluster structure, not the data, so it does not take long. If you have not added any roles or tablespaces, you can skip this step.
Execution result
3-1. PostgreSQL 9.6 Cluster Dump & Restore
Create a PostgreSQL 9.6 cluster dump with the PostgreSQL 12 pg_dump application. pg_dump can only dump one database at a time, so you must run pg_dump once per database.
📢 For pg_dump usage and documentation, see [PostgreSQL Setup - Version & Utility].
Execution result
pg_dump log
The PostgreSQL 9.6 cluster dump created above is in text format, so restore it to PostgreSQL 12 with psql.
Execution result
3-2. PostgreSQL 9.6 Cluster Dump & Restore (-F option)
The larger the database, the longer the dump takes. You can shorten it by running the dump in parallel. However, a parallel dump is only possible in directory format (pg_dump -Fd option), and you must restore it with the pg_restore application.
Execution result
pg_dump -Fd Log
The PostgreSQL 9.6 cluster dump created above is in directory format, so restore it to PostgreSQL 12 with pg_restore.
📢 For pg_restore usage and documentation, see [PostgreSQL Setup - Version & Utility].
Execution result
4. Run ANALYZE on PostgreSQL 12
Run ANALYZE on the whole cluster so that statistics for all databases are updated.
Execution result
5. Verify the PostgreSQL 12 upgrade
Verify the PostgreSQL major upgrade
Execution result
Check data in the exemdb_01 database
Execution result
Check data in the exemdb_02 database
Execution result
Major Upgrade Using pg_upgrade
This section covers upgrading from PostgreSQL 9.6 to PostgreSQL 12 with the
pg_upgrade application.With pg_upgrade, you can upgrade without running a global object dump or a database dump. pg_upgrade performs the upgrade by copying from the PostgreSQL 9.6 data directory to the PostgreSQL 12 data directory. You don't run pg_dump yourself, but pg_upgrade runs pg_dump and pg_restore internally. So if the database is large, it can take a long time.
As with the pg_dumpall upgrade, backing up the database cluster requires enough disk space on the server. If the upgrade runs on the same server, first check that you have more free disk space than the size of the existing database cluster. You need space dedicated to the new PostgreSQL database cluster, and so on.
1. Install and configure PostgreSQL 12
Install PostgreSQL 12
📢 For PostgreSQL installation, see [PostgreSQL Setup - Installation].
PostgreSQL 12 - postgresql.conf settings
Configuration differs between versions and may not be fully compatible. Because postgresql.conf is set to defaults when PostgreSQL 12 is installed, you need to compare it with PostgreSQL 9.6 and change the postgresql.conf settings PostgreSQL 12 needs. We also change the port PostgreSQL 12 uses to avoid a port conflict with the existing PostgreSQL 9.6 installation.
2. Cluster Consistency Check
Run a consistency check between the PostgreSQL 9.6 cluster and the PostgreSQL 12 cluster. Use the pg_upgrade -c option; PostgreSQL must be shut down for the consistency check. If an error occurs during the check, it is reported. Fix the error based on the report, then run the consistency check again.
📢 For pg_upgrade usage and documentation, see [PostgreSQL Setup - Version & Utility].
Execution result
3. Cluster Upgrade
Once
3. Cluster Consistency Check passes, run the cluster upgrade. PostgreSQL must be shut down for the upgrade. pg_upgrade performs the upgrade by copying from the PostgreSQL 9.6 data directory to the PostgreSQL 12 data directory. If the database is large, it can take a long time.Execution result
4. Start PostgreSQL 12 and run ANALYZE
Run ANALYZE on the whole cluster so that statistics for all databases are updated.
Execution result
5. Verify the PostgreSQL 12 upgrade
Verify the PostgreSQL major upgrade
Execution result
Check data in the exemdb_01 database
Execution result
Check data in the exemdb_02 database
Execution result
Major Upgrade Using pg_upgrade Link Option
This section covers upgrading from PostgreSQL 9.6 to PostgreSQL 12 with the link option of the
pg_upgrade application.The pg_upgrade method described above upgrades by copying the database cluster, so it can take a very long time when the database is large. For this reason, you can use the pg_upgrade -k option to upgrade with hard links instead of copying the database cluster. Because it uses links, it only works within the same server (file system), and the upgrade can finish within a few seconds.
1. Install and configure PostgreSQL 12
Install PostgreSQL 12
📢 For PostgreSQL installation, see [PostgreSQL Setup - Installation].
PostgreSQL 12 - postgresql.conf settings
Configuration differs between versions and may not be fully compatible. Because postgresql.conf is set to defaults when PostgreSQL 12 is installed, you need to compare it with PostgreSQL 9.6 and change the postgresql.conf settings PostgreSQL 12 needs. We also change the port PostgreSQL 12 uses to avoid a port conflict with the existing PostgreSQL 9.6 installation.
2. Cluster Consistency Check
Run a consistency check between the PostgreSQL 9.6 cluster and the PostgreSQL 12 cluster. Use the pg_upgrade -c option; PostgreSQL must be shut down for the consistency check. If an error occurs during the check, it is reported. Fix the error based on the report, then run the consistency check again.
📢 For pg_upgrade usage and documentation, see [PostgreSQL Setup - Version & Utility].
Execution result
3. Cluster Upgrade
Once
3. Cluster Consistency Check passes, run the cluster upgrade. PostgreSQL must be shut down for the upgrade. pg_upgrade performs the upgrade by copying from the PostgreSQL 9.6 data directory to the PostgreSQL 12 data directory.Execution result
4. Start PostgreSQL 12 and run ANALYZE
Run ANALYZE on the whole cluster so that statistics for all databases are updated.
Execution result
5. Verify the PostgreSQL 12 upgrade
Verify the PostgreSQL major upgrade
Execution result
Check data in the exemdb_01 database
Execution result
Check data in the exemdb_02 database
Execution result
Major Upgrade Using pg_upgradecluster (Debian-based only)
This section covers upgrading from PostgreSQL 9.6 to PostgreSQL 12 with the
pg_upgradecluster application.pg_upgradecluster upgrades an existing PostgreSQL cluster to a new major version. It is only available on Debian-based systems (Ubuntu, etc.) and makes the upgrade easier than pg_dumpall or pg_upgrade.
📢 For pg_upgradecluster, pg_lsclusters, and pg_dropcluster usage and documentation, see [PostgreSQL Setup - Version & Utility].
1. Install and configure PostgreSQL 12
Install PostgreSQL 12
📢 For PostgreSQL installation, see [PostgreSQL Setup - Installation].
An upgrade with pg_upgradecluster does not require a configured cluster. On Ubuntu, installing PostgreSQL creates a cluster automatically, so we drop the new version's cluster.
Check PostgreSQL clusters
Drop the PostgreSQL 12 cluster
2. Upgrade the PostgreSQL cluster
When you run pg_upgradecluster, the configuration files are upgraded automatically too.
- pg_hba.conf : The file used in PostgreSQL 9.6 is copied as is (copy & paste)
- postgresql.conf : The settings used in PostgreSQL 9.6 are adjusted for PostgreSQL 12. Parameters in use on PostgreSQL 9.6 are carried over during the upgrade, but parameters added in PostgreSQL 12 are not included. So you need to edit the file separately to use parameters added in PostgreSQL 12.
Execution result
3. Verify the PostgreSQL 12 upgrade
Verify the PostgreSQL major upgrade
Execution result
Check data in the exemdb_01 database
Execution result
Check data in the exemdb_02 database
Execution result
Recommended articles
