PostgreSQL Setup on Ubuntu

Install PostgreSQL

sudo apt update
sudo apt install postgresql postgresql-contrib

Verify Installation

sudo systemctl status postgresql

Create Root User for Development

Access PostgreSQL as postgres user

sudo -u postgres psql

Create root user with password

CREATE USER root WITH ENCRYPTED PASSWORD 'password' SUPERUSER CREATEDB CREATEROLE;

Create development database

CREATE DATABASE devdb OWNER root;

Exit

\q

Configure Local Access

Edit client authentication

sudo nano /etc/postgresql/16/main/pg_hba.conf

Change the local connection line to:

local   all             all                                     md5
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

Restart PostgreSQL

sudo systemctl restart postgresql

Connect as Root User

psql -U root -d devdb -h localhost

Enter password: password

Common Commands

sudo systemctl start postgresql
sudo systemctl stop postgresql
sudo systemctl restart postgresql
sudo systemctl enable postgresql

Useful psql Commands

\l                  -- List databases
\c dbname           -- Connect to database
\dt                 -- List tables
\du                 -- List users
\q                  -- Quit