From owner-freebsd-questions@freebsd.org Tue Mar 13 00:43:54 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 1997BA7C764 for ; Tue, 13 Mar 2018 00:43:54 +0000 (UTC) (envelope-from FreeBSD@shaneware.biz) Received: from ipmail06.adl2.internode.on.net (ipmail06.adl2.internode.on.net [150.101.137.129]) by mx1.freebsd.org (Postfix) with ESMTP id 44AE47CBC4 for ; Tue, 13 Mar 2018 00:43:52 +0000 (UTC) (envelope-from FreeBSD@shaneware.biz) Received: from ppp121-45-76-239.bras1.adl6.internode.on.net (HELO leader.local) ([121.45.76.239]) by ipmail06.adl2.internode.on.net with ESMTP; 13 Mar 2018 11:08:42 +1030 Subject: Re: Upgrading PostgreSQL postgresql92 -> postgresql93 To: Peter Boosten , Victor Sudakov Cc: freebsd-questions@freebsd.org References: <20180312042652.GA98390@admin.sibptus.transneft.ru> <607087CF-3925-4A03-8435-9634EB4A4279@boosten.org> From: Shane Ambler Message-ID: Date: Tue, 13 Mar 2018 11:08:39 +1030 User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:52.0) Gecko/20100101 Thunderbird/52.5.0 MIME-Version: 1.0 In-Reply-To: <607087CF-3925-4A03-8435-9634EB4A4279@boosten.org> Content-Type: text/plain; charset=utf-8 Content-Language: en-AU 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: Tue, 13 Mar 2018 00:43:54 -0000 On 12/03/2018 21:01, Peter Boosten wrote: > > >> On 12 Mar 2018, at 05:26, Victor Sudakov wrote: >> >> Dear Colleagues, >> >> 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? >> >> > > Hi Victor, > > I searched (and found) some procedure, which was basically this: > > -Package the current postgresql and install that in /tmp > -rename the current data directory > -delete current packages and install new ones > -initdb new database > -use some command to ‘copy’ (not cp ;) ) to transport old data to new > > I don’t have the url currently available, but it wasn’t that hard to find either. While the following should work as a script, (replacing some file edits with sed) I copy/paste the following line(s) by line into a terminal window - I use tcsh and run my own poudriere set OLDPGVERS=93 set NEWPGVERS=96 set PGDIR=/usr/local/pgsql ## either (if old package is there) cd ${PGDIR} cp /usr/local/poudriere/data/packages/11stableamd64/All/postgresql${OLDPGVERS}-server.txz ./ tar -xpf postgresql${OLDPGVERS}-server.txz -C ./ set OLDBINDIR=${PGDIR}/usr/local/bin ## or cd /usr/ports/databases/postgresql${OLDPGVERS}-server make mv work/stage/usr/local ${PGDIR}/local-${OLDPGVERS} make clean set OLDBINDIR=${PGDIR}/local-${OLDPGVERS}/bin service postgresql stop cd ${PGDIR} mv data data-${OLDPGVERS} pkg delete -f databases/postgresql${OLDPGVERS}-server pkg delete -f databases/postgresql${OLDPGVERS}-client pkg delete -f databases/postgresql${OLDPGVERS}-contrib pkg delete -f databases/postgresql${OLDPGVERS}-plpython pkg delete -f databases/postgresql${OLDPGVERS}-docs pkg install databases/postgresql${NEWPGVERS}-server pkg install databases/postgresql${NEWPGVERS}-client pkg install databases/postgresql${NEWPGVERS}-contrib pkg install databases/postgresql${NEWPGVERS}-plpython pkg install databases/postgresql${NEWPGVERS}-docs service postgresql initdb mv data/pg_hba.conf data/pg_hba.conf.orig cp data/postgresql.conf data/postgresql.conf.orig cp data-${OLDPGVERS}/pg_hba.conf data/pg_hba.conf vi data/postgresql.conf chown pgsql data/pg_hba.conf chown pgsql data/postgresql.conf ## set to trust for transfer vi data/pg_hba.conf su -l pgsql -c "exec pg_upgrade --check \ --old-datadir ${PGDIR}/data-${OLDPGVERS} \ --new-datadir ${PGDIR}/data \ --old-bindir ${OLDBINDIR} \ --new-bindir /usr/local/bin" ## if OK do the transfer su -l pgsql -c "exec pg_upgrade \ --old-datadir ${PGDIR}/data-${OLDPGVERS} \ --new-datadir ${PGDIR}/data \ --old-bindir ${OLDBINDIR} \ --new-bindir /usr/local/bin" service postgresql start su -l pgsql -c "exec ${PGDIR}/analyze_new_cluster.sh" service postgresql stop ## remove trust changes vi data/pg_hba.conf service postgresql start service postgresql status ## final cleanup cd ${PGDIR} rm analyze_new_cluster.sh rm delete_old_cluster.sh rm -R local-${OLDPGVERS} rm postgresql${OLDPGVERS}-server* rm -r usr rm +COMPACT_MANIFEST rm +MANIFEST rm -R data-${OLDPGVERS} -- FreeBSD - the place to B...Storing Data Shane Ambler