How to upgrade PostgreSQL from 8.4 to 9.1 on Plesk with Debian

Created:

2016-11-16 12:55:19 UTC

Modified:

2017-08-08 13:21:37 UTC

1

Was this article helpful?


Have more questions?

Submit a request

How to upgrade PostgreSQL from 8.4 to 9.1 on Plesk with Debian

Applicable to:

  • Plesk for Linux

Question

How to upgrade PostgreSQL from 8.4 to 9.1 on Plesk with Debian?

Answer

Please pay attention to the official Debian documentation:

http://www.postgresql.org/docs/9.1/static/upgrading.html

For major releases of PostgreSQL, the internal data storage format is subject to change, thus complicating upgrades. The traditional method for moving data to a new major version is to dump and reload the database. Other methods are available, as discussed below.

Note : before you upgraded Plesk and PostgreSQL to 9.1 make dump for all databases using pg_dumpall .

# export PGPASSWORD="********"
# pg_dumpall -Uadmin > /root/postgres_all.sql

and restore the dump after an upgrade:

# psql -Uadmin -d postgres -f /root/postgres_all.sql

If you already upgraded the system and 2 PostgreSQL instances are running at the same time:

# /usr/bin/pg_lsclusters
Version Cluster Port Status Owner Data directory Log file
8.4 main 5432 online postgres /var/lib/postgresql/8.4/main /var/log/postgresql/postgresql-8.4-main.log
9.1 main 5433 online postgres /var/lib/postgresql/9.1/main /var/log/postgresql/postgresql-9.1-main.log

To migrate the existing PostgreSQL databases from 8.4 to 9.1 and change Plesk to operate 9.1 instead of current 8.4 please follow to steps described below:

In PostgreSQL's case it is typical to use port 5432 if it is available. If it is not, most installers will choose the next free port, usually 5433. It is because you already have a PostgreSQL cluster on port 5432. Plesk is able to work with PostgreSQL cluster on the port 5432 only. So the solution is to change the port for the new and old PostgreSQL versions to 5432 and 5433 respectively.

  1. Create dump on the old instance:

    # /usr/lib/postgresql/8.4/bin/pg_dumpall -p 5432 -Uadmin > /root/postgres_all_8.4.sql
  2. Change port on the new instance from 5433 to 5432

    grep ^port /etc/postgresql/8.4/main/postgresql.conf
    port = 5433

    grep ^port /etc/postgresql/9.1/main/postgresql.conf
    port = 5432
  3. Check paths in psa.conf:

    cat /etc/psa/psa.conf | grep PGSQL
    PGSQL_DATA_D /var/lib/postgresql/9.1/main
    PGSQL_CONF_D /etc/postgresql/9.1/main
    PGSQL_BIN_D /usr/lib/postgresql/9.1/bin
  4. Restart PostgreSQL service

    # /etc/init.d/postgresql stop
    [ ok ] Stopping PostgreSQL 8.4 database server: main.
    [ ok ] Stopping PostgreSQL 9.1 database server: main.

    # /etc/init.d/postgresql start
    [ ok ] Starting PostgreSQL 8.4 database server: main.
    [ ok ] Starting PostgreSQL 9.1 database server: main.
  5. Make sure that new PostgreSQL instance is running on port 5432:

    /usr/bin/pg_lsclusters
    Version Cluster Port Status Owner Data directory Log file
    8.4 main 5433 online postgres /var/lib/postgresql/8.4/main /var/log/postgresql/postgresql-8.4-main.log
    9.1 main 5432 online postgres /var/lib/postgresql/9.1/main /var/log/postgresql/postgresql-9.1-main.log
  6. Create admin account:

    export PSA_PASSWORD=r****************v
    /opt/psa/admin/bin/pg_manage --create-admin-login admin
    export PSA_PASSWORD=""
  7. Restore the database:

    export PGPASSWORD=r****************v
    /usr/lib/postgresql/9.1/bin/psql -Uadmin -d postgres -f /root/postgres_all_8.4.sql
Have more questions? Submit a request
Please sign in to leave a comment.