# Upgrading PostgreSQL



## rihad (Nov 26, 2019)

This generally applies to any port which has different major version V like category/nameV. Such packages can not only NOT be installed together, a newer version can't even be built (`make package`  insists on installing the named package). Specifically I want to `pg_upgrade` from databases/postgresql10-server to databases/postgresql12-server. The upgrade itself went easy (I used PG-12 built in a custom location just for pg_upgrade to run), but then I need to de-install the old server and install the new one properly as a system package. And this involves spending time on building the port first (we need custom port build options and can't use pre-built packages which use default port options), although theoretically building a port and making a package out of it should be possible regardless of what is currently installed. Is there a clean way around this? I don't really want to risk building PG-12 on another dummy machine, making the package and copying it for installation. Or is this the only feasible way?


----------



## msplsh (Nov 26, 2019)

The short answer is to install in a jail to have both binaries on your system at the same time.






						FreeBSD and pg_upgrade
					

Make a temporary jail environment bsdinstall jail /tmp/pg_upgrade Install the older postgresql into the jail pkg -c /tmp/pg_upgrade install postgresql93-server Stop the postgres...




					kreynolds.com
				




These are my notes on the above:

Prepare for this upgrade by expecting a disaster: Dump PostgreSQL 10's database so you can restore it.

This is NOT going to work if PostgreSQL 10's user is pgsql and PostgreSQL 11's user is postgres.  They have to be the same otherwise you're doing a dump and restore.  You can try using that -U option, but I'm pretty sure you're going to be wasting your time.

I couldn't get the jail to work without doing this
`sudo mount -t devfs devfs //path/to/jail/dev`

When deleting the jail
`umount /path/to/jail/dev`
`chflags -R noschg /path/to/jail`
Then you can `rm -r` the jail


----------



## msplsh (Nov 26, 2019)

This thing says take a snapshot of your database with ZFS.  I suggest working on a copy if you're not comfortable with that.


----------



## obsigna (Nov 26, 2019)

In case your databases are of modest size, you may want to consider to use pg_dumpall(1) on the database cluster provided by the old server. Then stop the old Postgresql server, move the data directory out of the way, and install, initialize and start the new one. Once it is up and running, pipe the dump file into psql(1) for restoring everything. The benefit is, that the dump file serves as a backup in case something goes wrong.


----------



## rihad (Nov 27, 2019)

Nope, the downtime should be as small as possible. It just doesn't feel right that `make package` depends on installing the package first, otherwise it would be just what I need.


----------



## SirDice (Nov 27, 2019)

obsigna said:


> move the data directory out of the way


There should be no need for this as each version uses a data directory that includes the version number in its name. Still a good idea to, at the very least, make a backup copy of it. 

/var/db/postgres/data96 is from PostgreSQL 9.6
/var/db/postgres/data10 is from PostgreSQL 10
/var/db/postgres/data11 is from PostgreSQL 11
etc.

That's the default data directory, unless you've set postgresql_data to something else.


----------



## msplsh (Nov 27, 2019)

If downtime is that important, clusterize the server and upgrade one at a time


----------

