Category Archives: PostgreSQL

Anything related to the PostgreSQL DBMS

Upgrading PostgreSQL 9.4.6 to 9.5.1

All commands were done as the root user unless indicated.

su -l pgsql
pg_dumpall | bzip2 -9c > all-db-9.4.6-2016-02-17.sql.bz2
chmod 0600 all-db-9.4.6-2016-02-17.sql.bz2
exit
/usr/local/etc/rc.d/postgresql stop
make -C /usr/ports/databases/postgresql95-server config-recursive
pkg delete databases/postgresql94-contrib
portupgrade -fpvo databases/postgresql95-client databases/postgresql94-client
portupgrade -Nfpv databases/postgresql95-contrib
portupgrade -fpvo databases/postgresql95-server databases/postgresql94-server
portupgrade -fprv -x databases/postgresql95-client -x databases/postgresql95-server -x databases/postgresql95-contrib databases/postgresql95-client
mv /usr/local/pgsql/data /usr/local/pgsql/data0
su -l pgsql -c 'mkdir /usr/local/pgsql/data'
/usr/local/etc/rc.d/postgresql initdb
/usr/local/etc/rc.d/postgresql start
su -l pgsql
bzcat all-db-9.4.6-2016-02-17.sql.bz2 | psql -f - template1
# Transfer all relevant settings from /usr/local/pgsql/data0 to /usr/local/pgsql/data for pg_hba.conf and postgresql.conf
exit
/usr/local/etc/rc.d/postgresql restart
su -l pgsql
pg_dumpall | bzip2 -9c > all-db-9.5.1-2016-02-17.sql.bz2
chmod 0600 all-db-9.5.1-2016-02-17.sql.bz2
exit
rm -R /usr/local/pgsql/data0