3

Database Setup

houmgaor edited this page 2026-02-18 14:02:58 +01:00

Table of Contents

• Database Setup
• Initial Setup
• Patch Schemas
• Bundled Demo Data
• Key Tables
• Users & Characters
• Game Content
• Guilds (8 tables)
• Events
• Common Admin Operations
• Add an event quest
• Create a gift distribution
• Ban a user
• Reset Raviente
• Check active sessions
• Backup & Restore
• Backup
• Restore
• Maintenance
• Troubleshooting
• Performance Tuning

Database Setup

Erupe uses PostgreSQL. The schema is managed through three layers: a base dump, incremental patches, and optional demo data.

Initial Setup

Create the database and restore the base schema (pg_dump custom format, not plain SQL):

createdb -U postgres erupe
pg_restore -U postgres -d erupe schemas/init.sql

This bootstraps the schema to v9.1.0.

Patch Schemas

Apply all files in schemas/patch-schema/ in numerical order. Each patch is idempotent but must follow the previous ones:

for f in schemas/patch-schema/*.sql; do
  psql -U postgres -d erupe -f "$f"
done

Notable patches:

Patch	Effect
00-psn-id.sql	Adds PSN ID column to users
02-tower.sql	Adds Tower mode tables
08-kill-counts.sql	Adds kill_logs table
10-rework-distributions.sql	Migrates distribution system (deletes old distribution data)
12-event_quest_cycling.sql	Adds event quest rotation timestamps
14-fix-fpoint-trades.sql	Reworks FPoint shop (deletes old FPoint items)
16-discord-password-resets.sql	Adds Discord account linking columns

Bundled Demo Data

Optional SQL files in schemas/bundled-schema/ populate game content. Load any you want:

psql -U postgres -d erupe -f schemas/bundled-schema/EventQuests.sql

File	Content
DistributionDemo.sql	Gift box items (equipment, item box pages)
DivaShops.sql	Diva Defense shop items
EventQuests.sql	~200+ event quest definitions
FestaDefaults.sql	Festa prize tiers (personal/guild, soul thresholds)
FPointItems.sql	~100+ Frontier Point shop items
GachaDemo.sql	3 gacha examples (normal, step-up, box)
NetcafeDefaults.sql	Net Cafe bonus items
OtherShops.sql	Miscellaneous shop data
RoadShopItems.sql	Road shop unlockables
ScenarioDefaults.sql	Scenario counter initialization

Key Tables

Users & Characters

Table	Purpose
users	Accounts (bcrypt password, rights bitmask, PSN ID, Discord token)
characters	Character data with ~17 bytea savedata fields
sign_sessions	Active authentication tokens
bans	Ban records with optional expiry

Game Content

Table	Purpose
distribution + distribution_items	Gift box system (login bonuses, GM gifts)
event_quests	Event quest rotation (quest_type, quest_id, cycling timestamps)
gacha_shop + gacha_entries + gacha_items	Gacha lottery (normal, step-up, box)
shop_items	Shop inventory (cost, HR/SR/GR requirements)
fpoint_items	Frontier Point shop
cafebonus	Net Cafe time-based bonuses
scenario_counter	Scenario/cutscene unlock tracking

Guilds (8 tables)

Table	Purpose
guilds	Guild info (name, leader, RP, icon, recruiting)
guild_characters	Membership and roles
guild_applications	Pending invites/applications
guild_posts	Bulletin board
guild_adventures	Guild adventure tracking
guild_hunts	Guild hunt records
guild_meals	Guild meal cooking
guild_alliances	Inter-guild alliances

Events

Table	Purpose
events	Active events (type: festa/diva/vs/mezfes)
festa_registrations	Festa sign-ups
festa_trials + festa_prizes	Festa scores and rewards
feature_weapon	Daily featured weapon rotation
tower	Tower mode progress (TR, TRP, TSP)
rengoku_score	Rengoku survival scores
kill_logs	Monster kill tracking

Common Admin Operations

Add an event quest

INSERT INTO event_quests (max_players, quest_type, quest_id, mark)
VALUES (4, 28, 23045, 2);

See Enumerations for quest type and mark values.

Create a gift distribution

INSERT INTO distribution (type, deadline, event_name, description, times_acceptable)
VALUES (1, '2099-12-31', 'Welcome Gift', 'A gift for new hunters', 1)
RETURNING id;

INSERT INTO distribution_items (distribution_id, item_type, item_id, quantity)
VALUES (<returned_id>, 7, 482, 10);

See Enumerations for type/item_type values.

Ban a user

INSERT INTO bans (user_id, expires_at)
VALUES (<user_id>, NOW() + INTERVAL '7 days');

Reset Raviente

SELECT ravireset(0);

Check active sessions

SELECT u.username, s.token FROM sign_sessions s
JOIN users u ON u.id = s.user_id;

Backup & Restore

Backup

# Full backup (custom format, recommended)
pg_dump -U postgres -Fc erupe > erupe_backup.dump

# Plain SQL backup
pg_dump -U postgres erupe > erupe_backup.sql

# Compressed plain SQL
pg_dump -U postgres erupe | gzip > erupe_backup.sql.gz

Restore

# From custom format
dropdb -U postgres erupe
createdb -U postgres erupe
pg_restore -U postgres -d erupe erupe_backup.dump

# From plain SQL
dropdb -U postgres erupe
createdb -U postgres erupe
psql -U postgres -d erupe -f erupe_backup.sql

# From compressed SQL
gunzip -c erupe_backup.sql.gz | psql -U postgres -d erupe

Maintenance

# Reclaim space and update query planner statistics
psql -U postgres -d erupe -c "VACUUM ANALYZE;"

# Check database size
psql -U postgres -d erupe -c "SELECT pg_size_pretty(pg_database_size('erupe'));"

Troubleshooting

Error	Cause	Fix
Connection refused	PostgreSQL not running or wrong port	Verify service is running: systemctl status postgresql. Check Port in config.
password authentication failed	Wrong password or user	Verify Database.Password in config.json. Check user exists: psql -U postgres -c "\du"
database "erupe" does not exist	Database not created	Run createdb -U postgres erupe
permission denied for table	User lacks privileges	GRANT ALL ON ALL TABLES IN SCHEMA public TO <user>;
Server starts but data doesn't load	Schema patches missing or out of order	Re-apply patches in numerical order from schemas/patch-schema/

Performance Tuning

For servers with many concurrent players, consider tuning PostgreSQL:

# /etc/postgresql/*/main/postgresql.conf

shared_buffers = 2GB          # 25% of available RAM
work_mem = 16MB               # Per-operation memory
maintenance_work_mem = 512MB  # For VACUUM, CREATE INDEX

For high connection counts, consider PgBouncer for connection pooling. Restart PostgreSQL after changing postgresql.conf.