Date: Mon, 12 Mar 2018 09:58:58 -0500 From: Valeri Galtsev <galtsev@kicp.uchicago.edu> To: freebsd-questions@freebsd.org Subject: Re: Upgrading PostgreSQL postgresql92 -> postgresql93 Message-ID: <afba7030-fe8c-f76d-f762-46fb2e6c0b50@kicp.uchicago.edu> In-Reply-To: <2c301a0d-1e6f-86e8-5179-b1901a994762@FreeBSD.org> References: <20180312042652.GA98390@admin.sibptus.transneft.ru> <2c301a0d-1e6f-86e8-5179-b1901a994762@FreeBSD.org>
next in thread | previous in thread | raw e-mail | index | archive | help
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. Valeri > > Cheers, > > Matthew > _______________________________________________ > freebsd-questions@freebsd.org mailing list > https://lists.freebsd.org/mailman/listinfo/freebsd-questions > To unsubscribe, send any mail to > "freebsd-questions-unsubscribe@freebsd.org" -- ++++++++++++++++++++++++++++++++++++++++ Valeri Galtsev Sr System Administrator Department of Astronomy and Astrophysics Kavli Institute for Cosmological Physics University of Chicago Phone: 773-702-4247 ++++++++++++++++++++++++++++++++++++++++
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?afba7030-fe8c-f76d-f762-46fb2e6c0b50>