Upgrading PostgreSQL postgresql92 -> postgresql93

Shane Ambler FreeBSD at ShaneWare.Biz
Tue Mar 13 00:43:54 UTC 2018

On 12/03/2018 21:01, Peter Boosten wrote:
>> On 12 Mar 2018, at 05:26, Victor Sudakov <vas at 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 PGDIR=/usr/local/pgsql

## either (if old package is there)
cd ${PGDIR}
tar -xpf postgresql${OLDPGVERS}-server.txz -C ./
set OLDBINDIR=${PGDIR}/usr/local/bin
## or
cd /usr/ports/databases/postgresql${OLDPGVERS}-server
mv work/stage/usr/local ${PGDIR}/local-${OLDPGVERS}
make clean

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 -R data-${OLDPGVERS}

FreeBSD - the place to B...Storing Data

Shane Ambler

More information about the freebsd-questions mailing list