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