Solved Upgrading PostgreSQL

Hello good people of FreeBSD

This is more a comment-style question than that of a technical one, as I try to leverage the experience of other Postgres users, rather than the knowledge in the manual. I recently went from version 9 to 10 on my production server, and I didn't think to dump anything from Postgres first, since I have a rsync backup of all the database files. However, that didn't work, as version 10 can't read the files of a version 9. I couldn't easy go back to version 9, since it had been removed from the ports collection, and thus I set up a jail, compiled a version 9 in it from a Postgres source, and dumped everything with pg_dumpall.

But that didn't worked either, since version 10 gave a syntax error when I tried to import the dump.

So I dumped each database separately, recreated the users and passwords on version 10 from my notes, and then imported each database with success after that. But safe to say, that is not a upgrade path I would like to use in the future, since the database was offline for 24 hours together with Postfix and a few websites.

Unfortunately I don't have details enough now to hunt the syntax error down, since things was a bit hectic at the time, so it might very well be my own fault the dump didn't work. I don't know. What I would like to know is this:

How do you, fellow FreeBSD Postgres user, handles upgrades, both minor and major, to your Postgres installations?
 
That makes sense.

But will it not require that both version 9 and 10 is installed at the same time, and the old version (9 in this case) is running?
 
That makes sense.

But will it not require that both version 9 and 10 is installed at the same time, and the old version (9 in this case) is running?
No. pg_dumpall is a client binary. You can update client only.
@VladiBG is right, but postgresql suggests to use newer version of pg_dumpall.
 
No. pg_dumpall is a client binary. You can update client only.
Of course, I didn't think to check which of the ports installs pg_dumpall. I'll try this method in the future. But it's odd it hasn't happened before, because I started on version 8.x, when running into MySQLs 3 byte UTF-8 implementation on a UTF-8 project, and could upgrade from version 8 to 9 without problems. It might had been luck, though.

Thank you for taking the time
 
Back
Top