Intermittent CKAN database backup error

Question

I have scheduled a backup of my CKAN database which resides on a remote UNIX box using cron. I run a simple script with the code below:

. /usr/lib/ckan/default/bin/activate
cd /usr/lib/ckan/default/src/ckan

paster db dump --config=/etc/ckan/default/production.ini /home/gisadmin/CKAN_backup_tmp.pg

The script is always successful when I run it manually. However, the cron job sometimes runs successfully and sometimes it fails with the error message below. To test this I scheduled the job to run every hour and it succeeded approximately half the time with no real pattern that I can see.

Traceback (most recent call last):
  File "/usr/lib/ckan/default/bin/paster", line 9, in <module>
    load_entry_point('PasteScript==1.7.5', 'console_scripts', 'paster')()
  File "/usr/lib/ckan/default/local/lib/python2.7/site-packages/paste/script/command.py", line 104, in run
    invoke(command, command_name, options, args[1:])
  File "/usr/lib/ckan/default/local/lib/python2.7/site-packages/paste/script/command.py", line 143, in invoke
    exit_code = runner.run(args)
  File "/usr/lib/ckan/default/local/lib/python2.7/site-packages/paste/script/command.py", line 238, in run
    result = self.command()
  File "/usr/lib/ckan/default/src/ckan/ckan/lib/cli.py", line 141, in command
    self._load_config()
  File "/usr/lib/ckan/default/src/ckan/ckan/lib/cli.py", line 96, in _load_config
    load_environment(conf.global_conf, conf.local_conf)
  File "/usr/lib/ckan/default/src/ckan/ckan/config/environment.py", line 232, in load_environment
    p.load_all(config)
  File "/usr/lib/ckan/default/src/ckan/ckan/plugins/core.py", line 134, in load_all
    load(*plugins)
  File "/usr/lib/ckan/default/src/ckan/ckan/plugins/core.py", line 167, in load
    plugins_update()
  File "/usr/lib/ckan/default/src/ckan/ckan/plugins/core.py", line 116, in plugins_update
    environment.update_config()
  File "/usr/lib/ckan/default/src/ckan/ckan/config/environment.py", line 357, in update_config
    plugin.configure(config)
  File "/usr/lib/ckan/default/src/ckan/ckanext/datastore/plugin.py", line 77, in configure
    self._check_urls_and_permissions()
  File "/usr/lib/ckan/default/src/ckan/ckanext/datastore/plugin.py", line 121, in _check_urls_and_permissions
    if not self._read_connection_has_correct_privileges():
  File "/usr/lib/ckan/default/src/ckan/ckanext/datastore/plugin.py", line 164, in _read_connection_has_correct_privileges
    write_connection.execute(u'CREATE TABLE _foo ()')
  File "/usr/lib/ckan/default/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1449, in execute
    params)
  File "/usr/lib/ckan/default/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1628, in _execute_text
    statement, parameters
  File "/usr/lib/ckan/default/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1698, in _execute_context
    context)
  File "/usr/lib/ckan/default/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1691, in _execute_context
    context)
  File "/usr/lib/ckan/default/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 331, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (IntegrityError) duplicate key value violates unique constraint "pg_type_typname_nsp_index"
DETAIL:  Key (typname, typnamespace)=(_foo, 2200) already exists.
 'CREATE TABLE _foo ()' {}

Any help is greatly appreciated.

Thanks.

score 0 · Answer 1 · answered Nov 01 '14 at 10:42

I think this is a bug in the datastore code. If you disable the datastore plugin before running the backup, this shouldn't happen. You could for example create a copy of your production.ini file that's exactly the same but with the datastore plugin removed, and use that for the paster db dump command.

Alternatively you could just use PostgreSQL's pg_dump command directly.

Note that when backing up a CKAN site, there are a few things to backup:

CKAN's default "catalog" database, the paster db dump command or pg_dump will get this.
The DataStore database, I don't think paster db dump gets this, but it should be possible with pg_dump.
The contents of the FileStore's storage directory, if you're using it. rsync would be a simple way to back this up.
Your config file(s)

Intermittent CKAN database backup error

1 Answers1