I can't get PostgreSQL to work with Traccar

heet4 days ago

Tried Traccar with build-in datebase or with MySQL = both worked
With PostgreSQL I cannot get it to work. Firewall deactivated during testing. Trying it since few days.

  • Proxmox, 1 separate Container for Caddy, 1 separate Container for Traccar with PostgreSQL
cat /etc/systemd/system/traccar.service
[Unit]
Description=traccar
After=network.target

[Service]
Type=simple
WorkingDirectory=/opt/traccar
ExecStart=/opt/traccar/jre/bin/java -jar tracker-server.jar conf/traccar.xml
SyslogIdentifier=traccar
SuccessExitStatus=143
WatchdogSec=600
Restart=on-failure
RestartSec=10

[Install]
WantedBy=multi-user.target

Caddyfile:

traccar.my.domain {
        reverse_proxy traccarpostgrescontainerip:8082
        encode gzip
        tls {
                protocols tls1.2 tls1.3
        }
        header {
                Strict-Transport-Security "max-age=31536000;"
                X-Content-Type-Options "nosniff"
                X-Frame-Options "DENY"
                Referrer-Policy "same-origin"
        }
}

/etc/postgresql/16/main/postgresql.conf
#listen_addresses = 'localhost'

etc/postgresql/16/main/pg_hba.conf
see picture

/opt/traccar/conf/traccar.xml
see picture

postgres.png
traccar.png

Created my PostgreSQL-creditials with these commands into postgresql-shell:

CREATE DATABASE traccardb;
CREATE USER traccaruser WITH ENCRYPTED PASSWORD 'mypw';
ALTER USER traccaruser SET password_encryption = 'scram-sha-256';
GRANT ALL PRIVILEGES ON DATABASE traccardb TO traccaruser;
\q

The so-called “AI” chatbots (ChatGPT, Claude, Mistral...) don't help either.

Track-trace4 days ago

Normally you would check logfile

heet4 days ago

Yes, but the log files give absolutely no indication where the problem is, except that Tracker is constantly restarted and PostgreSQL “exited”.

I mean what is wrong with my settings that it works with sgqlite and mysql but not postgresql?
Someone here got it work with postgresql?

Track-trace4 days ago

I would suggest you post the logfile
And whats so interesting about postgres if it works with mysql ?

heet3 days ago
sudo tail -f /var/log/postgresql/postgresql-*.log
2024-11-19 04:28:35.811 CET [651] traccar@traccar ERROR:  permission denied for schema public at character 14
2024-11-19 04:28:35.811 CET [651] traccar@traccar STATEMENT:  CREATE TABLE public.databasechangeloglock (ID INTEGER NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED TIMESTAMP WITHOUT TIME ZONE, LOCKEDBY VARCHAR(255), CONSTRAINT databasechangeloglock_pkey PRIMARY KEY (ID))
2024-11-19 04:28:36.206 CET [651] traccar@traccar ERROR:  relation "public.databasechangeloglock" does not exist at character 22
2024-11-19 04:28:36.206 CET [651] traccar@traccar STATEMENT:  SELECT COUNT(*) FROM public.databasechangeloglock
2024-11-19 04:28:36.214 CET [651] traccar@traccar ERROR:  permission denied for schema public at character 14
2024-11-19 04:28:36.214 CET [651] traccar@traccar STATEMENT:  CREATE TABLE public.databasechangeloglock (ID INTEGER NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED TIMESTAMP WITHOUT TIME ZONE, LOCKEDBY VARCHAR(255), CONSTRAINT databasechangeloglock_pkey PRIMARY KEY (ID))
2024-11-19 04:28:37.537 CET [660] traccar@traccar LOG:  could not receive data from client: Connection reset by peer
2024-11-19 04:28:37.539 CET [659] traccar@traccar LOG:  could not receive data from client: Connection reset by peer
2024-11-19 04:28:37.541 CET [658] traccar@traccar LOG:  could not receive data from client: Connection reset by peer
2024-11-19 04:28:37.543 CET [657] traccar@traccar LOG:  could not receive data from client: Connection reset by peer
2024-11-19 04:28:37.545 CET [656] traccar@traccar LOG:  could not receive data from client: Connection reset by peer
2024-11-19 04:28:37.547 CET [655] traccar@traccar LOG:  could not receive data from client: Connection reset by peer
2024-11-19 04:28:37.549 CET [654] traccar@traccar LOG:  could not receive data from client: Connection reset by peer
2024-11-19 04:28:37.551 CET [652] traccar@traccar LOG:  could not receive data from client: Connection reset by peer
2024-11-19 04:28:37.553 CET [650] traccar@traccar LOG:  could not receive data from client: Connection reset by peer
2024-11-19 04:28:37.554 CET [649] traccar@traccar LOG:  could not receive data from client: Connection reset by peer
2024-11-19 04:28:51.335 CET [667] traccar@traccar ERROR:  relation "public.databasechangeloglock" does not exist at character 22
2024-11-19 04:28:51.335 CET [667] traccar@traccar STATEMENT:  SELECT COUNT(*) FROM public.databasechangeloglock
2024-11-19 04:28:51.441 CET [667] traccar@traccar ERROR:  permission denied for schema public at character 14
2024-11-19 04:28:51.441 CET [667] traccar@traccar STATEMENT:  CREATE TABLE public.databasechangeloglock (ID INTEGER NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED TIMESTAMP WITHOUT TIME ZONE, LOCKEDBY VARCHAR(255), CONSTRAINT databasechangeloglock_pkey PRIMARY KEY (ID))


sudo tail -f /opt/traccar/logs/tracker-server.log
2024-11-19 03:31:17  INFO: Memory limit heap: 3860mb non-heap: 0mb
2024-11-19 03:31:17  INFO: Character encoding: UTF-8
2024-11-19 03:31:17  INFO: Version: 6.5
2024-11-19 03:31:17  INFO: Starting server...
2024-11-19 03:31:17  INFO: HikariPool-1 - Starting...
2024-11-19 03:31:19  INFO: HikariPool-1 - Added connection org.postgresql.jdbc.PgConnection@5d235104
2024-11-19 03:31:19  INFO: HikariPool-1 - Start completed.
2024-11-19 03:31:19  INFO: Set default schema name to public
2024-11-19 03:31:19  INFO: Clearing database change log checksums
2024-11-19 03:31:26 ERROR: Main method error - Database is in a locked state. It could be due to early service termination on a previous launch. To unlock you can run this query: 'UPDATE DATABASECHANGELOGLOCK SET locked = 0'. Make sure the schema is up to date before unlocking the database. - DatabaseLockException (DatabaseModule:102 < <gener:-1 < *:-1 < ... < MainModule:138 < <gener:-1 < ...)
2024-11-19 03:31:37  INFO: Operating system name: Linux version: 6.8.12-3-pve architecture: amd64
2024-11-19 03:31:37  INFO: Java runtime name: OpenJDK 64-Bit Server VM vendor: Eclipse Adoptium version: 21.0.4+7-LTS
2024-11-19 03:31:37  INFO: Memory limit heap: 3860mb non-heap: 0mb
2024-11-19 03:31:37  INFO: Character encoding: UTF-8
2024-11-19 03:31:37  INFO: Version: 6.5
2024-11-19 03:31:37  INFO: Starting server...
2024-11-19 03:31:37  INFO: HikariPool-1 - Starting...
2024-11-19 03:31:39  INFO: HikariPool-1 - Added connection org.postgresql.jdbc.PgConnection@5d235104
2024-11-19 03:31:39  INFO: HikariPool-1 - Start completed.
2024-11-19 03:31:39  INFO: Set default schema name to public
2024-11-19 03:31:39  INFO: Clearing database change log checksums
2024-11-19 03:31:44 ERROR: Main method error - Database is in a locked state. It could be due to early service termination on a previous launch. To unlock you can run this query: 'UPDATE DATABASECHANGELOGLOCK SET locked = 0'. Make sure the schema is up to date before unlocking the database. - DatabaseLockException (DatabaseModule:102 < <gener:-1 < *:-1 < ... < MainModule:138 < <gener:-1 < ...)


sudo journalctl -u caddy -f
Nov 19 04:22:31 caddy caddy[272]: {"level":"info","ts":1731986551.4659288,"logger":"http.log","msg":"server running","name":"srv0","protocols":["h1","h2","h3"]}
Nov 19 04:22:31 caddy caddy[272]: {"level":"info","ts":1731986551.466168,"logger":"http.log","msg":"server running","name":"remaining_auto_https_redirects","protocols":["h1","h2","h3"]}
Nov 19 04:22:31 caddy caddy[272]: {"level":"info","ts":1731986551.4661777,"logger":"http","msg":"enabling automatic TLS certificate management","domains":["traccar.mydomain.com]}
Nov 19 04:22:31 caddy caddy[272]: {"level":"info","ts":1731986551.4694774,"msg":"autosaved config (load with --resume flag)","file":"/var/lib/caddy/.config/caddy/autosave.json"}
Nov 19 04:22:31 caddy caddy[272]: {"level":"info","ts":1731986551.4695678,"msg":"serving initial configuration"}
Nov 19 04:22:31 caddy systemd[1]: Started caddy.service - Caddy.
Nov 19 04:28:44 caddy caddy[272]: {"level":"error","ts":1731986924.2938595,"logger":"http.log.error","msg":"dial tcp 192.168.178.148:8082: connect: connection refused","request":{"remote_ip":"146.70.160.174","remote_port":"53186","client_ip":"146.70.160.174","proto":"HTTP/2.0","method":"GET","host":"traccar.mydomain.com]","uri":"/","headers":{"Accept-Encoding":["gzip, deflate, br, zstd"],"Upgrade-Insecure-Requests":["1"],"Sec-Fetch-Dest":["document"],"Sec-Fetch-Site":["none"],"Sec-Fetch-User":["?1"],"User-Agent":["Mozilla/5.0 (X11; Linux x86_64; rv:132.0) Gecko/20100101 Firefox/132.0"],"Accept":["text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8"],"Accept-Language":["de-DE,de;q=0.8,en-US;q=0.5,en;q=0.3"],"Priority":["u=0, i"],"Te":["trailers"],"Dnt":["1"],"Sec-Gpc":["1"],"Sec-Fetch-Mode":["navigate"]},"tls":{"resumed":false,"version":772,"cipher_suite":4865,"proto":"h2","server_name":"traccar.mydomain.com]"}},"duration":0.000843639,"status":502,"err_id":"hxtzzwcwr","err_trace":"reverseproxy.statusError (reverseproxy.go:1269)"}
Nov 19 04:28:44 caddy caddy[272]: {"level":"error","ts":1731986924.387961,"logger":"http.log.error","msg":"dial tcp 192.168.178.148:8082: connect: connection refused","request":4hbkyvp","err_trace":"reverseproxy.statusError (reverseproxy.go:1269)"}



Changed to...

local   all             postgres                                peer

 TYPE  DATABASE        USER            ADDRESS                 METHOD

"local" is for Unix domain socket connections only
local   all             all                                     peer
 IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
host    traccar         traccar         TRACKERCONTAINERIP/32      md5
 IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
host    all             all             0.0.0.0/0               md5
 Allow replication connections from localhost, by a user with the
 replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256
heet3 days ago
/opt/traccar/conf/traccar.xml
    entry key='database.driver'>org.postgresql.Driver</entry
    entry key='database.url'>jdbc:postgresql://POSTGRESQLCONTAINER-IP:5432/traccar</entry
    entry key='database.user'>traccar</entry
    entry key='database.password'>PW</entry

/etc/systemd/system/traccar.service
[Unit]
Description=traccar
After=network.target

[Service]
Type=simple
WorkingDirectory=/opt/traccar
ExecStart=/opt/traccar/jre/bin/java -jar tracker-server.jar conf/traccar.xml
SyslogIdentifier=traccar
SuccessExitStatus=143
WatchdogSec=600
Restart=on-failure
RestartSec=10

[Install]
WantedBy=multi-user.target
Track-trace3 days ago

You did search the forum or google the errors from traccar server log about locked database ?

heet3 days ago

I've already unlocked it. Never mind. I have given up.
I'll try it again in a few years.

heet2 days ago

Ok, by chance I noticed today that my router has a different mac address for port forwarding with ipv4 than with ipv6. Despite the correct selection of the caddy container.
I deleted the port forwarding, restarted the router, selected the caddy container IP again and then the correct mac address was visible.
Now it starts at least.