Date: Tue, 13 Mar 2018 11:08:39 +1030 From: Shane Ambler <FreeBSD@ShaneWare.Biz> To: Peter Boosten <peter@boosten.org>, Victor Sudakov <vas@mpeks.tomsk.su> Cc: freebsd-questions@freebsd.org Subject: Re: Upgrading PostgreSQL postgresql92 -> postgresql93 Message-ID: <a01da670-cd2d-d1da-dad9-ac3d9996311f@ShaneWare.Biz> In-Reply-To: <607087CF-3925-4A03-8435-9634EB4A4279@boosten.org> References: <20180312042652.GA98390@admin.sibptus.transneft.ru> <607087CF-3925-4A03-8435-9634EB4A4279@boosten.org>
next in thread | previous in thread | raw e-mail | index | archive | help
On 12/03/2018 21:01, Peter Boosten wrote:
>
>
>> On 12 Mar 2018, at 05:26, Victor Sudakov <vas@mpeks.tomsk.su> 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
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?a01da670-cd2d-d1da-dad9-ac3d9996311f>