Date: Mon, 12 Mar 2018 10:15:02 +0000 From: Matthew Seaman <matthew@FreeBSD.org> To: freebsd-questions@freebsd.org Subject: Re: Upgrading PostgreSQL postgresql92 -> postgresql93 Message-ID: <2c301a0d-1e6f-86e8-5179-b1901a994762@FreeBSD.org> In-Reply-To: <20180312042652.GA98390@admin.sibptus.transneft.ru> References: <20180312042652.GA98390@admin.sibptus.transneft.ru>
next in thread | previous in thread | raw e-mail | index | archive | help
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
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?2c301a0d-1e6f-86e8-5179-b1901a994762>