I can't get PostgreSQL to work with Traccar

heet a year 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-trace a year ago

Normally you would check logfile

heet a year 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-trace a year ago

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

heet a year 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
heet a year 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-trace a year ago

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

heet a year ago

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

heet a year 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.