Database Operations

Connection Information

Field	Default
Host	localhost
Port	5432
Database	hereby_platform
User	hereby_admin
DBMS	PostgreSQL 16 (TimescaleDB + pgvector)

pgAdmin Access

1. Navigate to http://localhost:5050
2. Login: admin@hereby.platform / admin123
3. When adding a server, use postgres (Docker network name) as host

Migrations

Check Migration Status

pnpm --filter @hereby/database typeorm migration:show -d src/config/data-source.ts

Executed migrations show [X], pending show [ ].

Run Migrations

make db-migrate

Revert Migration

pnpm --filter @hereby/database typeorm migration:revert -d src/config/data-source.ts

WARNING

Revert only rolls back the most recent migration. Repeat to revert multiple.

Generate New Migration

From entity changes:

pnpm --filter @hereby/database typeorm migration:generate \
  -- src/migrations/DescriptiveName \
  -d src/config/data-source.ts

Empty migration:

pnpm --filter @hereby/database typeorm migration:create \
  -- src/migrations/DescriptiveName

Backup & Recovery

Full Database Backup

docker exec hereby_postgres pg_dump \
  -U hereby_admin \
  -d hereby_platform \
  -F c \
  -f /tmp/backup_$(date +%Y%m%d_%H%M%S).dump

# Copy to host
docker cp hereby_postgres:/tmp/backup_*.dump ./backups/

Data-Only Backup

docker exec hereby_postgres pg_dump \
  -U hereby_admin \
  -d hereby_platform \
  --data-only \
  -F c \
  -f /tmp/data_backup.dump

Restore

docker exec -i hereby_postgres pg_restore \
  -U hereby_admin \
  -d hereby_platform \
  -c \
  /tmp/backup.dump

DANGER

The -c flag drops existing data. Always backup current data first.

Automated Backup Schedule (crontab)

# Daily backup at 2 AM
0 2 * * * docker exec hereby_postgres pg_dump -U hereby_admin -d hereby_platform -F c -f /backups/daily_$(date +\%Y\%m\%d).dump

PostgreSQL Shell

make db-shell

Useful Queries

-- List tables
\dt

-- Table sizes
SELECT schemaname, tablename,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename))
FROM pg_tables WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;

-- Active connections
SELECT count(*) FROM pg_stat_activity WHERE state = 'active';

-- Slow queries (>1s)
SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state != 'idle'
  AND now() - query_start > interval '1 seconds'
ORDER BY duration DESC;

Seed Data

make db-seed

Test accounts after seeding:

Role	Email	Password
Admin	admin@hereby.com	admin123
Developer	dev.lee@hereby.com	dev123
HR Manager	hr.kim@hereby.com	hr123
Sales	sales.park@hereby.com	sales123