CYBERTEC Logo

Upgrading and updating PostgreSQL

12.2020 / Category: / Tags: |

Blog post updated 05.12.2022 People keep asking: "What are the best ways to upgrade and update my PostgreSQL to the latest version?" This blog post covers how you can upgrade or update to the latest PostgreSQL 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.

Updating PostgreSQL

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 15.0 to PostgreSQL 15.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
yum/dnf/apt Minor release update Not needed Close to zero

Upgrading PostgreSQL

Now let’s take a look at upgrades: if you want to move from PostgreSQL 9.6, 10, 13 or some other older version to PostgreSQL 15, an upgrade is needed. To do that, you have various options:

  • pg_dumpall: 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/pg_dumpall and 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 hard links for those data files. The amount of data is not a limiting factor anymore, because hard links can be created quickly. “pg_upgrade --link” therefore promises close to zero downtime.

Tooling Task Reloading data Downtime needed
pg_upgrade Major release update Metadata and files are copied Downtime is needed
pg_upgrade --link Major release update Only metadata are copied Close to zero

 

What is important to note here is that pg_upgrade is never destructive. If something goes 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:

This is a fairly small database, but it is already large enough so that users can feel the difference when doing the upgrade:

7.4 GB are ready to be upgraded. Let’s see how it works.

pg_upgrade in action

To upgrade a database, three steps are needed:

  • initdb ...
  • copy pg_hba.conf and pg_ident.conf and adapt the new postgresql.conf
  • pg_upgrade ...

Let us start with initdb:

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.

After adapting the configuration files, we can run pg_upgrade: Basically, we need four pieces of information here: The old and the new data directory as well as the path of the old and the new binaries:

What is worth mentioning here is that the upgrade process takes over four 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:

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:

What you see here is that the relevant data files have two entries: “2” means that the same file (inode) shows up as two directories. pg_upgrade creates hard links, and this is exactly what we see here.

That's why it is super important to make sure that the mount point is /data and not /data/db12. Otherwise, hard links are not possible.

Finally …

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.

0 0 votes
Article Rating
Subscribe
Notify of
guest
9 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Ajit Kumar
Ajit Kumar
2 years ago

how to upgrade from 9.4 to 13.2

Arjun Pidugu
Arjun Pidugu
2 years ago

https://uploads.disquscdn.com/images/40f117442e2b213df3223ef1b9655e5c67dc91509657a77446acd8548c5028bb.png

pg_upgrade --link failed while migrating from postgres 10 to 12 with below error
[unrecognized win32 error code: 50]
[could not create hard link between old and new data directories: Invalid argument]
[In link mode the old and new data directories must be on the same file system.]

D:PostgreSQLbinpg_upgrade --old-datadir=D:OldPostgreSQLdata --new-datadir=D:PostgreSQLdata --old-bindir=D:OldPostgreSQLbin --new-bindir=D:PostgreSQLbin --old-port=5433 --new-port=49121 --username=postgres --retain --link

Is failure due to mounting issue between old data dir and new data dir?

Note: Getting issue only in one of the machines
Machine OS: Windows server 2019

Can you please help me in this?

anonmyous
anonmyous
8 months ago

how to upgrade form 8 to 9?

laurenz
laurenz
8 months ago
Reply to  anonmyous

You don't upgrade to an old, outdated version like 9.X.
Use a more recent version.
The method is dump/restore.

SSQQLL
SSQQLL
4 months ago

I'm also interested in upgrading from v8 to v13. Is it directly possible, or only via path 8->9->13(->16) ?
(Did not find any official info - perhaps it's my bad.)

laurenz
laurenz
8 months ago

Your developer is wrong.

anonmyous
anonmyous
8 months ago

thank you but in year 2024 our business software relies on postgres 13 and upgrade from 8 to 13 failed. developer said upgrade from 8 to 9 then 9 to 13 and if you want 13 to 16. or 9 to 16. dump/restore has to be the best solution, the developer told us.

laurenz
laurenz
8 months ago

I am not Mr. Schönig, I am Mr. Albe.
We are happy to provide support to you, simply contact sales.at and order a consulting pool.

anonmyous
anonmyous
8 months ago

dear laurenz, do you want to keep in contact with me, outside of you great webpage? i need help with the upgrade of postgres and i am willing to pay a fair price. are you Mr. Schönig in real life?

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    9
    0
    Would love your thoughts, please comment.x
    ()
    x
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram