Recommandations on maintenance and backup

[elfrinjo]

Do you have any recommandationcs on regular maintenance Jobs like vacuum, analyze and reindex?

Also, what is the best way to perform regular database-dumps? If I am not mistaken, pg-agent ist currently not able to do this because of https://github.com/Expaso/hassos-addon-pgadmin4/issues/6

[expaso]

Hi @elfrinjo!

If al is well, vacuuming of the databases will happen automatically. See also: https://stackoverflow.com/questions/66469419/when-need-to-run-a-reindex-in-postgres

Postgresql will just auto-vacuum, and to the maintenance by itself.

For database-dumps, you can use use pg_dump using any scheduler you like (CRON, or Windows TaskScheduler). Though the mentioned bug report is about using pg_agent to dump the database in the /share folder of Home Assistant. From there, you have to collect it to transfer it off your device, and in your backup for safety.

For now, you can just backup the addon using the home assistant backup. that works fine for regular backups.

[elfrinjo]

Thanks for your response! So I'll just leave the database alone :)

My plan for the backup would have been to

• schedule a job in pg_agent
• that creates a pg_dump in /share
• let ha backup take care of the contents of /share

However, if ha-backup covers the postgres-addon in a sensible manner I am happy with that!

[expaso]

Hi @elfrinjo ,

The pgAgent jobs are now working in the latest version of the addon.

For setup, see: https://www.digitalocean.com/community/tutorials/how-to-schedule-automatic-backups-for-postgresql-with-pgagent-in-pgadmin

An example command would be:

/usr/libexec/postgresql15/pg_dump --username=postgres --dbname=homeassistant --clean --file=/backup/homeassistant-`date +%Y-%m-%d-%H-%M-%S`.sql

This would place backups to the home assistant backup folder, which can be network storage nowadays.

This backup is NOT compressed. If you need compression on the backup, see: Using compression with PostgreSQL’s pg_dump – Dan Langille’s Other Diary

Please feel free to contact me if you need any help.

[eMerzh]

Hello @Expaso , sorry to repost on this, but i'm looking at timescale backup and i'm not sure how it's done or not... maybe you could add a small note in the readme or something?

I have Ltss and timescale and doing a pgdump of the timescale materialize view / continous aggregate doesn't really work , but you need to extract the data in csv or smth then reload it? how can i be sure that my views are well backed-up and that it contains data (that might not be in the ltss anymore)

thanks a lot for the work you do in this integration