Upgrading PostgreSQL

R

rihad

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?
 
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 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
 
This thing says take a snapshot of your database with ZFS. I suggest working on a copy if you're not comfortable with that.
 
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.
 
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.
 
obsigna said:
move the data directory out of the way
Click to expand...
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.
 
You must log in or register to reply here.
Back
Top