PostgreSQL Versioning Policy
1. PostgreSQL Release Policy
PostgreSQL releases a major version once a year. Major releases usually include new features and may change the internal format of system tables and data files (internal storage layout, disk format). Because of these internal architecture changes, different major versions are not compatible with each other. (Compatibility is not strictly maintained or guaranteed.) For example, WAL files were stored in the pg_xlog directory up to PostgreSQL 9.6, but from PostgreSQL 10 they are stored in the pg_wal directory.
Minor releases only fix frequently encountered bugs, security issues, and data corruption problems. There are no internal architecture changes, so minor versions within the same major version remain compatible.
Each new major version receives minor releases for 5 years after its initial release. After 5 years it reaches end-of-life (EOL) and is no longer supported.
2. PostgreSQL Release Status (as of 2023-11-23)
As of the date of writing, PostgreSQL 9.6 is EOL and PostgreSQL 10 is due for EOL. PostgreSQL 11 and later no longer support 32 Bit.
Major Version | Current Minor Version | Supported | First Release | Final Release | Notes |
9.6 | 9.6.24 | NO (EOL) | 2016-09-29 | 2021-11-11 | ㅤ |
10 | 10.23 | NO (EOL) | 2017-10-05 | 2022-11-10 | ㅤ |
11 | 11.22 | YES | 2018-10-18 | 2023-11-09 | No 32 Bit support |
12 | 12.17 | YES | 2019-10-03 | 2024-11-14 | No 32 Bit support |
13 | 13.13 | YES | 2020-09-24 | 2025-11-13 | No 32 Bit support |
14 | 14.10 | YES | 2021-09-30 | 2026-11-12 | No 32 Bit support |
15 | 15.5 | YES | 2022-10-13 | 2027-11-11 | No 32 Bit support |
16 | 16.1 | YES | 2023-09-14 | 2028-11-09 | No 32 Bit support |
3. PostgreSQL Version Numbering
The version numbering scheme changed around PostgreSQL 10.
- PostgreSQL 9.6 and earlier
In PostgreSQL 9.6 and earlier, versions have 3 parts separated by dots (.), such as 9.6.24. The first 2 parts are the major version, and the third part is the minor version.
Major version : 9.6 Minor version : 24
- PostgreSQL 10 and later
From PostgreSQL 10, versions have 2 parts separated by a dot (.), such as 10.21. The first part is the major version, and the second part is the minor version.
Major version : 10 Minor version : 21
PostgreSQL Upgrade Overview
You may consider upgrading PostgreSQL to use new features or because of EOL. PostgreSQL can be upgraded with commands such as
pg_dump, pg_restore, and pg_upgrade, but besides the upgrade itself, additional work such as configuration changes and VACUUM may be needed.Depending on the PostgreSQL version you want to use, you either do a minor version upgrade or a major version upgrade. This post gives only an overview of PostgreSQL upgrades; detailed upgrade methods and procedures are covered separately.
1. PostgreSQL Minor Upgrade
For a minor version upgrade, the major version is the same and the internal architecture does not change, so you install the new minor version and start the PostgreSQL Server with the existing data file (PGDATA) path.
2. PostgreSQL Major Upgrade
For a major version upgrade, compatibility between versions is not maintained because the internal architecture of system tables and data files changes. A major version upgrade must therefore be done with a database dump/restore or the pg_upgrade command. This can take more work time and service downtime than a minor version upgrade.
PostgreSQL Applications
When you install PostgreSQL, several applications are installed along with the PostgreSQL Server. The set of applications differs by OS and major version. Below are the applications for CentOS / PostgreSQL 14.
PostgreSQL applications (CentOS / PostgreSQL 14)
Utility | Description |
clusterdb | Re-cluster tables in a database |
createdb | Create a database |
createuser | Create a user (role) |
dropdb | Remove a database |
dropuser | Remove a user (role) |
initdb | Create a database cluster |
pg_archivecleanup | Clean up WAL archive files |
pg_basebackup | Take a base backup of a cluster |
pg_checksums | Enable, disable, or verify data checksums in a database cluster |
pg_config | Retrieve information about the PostgreSQL version |
pg_controldata | Display control information of a database cluster |
pg_ctl | Control the PostgreSQL Server (initialize, start, stop, etc.) |
pg_dump | Database Backup |
pg_dumpall | Database Cluster Backup |
pg_isready | Check the connection status of the PostgreSQL Server |
pg_receivewal | Stream WAL from a cluster |
pg_resetwal | Reset the WAL and other control information of a cluster |
pg_restore | Restore a database from an archive file created by pg_dump |
pg_rewind | Synchronize a data directory with another data directory |
pg_test_fsync | Determine the fastest wal_sync_method on the system |
pg_test_timing | Measure timing overhead |
pg_upgrade | Upgrade the PostgreSQL Server (major) |
pg_verifybackup | Verify the integrity of a base backup taken with pg_basebackup |
pg_waldump | Display WAL in a human-readable form |
pgbench | Benchmark testing |
postgres | PostgreSQL Database Server |
postmaster | Alias for postgres (deprecated) |
psql | Command-Line Terminal |
reindexdb | Database Index Re-Indexing |
vacuumdb | Database vacuum |
📢 Of the applications listed above, this post explains the backup, restore, and upgrade applications and how to use them. (
pd_dump, pg_dumpall, pg_restore, pg_upgrade, initdb)1. pg_dump
pg_dump is for database backups. It extracts a PostgreSQL database into a script file or an archive file. pg_dump backs up only a single database; to back up a cluster with multiple databases, use pg_dumpall, described next.A file extracted as a script is a plain text file containing the SQL commands needed to rebuild the database (plain-text format). You can restore this file into a different version of PostgreSQL with psql. With options, it can also be generated in a format that can be restored into other DBMSs.
An archive file cannot be restored into other DBMSs, but it is a backup format that can be restored quickly into another PostgreSQL database with
pg_restore.- pg_dump formats
Format | Output | Characteristics |
Plain-Text | Script file | Output as SQL statements. Portable to other DBMSs |
Custom | Archive file | Compressed by default |
Directory | Archive file | Compressed by default, parallel backup supported |
Tar | Archive file | Creates the backup file in tar format |
📢 Script file : A plain text file containing SQL commands that people can read and edit
Archive file : A kind of binary file that people cannot read or edit
pg_dump characteristics
- Cannot back up roles or tablespaces
- No incremental backup
- Script files are restored with psql, archive files with pg_restore
- Supports compression of backup files (custom and directory formats)
- Supports parallel backup (directory format only)
Syntax Example
pg_dump Option
Database backup (-d option)
Back up a specific schema only (-n option)
Back up everything except a specific schema (-N option)
Back up a specific table only (-t option)
Custom format backup (-Fc option)
Directory format, parallel backup (-Fd, -j options)
2. pg_dumpall
pg_dumpall backs up the entire PostgreSQL database cluster. Because pg_dumpall reads the tables of every database, you must connect as a superuser to run it. The script file created by pg_dumpall consists of plain-text SQL statements that psql can run, and the restore must also be run as a superuser to create roles, databases, and so on.pg_dumpall characteristics
- Can back up roles and tablespaces
- Can be restored only with
psql
- Does not support compression of backup files
- Writes all databases one after another into a single script file, so parallel backup and parallel restore are not possible
Syntax Example
pg_dumpall Option
Database Cluster Backup
Back up tablespaces only (-t option)
3. pg_restore
pg_restore is an application that restores a PostgreSQL database from a dump file created by pg_dump. It runs the commands needed to rebuild the database to its state at the time of the dump. pg_restore works with dump files in custom, directory, and tar format.Syntax Example
pg_restore Option
Restore when the database does not exist
Restore when the database already exists
Directory format, parallel restore (-Fd, -j options)
4. pg_upgrade
With the
pg_upgrade application, you can upgrade PostgreSQL to a new major version. It is used only for major version upgrades and is not needed for minor version upgrades.pg_upgrade characteristics
- pg_upgrade runs pg_dump and pg_restore internally, so it needs enough disk space
- Upgrading a large database takes a long time
- With the link option, the data file (PGDATA) structure is changed to match the new major version, which saves disk space and time (however, if an error occurs during the upgrade, you cannot roll back)
Syntax Example
pg_upgrade Options
- Basic usage
- Pre-upgrade check: check the cluster only (-c option)
Output
- Upgrade with different ports (-p, -P options)
- Use links for the data directory during the upgrade (-k option)
5. initdb
initdb is the application that creates a PostgreSQL database cluster. When initdb runs, it creates the directory where data will be stored, the shared catalog tables, and the postgres database. It also initializes the default locale and character set encoding of the database cluster and sets up the template1 database, which is used as the default for new databases. initdb can only be run by the user that owns the PostgreSQL Server process (postgres). It does not run as root.Syntax Example
initdb Options
Basic usage
Output
- Specify locale and encoding options
6. pg_upgradecluster
pg_upgradecluster is an application that upgrades a PostgreSQL database cluster from an older version to a newer one. It is provided on Debian-based systems (Ubuntu, etc.) and makes upgrades easier than pg_dumpall or pg_upgrade.pg_upgradecluster characteristics
- The newdatadir parameter specifies the data directory of the new version cluster. (Default : /var/lib/postgresql/{VERSION}/{CLUSTER_NAME})
- The configuration files of the old version are copied to the new version cluster. As a result, the new cluster uses the port that was in use before, and the old cluster is configured to use a different port.
- After the upgrade, the administrator must remove the old PostgreSQL cluster manually (with pg_dropcluster).
- A database cluster that uses separate tablespaces cannot use the pg_upgradcluster application. It must be upgraded with applications such as pg_dump or pg_dumpall.
Syntax Example
pg_upgardecluster Options
7. pg_dropcluster
pg_dropcluster is an application that deletes all files of a PostgreSQL cluster (data directory, log files, configuration files). It is provided on Debian-based systems (Ubuntu, etc.).The cluster of a running PostgreSQL Server is not deleted. If you want to delete the cluster anyway, the --stop option forcibly shuts down the PostgreSQL Server before the cluster is deleted.
Syntax Example
8. pg_lsclusters
pg_lsclusters is an application that shows information about all PostgreSQL clusters.Syntax Example
pg_lsclusters Options
9. pg_ctlcluster
pg_ctlcluster manages PostgreSQL operations (start/stop/restart/reload).Syntax Example
pg_ctlcluster Options
Related articles