RDS support

[avinaxhchandwani]

Can we enhance this to support AWS ec2 plus rds Postgres?

[leonardehrenfried]

Someone already did it: #245 (comment)

[chingan-tsc]

Someone already did it: #245 (comment)

That comment indeed does have some useful suggestions on connecting to external Postgres, but however it don't cover the RDS use case, for instance createuser -s nominatim would just fail on RDS as RDS don't allow you to create superuser by default.

[leonardehrenfried]

Contributions for making this work would be welcome.

[chingan-tsc]

I raised a PR to enhance the support for external Postgres already but it doesn't resolve the issue I mentioned above. Instead for now the quick solution is to run the following commands on RDS beforehand, basically to create the nominatim user before the script tries to create it with superuser permission.

CREATE ROLE nominatim WITH PASSWORD 'password' CREATEDB CREATEROLE LOGIN;
GRANT rds_superuser TO nominatim;

[leonardehrenfried]

Thanks for the PR, looks great.

We get occasional requests for RDS instructions, so would you be able to post a mini-tutorial on how to do it? One or two paragraphs in this issue would be enough. I can then link to it from the main README.

[chingan-tsc]

Sure. A lot of the instructions here actually came from @mausch here (#245 (comment)) but here goes:

In order to have nominatim use an external Postgres database like AWS RDS you can set the following environment variables when launching the Docker container:

NOMINATIM_DATABASE_DSN="pgsql:dbname=nominatim;host=database.ap-southeast-1.rds.amazonaws.com;user=my_user;password=my_pass" 
PGHOST=database.ap-southeast-1.rds.amazonaws.com
PGDATABASE=nominatim 
PGUSER=my_user 
PGPASSWORD=my_pass 

Also I find it easier (again thanks to @mausch) to also set the tokenizer to ICU as such NOMINATIM_TOKENIZER=icu.

Before launching the Docker container though, I would also recommend you to run the following SQL query on your RDS

CREATE ROLE nominatim WITH PASSWORD 'password' CREATEDB CREATEROLE LOGIN;
GRANT rds_superuser TO nominatim;

where it essentially creates a RDS user for nominatim app. Later when you run the container, the start.sh will then be smart enough to skip creating the nominatim superuser (as RDS don't allow you to create superuser) as it already exists and use the preexisting role for all future database communications.

[madelgi]

i apologize for bumping an old thread, but i have a related question that i imagine has a simple answer, so i didn't want to open a new issue. i was able to set this up using @chingan-tsc's method, but i'm wondering how i can prevent the database from getting destroyed/recreated on startup after the initial import has already been completed, i.e., prevent init.sh from running on startup once it has already populated the RDS instance. it seems like doing this could be as simple as allowing a user to override IMPORT_FINISHED, but this seems like it'd be a common problem, so i wanted to check if there's an existing method of solving it

[pabloalcain]

hey @madelgi (or anyone reading). i'm having the same issue and i'm working around it by splitting the init script in two: one for populating the db, the other one to serve it. i had a couple of hiccups because you have to duplicate setting permissions and users, but it's working ok now!

it took me a lot of time realizing that what you said was happening, so thanks for that question!

it isn't much, but if it's of interest i can upload the files i've created

[moshfrid]

hey @madelgi (or anyone reading). i'm having the same issue and i'm working around it by splitting the init script in two: one for populating the db, the other one to serve it. i had a couple of hiccups because you have to duplicate setting permissions and users, but it's working ok now!

it took me a lot of time realizing that what you said was happening, so thanks for that question!

it isn't much, but if it's of interest i can upload the files i've created

@pabloalcain that would be very helpful, thanks!