SIS Documentation

Appearance

Database Configuration ​

PostgreSQL database setup and configuration.

Database Requirements ​

  • PostgreSQL 15 or later
  • Minimum 8 GB RAM for the database server
  • SSD storage recommended

Creating the Database ​

Using psql ​

bash
# Connect as postgres superuser
psql -U postgres

# Create database
CREATE DATABASE sis;

# Create application user
CREATE USER sis_app WITH PASSWORD 'YourSecurePassword';
GRANT ALL PRIVILEGES ON DATABASE sis TO sis_app;

# Grant schema permissions (after connecting to sis database)
\c sis
GRANT ALL ON SCHEMA public TO sis_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO sis_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO sis_app;

Using pgAdmin ​

  1. Connect to your PostgreSQL server
  2. Right-click Databases > Create > Database
  3. Enter database name: sis
  4. Set owner to sis_app (or create the user first)
  5. Click Save

Running Migration Scripts ​

The database schema is managed through numbered SQL scripts in database/migrations/.

Order of Execution ​

Run scripts in numerical order by their prefix:

  1. 001_demographics.sql - Demographics tables
  2. 002_cohorts.sql - Cohort management
  3. 003_scheduling.sql - Scheduling tables
  4. Continue through all numbered scripts...

Do not replay the migration history

A loop over database/migrations/*.sql will not work on this repository and must not be used. Several files reference tables and columns that later migrations renamed away, some hardcode surrogate IDs from the database they were generated against, and several have unguarded bulk INSERTs. Populate a new database from a dump instead, then baseline it.

Use the runner ​

powershell
# Report what a database is missing. Read-only.
.\scripts\db-migrate.ps1
.\scripts\db-migrate.ps1 -Target Prod -Status

# Apply what is pending, in order, recording each in core.schema_migrations.
.\scripts\db-migrate.ps1 -Apply

# Once per database, before anything else: declare that its schema already
# reflects the files on disk. Records them; runs none of them.
.\scripts\db-migrate.ps1 -Baseline

Production is kept current by deploy.ps1, which applies pending migrations to sis and sis2 on every deploy. Restoring a dump over the live database is opt-in and confirmed:

powershell
.\deploy.ps1                                      # code + migrations; data untouched
.\deploy.ps1 -DryRun                              # show the plan, write nothing
.\deploy.ps1 -RestoreDump -ConfirmReplace sis     # also replace the database

See Migrations for the rails, the report format, and the rules new migrations must follow.

Connection Configuration ​

Set the following in your .env file in sis_backendgo/:

DB_HOST=127.0.0.1
DB_PORT=5432
DB_USER=postgres
DB_PASSWORD=postgres
DB_NAME=sis
DB_SSLMODE=disable
DB_MAX_CONNS=25

The application builds a connection string automatically from these variables:

host=127.0.0.1 port=5432 user=postgres password=postgres dbname=sis sslmode=disable

Database Maintenance ​

Backups ​

Configure regular backups:

bash
# Full backup
pg_dump -h localhost -p 5432 -U postgres sis > sis_backup_$(date +%Y%m%d).sql

# Compressed backup
pg_dump -h localhost -p 5432 -U postgres -Fc sis > sis_backup_$(date +%Y%m%d).dump

Restore ​

bash
# From SQL file
psql -h localhost -p 5432 -U postgres -d sis -f sis_backup.sql

# From compressed dump
pg_restore -h localhost -p 5432 -U postgres -d sis sis_backup.dump

Vacuum and Analyze ​

bash
# Run vacuum and analyze on all tables
psql -h localhost -p 5432 -U postgres -d sis -c "VACUUM ANALYZE;"

Performance Configuration ​

PostgreSQL Settings ​

Key settings in postgresql.conf:

# Memory
shared_buffers = 2GB          # 25% of RAM
effective_cache_size = 6GB     # 75% of RAM
work_mem = 64MB

# Connections
max_connections = 100

# Write-ahead log
wal_buffers = 64MB

Connection Pool Sizing ​

The DB_MAX_CONNS setting in the application .env controls the maximum number of connections the Go backend opens to PostgreSQL. The default of 25 works for most deployments. Increase it if you observe connection wait times under heavy load, but keep it below the PostgreSQL max_connections value.

Troubleshooting ​

Connection Issues ​

  • Verify PostgreSQL is running: pg_isready -h localhost -p 5432
  • Check pg_hba.conf for authentication rules
  • Verify credentials in .env
  • Check firewall rules for database port (5432)

Performance Issues ​

  • Run VACUUM ANALYZE regularly
  • Check for missing indexes with pg_stat_user_tables
  • Monitor active queries with pg_stat_activity
  • Review slow queries in PostgreSQL logs

Next Steps ​

Student Information System Documentation

Copyright 2026