Howto: Postgresql on Raspberry Pi All-In-One Installer

Archive All-In-One Installer
4

So, yeah, the difference between PostgreSQL and MySQL is that the former is the more ‘real’ database with a better general standing in the areas of data integrity and transaction processing. This probably does not account for much in the Home Assistant use case with its very simplistic database structure. In fact, it may well be that MySQL has better performance, precisely because it may be missing some safeguards.

But: The one thing I absolutely love about PostgreSQL and make use of here is its peer authentication method: Authentication as a postgres user is successful if it matches the unix user name. You saw that I created a hass postgres user which owns the database, but never did anything else with it. That’s because the home assistant process runs as the unix user hass and can therefore simply connect to postgres as the postgres user hass. No passwords required. No mucking around. And more secure.

4 Likes
5

Thank you. This worked right away for me. I have been trying to get MySQL working for a couple of weeks with with no luck.

6

@henryk - Thanks very much for taking the time to reply; learning is why I participate so actively here and in your two paragraphs I learned an awful lot. Thanks for sharing with me and with others!

7

Is it possible to have the recorder component to write to an installation of PG on a different server? From the syntax it seems like it might work, but will I need to have a local install of PG so HA can access the required libraries?

Thanks.

8

I don’t see a problem with this as long as there is support on the HA machine for the client.

In other words, I don’t think HA cares whether the server is local or not as long as the req client libraries are installed and HA can access them (if you’re running a venv, then the libs would have to be installed with the venv active).

9

How do I access the database external true pgAdmin? When I try i get “Server dosnt listen”

10

Where is the database file location? I have looked under “/var/lib/postgresql/9.4/main” and “/etc/postgresql/9.4/main”

11

I answer my self :slight_smile: I dont think there is a single file for the hass database. But if i run

root@hass:/var/lib/postgresql# du -sh
224M .

And in postgres

sudo -u postgres psql

postgres=# \l+ hass
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
------±------±---------±------------±------------±------------------±-------±-----------±------------
hass | hass | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | | 169 MB | pg_default |
(1 row)

I have postgres run for 24h and I have about 160 Entity.

12

Hi @henryk,

Thank you very much for your wonderful post.

I have been having trouble with handling my large db (over 500 Mb in one day) using SQLite. So I tried your method, and everything seemed ok at beginning (after rebooting my Pi), but then I ordered HA to restart itself and then it never loaded again. This is the error log;

Any suggestion on why I am having this problem?
Also, even when it was working (right after rebooting the Pi) the history tab doesn’t load anything!

I have to mention this as well. once I was trying to install “psycopg2”, the pi terminal looked like as if it ran into some sort of error;

One last thing, in my RPi (with the latest Raspbian), I had to change

“. /srv/hass/hass_venv/bin/activate”

to

. /srv/homeassistant/homeassistant_venv/bin/activate

13

i’m also having the same problem. not sure where to start looking.

14

this is what i did to get it working:

  • rpi2
  • home assistant 0.36.1 (all-in-one)
    [installed using the work around: post 7]

(taken from bottom of recorder page)

perl -e "print 'hello, world\n'"

locales set up properly

sudo apt install postgresql-9.4 postgresql-server-dev-9.4

sudo -u postgres createuser homeassistant
sudo -u postgres createdb -O homeassistant homeassistant

enter root

sudo -i

enter as user homeassistant (or whatever you want? be sure to be consistent for the environment and postgres db)

su homeassistant

change into directory for python environment and activate it

cd /srv/homeassistant/homeassistant_venv/
source bin/activate

install ps adapter

pip3 install psycopg2

you need to restart the service. if ssh, need to get out of environment and homeassistant user:

deactivate

switch user to your user name (let’s say “pi”, the default user for raspbian)

su - pi

(prompt for password for user pi)

now you can either restart the service:

sudo systemctl restart home-assistant.service

or restart the rpi:

sudo restart

this worked for me. hopefully it works for you.

i am curious as to how the orm knows the db is now postgres? i assume it’s the db_url flag in the yaml.

4 Likes
15

you can explore the postgres instance like a normal db, which seems obvious but perhaps not to everyone.

sudo -i
su homeassistant
psql

(i get an error: could not change directory to “/root”: Permission denied. but am able to access the db)

query away!

16

I will give it a go this weekend ! Thank you so much for sharing your experience @mouseandcat !

My new problem is that even though I use the SQLite in Recorder of my configuration.yaml , > the purge_days function doesn’t seem to be doing anything ! I monitored my *.db file, and it is growing in size continuously without purging any old data …

17

Nice Job. Very helpful. Two minor changes.

  1. Before you install postgresql, run “sudo apt update” and “sudo apt upgrade”. While my system was fairly up to date, I needed to do this before postresql would install.

  2. db_url: postgres://@/hass should be db_url: postgres://@/homeassistant

18

Thanks for this.

It looked like everything worked except that my logbook and history in my UI are empty and don’t populate now. I don’t see any errors in the logs. I logged into the psql database as shown above and I can see that it was growing while i was running home assistant as well:

sudo -u postgres psql

postgres=# \l+ hass

Pasted image886×102 3.36 KB

I am logged in as “hass” for the user, but I haven’t setup a virtual environment as the guide shows. But I don’t see why that would make any difference since I just installed psycopg2 in the normal environment…

Anyone have any clues? :slight_smile:

19

A tiny additon to your post is, also configuration.yaml file must be editted like this, otherwise logs and graphs cannot be observed.

recorder:
db_url: postgres://@/homeassistant

1 Like
20

ah, yes! that’s right, forgot that step, actually a big oversight! thanks!

21

I just did this using a stock Hassbian install, on a Raspi 2 Model B. HOLY COW!!!

Two things:

  1. The UI is A LOT faster than it was when using the SQLite database.
  2. The OS, HASS Server, and Postgres all combined use only about 70MB of RAM!!

My next step (after doing a dd of my SD card) is to create a RAM disk for /var. This will entail writing systemd modules to tar up /var on shutdown, and un-tar /var on startup. The purpose of this is to not burn up SD cards.

22

Anyone can confirm that?

23

Could anyone help mi to set this database in other location? On my pendrive.