JDBC Persistence with postgresql: No tables are created

Add-ons Persistence Services
1

I’ve setup OpenHAB 4.1.1 with JDBC Peristence.

As far as I can tell, OpenHAB is able to connect to Postgres. However, no tables are created. And my charts don’t show any datapoints (not sure if this is related).

psql output

postgres-# \c openhab
You are now connected to database "openhab" as user "postgres".
openhab-# \dt
Did not find any relations.

Furthermore, invoking jdbc reload on the OpenHAB console, fails with an exception:

openhab> jdbc reload
org.openhab.persistence.jdbc.internal.exceptions.JdbcSQLException: Error in SQL query!!!; ERROR: relation "items" does not exist
  Position: 30 Query: SELECT ItemId, ItemName FROM items Parameters: []; Pool Name= yank-default; SQL= SELECT ItemId, ItemName FROM items

I confirmed the the related JDBC bundles are active:

openhab> bundle:list -s|grep -i JDBC                                                                                                       
269 x Active   x  80 x 4.1.1                  x org.openhab.persistence.jdbc
270 x Active   x  80 x 42.4.3                 x org.postgresql.jdbc

I am stuck here. There seems to be no way to tell OpenHAB to create the database tables. I miss a jdbc re-initialize command that creates OpenHABs database tables.

On the other hand, I am not sure why there are no tables in the first place.

Any help is appreciated.

2

Turns out, the postgresql log had the answer:

Feb 16 12:47:58 exmaple.org postgres[13363]: 2024-02-16 11:47:58.629 GMT [13363] ERROR:  permission denied for schema public at character 28
Feb 16 12:47:58 example.org postgres[13363]: 2024-02-16 11:47:58.629 GMT [13363] STATEMENT:  CREATE TABLE IF NOT EXISTS items (itemid SERIAL NOT NULL, ItemName VARCHAR(500) NOT NULL, CONSTRAINT items_pkey PRIMARY KEY (itemid))

The “permission denied for schema public at character 28” indicates a missing permission. And as it turns out, with Postgresql 15 the following is not sufficient to create the openhab database and provision the openhab user access:

postgres=# create database openhab;
postgres=# create user openhab with encrypted password 'mypass';
postgres=# grant all privileges on database openhab to openhab;

You now also have to

postgres=# \c openhab
openhab=# grant all on schema public to openhab;

Related Stackoverflow answer:

It would be great if OpenHAB would better signal database errors.

3

Unfortunately, I still don’t get any data in the database: no rows are created and there is a single table named ‘items’ (not sure if there are meant to be more tables).

I get

2024-02-16 13:53:26.736 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::query: item is Wandthermostat_Wohnzimmer_Humidity
2024-02-16 13:53:26.738 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::query: unable to find table for item with name: 'Wandthermostat_Wohnzimmer_Humidity', no data in database.
4

After configuring persistence for all items in my main group (“Indoor”) I know see that OpenHAB persists data in the database.

I was under the assumption that having a defaultStrategy specified would be sufficient to persist all items. I remember reading that somewhere in the documentation. However, this appears to be insufficient. I had to explicitly define persistence for items.