Burgundywall - Skip version during PostgreSQL upgrade

Skip version during PostgreSQL upgrade

Kurt Neufeld @ February 18, 2017 wrote... (6 years, 7 months ago)

So it turns out I hadn't updated my Fedora installation for over two years (protip: don't run Fedora on a server kids) so I did a quick series of upgrades and went from Fedora 21 to Fedora 25.

Unfortunately that means that my PostgreSQL database was at version 9.3 but the installed software was version 9.5.

So can you upgrade from 9.3 to 9.5? No. But it's not impossible either.

# note to future me, this is how you upgrade with dnf
dnf system-upgrade download --refresh --allowerasing --releasever=25
dnf system-upgrade reboot

I'm writing this mostly from memory and looking at my terminal history so the steps might be slightly wrong so understand what you're doing before randomly copy/pasting.

A few websites were very helpful:

So the overall plan is:

install older versions of PostgreSQL from yum.postgresql.org
uninstall Fedora's version of PostgreSQL (permanently)
upgrade database from 9.3 -> 9.4 -> 9.5 -> 9.6 (latest at time of writing)
use official version of PostgreSQL going forward
use latest version of PostgreSQL even though it's newer (9.6) than Fedora version (9.5)

install repos

Browse to https://yum.postgresql.org/repopackages.php and install all the appropriate repo files. In my case I installed:

PostgreSQL 9.3 - Fedora 23, and then edited the file
PostgreSQL 9.4 - Fedora 25
PostgreSQL 9.5 - Fedora 25
PostgreSQL 9.6 - Fedora 25

Install the repos:

rpm -U https://download.postgresql.org/pub/repos/yum/9.3/fedora/fedora-23-x86_64/pgdg-fedora93-9.3-4.noarch.rpm
etc...

Because there wasn't a 9.3 version for Fedora 25 I needed to download the wrong repo file but then do a quick edit:

@/etc/yum.repos.d/pgdg-93-fedora.repo
< baseurl=https://download.postgresql.org/pub/repos/yum/9.3/fedora/fedora-$releasever-$basearch
---
> baseurl=https://download.postgresql.org/pub/repos/yum/9.3/fedora/fedora-23-$basearch

install postgresql versions

dnf install postgresql93-server posgresql94-contrib
dnf install postgresql94-server posgresql94-contrib
dnf install postgresql95-server posgresql95-contrib
dnf install postgresql96-server posgresql96-contrib

# delete the fedora version of postgresql
dnf remove postgresql-server

Quick note on paths.

Fedora installs postgres binaries to /usr/bin as you'd expect. The postgres repos install binaries to /usr/pgsql-$VERSION.

Fedora stores the actual database at /var/lib/pgsql/data, the postgres binaries install/expect the database to be at /var/lib/pgsql/$VERSION/data.

move database to expected location

Stop the database, very important.

systemctl stop postgresql

Move the database into the expected location of the official postgres binaries.

cd /var/lib/pgsql
mkdir 9.3              # or whatever your current old version is
mv data backups 9.3/
mv *.conf 9.3/data/

upgrade to next version

# set some variables for clarity
export PGOLD=9.3
export PGNEW=9.4
export PGSHORTNEW=94
export OLD_BIN=/usr/pgsql-$PGOLD/bin
export NEW_BIN=/usr/pgsql-$PGNEW/bin
export DATA_DIR=/var/lib/pgsql

# don't do this first time
# you already have original database because of previous step
$NEW_BIN/postgresql$PGSHORTNEW-setup initdb

# do the actual upgrade here
cd /tmp
sudo -u postgres $NEW_BIN/pg_upgrade \
-b $OLD_BIN -B $NEW_BIN \
-d $DATA_DIR/$PGOLD/data -D $DATA_DIR/$PGNEW/data

Now do the same again to go from 9.4 to 9.5. Then again from 9.5 to 9.6. Et cetera. You do need to run the initdb command on all subsequent iterations.

export PGOLD=9.4
export PGNEW=9.5
export PGSHORTNEW=95
export OLD_BIN=/usr/pgsql-$PGOLD/bin
export NEW_BIN=/usr/pgsql-$PGNEW/bin

$NEW_BIN/postgresql$PGSHORTNEW-setup initdb

# rinse and repeat

fix permissions

Your permissions are probably completely broken now because the initdb command just made the default pg_hba.conf file which is probably not what you want.

cd /var/lib/pgsql/9.6/data

# you probably just want to copy this file
cp ../../9.3/data/pg_hba.conf .

# you may want to copy the other files, I didn't
# cp ../../9.3/data/*.conf .

enable new version

systemctl enable postgresql-$PGNEW.service
systemctl start postgresql-$PGNEW.service
systemctl status postgresql-$PGNEW.service

psql

Note, psql (and all other postgres commands) are no longer in your path since it's now at /usr/pgsql/9.6/bin/psql. Depending on how often your run commands manually you can either: