Upgrade Postgresql from 9.2 to 9.6 on an uberspace

my-way-to-postgresql-9-6-2-on-an-uberspace.md

Upgrade Postgresql from 9.2 to 9.6 on an uberspace

Uberspace (an awesome german web hoster) has superb postgresql support, except that it currently only offers postgresql versions 9.2.x and 9.3.x. I run postgresql 9.6.2 on my uberspace and documented steps for a migration to postgresql 9.6 here.

My goal was to be as close to the original uberspace-postgresql-setup as possible, but I had to copy and modify some uberspace scripts so they correctly use the new postgresql version.

Warning: Here be dragons. This is what I did -- that doesn't mean you should do the same or what I did was clever. Always backup your data and maybe test on a throw-away uberspace first. If you find things to improve, let me know and I'll try to keep this updated.

What I assume you have

You are running Postgresql 9.2.x on your uberspace which is set-up as documented in the uberspace wiki including the part about backups.

Veryify (and remember) your current postgresql version with:

[tessi@uberspace ~]$ psql --version
psql (PostgreSQL) 9.2.6

FYI: With the setup documented in the wiki, you use the uberspace-global postgresql install which lives in /package/host/localhost/postgresql-\${POSTGRESVERSION}. Your data-directory is at ~/postgresql. ~/etc/postgresversion specifies the postgresql version you use. There are also two daemontools services: one (in ~/service/postgresql) to run postgresql and the other (in ~/service/postgresql-backup) to regularly backup your database.

Shutdown the postgres service

To operate a heart it must stop beating. We stop postgresql with:

svc -i ~/service/postgresql

It disconnects all connected clients and then stops the postgresql process.

Note: You should also stop all apps that connect to your postgresql instance.

Do backups first

As always, bash commands from a random guy are not save and your uberspace might explode. I assume you take care of backing up your data. In addition, you can always find nightly backups of your uberspace at the backup server. Just to be extra sure, we perform a manual backup of some postgresql-specific files:

mkdir ~/my-pg-backup
pg_dumpall -f ~/my-pg-backup/dump.sql
cp -r ~/postgresql ~/my-pg-backup/
cp ~/etc/postgresversion ~/my-pg-backup/

Install postgresql 9.6

Now on to the fun: We use toast to install postgresql within our uberspace -- note that (unlike the original setup) the required disk space for our postgresql installation is taken from the 10GB disk space limit.

toast arm https://ftp.postgresql.org/pub/source/v9.6.2/postgresql-9.6.2.tar.gz

(this may take a while)

It installs postgresql 9.6.2 within the ~/.toast directory. You can confirm this with which psql:

[tessi@uberspace ~]$ which psql
~/.toast/armed/bin/psql

Make postgres start with the new version

Postgresql is managed by a deamontools-service living in ~/service/postgresql. We need to tell the run-script where to find our new postgresql. Edit ~/service/postgresql/run to look like this (we only changed PATH and LD_LIBRARY_PATH):

#!/bin/sh -e
export USER=<your-username>
export HOME=/home/<your-username>
. $HOME/etc/postgresversion
export PATH=$HOME/.toast/armed/bin/:$PATH
export LD_LIBRARY_PATH=$HOME/.toast/armed/lib/:$LD_LIBRARY_PATH
exec postgres -D $HOME/postgresql 2>&1

and change ~/etc/postgresversion to point to our postgresql version:

echo "POSTGRESVERSION=9.6" > ~/etc/postgresversion

Now we could (but shouldn't yet) start our new shiny postgresql 9.6. It won't start, since updating major versions usually means changes in the config file and how postgres organizes its data directory. You should probably read through the postgresql changelog to catch all corner cases of the update.

If you would start postgresql now, you could find out what's wrong by inspecting the service log file:

# don't do this yet -- it's just a demonstration how to debug problems
svc -u ~/service/postgresql # start the service
# wait a sec
svc -i ~/service/postgresql # stop it again

# let's look at the error log
tail -f -n20 service/postgresql/log/main/current | tai64nlocal
# ...
# 2017-03-10 22:38:56.538672500 LOG:  skipping missing configuration file "/home/<your-username>/postgresql/postgresql.auto.conf"
# 2017-03-10 22:38:56.538771500 LOG:  unrecognized configuration parameter "unix_socket_directory" in file "/home/<your-username>/postgresql/postgresql.conf" line 69
# 2017-03-10 22:38:56.538815500 FATAL:  configuration file "/home/<your-username>/postgresql/postgresql.conf" contains errors

We see that the unix_socket_directory config option is invalid. It was renamed to unix_socket_directories. OK, the debugging demonstration is over, let's rename the config option for real (in ~/my-pg-backup/postgresql/postgresql.conf). Line 69 should now look like this:

unix_socket_directories = '/home/<your-username>/tmp'

Our config file should be fine now. However, we still need to migrate the actual data.

To upgrade the postgresql data directory, we first remove the old 9.2 directory (remember, we have a backup at ~/my-pg-backup/), then create a new 9.6 cluster with initdb, and finally import our backup data into the new 9.6 cluster.

# remove the data-directory of the 9.2 cluster
rm -rf ~/postgresql

# create the new 9.6 cluster, therefore we need to create a
# temporary password file so we don't have to manually type the password
cat ~/.pgpass | tail -1 | sed -E 's/.*:(.*)/\1/' > ~/pg_password.tmp
initdb --pwfile="${HOME}/pg_password.tmp" --auth=md5 -E UTF8 -D ~/postgresql
rm pg_password.tmp

# copy over the old postgresql.conf, since the uberspace folks did a good job configuring it
cp my-pg-backup/postgresql/postgresql.conf postgresql/postgresql.conf

Now it's time to start the postgresql server again:

svc -u ~/service/postgresql

Postgresql 9.6 should start (remember to have a look at the logfile in ~/service/postgresql/log/main/current, just in case of errors). Now import the backup:

# import our data into the new cluster
psql -f my-pg-backup/dump.sql postgres

At this point I also started my (rails) app, which successfully connected to postgresql and continued to work as if nothing ever changed. Success!

Making postgresql backups work

We still have the ~/service/postgresql-backup service which needs to talk to the new postgresql cluster.

That service is a wrapper around a global uberspace-script located at /usr/local/bin/uberspace-postgresql-backup. Unfortunately, we cannot tell that script to look at our postgresql location (instead it is hardcoded to search the postgresql binaries at the old preinstalled locations). Thus, we need to copy and modify the script (meaning that we now have to maintain that script, which was maintained by uberspace before).

cp /usr/local/bin/uberspace-postgresql-backup ~/bin/

Then modify the file (~/bin/uberspace-postgresql-backup) around line 73 from

# import ${POSTGRESVERSION} and setup $PATH and $LD_LIBRARY_PATH accordingly
. $HOME/etc/postgresversion
export PATH=/package/host/localhost/postgresql-${POSTGRESVERSION}/bin:$PATH
export LD_LIBRARY_PATH=/package/host/localhost/postgresql-${POSTGRESVERSION}/lib/:$LD_LIBRARY_PATH

to look like this

# modified to load a user-local postgresql
# import ${POSTGRESVERSION} and setup $PATH and $LD_LIBRARY_PATH accordingly
. $HOME/etc/postgresversion
export PATH=$HOME/.toast/armed/bin/:$PATH
export LD_LIBRARY_PATH=$HOME/.toast/armed/lib/:$LD_LIBRARY_PATH

Now point the service run-script to our own backup script. Edit the very last line of ~/service/postgresql-backup/run from

/usr/local/bin/uberspace-postgresql-backup -k 7

to

$HOME/bin/uberspace-postgresql-backup -k 7

Finally, you can start the backup service (don't forget to have a look at the service log) and your backups should work as they did before.

svc -u ~/service/postgresql-backup

The big clean up

After you are satisfied with your new postgresql cluster and made sure everything works, it's time to delete the backup files.

If you are in doubt, you might want to wait a night before deleting the backup. Then uberspace might backup it up for you :)

rm -rf my-pg-backup

That's it

Congratulations, you made it!

Let me know if this guide helped you or if you find something to improve.

cheers, tessi

Thanks a million @tessi! It seems I cannot install postgres extensions like citext, pg_trgm or temporal tables. Is there a way to do that with toast package manger?

(When I try to import a postgres_dump CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public; fails because of missing files)

OK, solved:

TL;DR:

Run:

toast arm https://ftp.postgresql.org/pub/source/v9.6.5/postgresql-9.6.5.tar.gz --compilecmd="./configure && make world" --installcmd="make install-world"

Change file ~/service/postgresql/run to:

#!/bin/sh -e
export USER=<your-username>
export HOME=/home/<your-username>
. $HOME/etc/postgresversion
# export PATH=/package/host/localhost/postgresql-${POSTGRESVERSION}/bin:$PATH
# export LD_LIBRARY_PATH=/package/host/localhost/postgresql-${POSTGRESVERSION}/lib/:$LD_LIBRARY_PATH
export PATH=$HOME/.toast/armed/pgsql/bin/:$PATH
export LD_LIBRARY_PATH=$HOME/.toast/armed/pgsql/lib/:$LD_LIBRARY_PATH
exec postgres -D $HOME/postgresql 2>&1

Add the following line to your ~/.bash_profile:

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$HOME/.toast/armed/pgsql/lib

Explanation

Check out the man page of toast package manager and the documentation how to build postgresql from source. Toast downloads the files and compiles them from source. But the default configuration excludes the contrib part of postgres. That's the core modules. So you have to instruct toast to build target world and install target install-world as explained in postgres' documentation. After the installation procedure, you should see your desired core modules and extensions in ~/.toast/armed/pgsql/lib/ e.g. citext.so or pg_trgm.so. Then for some reason, the armed binaries and libraries are not located in .toast/armed/bin and .toast/armed/lib but in.toast/armed/pgsql/bin and .toast/armed/pgsql/lib. That's why I had to update the run script above. Finally, you have to set the LD_LIBRARY_PATH in your default shell (e.g. via .bash_profile) - I encountered an error undefined symbol: PQconnectdbParams when I tried to create a new database with createdb.

@tessi how about updating this gist?

...and a follow up: If you deploy with capistrano, put PATH and LD_LIBRARY_PATH into .bashrc, too. Counter-intuitive as it is, on uberspace servers a non-interactive shell reads from .bashrc. 😕

Great. Thank you both @tessi and @roschaefer :)