Table of Contents

Upgrading Postgres

Upgrading Postgres

by ross at 07:12:23 on December 18, 2013

There are two methods: pg_dumpall and pg_upgrade. The later needs both the old server and the new version installed at the same time, which is hard to achieve with FreeBSD ports. So we will use pg_dumpall method:

First, create a backup:

# su -l pgsql
> pg_dumpall > /some/path/postgres.backup
> exit

Now, stop and deinstall the old version:

# service postgresql stop
# pkg_delete -f postgresql\*

Move the old pgsql home to some place (mine is /home/pgsql, default is /usr/local/pgsql):

# mv /home/pgsql /home/pgsql.old

Install the new version of Postgres:

# cd /usr/ports/databases/postgresql93-server
# make install clean
# cd /usr/ports/databases/postgresql93-contrib
# make install clean

Move the db dir to home (optional):

# mv /usr/local/pgsql /home/
# pw usermod pgsql -d /home/pgsql

Initialize, start and set the password:

# service postgresql initdb
# service postgresql start
# psql -U pgsql -d postgres
postgres=# alter user pgsql with password 'the_password';

Restore the databases:

# su -l pgsql
> psql postgres < /some/path/postgres.backup
> exit

To complete the upgrade rebuild the clients of Postgres. For example, PHP modules: php5-pgsql, php5-pdo_pgsql...