From owner-freebsd-questions@freebsd.org Mon Mar 12 14:59:06 2018 Return-Path: Delivered-To: freebsd-questions@mailman.ysv.freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2610:1c1:1:606c::19:1]) by mailman.ysv.freebsd.org (Postfix) with ESMTP id 2226DF4CC70 for ; Mon, 12 Mar 2018 14:59:06 +0000 (UTC) (envelope-from galtsev@kicp.uchicago.edu) Received: from kicp.uchicago.edu (kicp.uchicago.edu [128.135.20.70]) by mx1.freebsd.org (Postfix) with ESMTP id C50DB7F609 for ; Mon, 12 Mar 2018 14:59:05 +0000 (UTC) (envelope-from galtsev@kicp.uchicago.edu) Received: from point.uchicago.edu (point.uchicago.edu [128.135.52.6]) by kicp.uchicago.edu (Postfix) with ESMTP id 036D871804F for ; Mon, 12 Mar 2018 09:58:58 -0500 (CDT) Subject: Re: Upgrading PostgreSQL postgresql92 -> postgresql93 To: freebsd-questions@freebsd.org References: <20180312042652.GA98390@admin.sibptus.transneft.ru> <2c301a0d-1e6f-86e8-5179-b1901a994762@FreeBSD.org> From: Valeri Galtsev Message-ID: Date: Mon, 12 Mar 2018 09:58:58 -0500 User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:52.0) Gecko/20100101 Thunderbird/52.6.0 MIME-Version: 1.0 In-Reply-To: <2c301a0d-1e6f-86e8-5179-b1901a994762@FreeBSD.org> Content-Type: text/plain; charset=utf-8; format=flowed Content-Language: en-US Content-Transfer-Encoding: 8bit X-BeenThere: freebsd-questions@freebsd.org X-Mailman-Version: 2.1.25 Precedence: list List-Id: User questions List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 12 Mar 2018 14:59:06 -0000 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 ++++++++++++++++++++++++++++++++++++++++