Upgrade Postgresql from 9.3 to 11

Posted on Posted in Operating systems Tagged with , ,

I had to update an already unsupported Postgresql instance running on version 9.3 to the latest version 11. Luckily, this process is quite straightforward and all the hard lifting is done by the postgresql update tool.

The only problem there was that the database in question had bloated over the years to such an extent that it was not possible to have two separate data directories for both versions, 9.3 and 11, which is the normal way to go about this – you install the newer version, run pg_upgrade tool against the old data directory and it takes the data, converts them to the new format and saves them in a new directory of the newly installed postgresql version. This is usually more safe as you can go back to the older postgresql version if something went wrong; the old data directory is till intact. If that’s not possible, however, because you don’t have enough space on your system, you can use –link parameter that makes pg_upgrade tool reclaim the old directory as its own.

Here are the steps that I had to take:

  1. enable repository with the new version, as I was on Centos, I followed the instructions from here https://yum.postgresql.org/
  2. install new packages 
    yum install postgresql11 postgresql11-contrib postgresql11-devel postgresql11-libs postgresql11-server
  3. stop and disable the older version service
  4. initialize the new data directory, note that you need to do this as postgres user: 
    /usr/pgsql-11/bin/initdb -D /var/lib/pgsql/11/data"
  5. check consistency between the two versions (again, as postgres user): 
    /usr/pgsql-11/bin/pg_upgrade --old-bindir=/usr/pgsql-9.3/bin/ --new-bindir=/usr/pgsql-11/bin/ --old-datadir=/var/lib/pgsql/9.3/data/ --new-datadir=/var/lib/pgsql/11/data/ --check
  6. and finally run the upgrade itself (again, as postgres user): 
    /usr/pgsql-11/bin/pg_upgrade --old-bindir=/usr/pgsql-9.3/bin/ --new-bindir=/usr/pgsql-11/bin/ --old-datadir=/var/lib/pgsql/9.3/data/ --new-datadir=/var/lib/pgsql/11/data/ --link
  7. run analyze and delete scripts

Needless to say, it’s a very good idea to backup your database before the upgrade and and to have an emergency plan in case things go wrong.