Fixes 383: Pass parameters to psycopg instead of a connection string

[jmealo]

This should fix #383.

I didn't have time to get all of the tests passing, but, it's looking like 95%.

[jmealo]

Ah, maybe not quite:

Traceback (most recent call last):
  File "/app/docker/pgosm_flex.py", line 620, in <module>
    run_pgosm_flex()
  File "/usr/local/lib/python3.9/dist-packages/click/core.py", line 1157, in __call__
    return self.main(*args, **kwargs)
  File "/usr/local/lib/python3.9/dist-packages/click/core.py", line 1078, in main
    rv = self.invoke(ctx)
  File "/usr/local/lib/python3.9/dist-packages/click/core.py", line 1434, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/local/lib/python3.9/dist-packages/click/core.py", line 783, in invoke
    return __callback(*args, **kwargs)
  File "/app/docker/pgosm_flex.py", line 98, in run_pgosm_flex
    helpers.set_env_vars(region, subregion, srid, language, pgosm_date,
  File "/app/docker/helpers.py", line 139, in set_env_vars
    os.environ['PGOSM_CONN'] = db.connection_string()
  File "/app/docker/db.py", line 68, in connection_string
    conn_string = psycopg.conninfo(**conn_kwargs)
TypeError: 'module' object is not callable

[jmealo]

This mostly works, but when I run make locally, I get this (on MacOS)...

I'm trying to run the tests on Linux to see if that fixes it.

2024-05-16 10:18:53,573:INFO:pgosm-flex:helpers:2024-05-16 10:18:53  osm2pgsql version 1.11.0
2024-05-16 10:18:53,580:INFO:pgosm-flex:helpers:2024-05-16 10:18:53  ERROR: Connecting to database failed (context=check): local user with ID 501 does not exist
2024-05-16 10:18:53,580:INFO:pgosm-flex:helpers:.
2024-05-16 10:18:54,588:ERROR:pgosm-flex:pgosm_flex:Failed to run osm2pgsql. Return code: 1
Failed to run osm2pgsql. Return code: 1 - Check the log output for details
make: *** [docker-exec-region] Error 1

[jmealo]

@rustprooflabs: I'm currently running an import and can't run the test due to container name conflicts. This may be ready to go. I'll try to test it later after my import is done, but, I'm pretty swamped.}

2024-05-16 10:40:07,088:INFO:pgosm-flex:db:Deploying /app/db/data/roads-us.sql
Traceback (most recent call last):
  File "/app/docker/pgosm_flex.py", line 620, in <module>
    run_pgosm_flex()
  File "/usr/local/lib/python3.9/dist-packages/click/core.py", line 1157, in __call__
    return self.main(*args, **kwargs)
  File "/usr/local/lib/python3.9/dist-packages/click/core.py", line 1078, in main
    rv = self.invoke(ctx)
  File "/usr/local/lib/python3.9/dist-packages/click/core.py", line 1434, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/local/lib/python3.9/dist-packages/click/core.py", line 783, in invoke
    return __callback(*args, **kwargs)
  File "/app/docker/pgosm_flex.py", line 151, in run_pgosm_flex
    git_info=helpers.get_git_info(),
  File "/app/docker/helpers.py", line 195, in get_git_info
    latest_tag = repo.git.describe('--abbrev=0', tags=True)
  File "/usr/local/lib/python3.9/dist-packages/git/cmd.py", line 986, in <lambda>
    return lambda *args, **kwargs: self._call_process(name, *args, **kwargs)
  File "/usr/local/lib/python3.9/dist-packages/git/cmd.py", line 1598, in _call_process
    return self.execute(call, **exec_kwargs)
  File "/usr/local/lib/python3.9/dist-packages/git/cmd.py", line 1388, in execute
    raise GitCommandError(redacted_command, status, stderr_value, stdout_value)
git.exc.GitCommandError: Cmd('git') failed due to: exit code(128)
  cmdline: git describe --tags --abbrev=0
  stderr: 'fatal: No names found, cannot describe anything.'
make: *** [Makefile:167: docker-exec-region] Error 1

I got this on linux which seems very much unrelated.

[rustprooflabs]

@jmealo Thank you for this PR! I'll give this a try in the next couple of days. Assuming nothing major comes up I'd like to get this merged in before next week.

[rustprooflabs]

RE:

  cmdline: git describe --tags --abbrev=0
  stderr: 'fatal: No names found, cannot describe anything.'

I know I've seen this before, you got that when running make? I thought I had some sort of hack in place to prevent that but could be making up memories! Is that error from Ubuntu 22.04 or 24.04? My main system is PopOS 22.04 which behaves consistently like Ubuntu, and all my deployment targets are Ubuntu 20.04 or 22.04 currently.

[jmealo]

24.04, yep, when running make

[rustprooflabs]

Unfortunately, the approach using **kwargs to create the connection string doesn't work with the way the various osm2pgsql commands are currently ran. Those commands run using code like this:

    returncode = helpers.run_command_via_subprocess(cmd=osm2pgsql_command.split(),
                                                    cwd=flex_path,
                                                    print=True)

With the URI style connection string, the .split() returns the following list with the entire connection string being passed as a single option via the -d switch.

['osm2pgsql', '-d', 'postgresql://postgres:mysecretpassword@localhost:5432/pgosm?application_name=pgosm-flex', '--cache=798', '--slim', '--create', '--output=flex', '--style=./run.lua', '/app/output/custom_source_file.osm.pbf']

When the .split() applies to the connection string created from psycopg.conninfo.make_conninfo(), the parts are all separated. This shows why you got the error local user with ID 501 does not exist, osm2pgsql isn't able to properly detect the connection string details.

['osm2pgsql', '-d', 'dbname=pgosm', 'user=postgres', 'host=localhost', 'port=5432', 'application_name=pgosm-flex', 'password=mysecretpassword', '--create', '--output=flex', '--style=./run.lua', '/app/output/region-dc-latest.osm.pbf']

I'm sure this approach could be made to work, but it would take a more significant changes in this project, and likely changes in osm2pgsql-tuner too. In the sake of getting this problem fixed quickly I'm opting for the approach in #386.

Thank you for the time you've put into making this project better for everyone!