help to understand and plot logs

[alansenairj]

I read doc at https://nominatim.org/release-docs/latest/customize/Settings/
I enabled logs at .env and send it to a file too.
I can get logs at a file but I cannot select at database and see logs printed there.

nominatim is running as a service:

User=www-data  
Group=www-data  

● nominatim.service - Nominatim running as a gunicorn application
     Loaded: loaded (/etc/systemd/system/nominatim.service; enabled; preset: enabled)
     Active: active (running) since Tue 2025-07-08 13:00:30 -03; 6s ago
TriggeredBy: ● nominatim.socket

I can receive logs

tail -f /var/log/nominatim/queries.log
[2025-07-08 15:27:01.784] 0.4286 3 search "q=Sao+Paulo&format=json"
[2025-07-08 15:27:02.720] 0.4397 3 search "q=Sao+Paulo&format=json"

I tried to do grants to service owner.

sudo -u postgres psql nominatim
nominatim=# GRANT ALL PRIVILEGES ON TABLE new_query_log TO www_data;
ERROR:  role "www_data" does not exist

GRANT INSERT ON new_query_log TO "www-data";

GRANT SELECT, INSERT, DELETE ON new_query_log TO "www-data";

I try to test these owners

sudo -u www-data psql nominatim
psql (16.9 (Ubuntu 16.9-0ubuntu0.24.04.1))
Type "help" for help.

nominatim=> INSERT INTO new_query_log (type, starttime, ipaddress, query, results) VALUES ('test', NOW(), '127.0.0.1', 'test_query', 1);


nominatim=# \dp new_query_log
                                      Access privileges
 Schema |     Name      | Type  |      Access privileges      | Column privileges | Policies 
--------+---------------+-------+-----------------------------+-------------------+----------
 public | new_query_log | table | nominatim=arwdDxt/nominatim+|                   | 
        |               |       | "www-data"=arwd/nominatim   |                   | 
(1 row)

I check the schema

nominatim=# \d new_query_log
                       Table "public.new_query_log"
   Column   |            Type             | Collation | Nullable | Default 
------------+-----------------------------+-----------+----------+---------
 type       | text                        |           |          | 
 starttime  | timestamp without time zone |           |          | 
 ipaddress  | text                        |           |          | 
 useragent  | text                        |           |          | 
 language   | text                        |           |          | 
 query      | text                        |           |          | 
 searchterm | text                        |           |          | 
 endtime    | timestamp without time zone |           |          | 
 results    | integer                     |           |          | 
 format     | text                        |           |          | 
 secret     | text                        |           |          | 
Indexes:
    "idx_new_query_log_starttime" btree (starttime)

I created a script to keep that table lean

#!/bin/bash

# Log file for the cron job's output
LOG_FILE="/var/log/nominatim/cleanup_cron.log"
DATE=$(date +%Y-%m-%d_%H-%M-%S)

echo "[$DATE] Starting Nominatim DB log cleanup..." >> "$LOG_FILE"

# SQL command to delete records older than X days
# IMPORTANT: Adjust '90 days' to your desired retention period
SQL_COMMAND="DELETE FROM new_query_log WHERE query_date < NOW() - INTERVAL '90 days';"
VACUUM_COMMAND="VACUUM new_query_log;" # Optional: Reclaims disk space, can be slow for large tables

# Execute the delete command
sudo -u postgres psql -d nominatim -c "$SQL_COMMAND" >> "$LOG_FILE" 2>&1
DELETE_STATUS=$?

if [ $DELETE_STATUS -eq 0 ]; then
    echo "[$DATE] Successfully deleted old Nominatim DB log entries." >> "$LOG_FILE"
    # Execute VACUUM command
    echo "[$DATE] Starting VACUUM on new_query_log..." >> "$LOG_FILE"
    sudo -u postgres psql -d nominatim -c "$VACUUM_COMMAND" >> "$LOG_FILE" 2>&1
    VACUUM_STATUS=$?
    if [ $VACUUM_STATUS -eq 0 ]; then
        echo "[$DATE] VACUUM completed successfully." >> "$LOG_FILE"
    else
        echo "[$DATE] VACUUM failed with exit code $VACUUM_STATUS." >> "$LOG_FILE"
    fi
else
    echo "[$DATE] Failed to delete old Nominatim DB log entries. Exit code: $DELETE_STATUS" >> "$LOG_FILE"
fi

echo "[$DATE] Nominatim DB log cleanup finished." >> "$LOG_FILE"

Why this is not working?

[2025-07-08_12-26-55] Starting Nominatim DB log cleanup...
ERROR:  column "query_date" does not exist
LINE 1: DELETE FROM new_query_log WHERE query_date < NOW() - INTERVA...
                                        ^
[2025-07-08_12-26-55] Failed to delete old Nominatim DB log entries. Exit code: 1
[2025-07-08_12-26-55] Nominatim DB log cleanup finished.
[2025-07-08_12-27-06] Starting Nominatim DB log cleanup...
ERROR:  column "query_date" does not exist
LINE 1: DELETE FROM new_query_log WHERE query_date < NOW() - INTERVA...
                                        ^
[2025-07-08_12-27-06] Failed to delete old Nominatim DB log entries. Exit code: 1
[2025-07-08_12-27-06] Nominatim DB log cleanup finished.

[alansenairj]

Where are the correct tables?

I just can get a insert I did for testing.

sudo -u postgres psql nominatim
psql (16.9 (Ubuntu 16.9-0ubuntu0.24.04.1))
Type "help" for help.

nominatim=# SELECT starttime, type, query, ipaddress, results, endtime - starttime AS duration
FROM new_query_log
ORDER BY starttime DESC
LIMIT 10;
         starttime          | type |   query    | ipaddress | results | duration 
----------------------------+------+------------+-----------+---------+----------
 2025-07-08 12:43:15.356355 | test | test_query | 127.0.0.1 |       1 | 
(1 row)

nominatim=# SELECT starttime, type, query, ipaddress, results, endtime - starttime AS duration
FROM new_query_log
ORDER BY starttime DESC
LIMIT 10;
         starttime          | type |   query    | ipaddress | results | duration 
----------------------------+------+------------+-----------+---------+----------
 2025-07-08 12:43:15.356355 | test | test_query | 127.0.0.1 |       1 | 
(1 row)

[mtmail]

nominatim=# GRANT ALL PRIVILEGES ON TABLE new_query_log TO www_data;
ERROR: role "www_data" does not exist

You'd need to create the role in postgresql first (command createuser), see https://nominatim.org/release-docs/latest/admin/Install-on-Ubuntu-24/#setting-up-postgresql

ERROR: column "query_date" does not exist

A couple of lines before you print the full table schema. The table doesn't have a column by that name. Is the script copied from somewhere, from an old tutorial, (or AI generated)?

echo "[$DATE] Nominatim DB log cleanup finished."

Note, that the $DATE is set once at the start of the script. All echo will print the same date. That makes it harder to see how long each step took.

Please tell us with version of Nominatim you're using (nominatim --version). It's probably the latest stable (5.1.0-0) but it helps other users when they read old github issues in the future.

[alansenairj]

Nominatim version 5.1.0-0

[alansenairj]

ERROR: role "www_data" does not exist
Oh no! it was a typo! I did underscore and not a trace. :(

The user was there! And this user has any special grants

[lonvia]

I've recently seen a report somewhere that DB logging isn't working. I can't find the report now but a quick test seems to confirm that.

We haven't used DB logging in a while as file logging is much more efficient.

[lonvia]

Interesting, it looks like I forgot to port DB logging over to the Python frontend. Given that it went unnoticed for quite a while, I'm leaning towards keeping it that way. Code for DB logging would be quite complicated and hard to test.

[alansenairj]

I am worried about log table size increasing too much. They will do a ton of requests and I think it could be a good practice to empty this table sometimes.

BTW log file is working and doing it's job.

Are there some way to keep log table clean?

[mtmail]

Are there some way to keep log table clean?

In your SQL replace query_date with starttime

@lonvia answered that DB logging doesn't work in Nominatim 5.1 and is unsure if it makes sense to fix it. It might simply be removed because you're the first person in a year to notice it's missing.

[alansenairj]

thanx for your reply mtmail.

I think now I can understand the situation. It is empty because it is not working. It just brings me a test query from earlier.

So I think this script will do nothing unless file logging are coming from another place.

[alansenairj]

Ih lonvia.
Thanx for your reply. And to keep it as an help to other people. Great!

I keep all my process documented. I created it. Maybe I could droped this db sometime and forget to create this user again. Thanx.

You are correct. I use an old script and for this server I put it at IA to do better logging job. But I forget to review that code properly. Why it do that? :)
Thanx again :)

#!/bin/bash

# Log file for the cron job's output
LOG_FILE="/var/log/nominatim/cleanup_cron.log"


echo "$(date) Starting Nominatim DB log cleanup..." >> "$LOG_FILE"

# SQL command to delete records older than X days
# IMPORTANT: Adjust '90 days' to your desired retention period
SQL_COMMAND="DELETE FROM new_query_log WHERE query_date < NOW() - INTERVAL '90 days';"
VACUUM_COMMAND="VACUUM new_query_log;" # Optional: Reclaims disk space, can be slow for large tables

# Execute the delete command
sudo -u postgres psql -d nominatim -c "$SQL_COMMAND" >> "$LOG_FILE" 2>&1
DELETE_STATUS=$?

if [ $DELETE_STATUS -eq 0 ]; then
    echo "$(date) Successfully deleted old Nominatim DB log entries." >> "$LOG_FILE"
    # Execute VACUUM command
    echo "$(date) Starting VACUUM on new_query_log..." >> "$LOG_FILE"
    sudo -u postgres psql -d nominatim -c "$VACUUM_COMMAND" >> "$LOG_FILE" 2>&1
    VACUUM_STATUS=$?
    if [ $VACUUM_STATUS -eq 0 ]; then
        echo "$(date) VACUUM completed successfully." >> "$LOG_FILE"
    else
        echo "$(date) VACUUM failed with exit code $VACUUM_STATUS." >> "$LOG_FILE"
    fi
else
    echo "$(date) Failed to delete old Nominatim DB log entries. Exit code: $DELETE_STATUS" >> "$LOG_FILE"
fi

echo "$(date) Nominatim DB log cleanup finished." >> "$LOG_FILE"