@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!
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.
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).
How do I access the database external true pgAdmin? When I try i get “Server dosnt listen”
Where is the database file location? I have looked under “/var/lib/postgresql/9.4/main” and “/etc/postgresql/9.4/main”
I answer my self 
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.
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
i’m also having the same problem. not sure where to start looking.
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
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!
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 …
Nice Job. Very helpful. Two minor changes.
-
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.
-
db_url: postgres://@/hass should be db_url: postgres://@/homeassistant
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
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?
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
ah, yes! that’s right, forgot that step, actually a big oversight! thanks!
I just did this using a stock Hassbian install, on a Raspi 2 Model B. HOLY COW!!!
Two things:
- The UI is A LOT faster than it was when using the SQLite database.
- 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.
Anyone can confirm that?
Could anyone help mi to set this database in other location? On my pendrive.
Thanks for sharing this clear instruction!
I’ve done it on an Ubuntu 16.04 installation and had to take one more step: add
local all homeassistant peer
to the file
/etc/postgresql/9.5/main/pg_hba.conf
(where homeassistant is my Home Assistant user) and restart postgresql. The result is that Home Assistant can access the database without a password.
By the way, did anyone test the Home Assistant version 0.51.1 database upgrade with Postgresql?
Okay, tried (after making a backup) and it works Postgres and 0.51.1 are compatible
hmm, maybe I am wrong. History stopped after I upgraded to 0.51.1 
final update: had to trash my database and start over again with the instructions above. Got it working.
First stop hass, next drop the database
sudo -u postgres dropdb hass
Next, recreate the database:
sudo -u postgres createdb -O hass hass
Upgrade hass as usual
Hi,
Very interesting topic! In regard to pgAdmin here:
I have created the database as described and then created a password in pgsql so that I can access the database from pgAdmin3, since I wasn’t allowed to log into the database. Of course, I had to configure recorder: accordingly and edit postgresql.conf:
listen_addresses = ‘*’
to connect from LAN, and
pg_hba.conf
host dbname all 192.168.1.1/24 md5
And that works.
However, I have searched a bit more and peer authentication is feasible as well. The two links below:
give clear indication on how to proceed, risks and drawbacks. So helpful reading.
Now, mind that before running pgAdmin you Debian username and Postgresql username should match.
Hope it helps.
PD. Mind that pg_hba.conf shows by default the following line
Database administrative login by Unix domain socket
local all postgres peer
so if you switch to Debian user postgres
sudo su - postgres
and launch pgAdmin peer authentication should work.