From owner-freebsd-questions@freebsd.org Mon Mar 12 10:15:12 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 2CE42F329DC for ; Mon, 12 Mar 2018 10:15:12 +0000 (UTC) (envelope-from matthew@FreeBSD.org) Received: from smtp.infracaninophile.co.uk (smtp.infracaninophile.co.uk [IPv6:2001:8b0:151:1:c4ea:bd49:619b:6cb3]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (Client CN "smtp.infracaninophile.co.uk", Issuer "infracaninophile.co.uk" (not verified)) by mx1.freebsd.org (Postfix) with ESMTPS id A88C37326D for ; Mon, 12 Mar 2018 10:15:11 +0000 (UTC) (envelope-from matthew@FreeBSD.org) Received: from leaf.local (unknown [88.202.132.43]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (No client certificate requested) (Authenticated sender: m.seaman@infracaninophile.co.uk) by smtp.infracaninophile.co.uk (Postfix) with ESMTPSA id AE21312FD for ; Mon, 12 Mar 2018 10:15:09 +0000 (UTC) Authentication-Results: smtp.infracaninophile.co.uk; dmarc=none (p=none dis=none) header.from=FreeBSD.org Authentication-Results: smtp.infracaninophile.co.uk/AE21312FD; dkim=none; dkim-atps=neutral Subject: Re: Upgrading PostgreSQL postgresql92 -> postgresql93 To: freebsd-questions@freebsd.org References: <20180312042652.GA98390@admin.sibptus.transneft.ru> From: Matthew Seaman Message-ID: <2c301a0d-1e6f-86e8-5179-b1901a994762@FreeBSD.org> Date: Mon, 12 Mar 2018 10:15:02 +0000 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.13; rv:52.0) Gecko/20100101 Thunderbird/52.6.0 MIME-Version: 1.0 In-Reply-To: <20180312042652.GA98390@admin.sibptus.transneft.ru> Content-Type: text/plain; charset=utf-8; format=flowed Content-Language: en-GB Content-Transfer-Encoding: 7bit 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 10:15:12 -0000 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