Upgrading issue with postgresql and changeloglock (resolved)

Mathias3 years ago

During upgrade from v4.8 to v4.14, I bumped into an issue that prevented Traccar from modifying the database schema and starting the server. The following relevant errors where visible in the log:

ERROR: Could not release lock - This connection has been closed. - PSQLException (... < DataManager:297 < *:90 < Context:290 < Main:120 < ...)
ERROR: Main method error - FATAL: terminating connection due to idle-in-transaction timeout - PSQLException (... < DataManager:297 < *:90 < Context:290 < Main:120 < ...)

and later when attempting to restart:

INFO: Waiting for changelog lock....
ERROR: Main method error - FATAL: terminating connection due to idle-in-transaction timeout - PSQLException (... < DataManager:295 < *:90 < Context:290 < Main:120 < ...)
WARN: JNA: Callback org.traccar.WindowsService$ServiceMain@928763c threw the following exception - FATAL: terminating connection due to idle-in-transaction timeout - PSQLException (... < DataManager:295 < *:90 < Context:290 < Main:120 < ...)

Each time I had to kill the java process because the traccar service (windows) didn't listen.

The reason was a very limited "idle-in-transaction timeout" setting on our PostGresql database of 5s. First I had to increase this timeout value. I used this sql:

ALTER USER traccar SET idle_in_transaction_session_timeout TO 60000;

Then I needed to remove the existing lock manually by deleting the record in the traccar table "databasechangeloglock". After that, Traccar started successfully and made the necessary changes to the database.

Note, we have set a very limited idle_in_transaction_session_timeout setting ourselves because of reasons. I'm not sure but the standard setting is probably sufficient to allow traccar to execute the changes.

I wanted to write this experience on the forum so others might benefit from my solution.

Anton Tananaev3 years ago

Thanks for sharing this information. You can probably set idle_in_transaction_session_timeout back to a low value after initial migration.

Mathias3 years ago

Yes that's right. Although for the traccar user, keeping into account possible future upgrades, it makes sense to keep the value at 60s. The reasons for limiting the timeout value are not related to traccar.