Upgrading Postgres for the CKAN 2.6 release

CKAN 2.6 requires PostgreSQL to be of version 9.2 or later (previously it was 8.4). This is a guide to doing the upgrade if necessary.

What are CKAN’s PostgreSQL connection settings?

Find out the PostgreSQL connection settings, as used by CKAN and Datastore:

grep sqlalchemy.url /etc/ckan/default/production.ini
grep ckan.datastore.write_url /etc/ckan/default/production.ini

where the format of the connection strings is one of these:



If the ‘host’ is not configured as localhost then CKAN is using a PostgreSQL that is running on another machine. In this case, many of the commands below will need running on the remote machine, or if you also have PostgreSQL installed on the CKAN machine then PostgreSQL tools can usually run them on the remote host by using the –host parameter.

What version are you running?

To ask PostgreSQL its version:

sudo -u postgres psql -c 'SHOW server_version;'

Or if PostgreSQL is on a remote host then you can either run the command on that machine or if you have psql installed locally you can use:

psql --host=HOSTNAME --username=USERNAME -W -c 'SHOW server_version;'

(replace HOSTNAME and USERNAME with the values from your connection settings, as previously mentioned. It will prompt you for the password).

The version will look like this:

(1 row)

Ignoring the last number of the three, if your PostgreSQL version number is lower than 9.2 then you should upgrade PostgreSQL before you upgrade to CKAN 2.5 or later.



These instructions install the new PostgreSQL version alongside the existing one, so any install issues can be dealt before switching. However it is still wise to test the whole process on a test machine before upgrading for a public-facing CKAN.


These instructions are for Ubuntu, but can be adapted to other distributions.

  1. If the PostgreSQL cluster that ckan uses is not running on localhost then log-in to the PostgreSQL machine now.

  2. Check to see what PostgreSQL packages are installed:

    aptitude search '~i postgres'

    These instructions assume you have been using the installed package postgresql-9.1. If using ckanext-spatial then you will also have PostGIS too (e.g. postgresql-9.1-postgis), which needs upgrading at the same time.

  3. Install the Postgres Apt repository, containing newer versions. This is for Ubuntu 12.04 (Precise) - for other versions and more information, refer to: http://www.postgresql.org/download/linux/ubuntu/

    echo 'deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main' | sudo tee /etc/apt/sources.list.d/pgdg.list
    wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
    sudo apt-get update
    aptitude search postgresql-9.

    You should now see there are packages for postgresql-9.4 etc.

  4. Install the newer PostgreSQL version. It is suggested you pick the newest stable version (from those listed in the previous step). At the time of writing, 9.5 is listed but is actually still an alpha version, so instead we’ll use 9.4:

    sudo apt-get install postgresql-9.4

    And if you need PostGIS:

    sudo apt-get install postgresql-9.4-postgis
  5. If you have customized any PostgreSQL options, insert them into the new version’s config files.

    You can probably just copy the authentication rules straight:

    sudo cp /etc/postgresql/9.1/main/pg_hba.conf /etc/postgresql/9.4/main/pg_hba.conf

    And you should read through the differences in postgresql.conf. This is a handy way to do this whilst ignoring changes in the comment lines:

    diff -u -B <(grep -vE '^\s*(#|$)' /etc/postgresql/9.1/main/postgresql.conf)  <(grep -vE '^\s*(#|$)' /etc/postgresql/9.4/main/postgresql.conf)

    Once you’ve finished your changes, restart both versions of PostgreSQL:

    sudo /etc/init.d/postgresql restart 9.4
  6. Follow the instructions in 3. Setup a PostgreSQL database to setup PostgreSQL with a user and database. Ensure your username, password and database name match those in your connection settings (see previous section.)

  7. Now log-in to the CKAN machine, if you have a separate PostgreSQL machine.

  8. Activate your virtualenv and switch to the ckan source directory, e.g.:

    . /usr/lib/ckan/default/bin/activate
    cd /usr/lib/ckan/default/src/ckan
  9. Stop your server to prevent further writes to the database (because those changes would be lost):

    sudo service apache2 stop
  10. Create a back-up of the database roles:

    sudo -u postgres pg_dumpall --roles-only > backup_roles.sql

    or for a remote database:

    pg_dumpall --host=HOSTNAME --username=USERNAME -W --roles-only -f backup_roles.sql
  11. Make a note of the names of all the databases in your PostgreSQL so that you can create dumps of them. List them using:

    sudo -u postgres psql -l

    or remotely:

    psql --host=HOSTNAME --username=USERNAME -W -l

    The databases listed should comprise:

    • CKAN database - as given in sqlalchemy.url. Default: ‘ckan_default‘
    • Datastore database - as given in ckan.datastore.write_url. Default: ‘datastore_default‘
    • template0 - should not be dumped
    • template1 - you’ll only need to dump this if you have edited it for some reason

    You may also have:

    • Test CKAN database - default ‘ckan_test‘
    • Test Datastore database - default ‘datastore_test‘

    which do not need to be migrated - they will be regenerated later on.


    If you have other databases apart from these (or have created any PostgreSQL tablespaces) then you’ll have to decide how to deal with them - they are outside the scope of this guide.

  12. Create the backups of the databases you are migrating e.g.:

    sudo -u postgres pg_dump -Fc -b -v ckan_default > backup_ckan.sql
    sudo -u postgres pg_dump -Fc -b -v datastore_default > backup_datastore.sql

    or remotely:

    pg_dump --host=HOSTNAME --username=USERNAME -W ckan_default -f backup_ckan.sql
    pg_dump --host=HOSTNAME --username=USERNAME -W datastore_default -f backup_datastore.sql

    You need to use the -Fc -b options because that is required by PostGIS migration.

  13. Optional: If necessary, update the PostGIS objects (known as a ‘hard upgrade’). Please refer to the documentation if you find any issues.

    perl /usr/share/postgresql/9.4/contrib/postgis-2.1/postgis_restore.pl backup_ckan.sql > backup_ckan_postgis.sql
  14. Restore your PostgreSQL roles into the new PostgreSQL version cluster. If you’re not upgrading to PostgreSQL version 9.4, you’ll need to change the number in this psql command and future ones too. So:

    sudo -u postgres psql --cluster 9.4/main -f backup_roles.sql

    Expect there will be one error:

    psql:backup_roles.sql:22: ERROR:  role "postgres" already exists

    which you can ignore - it should carry on regardless and finish ok.

  15. Create the databases:

    sudo -u postgres createdb --cluster 9.4/main ckan_default
    sudo -u postgres createdb --cluster 9.4/main datastore_default
  16. Optional: If necessary, enable PostGIS on the main database:

    sudo -u postgres psql --cluster 9.4/main -d ckan_default -f /usr/share/postgresql/9.4/contrib/postgis-2.1/postgis.sql
    sudo -u postgres psql --cluster 9.4/main -d ckan_default -f /usr/share/postgresql/9.4/contrib/postgis-2.1/spatial_ref_sys.sql
    sudo -u postgres psql --cluster 9.4/main -d ckan_default -c 'ALTER TABLE geometry_columns OWNER TO ckan_default;'
    sudo -u postgres psql --cluster 9.4/main -d ckan_default -c 'ALTER TABLE geometry_columns OWNER TO ckan_default;'

    To check if PostGIS was properly installed:

    sudo -u postgres psql --cluster 9.4/main -d ckan_default -c "SELECT postgis_full_version()"
  17. Now restore your databases:

    sudo -u postgres psql --cluster 9.4/main -f backup_ckan.sql
    sudo -u postgres psql --cluster 9.4/main -f backup_datastore.sql
  18. Tell CKAN to use the new PostgreSQL database by switching the PostgreSQL port number in the /etc/ckan/default/production.ini. First find the correct port:

    sudo pg_lsclusters

    It is likely that the old PostgreSQL is port 5432 and the new one is on 5433.

    Now edit the /etc/ckan/default/production.ini to insert the port number into the sqlalchemy.url. e.g.:

    sqlalchemy.url = postgresql://ckan_default:pass@localhost:5433/ckan_default

    And restart CKAN e.g.:

  19. If you run the ckan tests then you should recreate the test databases, as described in Testing CKAN.

  20. Once you are happy everything is running ok, you can delete your old PostgreSQL version’s config and database files:

    sudo apt-get purge postgresql-9.1

    If you also have PostGIS installed, remove that too:

    sudo apt-get remove postgresql-9.1-postgis
  21. Download the CKAN package for the new minor release you want to upgrade to (replace the version number with the relevant one):

    wget http://packaging.ckan.org/python-ckan_2.5_amd64.deb