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

Archive All-In-One Installer
1

This howto should help people setting up recorder with PostgreSQL on an All-In-One installer based system. Please share your feedback, so this can become proper documentation. (Even better: I’d very much like the AiO installer do this by default, since sqlite is really bad performance-wise (I have at least two sensors that give a reading every 10 seconds) and the AiO installer does a lot of stuff already anyway so that a little bit extra wouldn’t hurt.)

Warning: This process does not migrate your data. Your old history data will still be in sqlite, but you won’t be able to see it. It’s therefore best to do this right after setting up the system.

Preliminaries: Check your locale

For me, locales weren’t set up properly. To test, log in to the Raspi and try to execute perl:

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

If it says

perl: warning: Setting locale failed.

you have a problem that you need to fix before going on. For me it was sufficient to do

sudo dpkg-reconfigure locales

which will bring up a menu with locales, select the one(s) you want and hit ok. If you try the perl command again it should work this time.

Installing PostgreSQL and development dependencies

Simply do

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

Create user and database

We’re not going to use TCP connections as other howtos may advise you to do but will stick with the default configuration of Unix domain sockets which is both easier and more secure.

Create the hass user in Postgres.

sudo -u postgres createuser hass

Create the hass database owned by the hass user:

sudo -u postgres createdb -O hass hass

Install python dependencies

Switch to the home assistant user and virtual environment, then install psycopg2:

sudo su - hass
. /srv/hass/hass_venv/bin/activate
pip3 install psycopg2

Reconfigure recorder

Edit (f.e. with nano) the .homeassistant/configuration.yaml file and put this in it:

recorder:
  db_url: postgres://@/hass

(Re)start

As the pi user, either reboot the entire Raspberry, or just restart the home-assistant service:

sudo systemctl restart home-assistant.service
19 Likes
2

@henryk ,

Thank you for writing this up. I myself have been noticing performance issues bringing up sensors with history graphs, and have been reading up on converting to a MySQL database (as others claim this speeds things up considerably, especially once the db size starts to climb over 100MB).

Do you recommend using PostgreSQL over MySQL? Any advantages or disadvantages you are aware of between the two for this particular use case? I thought I had plans to convert to MySQL this evening, but your concise and well written tutorial here now has me considering this as another alternative.

Thanks!
Mike

1 Like
3

I’d like to know this too as I got MySQL up and running and after a few days I am still really happy with it compared to running SQLlite. It would be nice to know the pros and cons of both versions.

Excellent write up BTW!

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!