Script for upgrading postgres from 9.3 to 9.4

[knutole]

If upgrading from 9.3 to 9.4, the databases created under 9.3 will no longer be available. This is due, at least in part, to databases being stored in /var/lib/postgresql/9.3/ and /var/lib/postgresql/9.4/ respectively.

Seems at least following logical steps are needed:

# upgrade
pg_upgrade -v \
    -d /usr/local/var/postgres \
    -D /usr/local/var/postgres9.5 \
    -b /usr/local/Cellar/postgresql/9.4.5_2/bin/ \
    -B /usr/local/Cellar/postgresql/9.5.0/bin/

# move files
cd /usr/local/var
mv postgres postgres9.4
mv postgres9.5 postgres

# reference: https://stackoverflow.com/questions/24379373/how-to-upgrade-postgres-from-9-4-to-9-5-without-losing-data 

We have scripts for upgrading PostGIS, but definitively need one for upgrading PostgreSQL also. Script should take into consideration that there already exists data in 9.4 folder.

Proceed with caution, possible bugs:

• https://gis.stackexchange.com/questions/97871/postgis-2-1-error-after-update

[strk]

Note that one way to upgrade PostgreSQL would be simply restoring last backup into a new version of the PostGIS docker (with a new PostgreSQL, and possibly even PostGIS version).

I'm tweaking the postgis docker build script to allow building 9.5 (currently only accepting 9.3 and 9.4)

[strk]

With 01b2fcb postgis image can be built with PostgreSQL 9.5.
At that point the build/backup/postgis/restore/do_restore.sh script can be used to restore a given backup into a new pgdata store compatible with the 9.5 docker, and then swap the containers.

The do_restore.sh script sets SYSTEMAPIC_RESTORE_POSTGIS_FROM=${BACKUP_PATH} to force restoring the backup.

I understand you're asking for a way to directly "upgrade" the cluster instead, which should indeed be faster. That'd require mounting both old and new pgdata store volumes somehow, which is likely the main thing to resolve to do that.

PS: I just realized the ticket title is about 9.3->9.4 but your comments are about 9.4->9.5...

[strk]

What happened to the "backup" container, btw ? I see compose/create_storage_containers.sh still creates it but none of the YMLs ever references it. Did policy change ?

[strk]

So it looks having access to both old and new PostgreSQL binaries is mandatory for using pg_upgrade, so the only way to perform such an upgrade would be to have both in the same docker.

This would probably mean building a special docker with access to both versions.

[strk]

Ubuntu/Debian come with a nice pre-made script to upgrade clusters:

pg_upgradecluster -v  9.5 9.4 main

But both the old and new cluster would need to be known and available (pg_lsclusters gives a list).

I'm not sure where the script gets the available clusters info from, maybe from /etc/postgresql which is part of the docker image right now, and not mounted from any volume

[strk]

I've been trying pg_upgrade locally and got some problems with PostGIS too in that the binary still performs a dump/restore but using pg_dump --binary-upgrade which seems to be dumping and restoring all the PostGIS function one after the other (rather than just dumping the CREATE EXTENSION).

I'm more convinced that the best thing to do here is to restore from the dump. Safe, easy, tested, can be done offline etc. etc.

[strk]

Only the backup strategy needs to be clarified (see #54)

[strk]

See how you like this: #55
Or more specifically: https://github.com/systemapic/docker-systemapic/blob/newbackup/build/backup/postgis/README.md

[strk]

I successfully upgraded from 9.3 to 9.4 using the backup/run.sh, restore/run.sh, compose/restart.sh procedure on my local host. It's probably to be refined to ensure no changes are made on the database during upgrade...