Home Assistant Add-on: PostgreSQL + TimescaleDB

Expaso · August 22, 2024, 8:00pm

I don’t have system_stats included in the addon by default. I don’t have it installed anyway.
Perhaps I could add it to the next version of the addon. I have created a feature request for it so I won’t forget.

Expaso · September 1, 2024, 6:28pm

New versions of the addon have been released (pgAdmin as well as Timescale).
For any issues, please use the github issue tracker as mentioned in the addon-docs.

Thank you all for your support and valuable input!

se7entynine · September 2, 2024, 8:16pm

Hey, is there a way to connect the postgresql with home assistant via the socket connection?

My recorder won’t connect with the link from the documentation, e.g. postgresql://@/DB_NAME

Appreciate any help!

maxym · September 3, 2024, 5:20am

socket connection is available only if db is running on the same machine.

Your example of connection URI is incomplete though

Expaso · September 3, 2024, 9:58am

The recorder URL that I use is:

postgresql://<username>:<password>@77b2833f-timescaledb/homeassistant

For fresh installations (you haven’t yet created a user of password yourself), the full connectionstring would be:

postgresql://postgres:homeassistant@77b2833f-timescaledb/homeassistant

You can try that for a start, but I would strongly encourage you to create a separate username, and change the default password of the postgresql user.

The /homeassistant at the end is the postgresql database name. The Addon will create this database for you by default.

The 77b2833f-timescaledb part is fixed for everyone. It’s the unique internal hostname of the addon.
You should only change this if you run the addon-docker-image from a different machine or on a non-supervised installation.

potat0 · September 14, 2024, 9:17pm

Queries and deleting data

these work in pgAdmin4

statistics count:

SELECT
  COUNT(*) as cnt,
  COUNT(*) * 100 / (SELECT COUNT(*) FROM statistics) AS cnt_pct,
  statistics_meta.statistic_id
FROM statistics
LEFT JOIN statistics_meta ON statistics.metadata_id = statistics_meta.id
GROUP BY statistics_meta.statistic_id
ORDER BY cnt DESC

events count:

SELECT
  COUNT(*) as cnt,
  COUNT(*) * 100 / (SELECT COUNT(*) FROM events) AS cnt_pct,
  event_types.event_type
FROM events
INNER JOIN event_types ON events.event_type_id = event_types.event_type_id
GROUP BY event_types.event_type
ORDER BY cnt DESC

states count:

SELECT 
    COUNT(*) cnt,
  	COUNT(*) * 100 / (SELECT COUNT(*) FROM states) AS cnt_pct,
    states_meta.entity_id
FROM states
LEFT JOIN states_meta ON states.metadata_id=states_meta.metadata_id
GROUP BY states_meta.metadata_id
ORDER BY cnt DESC

delete sensor:

DELETE
FROM
  states
WHERE
  metadata_id = (
    SELECT
      metadata_id
    FROM
      states_meta
    WHERE
      entity_id = 'sensor.bed'
  )

deleting sensor only deletes it in STATES but in EVENTS it is still remains, please advise query deleting sensor from events

also query for events to sort by entity_id would be great

also tried deleting from service Recorder: Purge entities but it will not delete from events

efpv · October 2, 2024, 3:57pm

Apologies if this is a daft question but if i have a backup of my HA instance that used the in built SQLite db.
Lets say i spun up a new HA VM with postgres as the main db with the tables all initialised.
If i performed a restore would it not ingest the data into the postgres db or would it just fail.?

maxym · October 2, 2024, 5:06pm

HA doesn’t support data migration between databases. If you want to maintain old data you have to migrate them manually from sqlite to pg. then run HA with the PG as a recorder storage

janSpub · October 6, 2024, 11:42am

Hi there,

I used this great Add-On for some month on Raspberry Pi 3 with an external SSD as the storage.
Now I migrated to an Raspberry Pi 4 with another external SDD.

Just to mention, on that RPi4+SSD had been another HA instance with another TimescaleDB Add-On.

I formatted the SD-Card but not the SSD, flashed a new HA image and recovered from the RPi 3 full backup. For example the Shelly-Integration and the Zigbee2MQTT Add-On work fine.

But the TimescaleDB log shows an error:

s6-rc: info: service legacy-services: stopping
s6-rc: info: service legacy-services successfully stopped
s6-rc: info: service pgagent: stopping
[13:37:32] INFO: Service code-server exited with code 256 (by signal 15)
s6-rc: info: service pgagent successfully stopped
s6-rc: info: service postgres: stopping
[13:37:32] INFO: Service code-server exited with code 256 (by signal 15)
s6-rc: info: service postgres successfully stopped
s6-rc: info: service init-addon: stopping
s6-rc: info: service init-addon successfully stopped
s6-rc: info: service init-user: stopping
s6-rc: info: service init-user successfully stopped
s6-rc: info: service legacy-cont-init: stopping
s6-rc: info: service legacy-cont-init successfully stopped
s6-rc: info: service fix-attrs: stopping
s6-rc: info: service base-addon-log-level: stopping
s6-rc: info: service fix-attrs successfully stopped
s6-rc: info: service base-addon-log-level successfully stopped
s6-rc: info: service base-addon-banner: stopping
s6-rc: info: service base-addon-banner successfully stopped
s6-rc: info: service s6rc-oneshot-runner: stopping
s6-rc: info: service s6rc-oneshot-runner successfully stopped
s6-rc: info: service s6rc-oneshot-runner: starting
s6-rc: info: service s6rc-oneshot-runner successfully started
s6-rc: info: service base-addon-banner: starting

-----------------------------------------------------------
 Add-on: TimescaleDB
 An open-source database built on PostgreSQL for analyzing time-series data with the power and convenience of SQL
-----------------------------------------------------------
 Provided by: Hans van Essen <[email protected]>
-----------------------------------------------------------
 Add-on version: 4.0.1
 There is an update available for this add-on!
 Latest add-on version: 4.2.0
 Please consider upgrading as soon as possible.
 System: Home Assistant OS 13.1  (aarch64 / raspberrypi4-64)
 Home Assistant Core: 2024.9.1
 Home Assistant Supervisor: 2024.09.1
-----------------------------------------------------------
 Please, share the above information when looking for help
 or support in, e.g., GitHub, forums or the Discord chat.
-----------------------------------------------------------
s6-rc: info: service base-addon-banner successfully started
s6-rc: info: service fix-attrs: starting
s6-rc: info: service base-addon-log-level: starting
s6-rc: info: service fix-attrs successfully started
s6-rc: info: service base-addon-log-level successfully started
s6-rc: info: service legacy-cont-init: starting
s6-rc: info: service legacy-cont-init successfully started
s6-rc: info: service init-user: starting
[13:37:39] INFO: Running user-init..
[13:37:39] INFO: done
s6-rc: info: service init-user successfully started
s6-rc: info: service init-addon: starting
[13:37:39] INFO: done
[13:37:39] INFO: Tuning resources..
Using postgresql.conf at this path:
/data/postgres/postgresql.conf

Writing backup to:
max_connections = 25
/tmp/timescaledb_tune.backup202410061337

Recommendations based on 256.00 MB of available memory and 4 CPUs for PostgreSQL 16
timescaledb.last_tuned = '2024-10-06T13:37:39+02:00'
timescaledb.last_tuned_version = '0.15.0'
Saving changes to: /data/postgres/postgresql.conf
[13:37:39] INFO: done
[13:37:39] INFO: Applying max connections..
[13:37:39] INFO: done
s6-rc: info: service init-addon successfully started
s6-rc: info: service postgres: starting
s6-rc: info: service postgres successfully started
s6-rc: info: service pgagent: starting
s6-rc: info: service pgagent successfully started
s6-rc: info: service legacy-services: starting
[13:37:40] INFO: Starting PostgreSQL..
[13:37:40] INFO: done
s6-rc: info: service legacy-services successfully started
2024-10-06 11:37:40.097 UTC [245] LOG:  starting PostgreSQL 16.2 on aarch64-alpine-linux-musl, compiled by gcc (Alpine 13.2.1_git20231014) 13.2.1 20231014, 64-bit
2024-10-06 11:37:40.098 UTC [245] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2024-10-06 11:37:40.098 UTC [245] LOG:  listening on IPv6 address "::", port 5432
2024-10-06 11:37:40.112 UTC [245] LOG:  listening on Unix socket "/run/postgresql/.s.PGSQL.5432"
2024-10-06 11:37:40.134 UTC [255] LOG:  database system was interrupted; last known up at 2024-10-05 17:55:39 UTC
2024-10-06 11:37:40.410 UTC [255] LOG:  unexpected pageaddr C/33AA000 in WAL segment 000000010000000C00000005, LSN C/53AA000, offset 3842048
2024-10-06 11:37:40.410 UTC [255] LOG:  invalid checkpoint record
2024-10-06 11:37:40.410 UTC [255] PANIC:  could not locate a valid checkpoint record
2024-10-06 11:37:40.765 UTC [245] LOG:  startup process (PID 255) was terminated by signal 6: Aborted
2024-10-06 11:37:40.765 UTC [245] LOG:  aborting startup due to startup process failure
2024-10-06 11:37:40.767 UTC [245] LOG:  database system is shut down

Same log after an partial Add-On backup.

Here my config

databases:
  - homeassistant
timescale_enabled:
  - homeassistant
timescaledb:
  telemetry: basic
  maxmemory: 256MB
  maxcpus: 4
max_connections: 50
system_packages: []
init_commands: []
retry_upgrade: false

and Port

May anyone please help?
Cheers,
Jan

Expaso · October 6, 2024, 7:54pm

Hi @janSpub, Thank you!

Did you do a full backup of the addon from the old PI3, and restored that on the new PI4? Or did you just swapped the disks?

Looking at your logs, the database on the PI3 was not shut down properly (cut the power?) instead of stopping the addon first, and PostgreSql has trouble recovering from that because it might lost data during that unexpected powerof.

(I don’t know for sure of course, but it looks that way).

Please check this link for info of the error: postgresql error PANIC: could not locate a valid checkpoint record - Stack Overflow

Basically, you need to run pg_resetwal -f /data/postgres inside the addon.

You can do this by adding the following command the the init-commands in the addon config and press enter:

apt get sudo && sudo -i -u postgres pg_resetwal -f /data/postgres && echo "Wal was successfully reset"

Like so:

and don’t forget to save the configuration with the save button!

Remember, REMOVE THE COMMAND as soon as it has run once, otherwise, you will be resetting your WAL everytime the addon starts. You don’t want that. This is a 1-time workaround for your issue only.

Please post here if this worked for you, and the logs of the first start after adding the command.

janSpub · October 7, 2024, 8:30pm

Hi @Expaso,

thank you very much for your help! Unfortunately it did not work, the apt command raises an error.

/run/s6/basedir/scripts/rc.init: fatal: stopping the container.
s6-rc: info: service legacy-cont-init: stopping
s6-rc: info: service legacy-cont-init successfully stopped
s6-rc: info: service fix-attrs: stopping
s6-rc: info: service base-addon-log-level: stopping
s6-rc: info: service fix-attrs successfully stopped
s6-rc: info: service base-addon-log-level successfully stopped
s6-rc: info: service base-addon-banner: stopping
s6-rc: info: service base-addon-banner successfully stopped
s6-rc: info: service s6rc-oneshot-runner: stopping
s6-rc: info: service s6rc-oneshot-runner successfully stopped
s6-rc: info: service s6rc-oneshot-runner: starting
s6-rc: info: service s6rc-oneshot-runner successfully started
s6-rc: info: service base-addon-banner: starting

-----------------------------------------------------------
 Add-on: TimescaleDB
 An open-source database built on PostgreSQL for analyzing time-series data with the power and convenience of SQL
-----------------------------------------------------------
 Provided by: Hans van Essen <[email protected]>
-----------------------------------------------------------
 Add-on version: 4.0.1
 There is an update available for this add-on!
 Latest add-on version: 4.2.0
 Please consider upgrading as soon as possible.
 System: Home Assistant OS 13.1  (aarch64 / raspberrypi4-64)
 Home Assistant Core: 2024.9.1
 Home Assistant Supervisor: 2024.10.0
-----------------------------------------------------------
 Please, share the above information when looking for help
 or support in, e.g., GitHub, forums or the Discord chat.
-----------------------------------------------------------
s6-rc: info: service base-addon-banner successfully started
s6-rc: info: service fix-attrs: starting
s6-rc: info: service base-addon-log-level: starting
s6-rc: info: service fix-attrs successfully started
s6-rc: info: service base-addon-log-level successfully started
s6-rc: info: service legacy-cont-init: starting
s6-rc: info: service legacy-cont-init successfully started
s6-rc: info: service init-user: starting
[22:09:20] INFO: Running user-init..
/etc/s6-overlay/s6-rc.d/init-user/run: line 24: apt: command not found
[22:09:20] FATAL: Failed executing init command: apt get sudo && sudo -i -u postgres pg_resetwal -f /data/postgres && echo "Wal was successfully reset"
s6-rc: warning: unable to start service init-user: command exited 1
/run/s6/basedir/scripts/rc.init: warning: s6-rc failed to properly bring all the services up! Check your logs (in /run/uncaught-logs/current if you have in-container logging) for more information.
/run/s6/basedir/scripts/rc.init: fatal: stopping the container.
s6-rc: info: service legacy-cont-init: stopping
s6-rc: info: service legacy-cont-init successfully stopped
s6-rc: info: service fix-attrs: stopping
s6-rc: info: service base-addon-log-level: stopping
s6-rc: info: service fix-attrs successfully stopped
s6-rc: info: service base-addon-log-level successfully stopped
s6-rc: info: service base-addon-banner: stopping
s6-rc: info: service base-addon-banner successfully stopped
s6-rc: info: service s6rc-oneshot-runner: stopping
s6-rc: info: service s6rc-oneshot-runner successfully stopped
s6-rc: info: service s6rc-oneshot-runner: starting
s6-rc: info: service s6rc-oneshot-runner successfully started
s6-rc: info: service base-addon-banner: starting

-----------------------------------------------------------
 Add-on: TimescaleDB
 An open-source database built on PostgreSQL for analyzing time-series data with the power and convenience of SQL
-----------------------------------------------------------
 Provided by: Hans van Essen <[email protected]>
-----------------------------------------------------------
 Add-on version: 4.0.1
 There is an update available for this add-on!
 Latest add-on version: 4.2.0
 Please consider upgrading as soon as possible.
 System: Home Assistant OS 13.1  (aarch64 / raspberrypi4-64)
 Home Assistant Core: 2024.9.1
 Home Assistant Supervisor: 2024.10.0
-----------------------------------------------------------
 Please, share the above information when looking for help
 or support in, e.g., GitHub, forums or the Discord chat.
-----------------------------------------------------------
s6-rc: info: service base-addon-banner successfully started
s6-rc: info: service fix-attrs: starting
s6-rc: info: service base-addon-log-level: starting
s6-rc: info: service fix-attrs successfully started
s6-rc: info: service base-addon-log-level successfully started
s6-rc: info: service legacy-cont-init: starting
s6-rc: info: service legacy-cont-init successfully started
s6-rc: info: service init-user: starting
[22:09:25] INFO: Running user-init..
/etc/s6-overlay/s6-rc.d/init-user/run: line 24: apt: command not found
[22:09:25] FATAL: Failed executing init command: apt get sudo && sudo -i -u postgres pg_resetwal -f /data/postgres && echo "Wal was successfully reset"
s6-rc: warning: unable to start service init-user: command exited 1
/run/s6/basedir/scripts/rc.init: warning: s6-rc failed to properly bring all the services up! Check your logs (in /run/uncaught-logs/current if you have in-container logging) for more information.
/run/s6/basedir/scripts/rc.init: fatal: stopping the container.
s6-rc: info: service legacy-cont-init: stopping
s6-rc: info: service legacy-cont-init successfully stopped
s6-rc: info: service fix-attrs: stopping
s6-rc: info: service base-addon-log-level: stopping
s6-rc: info: service fix-attrs successfully stopped
s6-rc: info: service base-addon-log-level successfully stopped
s6-rc: info: service base-addon-banner: stopping
s6-rc: info: service base-addon-banner successfully stopped
s6-rc: info: service s6rc-oneshot-runner: stopping
s6-rc: info: service s6rc-oneshot-runner successfully stopped

I did not swap the disc but this procedure:

RPi3+SSD#1 → Backup HA-Instance#1
RPi4+SSD#2 → Backup HA-Instance#2 (just in case)
RPi4 new image but SSD#2 not formated (I guess that was not necessary, could have restored from HA-Instance#2.)
RPi4+SSD#2 full backup HA-Instance#1 restored from that new image.

No, after steps 1) and 2) I actually shot both RPis down properly, but does that matter, since I restored the backups? Should I have stopped the Add-On before executing the backup?

Expaso · October 7, 2024, 8:38pm

Huh? eehm I don’t know what I was smoking, but I gave you the wrong command indeed. Sorry…

Try this one:

apk add sudo && sudo -i -u postgres pg_resetwal -f /data/postgres && echo "Wal was successfully reset"

janSpub · October 7, 2024, 8:43pm

Hi @Expaso,

here is another puzzle.
Every Sunday I created a backup automatically using this Add-On. hassio-addons/samba-backup at master · thomasmauerer/hassio-addons · GitHub

But I executed those Backups from the GUI manually.

After restoring from the last auto-backup, the log is slightly another one.

-----------------------------------------------------------
 Add-on: TimescaleDB
 An open-source database built on PostgreSQL for analyzing time-series data with the power and convenience of SQL
-----------------------------------------------------------
 Provided by: Hans van Essen <[email protected]>
-----------------------------------------------------------
 Add-on version: 4.0.1
 There is an update available for this add-on!
 Latest add-on version: 4.2.0
 Please consider upgrading as soon as possible.
 System: Home Assistant OS 13.1  (aarch64 / raspberrypi4-64)
 Home Assistant Core: 2024.9.1
 Home Assistant Supervisor: 2024.10.0
-----------------------------------------------------------
 Please, share the above information when looking for help
 or support in, e.g., GitHub, forums or the Discord chat.
-----------------------------------------------------------
s6-rc: info: service base-addon-banner successfully started
s6-rc: info: service fix-attrs: starting
s6-rc: info: service base-addon-log-level: starting
s6-rc: info: service fix-attrs successfully started
s6-rc: info: service base-addon-log-level successfully started
s6-rc: info: service legacy-cont-init: starting
s6-rc: info: service legacy-cont-init successfully started
s6-rc: info: service init-user: starting
[22:37:37] INFO: Running user-init..
[22:37:37] INFO: done
s6-rc: info: service init-user successfully started
s6-rc: info: service init-addon: starting
[22:37:37] INFO: done
[22:37:38] INFO: Tuning resources..
Using postgresql.conf at this path:
/data/postgres/postgresql.conf

Writing backup to:
/tmp/timescaledb_tune.backup202410072237

Recommendations based on 256.00 MB of available memory and 4 CPUs for PostgreSQL 16
max_connections = 25
timescaledb.last_tuned = '2024-10-07T22:37:38+02:00'
timescaledb.last_tuned_version = '0.15.0'
Saving changes to: /data/postgres/postgresql.conf
[22:37:38] INFO: done
[22:37:38] INFO: Applying max connections..
[22:37:38] INFO: done
s6-rc: info: service init-addon successfully started
s6-rc: info: service postgres: starting
s6-rc: info: service postgres successfully started
s6-rc: info: service pgagent: starting
s6-rc: info: service pgagent successfully started
s6-rc: info: service legacy-services: starting
[22:37:38] INFO: Starting PostgreSQL..
[22:37:38] INFO: done
s6-rc: info: service legacy-services successfully started
2024-10-07 20:37:38.446 UTC [244] LOG:  starting PostgreSQL 16.2 on aarch64-alpine-linux-musl, compiled by gcc (Alpine 13.2.1_git20231014) 13.2.1 20231014, 64-bit
2024-10-07 20:37:38.447 UTC [244] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2024-10-07 20:37:38.447 UTC [244] LOG:  listening on IPv6 address "::", port 5432
2024-10-07 20:37:38.464 UTC [244] LOG:  listening on Unix socket "/run/postgresql/.s.PGSQL.5432"
2024-10-07 20:37:38.485 UTC [255] LOG:  database system was interrupted; last known up at 2024-09-22 21:52:49 UTC
2024-10-07 20:37:38.823 UTC [255] LOG:  invalid record length at B/8130B4A0: expected at least 24, got 0
2024-10-07 20:37:38.823 UTC [255] LOG:  invalid checkpoint record
2024-10-07 20:37:38.823 UTC [255] PANIC:  could not locate a valid checkpoint record
2024-10-07 20:37:39.230 UTC [244] LOG:  startup process (PID 255) was terminated by signal 6: Aborted
2024-10-07 20:37:39.230 UTC [244] LOG:  aborting startup due to startup process failure
2024-10-07 20:37:39.232 UTC [244] LOG:  database system is shut down

Actually I am kinda puzzled. First thing after installing the TimescaleDB Add-On was to check, if the samba-backup worked for that Add-On. And it did! So I thought to be set nice and sound!?

So, can you please recommend a solid way for a TimescaleDB backup?

janSpub · October 7, 2024, 9:07pm

Alright, so here is what I did now.
5. Back to the backup, which resulted in this error and your answer was addressed to.

Same error.
Your second solution.

And now it works! Great!

s6-rc: info: service legacy-services: stopping
s6-rc: info: service legacy-services successfully stopped
s6-rc: info: service pgagent: stopping
[22:55:20] INFO: Service code-server exited with code 256 (by signal 15)
s6-rc: info: service pgagent successfully stopped
s6-rc: info: service postgres: stopping
[22:55:20] INFO: Service code-server exited with code 256 (by signal 15)
s6-rc: info: service postgres successfully stopped
s6-rc: info: service init-addon: stopping
s6-rc: info: service init-addon successfully stopped
s6-rc: info: service init-user: stopping
s6-rc: info: service init-user successfully stopped
s6-rc: info: service legacy-cont-init: stopping
s6-rc: info: service legacy-cont-init successfully stopped
s6-rc: info: service fix-attrs: stopping
s6-rc: info: service base-addon-log-level: stopping
s6-rc: info: service fix-attrs successfully stopped
s6-rc: info: service base-addon-log-level successfully stopped
s6-rc: info: service base-addon-banner: stopping
s6-rc: info: service base-addon-banner successfully stopped
s6-rc: info: service s6rc-oneshot-runner: stopping
s6-rc: info: service s6rc-oneshot-runner successfully stopped
s6-rc: info: service s6rc-oneshot-runner: starting
s6-rc: info: service s6rc-oneshot-runner successfully started
s6-rc: info: service base-addon-banner: starting

-----------------------------------------------------------
 Add-on: TimescaleDB
 An open-source database built on PostgreSQL for analyzing time-series data with the power and convenience of SQL
-----------------------------------------------------------
 Provided by: Hans van Essen <[email protected]>
-----------------------------------------------------------
 Add-on version: 4.0.1
 There is an update available for this add-on!
 Latest add-on version: 4.2.0
 Please consider upgrading as soon as possible.
 System: Home Assistant OS 13.1  (aarch64 / raspberrypi4-64)
 Home Assistant Core: 2024.9.1
 Home Assistant Supervisor: 2024.10.0
-----------------------------------------------------------
 Please, share the above information when looking for help
 or support in, e.g., GitHub, forums or the Discord chat.
-----------------------------------------------------------
s6-rc: info: service base-addon-banner successfully started
s6-rc: info: service fix-attrs: starting
s6-rc: info: service base-addon-log-level: starting
s6-rc: info: service fix-attrs successfully started
s6-rc: info: service base-addon-log-level successfully started
s6-rc: info: service legacy-cont-init: starting
s6-rc: info: service legacy-cont-init successfully started
s6-rc: info: service init-user: starting
[22:55:25] INFO: Running user-init..
fetch https://dl-cdn.alpinelinux.org/alpine/v3.19/main/aarch64/APKINDEX.tar.gz
fetch https://dl-cdn.alpinelinux.org/alpine/v3.19/community/aarch64/APKINDEX.tar.gz
(1/1) Installing sudo (1.9.15_p2-r0)
Executing busybox-1.36.1-r15.trigger
OK: 1313 MiB in 193 packages
Write-ahead log reset
Wal was successfully reset
[22:55:28] INFO: done
s6-rc: info: service init-user successfully started
s6-rc: info: service init-addon: starting
[22:55:28] INFO: done
[22:55:28] INFO: Tuning resources..
Using postgresql.conf at this path:
/data/postgres/postgresql.conf

Writing backup to:
/tmp/timescaledb_tune.backup202410072255

Recommendations based on 256.00 MB of available memory and 4 CPUs for PostgreSQL 16
max_connections = 25
timescaledb.last_tuned = '2024-10-07T22:55:28+02:00'
timescaledb.last_tuned_version = '0.15.0'
Saving changes to: /data/postgres/postgresql.conf
[22:55:28] INFO: done
[22:55:28] INFO: Applying max connections..
[22:55:28] INFO: done
s6-rc: info: service init-addon successfully started
s6-rc: info: service postgres: starting
s6-rc: info: service postgres successfully started
s6-rc: info: service pgagent: starting
s6-rc: info: service pgagent successfully started
s6-rc: info: service legacy-services: starting
[22:55:29] INFO: Starting PostgreSQL..
[22:55:29] INFO: done
s6-rc: info: service legacy-services successfully started
2024-10-07 20:55:29.118 UTC [254] LOG:  starting PostgreSQL 16.2 on aarch64-alpine-linux-musl, compiled by gcc (Alpine 13.2.1_git20231014) 13.2.1 20231014, 64-bit
2024-10-07 20:55:29.118 UTC [254] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2024-10-07 20:55:29.118 UTC [254] LOG:  listening on IPv6 address "::", port 5432
2024-10-07 20:55:29.132 UTC [254] LOG:  listening on Unix socket "/run/postgresql/.s.PGSQL.5432"
2024-10-07 20:55:29.152 UTC [264] LOG:  database system was shut down at 2024-10-07 20:55:27 UTC
2024-10-07 20:55:29.201 UTC [254] LOG:  database system is ready to accept connections
2024-10-07 20:55:29.225 UTC [267] LOG:  TimescaleDB background worker launcher connected to shared catalogs
2024-10-07 20:55:30.052 UTC [271] LOG:  the "timescaledb" extension is not up-to-date
2024-10-07 20:55:30.052 UTC [271] HINT:  The most up-to-date version is 2.16.1, the installed version is 2.14.2.
[22:55:30] INFO: Starting PgAgent..
[22:55:30] INFO: Create database if not exist: 'homeassistant'
[22:55:30] INFO: done
[22:55:30] INFO: done
[22:55:30] NOTICE: TimescaleDb is running!

Expaso · October 8, 2024, 7:28am

Great news!!
I am glad this worked. Good luck with your upgraded setup.

For the backup part: I will answer that question shortly. Basically, the HA backup takes no notion of shutting down anything, so that could lead to this.
We can circumvent that fortunately…

janSpub · October 8, 2024, 8:02pm

Hi @Expaso,

unfortunately there is another issue, which might be related to the update.

The ltss custom-component trys to update the entities but fails. Here is an example log:

2024-10-08 19:55:06.600 UTC [299] ERROR:  heap tid from index tuple (12810,28) points past end of heap page line pointer array at offset 97 of block 1 in index "_hyper_1_10_chunk_ltss_time_idx"
2024-10-08 19:55:06.600 UTC [299] STATEMENT:  INSERT INTO ltss (time, entity_id, state, attributes, location) VALUES ('2024-10-08T19:55:06.591046+00:00'::timestamptz, 'sensor.total_power', '247.86', '{"state_class": "measurement", "unit_of_measurement": "W", "device_class": "power", "friendly_name": "Total Power"}', ST_GeomFromEWKT(NULL))

But there are some other tables on that database I created for some very small amount of data to be recorded manually. They work fine.

Do you have another hint, please?

Expaso · October 9, 2024, 8:04am

There still seems to be some corruption on the DB itself.

Can you try executing this command in pgAdmin for this database: REINDEX (VERBOSE) TABLE CONCURRENTLY ltss;

janSpub · October 9, 2024, 7:40pm

Executed with psql for convenience.

homeassistant=# REINDEX (VERBOSE) TABLE CONCURRENTLY ltss;
ERROR:  concurrent index creation on hypertables is not supported

Expaso · October 10, 2024, 6:27am

Allright. Stubborn little hypertable. Can you try without the CONCURRENTLY keyword?

janSpub · October 10, 2024, 10:58am

Same error.