Upgrading PostgreSQL postgresql92 -> postgresql93

Valeri Galtsev galtsev at kicp.uchicago.edu
Mon Mar 12 14:59:06 UTC 2018

On 03/12/18 05:15, Matthew Seaman wrote:
> On 12/03/2018 04:26, Victor Sudakov wrote:
>> Anyone using PostgreSQL, how do you upgrade it?
>> The pg_upgrade utility is not packaged in
>> postgresql93-server-9.3.22.txz or postgresql93-client-9.3.22.txz, and
>> even if it were, it requires the binaries from both the old version
>> and the new version to be present while the pkg system simply replaces
>> postgresql92-* with postgresql93-*
>> Anything less radical than "pg_dumpall | psql" i.e. some in-place
>> procedure?
>> Any ideas please?
> Hi, Victor,
> You are correct that the FreeBSD pkgs for the 9.2 and 9.3 branches of 
> postgresql-server conflict with each other, meaning that pg_upgrade 
> can't work.
> However if you upgrade to 9.6 or preferably 10.0 then pg_upgrade /is/ 
> included in the postgresql-server packages.  There are some other 
> important changes, like the default user changing from pgsql to postgres 
> and the default data dir changing from /usr/local/pgsql/data to 
> /var/db/postgres/data10 (or .../data96)  Unfortunately the different 
> server packages still do conflict with each other, so you can't just 
> install both and use pg_upgrade(1) without any further ado.
> Depending on the size of your data and how much disk space you have 
> available, doing a database dump, upgrading, and then initdb'ing the new 
> version and reloading the backup into it is still probably your best bet.
> If you really need to do an in-place upgrade, then somehow you'll need 
> to get both old and new versions of postgresql-server installed on your 
> system simultaneously.  As you've found, you can't do that easily with 
> pkgs.  I believe you can do tricks like installing the older postgresql 
> pkgs in a jail or chroot and then accessing those from the host, plus 
> fiddling with $PATH and maybe $LD_LIBRARY_PATH but I don't know any of 
> the details there.  Failing that, downloading the sources and building 
> the older version yourself, outside of the ports tree, is a possible 
> approach.  (ie. upgrade your packages to the new postgresql version, and 
> then build a special copy of the old version purely for doing the 
> upgrade, but you need to ensure the freshly compiled "old" binaries go 
> into a version-specific directory so they don't overwrite the binaries 
> for the upgraded version.)

My case is probably somewhat different: I have postgresql in one of 
jails. When I was upgrading jail base from 9.x to 10.x I just built new 
jail base for postgresql, keeping content of old intact, started new 
jail, installed latest postgresql pkg in new jail, initiated database, 
mounted old jail some place inside new jail, then used pg_upgrade. The 
only trick was: you need to install /usr/ports/misc/compat9x in new jail 
to have binaries from old jail run in it (pretty much as you do when you 
upgrade base).

I hope, this helps.


>      Cheers,
>      Matthew
> _______________________________________________
> freebsd-questions at freebsd.org mailing list
> https://lists.freebsd.org/mailman/listinfo/freebsd-questions
> To unsubscribe, send any mail to 
> "freebsd-questions-unsubscribe at freebsd.org"

Valeri Galtsev
Sr System Administrator
Department of Astronomy and Astrophysics
Kavli Institute for Cosmological Physics
University of Chicago
Phone: 773-702-4247

More information about the freebsd-questions mailing list