Upgrade PostgreSQL version 9 to 10

I had to perform multiple PostgreSQL database upgrades from version 9 to version 10. The database was used for vCloud Director but I believe it is generic enough for other purposes.

The base operating systems I am using is CentOS 7.

Here follows the step-by-step procedure:

  1. Create database backup:
    su – postgres
    pg_dumpall > /tmp/pg9backup
    exit
  2. Shutdown and uninstall old PostgreSQL v9:
    systemctl stop postgresql-9.5.service
    yum remove postgresql*
  3. Archive old datafiles (you will need them later):
    mv /var/lib/pgsql/data/ /data.old
  4. Install new PostgreSQL v10:
    yum -y install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm
    yum -y install postgresql10-server
    systemctl enable postgresql-10
  5. Initiate and start DB:
    su – postgres
    /usr/pgsql-10/bin/initdb
    cp /data.old/pg_hba.conf /var/lib/pgsql/10/data/
    cp /data.old/postgresql.conf /var/lib/pgsql/10/data/
    exit
    systemctl start postgresql-10
  6. Restore backup
    su – postgres
    psql -d postgres -f /tmp/pg9backup
  7. Reboot server. If everything works, you can delete your pg9backup and data.old archive.

3 thoughts on “Upgrade PostgreSQL version 9 to 10

  1. The link to the postgres v10 download is no longer valid for centos. Do you know what one we are supposed to use?

  2. Hi Tomas, will it be viable if instead of upgrading the same PGSQL, install new machine with PGSQL 10 and migrate the DB from old machine to new and repoint the Cells to the new DB?
    what other steps required?
    Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.