Install and Configure PostgreSQL

🎯 Objective

Learn how to install, start, and configure PostgreSQL, create databases and users, and allow connections to the database server.

PostgreSQL is a powerful open-source relational database widely used in modern applications.

# 📜 What is PostgreSQL?

PostgreSQL is an advanced SQL-based database system that supports:

Used by companies like:

# 1️⃣ Install PostgreSQL

Ubuntu / Debian

sudo apt update
sudo apt install postgresql postgresql-contrib-y

RHEL / CentOS / Amazon Linux

sudo yum install postgresql-server postgresql-contrib-y

Initialize database (RHEL/CentOS):

sudo postgresql-setup initdb

# 2️⃣ Start PostgreSQL Service

sudo systemctlstart postgresql
sudo systemctl enable postgresql

Check status:

sudo systemctl status postgresql

# 3️⃣ Login to PostgreSQL

Switch to postgres user:

sudo -i -u postgres

Open PostgreSQL shell:

psql

Prompt will appear:

postgres=#

Exit shell:

\q

# 4️⃣ Create Database

Inside psql:

CREATE DATABASE mydb;

List databases:

\l

# 5️⃣ Create Database User

CREATE USER dbuser WITH PASSWORD 'StrongPassword';

Grant privileges:

GRANT ALL PRIVILEGES ON DATABASE mydb TO dbuser;

List users:

\du

# 6️⃣ Configure Remote Access

Edit PostgreSQL configuration:

sudo nano /var/lib/pgsql/data/postgresql.conf

Find:

listen_addresses = 'localhost'

Change to:

listen_addresses = '*'

# 7️⃣ Configure Client Authentication

Edit:

sudo nano /var/lib/pgsql/data/pg_hba.conf

Add:

host    all    all    0.0.0.0/0    md5

This allows remote authentication with password.

# 8️⃣ Restart PostgreSQL

sudo systemctlrestart postgresql

# 9️⃣ Test Connection

From client machine:

psql-h server-ip-U dbuser-d mydb

# 🧠 PostgreSQL Architecture

Client Application
        │
        ▼
   PostgreSQL Server
        │
        ▼
   Database Cluster
        │
 ┌──────┼──────┐
 ▼      ▼      ▼
Table  Table  Table

# 🧪 Useful PostgreSQL Commands

# 🔎 Troubleshooting

Check PostgreSQL port

sudo ss-tunlp |grep5432

Default port:

5432

View logs

sudo journalctl-u postgresql

# ✅ Summary