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.
Advertisements

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.