Database - PostgreSQL

May 2nd 2023

Install PostgreSQL

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

---

Database OPS

login to db using default user

sudo -u postgres psql

create a database and cd into it

CREATE DATABASE db_name;
\c db_name

CREATE TABLE table_name (
    inp1 VARCHAR(42) PRIMARY KEY,
    inp2 NUMERIC
);

\q

create tables and columns for database. set its options

CREATE TABLE table_name (
    column1_name VARCHAR(42) PRIMARY KEY,
    column2_name NUMERIC
);

remove a column from table

ALTER TABLE table_name DROP COLUMN column_name

---

Connection Settings

allowing users and ip addresses to connect.

nano /etc/postgresql/12/main/pg_hba.conf 

then add this line to allow connection from other ip's

# IPv4 remote connections for the postgres user
host    all             all             ip.address.to.allow/32          md5

below is to control listening addresses

nano /etc/postgresql/12/main/postgresql.conf 

then find and change this line to control listening addresses

listen_addresses = '*'