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

Archive All-In-One Installer
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.

24

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 :slight_smile: Postgres and 0.51.1 are compatible

hmm, maybe I am wrong. History stopped after I upgraded to 0.51.1 :frowning:

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

25

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.