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)
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
Fedora stores the actual database at
/var/lib/pgsql/data, the postgres binaries install/expect
the database to be at
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
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 (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:
- add it to your path:
- make a symlink:
cd /usr/local/bin; ln -s /usr/pgsql/9.6/bin/psql psql
- give full path:
/usr/pgsql/9.6/bin/psqlon command line
Easy peasy. Ug.