Postgres Database¶
Aim¶
Setup data storage location.
Summary¶
Setup a postgres database running on your local network. Add a user and allow the new user to have full privileges.
Install and open¶
# search for postgres
sudo apt-cache search postgres
# install
sudo apt-get install postgresql-9.6
# open
sudo -u postgres psql postgres
Change password for user called “postgres”¶
\password postgres
\q
Allow local connections¶
# step 1
nano /etc/postgresql/9.4/main/pg_hba.conf
# update the following section...
# Database administrative login by Unix domain socket
local all postgres peer
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local replication postgres peer
#host replication postgres 127.0.0.1/32 md5
#host replication postgres ::1/128 md5
host all all 0.0.0.0/0 md5
host all all ::/0 md5
# exit => ctrl-x > y > enter
# step 2
nano /etc/postgresql/9.4/main/postgresql.conf
# locate the following line...
# listen_addresses = "127.0.0.1"
# Change to this...
listen_addresses = "*" #WARNING: Make sure you uncomment this line by removing the # symbol
# exit => ctrl-x > y > enter
Create database¶
Start database using user “postgres” (first code block above)
-- list databases
\l+
-- create db
CREATE DATABASE homesensors
-- connect
\c homesensors
Warning
A database called homesensors is required in the next step. The model we create in the Flask API will be called HomeSensorsData and it will live in the homesensors database.
Create user¶
-- create
CREATE USER ray WITH PASSWORD 'password'
-- list users
\dg+
-- add roles
ALTER USER ray WITH CREATEROLE
ALTER USER ray WITH createdb;
ALTER USER ray WITH Superuser;
-- add privileges
GRANT ALL PRIVILEGES ON homesensors To ray
Warning
The Flask API requires that the user name “ray” is available to access the database in following steps. If you change this name, and you most likely will, make the appropriate changes to the Flask API. The location for making changes will be highlighted in the next step.
Restart postgres¶
sudo service postgresql restart
Signing in¶
# sign in with new user
psql homesensors ray
#Password for user ray:
password
