Home Assistant Add-on: PostgreSQL + TimescaleDB

Hmm, pgAgent seems to be quite unreliable in terms of marking jobs as done.
Most of the scheduled backups so far were stuck in state ‘r’ (running) and never set to ‘s’ (success), even though the pg_dump process itself worked just fine.

This means that the backup is run exactly once and then never again (since it is still “running”). Have to reset the jobs manually.
Anyone else experiencing this?

Hmm will do some more testing myself…
Do you have any additional logging?

Unfortunately no. Not much going on.

Just tried a simple echo "Hello World" job which is also stuck on ‘running’.

I’ve been fighting this one today, and have had various results.
It basically boils down to pgAgent running the job just fine, but when the script is finished, the connection used to write back the job result is gone, leaving the job on running, thus failing to start again.

I really have no clue why the connection to postgres is gone after running the script, but I am tempted to modify this piece of pgAgent to make it a whole lot more resilient…

Oh yeah: And when running with all debug logging enabled, all is just fine :wink: … great.

" but I am tempted to modify this piece of pgAgent to make it a whole lot more resilient…"

I did just that. @tldp Can you try version 3.0.2, to see if this is fixed now?

1 Like

Seems to work perfectly fine now! Old jobs were marked as ‘d’ - aborted - and my hello world test job every 5min works reliably since the update.

Many thanks for your quick fix!

1 Like

Is there a way to run this postgres with ssl require support?

How can i solved this “problem” with Timescale/dsmrreader:

Error in the “timescale addon log” :

127.0.0.1 - - [14/Sep/2023:16:27:11 +0200] "GET /about HTTP/1.1" 200 15212 "-" "curl/7.83.1" "-" 127.0.0.1 - - [14/Sep/2023:16:27:26 +0200] "GET /about HTTP/1.1" 200 15212 "-" "curl/7.83.1" "-" pg_dump: error: server version: 15.3; pg_dump version: 14.5 pg_dump: error: aborting because of server version mismatch 2023-09-14 16:27:39,965 ERROR schedule execute_scheduled_processes 39 | (AttributeError) dsmr_backup.services.backup.run errored: 'NoneType' object has no attribute "read''

Thanks in advanced

Is this add-on still actively supported? Asking because there was no new commit on the repo for 5 months and this thread is pretty quiet as well.

Yes it is (author speeking here)
It doesn’t get a lot of commits at the moment because:

  1. It’s pretty stable (and needs to be, people rely on this as it’s a database)
  2. My focus was on a healhcare project for my aunt ( link)

But… recently someone has helped with removing a roadblock for TimescaleDb Tools integration, so it’s about time for it’s 6 months release cycle.

Can I help you with something specific or some advice/question?

Thank you for your quick reply and that’s great to hear!
I bought a mini server as a replacement for my raspberry pi, and I’m now reconsidering and redoing everything I configured in HA on the pi.
I prefer Postgresql over MariaDB (as I can use it for other purposes as well) but, as you said, people rely on it, so it needs to be supported, and since MariaDB is an official plugin, it felt like it might be the safer option.
However, your response convinced me to go with your add-on :slight_smile:
Thanks!

PS: I found out that the last commit on the MariaDB add-on was 7 months ago :wink:

Installed and everything works great out-of-the-box!
I do get a message in pgAdmin4 that a new version is available:
image

I guess the add-on needs to be upgraded for this?

Hi @danieldotnl ,

Thank you for your kind words!

The pgAdmin plugin is just a wrapper around pgAdmin for convenience and ‘feature completeness’.
It follows roughly the same release cycle as the postgresql plugin, but since pgAdmin is updated more frequently, it will show such a message about 2 weeks after I just tested and launched the latest version :rofl:.

I will try to find time to give that one also an update.

If you insist on using the latest of the latest, you could ofcourse just install the latest pgAdmin on your workstation.
The plugin is merely there so you always have the option of ‘accesing postgres from anywhere’.

If you need any assistance, please let me know. I am more then happy to help you if I can.

1 Like

I try to install the Hassio pgadmin4 addon. After i try to start the addon and look in the log it looks ok. However, when i try to open the web-ui it stops direct and reports 502: bad gateway. I tried several times to reinstall the addon but same results. Below you’ll find the logs:

Add-on version: 2.1.1
You are running the latest version of this add-on.
System: Home Assistant OS 11.4 (aarch64 / raspberrypi3-64)
Home Assistant Core: 2024.1.3
Home Assistant Supervisor: 2023.12.1

Please, share the above information when looking for help
or support in, e.g., GitHub, forums or the Discord chat.

cont-init: info: /etc/cont-init.d/00-banner.sh exited 0
cont-init: info: running /etc/cont-init.d/01-log-level.sh
cont-init: info: /etc/cont-init.d/01-log-level.sh exited 0
cont-init: info: running /etc/cont-init.d/nginx.sh

After the web-ui the following log lines are added:

cont-init: info: /etc/cont-init.d/nginx.sh exited 0
cont-init: info: running /etc/cont-init.d/pgadmin4.sh
s6-rc: fatal: timed out
s6-sudoc: fatal: unable to get exit status from server: Operation timed out
/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.
prog: fatal: stopping the container.

Is this the compete log?
Can you try another port number in the configuration, to make sure you don’t have a port-conflict?

tried several different ports. The only positive effect from switching away from port 80 is the fact that the application is not shutting down (see logs below) I’m still getting the 502 bad gateway.

cont-init: info: /etc/cont-init.d/00-banner.sh exited 0
cont-init: info: running /etc/cont-init.d/01-log-level.sh
cont-init: info: /etc/cont-init.d/01-log-level.sh exited 0
cont-init: info: running /etc/cont-init.d/nginx.sh
cont-init: info: /etc/cont-init.d/nginx.sh exited 0
cont-init: info: running /etc/cont-init.d/pgadmin4.sh

You can’t use port 80, because that is already in use for the normal Home Assistant UI.

Are you also getting this when using pgAdmin from the menu (on the left). The logging looks very imcomplete. Do you have the complete logs for me?

Also, is the sidebar menu working?

It looks like it takes too long to startup pgAdmin, hence the timeouts What hardware are you using?

The side bar menu works fine. Pgpadmin4, however gave the same result as starting from the setup menu.
These are all the log the addon give:

s6-rc: info: service s6rc-oneshot-runner: starting
s6-rc: info: service s6rc-oneshot-runner successfully started
s6-rc: info: service fix-attrs: starting
s6-rc: info: service fix-attrs successfully started
s6-rc: info: service legacy-cont-init: starting
cont-init: info: running /etc/cont-init.d/00-banner.sh


Add-on: pgAdmin4
A PostgreSQL Management and Query tool

Add-on version: 2.1.1
You are running the latest version of this add-on.
System: Home Assistant OS 11.4 (aarch64 / raspberrypi3-64)
Home Assistant Core: 2024.1.3
Home Assistant Supervisor: 2023.12.1

Please, share the above information when looking for help
or support in, e.g., GitHub, forums or the Discord chat.

cont-init: info: /etc/cont-init.d/00-banner.sh exited 0
cont-init: info: running /etc/cont-init.d/01-log-level.sh
cont-init: info: /etc/cont-init.d/01-log-level.sh exited 0
cont-init: info: running /etc/cont-init.d/nginx.sh

I’m running on a RPI 3 with an extra USB-HD. Everything works smoothly except the pgadmin4