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 -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
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.
hass user in Postgres.
sudo -u postgres createuser hass
hass database owned by the
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
Edit (f.e. with nano) the
.homeassistant/configuration.yaml file and put this in it:
recorder: db_url: postgres://@/hass
As the pi user, either reboot the entire Raspberry, or just restart the home-assistant service:
sudo systemctl restart home-assistant.service