🗄️ Database Overview

The Gaming Platform PAM uses PostgreSQL 17 running inside Docker containers on dedicated VPS servers for production and staging. Backups, partitions, logs and S3 storage are fully automated inside the container environment.

• 💾 PostgreSQL 17 optimized for high-volume JSONB
• 🧩 Auto partitioning for gaming transactions
• ☁️ Daily AWS S3 full backups
• 📜 Detailed logs: Supervisor, Backups, Partitions

⚙️ Required Environment (.env)

Before starting your PostgreSQL container, you MUST configure the following .env file. These values are used for database creation, partitions, and S3 backups.

# Environment Configuration
ENVIRONMENT=production  # or 'staging'

# PostgreSQL Database Configuration
POSTGRES_DB=aggregator
POSTGRES_USER=postgres
POSTGRES_PASSWORD=fLTFetYqdxc0ScSKiocfW
POSTGRES_PORT=5432

# Connection inside the container
DB_HOST=localhost
DB_SCHEMA=production

# AWS S3 Backup Credentials
AWS_ACCESS_KEY_ID=
AWS_SECRET_ACCESS_KEY=
AWS_DEFAULT_REGION=
AWS_BUCKET=
AWS_ENDPOINT=

WARNING

⚠️ Important: Without S3 credentials, backups will NOT upload.

🔗 Database Server IPs

Use these IPs to configure API connections and remote access (same for Staging & Production as in documentation):

Environment	IP	Port	Database	User
Production	192.168.10.101	5432	aggregator	postgres
Staging	192.168.10.102	5432	aggregator	postgres

⚙️ Build & Control Commands

# Start the container
docker compose build --no-cache
docker compose up -d

# PostgreSQL control
docker start postgres
docker stop postgres
docker compose down
docker compose restart postgres

# Logs & Monitoring
docker-compose logs -f postgres
docker exec postgres supervisorctl status
docker exec postgres pg_isready -h localhost -p 5432

# Enter the container
docker exec -it postgres sh

# Manual scripts
docker exec postgres /scripts/create_partitions.sh
docker exec postgres /scripts/backup.sh --type=production
docker exec postgres /scripts/backup.sh --type=staging

📜 Automation Scripts

backup.sh

#!/bin/bash
# Creates full backups, uploads to S3 if configured.
# Runs daily via Supervisor inside the container.

create_partitions.sh

#!/bin/bash
# Generates monthly partitions for transactions table
# Logs saved in /var/log/partition/partition.log

🌐 Architecture Overview

graph TD
    A[⚙️ Aggregator API + Backoffice<br />Laravel + Redis + Workers] -->|DB Connection| B[🗄️ PostgreSQL<br />Dockerized Database]
    B -->|Daily Sync| C[(☁️ AWS S3 Backup Storage)]

---

© 2025 Gaming Platform – PAM Deployment Documentation