Upgrading Postgres for the CKAN 2.6 release

3. Setup a PostgreSQL database

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:
postgres://USERNAME:PASSWORD@HOST/DBNAME
postgres://USERNAME:PASSWORD@HOST:PORT/DBNAME

Note
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:
server_version
----------------
9.1.9
(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.

Upgrading¶

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

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

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

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.

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.

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

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

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

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

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

Stop your server to prevent further writes to the database (because those
changes would be lost):
sudo service apache2 stop

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

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.

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

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.

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

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.

Create the databases:
sudo -u postgres createdb --cluster 9.4/main ckan_default
sudo -u postgres createdb --cluster 9.4/main datastore_default

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()"

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

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.:
|restart_apache|

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

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

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