Database Operations Guide

Database Overview

The HostingCo system uses PostgreSQL as the primary database with Redis for caching and session management.

Database Components

Database Schema

Core Tables

users

User accounts and authentication data.

CREATE TABLE users (
    id VARCHAR(50) PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(255) NOT NULL,
    phone VARCHAR(50),
    company VARCHAR(255),
    role VARCHAR(50) DEFAULT 'user',
    permissions JSONB DEFAULT '[]',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

servers

Server configurations and status information.

CREATE TABLE servers (
    id VARCHAR(50) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    hostname VARCHAR(255) NOT NULL,
    ip_address INET NOT NULL,
    status VARCHAR(50) DEFAULT 'active',
    specs JSONB NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Database Operations

Connection Management

# Test database connection
npm run db:test

# Check connection pool status
npm run db:pool-status

# Reset connection pool
npm run db:reset-pool

Query Operations

// Using Knex.js for queries
const knex = require('knex')(databaseConfig);

// Select users
const users = await knex('users').where('active', true);

// Insert new user
const userId = await knex('users').insert({
  email: 'user@example.com',
  name: 'John Doe'
}).returning('id');

Database Maintenance

Backup Procedures

# Create full backup
pg_dump hostingco_prod > backup_$(date +%Y%m%d).sql

# Automated backup
npm run db:backup

# Restore from backup
psql hostingco_prod < backup_20240508.sql

Performance Optimization

# Analyze query performance
npm run db:analyze-queries

# Rebuild indexes
npm run db:reindex

# Vacuum tables
npm run db:vacuum

Redis Caching

Cache Management

# Check Redis status
npm run redis:status

# Clear cache
npm run redis:clear

# Monitor Redis performance
npm run redis:monitor

Session Storage