[ckan-dev] Backup problems using paster db dump with spatial extension

[David Read]

David,

These spatial tables aren't accessible unless you run it as
Postgresql's superuser called 'postgres'. By default, the unix 'root'
user isn't a superuser for Postgresql (I'm sure they have their
reasons!).

The solution is to use 'sudo -u postgres' before the
paster/pgdump/psql commands. So in your case:

$ sudo -u postgres /usr/lib/ckan/default/bin/paster db dump
--config=/etc/ckan/default/
production.ini my_ckan_database.pg_dump

If you can confirm this works, I'll update the docs.

David

On 16 October 2014 11:23, David <davisoski at gmail.com> wrote:
> Hi.
>
> I'm trying to backup and restore my database, but I'm getting problems.
>
> Making this steps in another simple ckan installation without any extension
> everything works great. I make backup and restore without problems.
>
>
> My ckan in Internet has spatial-extension and is where the problem occurs.
>
> Using steps in:
> https://ckan.readthedocs.org/en/1377-javascript-tutorial/maintaining/upgrading/index.html
>
> . /usr/lib/ckan/default/bin/activate
> cd /usr/lib/ckan/default/src/ckan
> paster db dump --config=/etc/ckan/default/production.ini
> my_ckan_database.pg_dump
>
> ERROR;
> (default)root at server:/usr/lib/ckan/default/src/ckan# paster db dump --conf
> ig=/etc/ckan/default/production.ini my_ckan_database_16102014.sql
> 2014-10-16 11:42:59,794 WARNI [ckan.plugins.core] Plugin 'googleanalytics'
> is us
> ing deprecated interface IGenshiStreamFilter
> pg_dump: SQL command failed
> pg_dump: Error message from server: ERROR:  permission denied for relation
> spati
> al_ref_sys
> pg_dump: The command was: LOCK TABLE public.spatial_ref_sys IN ACCESS SHARE
> MODE
> 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/com
> mand.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/com
> mand.py", line 143, in invoke
>     exit_code = runner.run(args)
>   File
> "/usr/lib/ckan/default/local/lib/python2.7/site-packages/paste/script/com
> mand.py", line 238, in run
>     result = self.command()
>   File "/usr/lib/ckan/default/src/ckan/ckan/lib/cli.py", line 172, in
> command
>     self.dump()
>   File "/usr/lib/ckan/default/src/ckan/ckan/lib/cli.py", line 246, in dump
>     pg_cmd = self._postgres_dump(dump_path)
>   File "/usr/lib/ckan/default/src/ckan/ckan/lib/cli.py", line 223, in
> _postgres_
> dump
>     self._run_cmd(pg_dump_cmd)
>   File "/usr/lib/ckan/default/src/ckan/ckan/lib/cli.py", line 237, in
> _run_cmd
>     raise SystemError('Command exited with errorcode: %i' % retcode)
> SystemError: Command exited with errorcode: 1
> (default)root at server:/usr/lib/ckan/default/src/ckan#
>
>
>
> _______________________________________________
> ckan-dev mailing list
> ckan-dev at lists.okfn.org
> https://lists.okfn.org/mailman/listinfo/ckan-dev
> Unsubscribe: https://lists.okfn.org/mailman/options/ckan-dev
>