Quick Postgres SQL Setup on rpi

Posted on March 2, 2022
Tags: sql

1 Docker

docker pull postgres

from 192.168.1.244

docker run -p 7890:5432 --name some-postgres -e POSTGRES_PASSWORD=root -d postgres

from 192.168.1.245

psql -h 192.168.1.244 -p 7890 -U postgres

2 rpi

sudo apt-get update
sudo apt-get install postgres

starting up postgres

sudo pg_ctlcluster 11 main start
#alternatively
sudo systemctl restart postgresql@11-main

this creates a default postgres linux user account

less /etc/passwd
#use command to show list of linux accounts
#postgres:x:112:121:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash

2.1 Make internal Postgres Account

switch to the postgres account

sudo su postgres

make an account within postgres(This is not a linux acc but an internal postgres accs)

createuser pi -P --interactive
exit 
#exit out of postgres account back to linux pi

2.2 Accessing Postgres

Check if your postgres is accessible in your LAN

sudo netstat -tlnpu
#tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      17917/postgres

It says 127.0.0.1 but it should be 0.0.0.0 for it to be accessible on LAN.

3 Where is postgresql.conf

To resolve this, we need to know where is postgres config file.

sudo -u postgres psql -c 'SHOW config_file'
#               config_file
#-----------------------------------------
# /etc/postgresql/11/main/postgresql.conf
#(1 row)
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                             # (change requires restart)
max_connections = 100                   # (change requires restart)
#superuser_reserved_connections = 3     # (change requires restart)

Change the above file 'localhost' to '0.0.0.0'

sudo systemctl restart postgresql@11-main

You may attempt to now try to login but will see ERROR:
[28000] FATAL: no pg_hba.conf entry for host "192.168.1.26", user "pi", database "postgres", SSL on

Error is b/c any newly made database by default will reject users unless authorized in pga_hba.conf

3.0.1 Where is pga_hba.conf

sudo -u postgres psql -c 'SHOW hba_file'
#              hba_file
#-------------------------------------
# /etc/postgresql/11/main/pg_hba.conf
#(1 row)
# PostgreSQL Client Authentication Configuration File
# ===================================================
#
# Refer to the "Client Authentication" section in the PostgreSQL
# documentation for a complete description of this file.  A short
# synopsis follows.
#
# This file controls: which hosts are allowed to connect, how clients
# are authenticated, which PostgreSQL user names they can use, which
# databases they can access.  Records take one of these forms:
#
# local      DATABASE  USER  METHOD  [OPTIONS]
# host       DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostssl    DATABASE  USER  ADDRESS  METHOD  [OPTIONS]

host  all  all 192.168.1.0/0 md5 #<--- Add this line
sudo systemctl restart postgresql@11-main