akiyama-manga/DATABASE_SETUP.md

Database Setup Guide

This guide helps you set up PostgreSQL for Akiyama Manga.

Prerequisites

• PostgreSQL 12 or higher
• psql command-line tool

Windows Setup

1. Install PostgreSQL

1. Download from postgresql.org
2. Run installer
3. Remember the password you set for postgres user
4. Accept default port (5432)
5. Complete installation

2. Create Database and User

Open PowerShell as Administrator:

psql -U postgres

Execute in psql:

CREATE DATABASE akiyama_manga;
CREATE USER manga_user WITH PASSWORD 'your_password';
ALTER ROLE manga_user SET client_encoding TO 'utf8';
ALTER ROLE manga_user SET default_transaction_isolation TO 'read committed';
ALTER ROLE manga_user SET default_transaction_deferrable TO on;
ALTER ROLE manga_user SET default_transaction_deferrable TO on;
GRANT ALL PRIVILEGES ON DATABASE akiyama_manga TO manga_user;
\c akiyama_manga
CREATE SCHEMA IF NOT EXISTS public;
GRANT ALL PRIVILEGES ON SCHEMA public TO manga_user;
\q

Linux/macOS Setup

1. Install PostgreSQL

Ubuntu/Debian:

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

macOS:

brew install postgresql

2. Start PostgreSQL

Ubuntu/Debian:

sudo systemctl start postgresql

macOS:

brew services start postgresql

3. Create Database and User

sudo -u postgres psql

Execute in psql:

CREATE DATABASE akiyama_manga;
CREATE USER manga_user WITH PASSWORD 'your_password';
ALTER ROLE manga_user SET client_encoding TO 'utf8';
ALTER ROLE manga_user SET default_transaction_isolation TO 'read committed';
ALTER ROLE manga_user SET default_transaction_deferrable TO on;
GRANT ALL PRIVILEGES ON DATABASE akiyama_manga TO manga_user;
\c akiyama_manga
CREATE SCHEMA IF NOT EXISTS public;
GRANT ALL PRIVILEGES ON SCHEMA public TO manga_user;
\q

Docker Setup (Recommended)

Using Docker Compose

Create a docker-compose.yml:

version: '3.8'

services:
  postgres:
    image: postgres:15-alpine
    environment:
      POSTGRES_DB: akiyama_manga
      POSTGRES_USER: manga_user
      POSTGRES_PASSWORD: your_password
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U manga_user"]
      interval: 10s
      timeout: 5s
      retries: 5

volumes:
  postgres_data:

Run:

docker-compose up -d

Environment Configuration

Update your .env file:

DB_HOST=localhost
DB_PORT=5432
DB_USER=manga_user
DB_PASSWORD=your_password
DB_NAME=akiyama_manga

Verify Connection

Test the connection:

psql -U manga_user -d akiyama_manga -h localhost

Database Schema

The application will automatically create these tables:

• users - User accounts and profiles
• mangas - Manga metadata
• chapters - Manga chapters
• pages - Individual pages
• reviews - User reviews and ratings
• tags - Genre/category tags
• user_library - Many-to-many relationship for library

Backup and Restore

Backup Database

pg_dump -U manga_user -d akiyama_manga > backup.sql

Restore Database

psql -U manga_user -d akiyama_manga < backup.sql

Troubleshooting

Error: "could not connect to server"

• Verify PostgreSQL is running
• Check host and port in connection string
• Verify credentials

Error: "FATAL: Ident authentication failed"

• Use -h localhost to force TCP connection
• Edit pg_hba.conf to change authentication method

Error: "Database does not exist"

• Create the database using the SQL commands above

Performance Optimization

Create useful indexes:

CREATE INDEX idx_manga_title ON mangas(title);
CREATE INDEX idx_manga_status ON mangas(status);
CREATE INDEX idx_chapter_manga ON chapters(manga_id);
CREATE INDEX idx_page_chapter ON pages(chapter_id);
CREATE INDEX idx_review_manga ON reviews(manga_id);
CREATE INDEX idx_user_email ON users(email);

Connection Pooling (Production)

For production, use PgBouncer:

sudo apt-get install pgbouncer

Configure /etc/pgbouncer/pgbouncer.ini:

[databases]
akiyama_manga = host=localhost port=5432 user=manga_user password=your_password

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 10