Saturday, November 2, 2013

Upgrading PostgreSQL 9.1/PostGIS 1.5 to PostgreSQL 9.3/PostGIS 2.1 on Ubuntu 12.04 (precise)

Newer versions of PostgreSQL and PostGIS (9.3 and 2.1) have some nice features like materialized view and raster support respectively. My experience of upgrading both packages from version 9.3 and 1.5 was rather painless and is outlined below.

  1. The versions are not binary compatible, so I have had to dump and restore all databases that I wanted to see after upgrade (no need to dump system templates like template0 and template1)
  2. Replace gis1 gis2 gis3 with a space-delimited list of the databases which you want to preserve after the upgrade. This command will result in a set of binary dump files, one file per database. The file names will containing the name of the database and the date of the dump.

    You will also need a list of database users. You can get the list with \du command in psql.

  3. Remove old packages (this is likely optional, postgresql versions can co-exist but I did not try)
  4. Install new postgresql version following these instructions:
  5. (automatic install of 9.3, may need to specify version explicitly)
  6. By this time you should have postgreql server up and running but not responding to external connections. This is a great time to restore dumped databases. Typical cycle for restoring a database includes:
    1. Recreate all database users
    2. Create the database
    3. Recreate extensions, PostGIS 2.0 uses extension mechanism and there is no need to use scripts
    4. restore all other extensions that are needed by the database
    5. Restore the database from the dump file
    6. Now check the content of the err.txt file, it should be empty
      1. However, if you see errors because of any users or extensions missing, drop the database, repeat all the previous steps and also create the missing users and extensions.
      2. If you see an error like 'invalid command \N', first fix all other problems with the databases and repeat the import again, this error will be likely gone.
  7. Reclaim space and update statistics:
  8. modify hba file to your need (details), enable listening on external address in postgresql.conf and finally do