🐘 Postgre-SQL: From Basics to Full-Stack Integration¶

sudo systemctl start postgresql
sudo systemctl status postgresql
sudo -u postgres psql

\l           -- list databases
\c mydb      -- connect to database
\dt          -- list tables
\d tablename -- describe table structure
\q           -- quit

CREATE USER devuser WITH PASSWORD 'secret';
CREATE DATABASE devdb OWNER devuser;
GRANT ALL PRIVILEGES ON DATABASE devdb TO devuser;

-- Create table
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  email TEXT UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert data
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

-- Query data
SELECT * FROM users;
SELECT name FROM users WHERE email LIKE '%@example.com';

-- Update
UPDATE users SET name='Alicia' WHERE id=1;

-- Delete
DELETE FROM users WHERE id=1;

docker run -d \
  --name postgres \
  -e POSTGRES_USER=devuser \
  -e POSTGRES_PASSWORD=secret \
  -e POSTGRES_DB=devdb \
  -p 5432:5432 \
  -v pg_data:/var/lib/postgresql/data \
  postgres:16

Host: localhost
Port: 5432
Database: devdb
User: devuser
Password: secret

services:
  postgres:
    image: postgres:16
    environment:
      POSTGRES_USER: devuser
      POSTGRES_PASSWORD: secret
      POSTGRES_DB: devdb
    ports:
      - "5432:5432"
    volumes:
      - pg_data:/var/lib/postgresql/data

  pgadmin:
    image: dpage/pgadmin4
    environment:
      PGADMIN_DEFAULT_EMAIL: admin@example.com
      PGADMIN_DEFAULT_PASSWORD: admin
    ports:
      - "5050:80"
    depends_on:
      - postgres

volumes:
  pg_data:

-- Create user
CREATE USER api_user WITH PASSWORD 'apipass';

-- Grant permissions
GRANT CONNECT ON DATABASE devdb TO api_user;
GRANT USAGE ON SCHEMA public TO api_user;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO api_user;

-- Future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE ON TABLES TO api_user;

psql -U devuser -d devdb        # connect to DB
psql -h localhost -U devuser    # specify host
pg_dump -U devuser devdb > dump.sql    # backup
psql -U devuser -d devdb < dump.sql    # restore

sudo systemctl status postgresql

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

listen_addresses = '*'
max_connections = 100
shared_buffers = 256MB
work_mem = 16MB
logging_collector = on
log_directory = 'log'

sudo systemctl reload postgresql

{
  "connections": [
    {
      "name": "Local Postgres",
      "driver": "PostgreSQL",
      "previewLimit": 50,
      "server": "localhost",
      "port": 5432,
      "database": "devdb",
      "username": "devuser",
      "password": "secret"
    }
  ]
}

spring.datasource.url=jdbc:postgresql://localhost:5432/devdb
spring.datasource.username=devuser
spring.datasource.password=secret
spring.jpa.hibernate.ddl-auto=update
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

jdbc:postgresql://postgres:5432/devdb

POSTGRES_USER=devuser
POSTGRES_PASSWORD=secret
POSTGRES_DB=devdb
POSTGRES_PORT=5432

export $(grep -v '^#' .env | xargs)

VACUUM ANALYZE;

CREATE INDEX idx_users_email ON users(email);

pg_dumpall > full_backup.sql

{
  "connections": [
    {
      "name": "Local Postgres",
      "driver": "PostgreSQL",
      "server": "localhost",
      "port": 5432,
      "database": "devdb",
      "username": "devuser",
      "password": "secret"
    }
  ]
}
````

You can run and test queries directly in `.sql` files without switching tools.

---

## 🧰 17. PostgreSQL + Docker Compose for Multi-Environments

A single Compose file can handle both **development** and **testing** databases.

```yaml
version: "3.9"
services:
  postgres:
    image: postgres:16
    environment:
      POSTGRES_USER: devuser
      POSTGRES_PASSWORD: secret
      POSTGRES_DB: devdb
    ports:
      - "5432:5432"
    volumes:
      - pg_data:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U devuser -d devdb"]
      interval: 10s
      retries: 5

  testdb:
    image: postgres:16
    environment:
      POSTGRES_USER: testuser
      POSTGRES_PASSWORD: testpass
      POSTGRES_DB: testdb
    ports:
      - "55432:5432"
    tmpfs:
      - /var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U testuser -d testdb"]
      interval: 5s
      retries: 3

volumes:
  pg_data:

name: Backend Tests
on: [push, pull_request]
jobs:
  test:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_USER: testuser
          POSTGRES_PASSWORD: testpass
          POSTGRES_DB: testdb
        ports: ['5432:5432']
        options: >-
          --health-cmd="pg_isready -U testuser -d testdb"
          --health-interval=5s --health-timeout=5s --health-retries=5
    steps:
      - uses: actions/checkout@v4
      - name: Wait for Postgres
        run: |
          until pg_isready -h localhost -U testuser; do
            sleep 1
          done
      - name: Run Tests
        run: ./gradlew test
        env:
          SPRING_DATASOURCE_URL: jdbc:postgresql://localhost:5432/testdb
          SPRING_DATASOURCE_USERNAME: testuser
          SPRING_DATASOURCE_PASSWORD: testpass

env_file:
  - .env.${ENVIRONMENT}

spring.datasource.url=${DB_URL}
spring.datasource.username=${DB_USER}
spring.datasource.password=${DB_PASS}

docker run -d \
  --name pgbouncer \
  -p 6432:6432 \
  -e DB_USER=devuser \
  -e DB_PASSWORD=secret \
  -e DB_HOST=postgres \
  edoburu/pgbouncer

pg_dump -U devuser devdb | gzip > backup_$(date +%F).sql.gz