How do I use pg_upgrade on arch linux

Question

I was running postgres 8.4 on arch but now have 9 installed I want to use pg_upgrade to upgrade but I don't know what all the options I have to use are, and all the directories that I have to point it to. it asks for a previous pg binary directory... but that's gone. Will it still work? note: I've no important data, just play db's I just want to know how to do it.

You said the previous binaries are gone. So, you should be doing a new install? — Khaled, Nov 27 '10 at 15:35
@khaled no I just upgraded the install with `pacman` the database is still there, just not the previous 8.4 binaries. do both (8.4 and 9.0) have to be installed side by side for it to work? — xenoterracide, Nov 27 '10 at 17:26

score 2 · Answer 1 · answered Nov 27 '10 at 17:46

2

That's one of the problems with using Postgres on Arch: upon upgrade it won't change the file format automatically.

I usually just do database dump and restore as upgrade procedure. (you should have database dumps anyway as a backup...)

answered Nov 27 '10 at 17:46

Hubert Kario

6,361
6
36
65

well until recently it wasn't possible... – xenoterracide Nov 28 '10 at 00:20
wait does it do it automatically in another distro? – xenoterracide Nov 29 '10 at 22:10
I don't have any experience on this but I'd guess that the likes of Ubuntu, Fedora or Debian do this automatically. Arch is a bare-bone distro following KISS principles on from the programmers point of view... – Hubert Kario Nov 30 '10 at 19:07

score 2 · Accepted Answer · answered Nov 29 '10 at 12:42

2

I'm sure you've read the documentation but just in case you haven't - "F.32. pg_upgrade".

The step-by-step instructions are especially detailed.

As a minumum you need to provide values for --old-datadir, --new-datadir, --old-bindir and --new-bindir options.

If your old installation is missing you could always (I hope so, I've never used Arch Linux) re-install it without overwriting the old data directory. If your distribution doesn't facilitate that you could re-install (temporarily) the old version from source.

Then you could run pg_upgrade. Remember to configure the two co-existing installations (the new one and the old one) to use different ports. And also set (temporarily) the local access method for both to "trust".

answered Nov 29 '10 at 12:42

Milen A. Radev

962
5
17

arch now has the package `extra/postgresql-old-upgrade` to allow concurrent installs – xenoterracide Oct 15 '13 at 19:37
I'm confused, all the guides I've read tell me to shut down both servers when running pg_upgrade. You're saying I should have both running? The pg_upgrade errors suck, if I have the new server running it complains the old one isn't, and if the new one isn't it complains about that instead. – Alkanshel Apr 08 '17 at 02:12
It might not be sensible to recompile the old version from source unless you know the compile time options that where used in the one from the repo. It says in the docs that checks that in the upgrade process. [here](https://www.postgresql.org/docs/current/pgupgrade.html) – Lightbulb1 Dec 19 '18 at 11:37

15 Volts · Answer 3 · 2023-01-31T08:50:51.683

Old question, but in case someone faces the same issue, I'm writing an answer.

Just yesterday, my PostgreSQL was upgraded to 14.2 and I had 13.6 before. Upgrading removes the old directories so there's no way to run pg_upgrade. I got one package from AUR, but that replaces the existing binaries as well.

But fortunately, Archlinux now provides the package postgresql-old-upgrade. It contains the last version of PostgreSQL.

So here are the steps to fix the upgrade issue you face every time you upgrade Postgresql.

Install postgresql-old-upgrade with pacman:

# pacman -S postgresql-old-upgrade

Copy / Move contents from /var/lib/postgres/data/ (or whatever your DB directory is) to some other directories. Say I copied with cp -a to /tmp/. Note that copying to tmp/ will increase memory usage until you delete the files. It can use a few megabytes to gigabytes even.

Note that all your data will be restored once you follow step 5. You don't need to take backups of the database with pg_dump and restore with pg_restore when doing this.

Delete /var/lib/postgres/data/ (or your DB path).
Log in to your postgres user with su postgres. [ Reset password from root user if you don't know the password ].
And you can now upgrade your old database normally:

postgres$ pg_upgrade -b /opt/pgsql-13/bin/ -B /usr/bin/ -d /tmp/data/ -D /var/lib/postgres/data/

Remove postgresql-old-upgrade:

# pacman -Rns postgresql-old-upgrade

This final step is of course, optional, you can leave postgresql-old-upgrade for future upgrades as well when PostgreSQL will be upgraded.

Directories and versions might differ for you, but in general, that's how it should fix the issue.

How do I use pg_upgrade on arch linux

3 Answers3