Chapter 1: PostgreSQL - Installation and Configuration

Introduction

PostgreSQL is a powerful and reliable open-source relational database management system (RDBMS) used in production worldwide by companies like Apple, Spotify, Instagram, and Reddit.

Why PostgreSQL?

So far, we have used H2, a perfect database for development and testing. But to deploy an application in production, we need a robust, high-performance, and scalable database: PostgreSQL.

---

H2 vs PostgreSQL: Understanding the Difference

Comparison Table

Aspect	H2	PostgreSQL
Type	In-memory/local file	Dedicated server
Usage	Development/Testing	Production
Performance	Limited	High performance
Concurrent users	Not suitable	Excellent
Tools	Basic console	pgAdmin, DBeaver, etc.
Scalability	Low	Excellent
Backup	Manual/complex	Native and automated
Security	Basic	Advanced (roles, permissions)

Analogy

H2 = Bicycle 🚲

• Perfect for learning and testing
• Easy to start
• Not suitable for transporting 100 people

PostgreSQL = Bus 🚌

• Designed for mass transport
• Reliable and robust
• Requires a special license (configuration)

---

Installing PostgreSQL

Windows

Step 1: Download

Go to: postgresql.org/download/windows

Download PostgreSQL 14+ (or newer version).

Step 2: Installation

1. Run the installer
2. Follow the installation wizard
3. IMPORTANT: Note the password for the postgres user (you will need it!)
4. Default port: 5432 (leave it as is)
5. Install Stack Builder (optional but recommended)

Step 3: Verification

Open a terminal and type:

psql --version

You should see:

psql (PostgreSQL) 14.x

---

macOS

Via Homebrew:

brew install postgresql@14
brew services start postgresql@14

Verification:

psql --version

---

Linux (Ubuntu/Debian)

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql
sudo systemctl enable postgresql

---

Getting Started with PostgreSQL

Connecting to PostgreSQL

Windows (via psql):

psql -U postgres

Enter the password you set during installation.

macOS/Linux:

sudo -u postgres psql

---

Basic SQL Commands

1. List Databases

\l

You will see the default databases:

postgres
template0
template1

---

2. Create a Database

CREATE DATABASE greeting_db;

Result:

CREATE DATABASE

---

3. Connect to a Database

\c greeting_db

Result:

You are now connected to database "greeting_db" as user "postgres".

---

4. List Tables

\dt

Initially, there will be no tables.

---

5. View Table Structure

\d table_name

---

6. Exit psql

\q

---

Spring Boot Configuration for PostgreSQL

Step 1: Add Maven Dependency

File: pom.xml

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>

Note: The <scope>runtime</scope> indicates that this dependency is only needed at runtime, not at compile time.

---

Step 2: Create the Database

Before configuring Spring Boot, create the database:

psql -U postgres

CREATE DATABASE greeting_db;
\q

---

Step 3: Configure application.yml

File: src/main/resources/application.yml

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/greeting_db
    username: postgres
    password: your_password
    driver-class-name: org.postgresql.Driver
  
  jpa:
    hibernate:
      ddl-auto: update
    show-sql: true
    properties:
      hibernate:
        dialect: org.hibernate.dialect.PostgreSQLDialect
        format_sql: true

Explanations:

Property	Meaning
url	Database address (localhost:5432/database_name)
username	PostgreSQL user (default: postgres)
password	Password set during installation
driver-class-name	JDBC driver for PostgreSQL
ddl-auto: update	Hibernate automatically updates the schema
show-sql: true	Displays SQL queries in the console
dialect	SQL dialect specific to PostgreSQL
format_sql: true	Formats SQL queries for readability

---

Testing the Connection

Create a Simple Entity

File: model/Product.java

package com.example.demo.model;

import jakarta.persistence.*;

@Entity
@Table(name = "products")
public class Product {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    private String name;
    private Double price;
    
    // Constructors
    public Product() {}
    
    public Product(String name, Double price) {
        this.name = name;
        this.price = price;
    }
    
    // Getters and Setters
    public Long getId() { return id; }
    public void setId(Long id) { this.id = id; }
    
    public String getName() { return name; }
    public void setName(String name) { this.name = name; }
    
    public Double getPrice() { return price; }
    public void setPrice(Double price) { this.price = price; }
}

---

Start the Application

mvn clean install -DskipTests spring-boot:run

Console:

Hibernate: 
    create table products (
       id bigserial not null,
        name varchar(255),
        price float8,
        primary key (id)
    )

✅ PostgreSQL automatically creates the table!

---

Verify in PostgreSQL

psql -U postgres -d greeting_db

\dt

Result:

         List of relations
 Schema |   Name   | Type  |  Owner   
--------+----------+-------+----------
 public | products | table | postgres

\d products

Result:

                                      Table "public.products"
 Column |          Type          | Collation | Nullable |               Default                
--------+------------------------+-----------+----------+--------------------------------------
 id     | bigint                 |           | not null | nextval('products_id_seq'::regclass)
 name   | character varying(255) |           |          | 
 price  | double precision       |           |          | 

✅ The table exists in PostgreSQL!

---

Administration Tools

pgAdmin

Installation:

Download from: pgadmin.org

Usage:

1. Open pgAdmin
2. Create a new server connection
3. Name: Local PostgreSQL
4. Host: localhost
5. Port: 5432
6. Username: postgres
7. Password: Your password

Features:

• Intuitive graphical interface
• Database explorer
• SQL editor
• Data visualization
• User and permission management

---

DBeaver

Installation:

Download from: dbeaver.io

Advantages:

• Free and open-source
• Supports multiple databases (PostgreSQL, MySQL, Oracle, etc.)
• Modern interface
• SQL auto-completion
• Data visualization

---

Security and Best Practices

1. Never Hardcode Passwords

❌ BAD:

spring:
  datasource:
    password: myPassword123

✅ GOOD:

spring:
  datasource:
    password: ${DB_PASSWORD}

Environment Variable (Windows):

$env:DB_PASSWORD = "myPassword123"
mvn spring-boot:run

Environment Variable (Linux/macOS):

export DB_PASSWORD="myPassword123"
mvn spring-boot:run

---

2. Create a Dedicated User (Production)

Do not use postgres in production!

-- Create a user
CREATE USER myapp_user WITH PASSWORD 'secure_password';

-- Create a database
CREATE DATABASE myapp_db OWNER myapp_user;

-- Grant permissions
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO myapp_user;

Spring Boot Configuration:

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/myapp_db
    username: myapp_user
    password: ${DB_PASSWORD}

---

3. Limit Permissions

-- Grant only necessary permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myapp_user;

---

Summary

What You Have Learned

✅ Install PostgreSQL on Windows/Mac/Linux
✅ Create databases and users
✅ Configure Spring Boot for PostgreSQL
✅ Use pgAdmin and DBeaver
✅ Secure connections with environment variables
✅ Understand the differences between H2 and PostgreSQL

---

Key Points

PostgreSQL = Production-Ready

• Robust and reliable
• High performance with many users
• Professional administration tools
• Advanced security

Spring Boot Configuration = Simple

• 1 Maven dependency
• 1 application.yml file
• Hibernate automatically manages the schema

---

Next Step

Now that PostgreSQL is configured, let's move on to Chapter 2 to understand Jakarta vs Javax Persistence.

➡️ Chapter 2: Jakarta vs Javax

⬅️ Back to Module 4