Recently, PostgreSQL 13 was released. People are asking what are best ways upgrading and updating PostgreSQL 12 or some other version to PostgreSQL 13. This blog post covers how you can move to the latest release.
Before we get started, we have to make a distinction between two things:
- Updating PostgreSQL
- Upgrading PostgreSQL
Let’s take a look at both scenarios.
The first scenario I want to shed some light on is updating within a major version of PostgreSQL: What does that mean? Suppose you want to move from PostgreSQL 13.0 to PostgreSQL 13.1. In that case, all you have to do is to install the new binaries and restart the database.
There is no need to reload the data. The downtime required is minimal. The binary format does not change; a quick restart is all you need. The same is true if you are running a HA (high availability cluster) solution such as Patroni. Simply restart all nodes in your PostgreSQL cluster and you are ready to go.
The following table contains a little summary:
|Tooling||Task||Reloading data||Downtime needed|
|pg_dump||Minor release update||Not needed||Close to zero|
Now let’s take a look at upgrades: if you want to move from PostgreSQL 9.5, 9.6, 10, 11 or 12 to PostgreSQL 13, an upgrade is needed. To do that, you have various options:
- pg_dump: Dump / reload
- pg_upgrade: Copy data on a binary level
- pg_upgrade –link: In-place upgrades
If you dump and reload data, it might take a lot of time. The bigger your database is, the more time you will need to do the upgrade. It follows that pg_dump(all) and pg_restore are not the right tools to upgrade a large multi-terabyte database.
pg_upgrade is here to do a binary upgrade. It copies all the data files from the old directory to the new one. Depending on the amount of data, this can take quite a lot of time and cause serious downtime. However, if the new and the old data directory are on the same filesystem, there is a better option: “pg_upgrade –link”. Instead of copying all the files, pg_upgrade will create hardlinks for those data files. The amount of data is not a limiting factor anymore, because hardlinks can be created quickly. “pg_upgrade –link” therefore promises close-to-zero downtime.
|Tooling||Task||Reloading data||Downtime needed|
|pg_upgrade||Major release update||Copy is needed||Downtime is needed|
|pg_upgrade –link||Major release update||Only hardlinks||Close to zero|
What is important to note here is that pg_upgrade is never destructive. If things go wrong, you can always delete the new data directory and start from scratch.
Preparing a sample database
To show pg_upgrade in action, I have created a little sample database to demonstrate how things work in real life:
test=# CREATE TABLE a AS SELECT id AS a, id AS b, id AS c FROM generate_series(1, 50000000) AS id; SELECT 50000000 test=# CREATE TABLE b AS SELECT * FROM a; SELECT 50000000 test=# CREATE INDEX idx_a_a ON a (a); CREATE INDEX test=# CREATE INDEX idx_a_b ON a (b); CREATE INDEX test=# CREATE INDEX idx_b_a ON b (a); CREATE INDEX
This is a fairly small database, but it is already large enough so that users can feel the difference when doing the upgrade:
test=# SELECT pg_size_pretty(pg_database_size('test')); pg_size_pretty ---------------- 7444 MB (1 row)
7.4 GB are ready to be upgraded. Let’s see how it works.
pg_upgrade in action
To upgrade a database, two steps are needed:
- initdb …
- pg_upgrade …
Let us start with initdb:
iMac:tmp hs$ initdb -D /data/db13 The files belonging to this database system will be owned by user "hs". This user must also own the server process. The database cluster will be initialized with locales COLLATE: en_US CTYPE: UTF-8 MESSAGES: en_US MONETARY: en_US NUMERIC: en_US TIME: en_US The default database encoding has accordingly been set to "UTF8". initdb: could not find suitable text search configuration for locale "UTF-8" The default text search configuration will be set to "simple". Data page checksums are disabled. creating directory /data/db13 ... ok creating subdirectories ... ok selecting dynamic shared memory implementation ... posix selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting default time zone ... Europe/Vienna creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok syncing data to disk ... ok initdb: warning: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb. Success. You can now start the database server using: pg_ctl -D /data/db13 -l logfile start
Note that pg_upgrade is only going to work in case the encodings of the old and the new database instance match. Otherwise, it will fail.
Then we can run pg_upgrade: Basically, we need 4 pieces of information here: The old and the new data directory as well as the path of the old and the new binaries:
iMac:tmp hs$ time pg_upgrade -d /data/db12/ \ -D /data/db13 \ -b /path/pg12/bin/ \ -B /usr/local/Cellar/postgresql/13.0/bin/ Performing Consistency Checks ----------------------------- Checking cluster versions ok Checking database user is the install user ok Checking database connection settings ok Checking for prepared transactions ok Checking for reg* data types in user tables ok Checking for contrib/isn with bigint-passing mismatch ok Creating dump of global objects ok Creating dump of database schemas ok Checking for presence of required libraries ok Checking database user is the install user ok Checking for prepared transactions ok If pg_upgrade fails after this point, you must re-initdb the new cluster before continuing. Performing Upgrade ------------------ Analyzing all rows in the new cluster ok Freezing all rows in the new cluster ok Deleting files from new pg_xact ok Copying old pg_xact to new server ok Setting next transaction ID and epoch for new cluster ok Deleting files from new pg_multixact/offsets ok Copying old pg_multixact/offsets to new server ok Deleting files from new pg_multixact/members ok Copying old pg_multixact/members to new server ok Setting next multixact ID and offset for new cluster ok Resetting WAL archives ok Setting frozenxid and minmxid counters in new cluster ok Restoring global objects in the new cluster ok Restoring database schemas in the new cluster ok Copying user relation files ok Setting next OID for new cluster ok Sync data directory to disk ok Creating script to analyze new cluster ok Creating script to delete old cluster ok Upgrade Complete ---------------- Optimizer statistics are not transferred by pg_upgrade so, once you start the new server, consider running: ./analyze_new_cluster.sh Running this script will delete the old cluster's data files: ./delete_old_cluster.sh <strong>real 4m11.702s</strong> user 0m0.255s sys 0m13.385s
What is worth mentioning here is that the upgrade process takes over 4 minutes because all the data had to be copied to the new directory. My old Mac is not very fast and copying takes a very long time.
To reduce downtime, we can clean out the directory, run initdb again and add the –link option:
iMac:~ hs$ time pg_upgrade -d /data/db12/ \ -D /data/db13 \ -b /path/pg12/bin/ \ -B /usr/local/Cellar/postgresql/13.0/bin/ \ --link Performing Consistency Checks ----------------------------- Checking cluster versions ok Checking database user is the install user ok … Upgrade Complete ---------------- Optimizer statistics are not transferred by pg_upgrade so, once you start the new server, consider running: ./analyze_new_cluster.sh Running this script will delete the old cluster's data files: ./delete_old_cluster.sh <strong>real 0m3.538s</strong> user 0m0.198s sys 0m0.322s
In this case, it took only 3.5 seconds to upgrade. We can start the database instance directly and keep working with the new instance.
“pg_upgrade –link” under the hood
The –link option can reduce the downtime to close to zero. The question is what happens under the hood? Let’s check out the data files:
-rw------- 2 hs wheel 1073741824 Nov 22 11:55 16388 -rw------- 2 hs wheel 1073741824 Nov 22 11:58 16388.1 -rw------- 2 hs wheel 66576384 Nov 22 12:01 16388.2 -rw------- 2 hs wheel 1073741824 Nov 22 12:12 16391 -rw------- 2 hs wheel 1073741824 Nov 22 12:13 16391.1 -rw------- 2 hs wheel 66576384 Nov 22 12:14 16391.2 -rw------- 2 hs wheel 1073741824 Nov 22 12:01 16394 -rw------- 2 hs wheel 49373184 Nov 22 12:01 16394.1 -rw------- 2 hs wheel 1073741824 Nov 22 12:03 16395 -rw------- 2 hs wheel 49373184 Nov 22 12:03 16395.1 -rw------- 2 hs wheel 1073741824 Nov 22 12:15 16396 -rw------- 2 hs wheel 49373184 Nov 22 12:15 16396.1 -rw------- 1 hs wheel 8192 Nov 24 10:03 174 -rw------- 1 hs wheel 8192 Nov 24 10:03 175
What you see here is that the relevant data files have two entries: “2” means that the same piece of storage shows up as two files. pg_upgrade creates hardlinks and this is exactly what we see here.
That’s why it is super important to make sure that the mountpoint is /data and not /data/db12. Otherwise, hardlinks are not possible.
pg_upgrade is an important tool. However, there are more ways to upgrade: Logical replication is an alternative method. Check out our blog post about this exciting technology.