PostgreSQL major UPdate

Support Software & Applications
1

Over the time, I noticed different behaviour for PostgreSQL major UPdates. Before December 2025, I delayed the PostgreSQL update and did the MANJARO-UPdate first, ref. my edits below to /etc/pacman.conf.

@linux-aarhus commented this here:

…
When postgresql is updated from one major to the next, there is update procedures that must be run to ensure the database structure matches the new major version.
…
If you want to ensure a postgresql production database is never touched by an update, add postgresql to the list of IgnorePkg in the [options] section of /etc/pacman.conf.

As usual, seeing the PostgreSQL UPdate 17 ->18, I edited /etc/pacman.conf in advance running MANJARO update;

IgnorePkg = postgresql
IgnorePkg = postgresql-libs
IgnorePkg = postgresql-old-upgrade

This kept PostgreSQL 17 but this did not start. I have been advised, that this delay is no longer allowed?
I’m sure, I have done this in the past. Why this difference and how to separate the UPdates in future?

PostgreSQL: How to define a custom data directory to be update-proof
2

It is very simple, update every package or nothing. If you don’t have time to prepare, do not update anything.

Adding packages to IgnorePkg was never recommend, however it is your system do what you like.

If this works depends only on the packages that get updated. If a package get an soname version number bump and is for postgresql important, it wont work. If nothing important changes, ignoring the postgresql package will work.
On Arch or Manjaro unstable and if you do updates often, there is only a small number of packages that get updated. So the likelihood of a library soname version number bump that will break postgresql is very low.
But on Manjaro stable, the number of packages that get updated at once is huge. The likelihood of a failure is very high.
Nothing has changed, it was just luck that is worked.

Personally, running Postgresql on Manjaro stable is only for user who have to much time. Just put it in an container.

1 Like
3

Postgres major version updates always require intervention. Minor versions no problem, but every major version you have to do this yourself.

The point of using Ignorepkg is not to ignore permanently. People do it that way just not to accidentally do a major version upgrade. And you can do the manual part of the upgrade when you have time. If it’s a minor version upgrade, you can just install those through manually.

The database data files even change in major upgrades. So you can’t just upgrade postgres, and have it work on an older version data directory.

You have one of two ways:

  • pg_dump/restore
  • pg_upgrade

if you pg_dump while it’s on the old version, then upgrade, restoring to the new version is one way to upgrade.

The steps may seem more difficult, but it truly is quicker once setup, and it is the postgres best practice way. This is to update is through pg_upgrade. Especially for larger or more advanced databases, it’s much more efficient. The problem is you have to have your old and new binaries to run this. So Arch has an official package called:

pacman -Si postgresql-old-upgrade
Repository      : extra
Name            : postgresql-old-upgrade
Version         : 17.7-2
Description     : Older PostgreSQL for migrating major versions with pg_upgrade
Architecture    : x86_64
URL             : https://www.postgresql.org/
Licenses        : PostgreSQL
Groups          : None
Provides        : None
Depends On      : gcc-libs  glibc  icu  krb5  libldap  libxml2  llvm-libs  lz4  openssl  pam  systemd-libs  util-linux-libs  zlib  zstd  postgresql-libs>=17.7
Optional Deps   : perl: for PL/Perl support
                  python: for PL/Python 3 support
                  tcl: for PL/Tcl support
Conflicts With  : None
Replaces        : None
Download Size   : 16.79 MiB
Installed Size  : 50.89 MiB
Packager        : Jelle van der Waa <jelle@archlinux.org>
Build Date      : Thu 18 Dec 2025 02:05:01 AM
Validated By    : MD5 Sum  SHA-256 Sum  Signature

As you are are postres 18, this package will always be one major version behind.

This provides what PostgreSQL: Documentation: 18: pg_upgrade needs.

-b bindir
--old-bindir= bindir

the old PostgreSQL executable directory; environment variable PGBINOLD

-B bindir
--new-bindir= bindir

the new PostgreSQL executable directory; default is the directory where pg_upgrade resides; environment variable PGBINNEW

Now you have both options for the old and new postgres binaries (this package provides the old).

1 Like
4

Thank you @Molski,

exactly what you explained

was my goal for PostgreQL major UPdate 17 → 18; I had done it in the past several times.

The question is, why this time PostgreSQL 17 was kept but I could not systemctl start postgresql.

5

In this case, it was the soname version bump for the icu package , which prevented the old postgresql binary from starting.

6

Which method were you planning to upgrade with? Dump/restore or pg_upgrade?

What are your current versions? e.g.

$ pacman -Qs postgresql
local/postgresql 18.1-2
    Sophisticated object-relational DBMS
local/postgresql-libs 18.1-2
    Sophisticated object-relational DBMS - Client binaries and libraries
local/postgresql-old-upgrade 17.7-2
    Older PostgreSQL for migrating major versions with pg_upgrade

The former two are at v17?

Also, I assume the data is still at that version too..

cat /var/lib/postgres/data/PG_VERSION

(Or where ever you have your data dir.)

So you held back both postgresql and postgresql-libs? It makes no sense to hold back postgresql-old-upgrade. But that shouldn’t affect anything, it’s only for pg_upgrade and you always want the latest previous version, so you don’t ignore it.

As far as I know that’s all you need to do. Your database should come up fine on the old version.

Are there any third party stuff baked in? I know there are official repo stuff that will break postgres upgrades.

What’s the error?

Start with: journalctl -u postgresql (+ --no-pager for copy/pasting)

But look at all the errors around it in journalctl just in case.

7

Thank you @Molski,
as I described in my initial post here, it’s not an actual issue, my PostgreSQL is running. But I would like to understand, why the separation of MANJRO UPdate and PostgreSQL UPdate 17 → 18 using IgnorePkg in /etc/pacman.conf did not work this time.
Unfortunately, I do not understand the post from @xabbu (thanks for this)

What does this mean and how can I check it in future?

I prefer pg_upgrade, actually I’m running PostgreSQL18.

8

On most (nearly all) Distributions binaries are dynamically linked to libraries. This has some advantages in comparison to statically linked binaries.

On fixed Distributions, like Debian, the major version of libraries in one release, like Trixie, doesn’t change. So it doesn’t make a different in which point in time a binary for a specific release is build. It will always find the correct libraries.

On a rolling release Distribution, like Arch, the major version of libraries changes. Most of the time, if a libraries gets a new major version, the so called “so-name” version of a library changes. It is not always the major version, it depends on the developers of a library, but it is usually the case.

The Arch Linux website for packages lists the libraries and the so-name version the binary is linked to, for a specific version of a package.

scroll down, and click on “View the soname list for postgresql” or klick on

You will see libraries names and version numbers like libicuuc.so.78 . And your case the icu package got updated, and the soname of the library “libicuuc” changed from libicuuc.so.76 to libicuuc.so.78

After the update the libicuuc.so.76 was replaced by libicuuc.so.78 , which resulted in an error since the postgresql binary form the old packaged was build for libicuuc.so.76

And this is the reason why you on a rolling distribution always update every package at once or no package at all.

Detecting it, is quite simple, you need to know which libraries your binary is linked to, and the package name of these libraries and you need to watch for version changes of these packages.

Sometimes, just copying or linking a new library the the old name and version works. But the problem is, sometimes the new library also depends on other libraries which usually results in an big mess and the program itself will not work with the new version and result in an unpredictable outcome. I do not recommend it.

Edit: Just to be sure, do NOT hold ( IgnorePkg ... ) the packages of the libraries. This will result in a none working system. If you do this, many program will stop working.

2 Likes
9

I guess I should of figured out that was you again with 2 postgres posts. This just looked like another person having trouble with a postgres upgrade. To be fair I had a major surgery in the middle of that!

Then the threads got combined? It’s confusing.

So you’ve done it, all good. Except two things? One being..

Then the logs request still applies. We can’t tell you why it didn’t start. Can you still find the logs?

journalctl --list-boots
# Find the boot number from above, eg:
jourtalctl --boot=-15
# Or short: journalctl -b -15

Power cycle too much, or too hard to find.. Filter by date ranges, errors, and/or units.

For example:

journalctl --unit=postgresql --priority=3 --since=2026-01-20 --until=2026-02-02

Or some variation on that, so you can find out when it happened. Once you know when, you can look at all the logs around that time. As there may be useful entries outside just postgres logs that may give us a clue what happened.

10

Thank you @Molski,
no problem with thhat:

Thank you very much for your feedback under these difficult circumstances.

I guess, that the detailed explanation from @xabbu is the solution, although I don’t like it …

11

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.