Nominatim with IBM cloud database

[natalia-pokrovskaya]

Describe the bug Hello, we are trying to run nominatim (from a docker image) against IBM cloud database. We don't have superuser rights, but after following this guide : https://nominatim.org/release-docs/latest/admin/Advanced-Installations/#importing-with-a-database-user-without-superuser-rights the problem was solved. However, we are still facing issue with database import during injection of country_osm_grid.sql.gz file

2024-06-20 08:02:34: Using project directory: /app
2024-06-20 08:02:36: Setting up country tables
ERROR:  type "geometry" does not exist
LINE 4:     geometry geometry
                     ^
Traceback (most recent call last):
  File "/usr/local/lib/nominatim/lib-python/nominatim/db/utils.py", line 28, in _pipe_to_proc
    proc.stdin.write(chunk)
BrokenPipeError: [Errno 32] Broken pipe

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/lib/nominatim/lib-python/nominatim/db/utils.py", line 62, in execute_file
    remain = _pipe_to_proc(proc, fdesc)
  File "/usr/local/lib/nominatim/lib-python/nominatim/db/utils.py", line 30, in _pipe_to_proc
    raise UsageError("Failed to execute SQL file.") from exc
nominatim.errors.UsageError: Failed to execute SQL file.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/bin/nominatim", line 12, in <module>
    exit(cli.nominatim(module_dir='/usr/local/lib/nominatim/module',
  File "/usr/local/lib/nominatim/lib-python/nominatim/cli.py", line 228, in nominatim
    return get_set_parser().run(**kwargs)
  File "/usr/local/lib/nominatim/lib-python/nominatim/cli.py", line 121, in run
    return args.command.run(args)
  File "/usr/local/lib/nominatim/lib-python/nominatim/clicmd/setup.py", line 110, in run
    country_info.setup_country_tables(args.config.get_libpq_dsn(),
  File "/usr/local/lib/nominatim/lib-python/nominatim/data/country_info.py", line 118, in setup_country_tables
    db_utils.execute_file(dsn, sql_dir / 'country_osm_grid.sql.gz')
  File "/usr/local/lib/nominatim/lib-python/nominatim/db/utils.py", line 70, in execute_file
    proc.stdin.close()
BrokenPipeError: [Errno 32] Broken pipe

After analysing this sql file, I found that the search_path was explicitely set to

SET search_path = public, pg_catalog;

But for IBM databases, installing extensions in public schema is not allowed

ibmclouddb=> CREATE EXTENSION postgis  with schema public;

ERROR:  extensions can only be installed in schema 'ibm_extension'

ibmclouddb=>

What could be the workaround for this? Is it possible to remove the line for search_path or it least make it configurable through environment variables?

To Reproduce Run nominatim with

NOMINATIM_DATABASE_DSN="pgsql:host=[IBM DATABASE HOST];port=4231;user=[USER];password=[password];dbname=nominatim" nominatim import --continue import-from-file --osm-file my-osm.pbf

Which will result in the stacktrace I shared at the description.

Software Environment (please complete the following information):

• Nominatim version: 4.4.0
• Postgresql version: 3.4.2
• Postgis version: 12.18
• OS: IBM cloud

Hardware Configuration (please complete the following information):

• RAM:
• number of CPUs:
• type and size of disks:

Postgresql Configuration:

none

Nominatim Configuration:

NOMINATIM_DATABASE_DSN="pgsql:host=[IBM DATABASE HOST];port=4231;user=[USER];password=[password];dbname=nominatim"

Additional context

IBM cloud database...

Could someone please help me on this?

Thank you very much in advance!

[mtmail]

In Nominatim's database_import.py I only see

        # Create extensions.
        with conn.cursor() as cur:
            cur.execute('CREATE EXTENSION IF NOT EXISTS hstore')
            cur.execute('CREATE EXTENSION IF NOT EXISTS postgis')

            postgis_version = conn.postgis_version_tuple()
            if postgis_version[0] >= 3:
                cur.execute('CREATE EXTENSION IF NOT EXISTS postgis_raster')

Can you install the extension manually? For example CREATE EXTENSION postgis with schema ibm_extensions;

Can you remove the SET search_path line from the country_grid.sql.gz file after the file gets downloaded? I don't think it has an effect or was added on purpose.

[natalia-pokrovskaya]

@mtmail thank you for your reply! yes, I can do it, but in that case I need to customize the image, right? I mean the normal start of the container will always download the data from https://www.nominatim.org/data/country_grid.sql.gz and try to reinstall it. How can I do to workaround that?

Thank you for your help!

[mtmail]

In your Docker image, are you downloading a stable release (from https://nominatim.org/release) or doing a pull/copy/clone from github? Both works, but I can give better instructions. The stable releases don't download the file, they already have it included in their package.

In your Docker setup add something like

gunzip data/country_grid.sql.gz
sed -i 's/SET search_path//' data/country_grid.sql # removes one line
gzip data/country_grid.sql

Test with a small country, for example Monaco, Liechtenstein, Luxembourg. At this point we only want to make sure Nominatim finishes the full setup.

[natalia-pokrovskaya]

@mtmail I don't see inside of docker image (version 4.4.0) https://hub.docker.com/r/mediagis/nominatim/

root@45891fbe47a3:/# cd /
root@45891fbe47a3:/# find -name country_grid.sql.gz
root@45891fbe47a3:/# 

I see that they actually get nominatim from official releases https://github.com/mediagis/nominatim-docker/blob/master/4.4/Dockerfile#L91

so I suppose that the file gets downloaded during the installation (nominatim import --continue import-from-file --osm-file / my.osm.pbf)

Do you have an idea how I can do it ? I see that the file gets downloaded if it doesn't present locally, but where the data/ folder should be located in this case?

Thank you very much for your help and investigations!

[mtmail]

The file is named country_grid on the nominatim.org server, but country_osm_grid when you install it. I never noticed and was equally confused.

docker run -it mediagis/nominatim:4.4 /bin/bash
find / -type f | grep country_osm_grid
/usr/local/share/nominatim/country_osm_grid.sql.gz

[lonvia]

We could remove all the 'SET' commands from the country file but I do wonder if this really solves your problem. Sounds like you need to be able to run Nominatim in a custom schema.