sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:postgresql.psycopg

[tarasinf]

I am installing Nominatim into docker (custom image based on Ubuntu 22.04) with python frontend, where using external docker DB.

• I installed all required packages and imported test data with Moldova.
• nominatim admin --check-database works well.
• http://localhost:8082/status gives me 500 error.
• I am able to connect to DB from server image suing PGPASSWORD=password psql -h db -p 5432 -U username -d database
• nominatim search --query Dnister throws an error:

root@8e8a59e2c626:/srv/nominatim# nominatim search --query Dnister
2024-01-09 09:06:31: Using project directory: /srv/nominatim
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 225, 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/api.py", line 117, in run
    results = api.search(args.query, **params)
  File "/usr/local/lib/nominatim/lib-python/nominatim/api/core.py", line 709, in search
    return self._loop.run_until_complete(
  File "/usr/lib/python3.10/asyncio/base_events.py", line 649, in run_until_complete
    return future.result()
  File "/usr/local/lib/nominatim/lib-python/nominatim/api/core.py", line 217, in search
    async with self.begin() as conn:
  File "/usr/lib/python3.10/contextlib.py", line 199, in __aenter__
    return await anext(self.gen)
  File "/usr/local/lib/nominatim/lib-python/nominatim/api/core.py", line 140, in begin
    await self.setup_database()
  File "/usr/local/lib/nominatim/lib-python/nominatim/api/core.py", line 96, in setup_database
    engine = sa_asyncio.create_async_engine(dburl, future=True,
  File "/usr/lib/python3/dist-packages/sqlalchemy/ext/asyncio/engine.py", line 40, in create_async_engine
    sync_engine = _create_engine(*arg, **kw)
  File "<string>", line 2, in create_engine
  File "/usr/lib/python3/dist-packages/sqlalchemy/util/deprecations.py", line 309, in warned
    return fn(*args, **kwargs)
  File "/usr/lib/python3/dist-packages/sqlalchemy/engine/create.py", line 534, in create_engine
    entrypoint = u._get_entrypoint()
  File "/usr/lib/python3/dist-packages/sqlalchemy/engine/url.py", line 661, in _get_entrypoint
    cls = registry.load(name)
  File "/usr/lib/python3/dist-packages/sqlalchemy/util/langhelpers.py", line 343, in load
    raise exc.NoSuchModuleError(
sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:postgresql.psycopg

To Reproduce
I can share docker-image, but here looking for ideas how to debug the issue.

Software Environment (please complete the following information):

• Nominatim version: 4.3.2
• Postgresql version: 15.5
• Postgis version: 3.4 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
• OS: Ubuntu 22.04

Hardware Configuration (please complete the following information):

• RAM: 16GB
• number of CPUs: 4
• type and size of disks: SSD, 128GB

Postgresql Configuration:

• created extension
• created two users

Nominatim Configuration:

Additional context

[lonvia]

It can't find the psycopg Python package. Are you sure you have installed it? psycopg is different from psycopg2 and you need both. If the package is installed, then the server might not have the right paths. For example, if you installed them in a virtualenv, your server might not be using it.

[tarasinf]

Thank you for the response!

1. Looks like psycopg and psycopg2 are installed.

root@8e8a59e2c626:/srv/nominatim# pip freeze | grep psycopg
psycopg==3.1.17
psycopg-binary==3.1.17
psycopg2==2.9.2
psycopg2-binary==2.9.9

2. I can access psycopg from python. Python path looks good:

>>> import sys, psycopg
>>> psycopg.__file__
'/usr/local/lib/python3.10/dist-packages/psycopg/__init__.py'
>>> sys.path
['', '/srv/nominatim', '/usr/local/lib/nominatim/lib-python', '/usr/lib/python310.zip', '/usr/lib/python3.10', '/usr/lib/python3.10/lib-dynload', '/usr/local/lib/python3.10/dist-packages', '/usr/lib/python3/dist-packages']

3. I tried to do everything with a single root user in docker, the same result as with two users (nominatim - is the second one)

Any other ideas where I can dive in?

[tarasinf]

The issue was in SQLAlchemy version.
Command pip install SQLAlchemy PyICU psycopg[binary] psycopg2-binary python-dotenv PyYAML falcon uvicorn gunicorn installed old version SQLAlchemy==1.4.31. After upgrade to SQLAlchemy==2.0.25 everything works well.