Article ID: 120578, created on Mar 17, 2014, last review on May 11, 2014

  • Applies to:
  • Plesk 11.5 for Linux


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



Please pay your attention to the official Debian documentation:

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:

    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

Search Words

Postgresql upgrade

a914db3fdc7a53ddcfd1b2db8f5a1b9c 56797cefb1efc9130f7c48a7d1db0f0c 01bc4c8cf5b7f01f815a7ada004154a2 29d1e90fd304f01e6420fbe60f66f838 0a53c5a9ca65a74d37ef5c5eaeb55d7f

Email subscription for changes to this article
Save as PDF