Upgrading PostgreSQL postgresql92 -> postgresql93

[Matthew Seaman]

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.)

	Cheers,

	Matthew