How to upgrade Postgres version from 8.4 to 9.1 on Plesk server with Debian.

Refers to:

  • Plesk 12.5 for Linux
  • Plesk 11.0 for Linux
  • Plesk 11.5 for Linux
  • Plesk 12.0 for Linux

Created:

2016-11-16 12:55:19 UTC

Modified:

2017-02-14 12:15:00 UTC

1

Was this article helpful?


Have more questions?

Submit a request

How to upgrade Postgres version from 8.4 to 9.1 on Plesk server with Debian.

Symptoms

We need the instructions how to move Plesk to use PostgreSQL 9.1

Cause

Resolution

Please pay your 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.

BEFORE (!) you upgraded Plesk and Postgres to 9.1 make dump for all databases using pg_dumpall .

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

After OS upgrade restore the dump:

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

If you already upgraded the system and 2 Postgres 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's typical to use port 5432 if it is available. If it isn't, 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 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 that correct paths are specified 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.

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
  1. Create admin account:

    export PSA_PASSWORD=r****************v
    /opt/psa/admin/bin/pg_manage --create-admin-login admin
    export PSA_PASSWORD=""
  2. 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.