Skip to content

Database_Import.md

Latest commit

 

History

History
574 lines (412 loc) · 16.1 KB

Database_Import.md

File metadata and controls

574 lines (412 loc) · 16.1 KB
Raw

New PostgreSQL Database (use lowercase for database name)

Virtual World Project - Database Import Deployment Guide

I. Applicable Scenarios

Import the complete database SQL file (db_export.sql) into a production server (Linux/Ubuntu, BT Panel), and complete the project deployment configuration.

This guide summarizes all typical issues encountered during actual deployment and their solutions.

II. Overall Process Overview

Upload SQL file to server
        ↓
Create target database on server
        ↓
Import SQL data
        ↓
Permission check (critical step!)
        ↓
If permission issues → Grant permissions to app user
        ↓
Modify .env configuration
        ↓
Restart Node service
        ↓
Verify API works

III. Detailed Steps

3.1 Upload SQL File to Server

Use SCP, SFTP, or BT Panel File Manager to upload db_export.sql to the server, for example:

/www/wwwroot/virtual-world-bt/db_export.sql

3.2 Create Target Database on Server

Method A: BT Panel (Recommended)

  1. Log in to BT Panel
  2. Database → PostgreSQL → Add Database
  3. Fill in database name, username, password (use lowercase, see Chapter VI)
  4. Record the credentials generated by BT Panel:
    • Database name (e.g., s8wiykdryzpjrrp4)
    • Username (e.g., S8WiYKdRyzPJrrp4)
    • Password (e.g., S8WiYKdRyzPJrrp4)

Method B: Command Line

# Switch to postgres user
sudo -u postgres psql

# Create database and user (use lowercase)
CREATE USER vw_app WITH PASSWORD 'your_strong_password';
CREATE DATABASE virtual_world OWNER vw_app;
GRANT ALL PRIVILEGES ON DATABASE virtual_world TO vw_app;
\q

3.3 Import SQL Data

# Import using postgres superuser
sudo -u postgres psql -d your_database_name -f /www/wwwroot/virtual-world-bt/db_export.sql

# Verify import result (check table count)
sudo -u postgres psql -d your_database_name -c "SELECT count(*) FROM information_schema.tables WHERE table_schema='public';"

Expected result: Returns 60+ tables.

Common Issue: Encoding Error "0x0ff 0xfe has no equivalent in UTF8"

Symptom:

psql: error: encoding "GBK" has no equivalent for byte 0xff 0xfe in encoding "UTF8"

Cause: The db_export.sql file was saved or converted to UTF-16 LE format instead of UTF-8. UTF-16 LE files have a BOM marker 0xFF 0xFE at the beginning, which causes psql to fail when parsing as UTF-8.

Solution:

# Method 1: Convert encoding with iconv (Recommended)
iconv -f UTF-16LE -t UTF-8 db_export.sql > db_export_utf8.sql

# Then import the converted file
sudo -u postgres psql -d your_database_name -f db_export_utf8.sql
# Method 2: PowerShell (on Windows)
Get-Content db_export.sql -Encoding Unicode | Set-Content db_export_utf8.sql -Encoding UTF8

Prevention: Always specify UTF-8 encoding when exporting SQL files to avoid accidental encoding switches.

3.4 Permission Check (Must Do After Import)

After import, check permissions first, then decide whether authorization is needed.

Step 1: Test query with app user

# Test if app user (not postgres) can query tables
PGPASSWORD='your_app_password' psql -h 127.0.0.1 -U "your_app_username" -d your_database_name -c "SELECT count(*) FROM system_config;"

Step 2: Check result and take action

Refer to the table below based on the result:

Result Meaning Action
Row count (e.g., 39) ✅ Permissions OK Skip 3.5, go test sequence permissions
permission denied for table system_config ❌ Table permission missing Execute 3.5 authorization
role "xxx" does not exist ❌ Username case issue See Chapter IV, Problem 4
password authentication failed ❌ Wrong password Check password in .env
connection refused ❌ Database service not running See Chapter IV, Problem 1

Step 3: If table permissions OK, test sequence permissions

# Test sequence permissions (needed for INSERT)
PGPASSWORD='your_app_password' psql -h 127.0.0.1 -U "your_app_username" -d your_database_name -c "SELECT nextval('ui_controls_id_seq');"
Result Meaning Action
Returns a number (e.g., 2542) ✅ Sequence permissions OK All permissions OK, go to 3.6
permission denied for sequence ui_controls_id_seq ❌ Sequence permission missing Execute 3.5 authorization

3.5 Grant Permissions to App User (Only If Check Failed)

If permission issues were found in 3.4, execute the following commands.

3.5.1 Root Cause

  • Importing data with postgres user → table owner is postgres
  • App user has no permissions on these tables and sequences by default
  • Must explicitly GRANT permissions

3.5.2 Grant Commands

# 1. Grant permissions on all tables
sudo -u postgres psql -d your_database_name -c 'GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "your_app_username";'

# 2. Grant permissions on all sequences (important! INSERT needs auto-increment IDs)
sudo -u postgres psql -d your_database_name -c 'GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO "your_app_username";'

# 3. Grant schema usage
sudo -u postgres psql -d your_database_name -c 'GRANT USAGE ON SCHEMA public TO "your_app_username";'

# 4. Auto-grant for future tables (prevent new tables from missing permissions)
sudo -u postgres psql -d your_database_name -c 'ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO "your_app_username";'
sudo -u postgres psql -d your_database_name -c 'ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO "your_app_username";'

3.5.3 Re-verify After Grant

# Test table permissions again
PGPASSWORD='your_password' psql -h 127.0.0.1 -U "your_username" -d your_database_name -c "SELECT count(*) FROM system_config;"

# Test sequence permissions again
PGPASSWORD='your_password' psql -h 127.0.0.1 -U "your_username" -d your_database_name -c "SELECT nextval('ui_controls_id_seq');"

If both return results, permissions are complete.

3.6 Modify .env Configuration

# Edit .env
nano /www/wwwroot/virtual-world-bt/.env

Update the following fields:

# Database configuration (use actual server values)
DB_HOST=127.0.0.1
DB_PORT=5432
DB_NAME=your_database_name
DB_USER=your_database_username
DB_PASSWORD=your_database_password

# Server configuration
PORT=3002
NODE_ENV=production

# World configuration
WORLD_URL=https://your_domain

Verify configuration:

grep -E "^DB_|^PORT" /www/wwwroot/virtual-world-bt/.env

3.7 Restart Node Service

Important: Must restart Node after modifying .env, otherwise changes won't take effect!

# Enter project directory
cd /www/wwwroot/virtual-world-bt

# Start with PM2
pm2 start src/server.js --name virtual-world

# If already running, restart
pm2 restart virtual-world

# Enable auto-start on boot (recommended)
pm2 save
pm2 startup
# Follow the prompt and execute the command it gives you

3.8 Verify Deployment Success

# 1. Check Node service status
pm2 status

# 2. Test APIs
curl -s http://localhost:3002/api/health
# Expected: {"status":"ok","timestamp":"..."}

curl -s http://localhost:3002/api/federation/info | head -c 200
# Expected: {"success":true,"world":{...}}

curl -s http://localhost:3002/api/config/seo | head -c 100
# Expected: returns SEO config JSON

# 3. Check error logs (should be empty or warnings only)
pm2 logs virtual-world --lines 20 --nostream --err

Refresh your browser and visit https://your_domain. The game should load normally.

IV. Common Issues Troubleshooting

Problem 1: PostgreSQL Service Not Running

Symptom:

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: could not open shared memory segment "/PostgreSQL.xxx": No such file or directory

Cause:

  • postgresql.service is a wrapper; the actual database runs on postgresql@version-main.service
  • Occasional issue after system updates or shared memory config changes

Solution:

# 1. Check actual PostgreSQL instance services
systemctl list-units --type=service | grep postgres

# 2. Restart the version-specific instance service (e.g., version 18)
systemctl restart postgresql@18-main

# 3. If restart fails, increase shared memory
sysctl -w kernel.shmmax=17179869184
sysctl -w kernel.shmall=4194304

# Make persistent
echo "kernel.shmmax=17179869184" >> /etc/sysctl.conf
echo "kernel.shmall=4194304" >> /etc/sysctl.conf
sysctl -p

# 4. Restart again
systemctl restart postgresql@18-main

Problem 2: All APIs Return 500/503

Symptom:

GET /api/federation/info 503 (Service Unavailable)
GET /api/config/seo 500 (Internal Server Error)
GET /api/users/character/xxx 500 (Internal Server Error)

Cause: Node service is not running, or database connection failed.

Troubleshooting:

# 1. Check if Node service is running
pm2 status

# 2. If not running, start it
cd /www/wwwroot/virtual-world-bt
pm2 start src/server.js --name virtual-world

# 3. Check error logs
pm2 logs virtual-world --lines 50 --nostream --err

# 4. Test database connection
PGPASSWORD='your_password' psql -h 127.0.0.1 -U "your_username" -d your_database_name -c "\dt"

Problem 3: Permission Denied

Symptom:

Database query error: error: permission denied for table ui_controls
[UIControls] Failed to init default controls: permission denied for table ui_controls

Or:

permission denied for sequence ui_controls_id_seq

Cause:

  • Table owner is postgres (imported by postgres user)
  • App user has no access permissions
  • Sequence permissions also not granted (can't get auto-increment ID for INSERT)

Solution: See Section 3.5, execute the full GRANT commands.

Problem 4: Role Does Not Exist

Symptom:

ERROR: role "s8wiykdryzpjrrp4" does not exist

Cause:

  • PostgreSQL role names are case-sensitive
  • Unquoted identifiers in SQL are automatically lowercased
  • Actual role name is S8WiYKdRyzPJrrp4 (mixed case), but s8wiykdryzpjrrp4 can't be found

Solution:

# 1. Check actual role names
sudo -u postgres psql -c "\du"

# 2. Use double quotes around role name in SQL (single quotes around entire SQL)
sudo -u postgres psql -d your_database_name -c 'GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "S8WiYKdRyzPJrrp4";'

Prevention: Always use all lowercase when creating databases and users to avoid case issues.

Problem 5: Port 3002 Already in Use

Symptom:

❌ Port 3002 is already in use!
Please run the following command to free the port:

Solution:

# Kill the process occupying port 3002
fuser -k 3002/tcp

# Or
pkill -f "node.*server.js"

# Then restart
pm2 restart virtual-world

Problem 6: .env Changes Not Taking Effect

Symptom: Changed database config in .env, but API still connects to the old database.

Cause: Node.js only reads .env once at startup; runtime changes won't take effect automatically.

Solution:

pm2 restart virtual-world

Problem 7: sudo -u postgres Asks for Password

Symptom:

Password for user postgres:

Cause: BT Panel's PostgreSQL installation enforces MD5 authentication and does not allow local trust.

Solution:

# Method 1: Connect via TCP + password
PGPASSWORD='postgres_password' psql -h 127.0.0.1 -U postgres -d your_database_name -c "your_SQL"

# Method 2: Check postgres password in BT Panel
# BT Panel → Database → PostgreSQL → Manage

V. One-Click Deployment Script

Save the following script as deploy_db.sh and run it directly during deployment:

#!/bin/bash
# Database import deployment script
# Usage: bash deploy_db.sh database_name username password

DB_NAME=$1
DB_USER=$2
DB_PASS=$3
PROJECT_DIR="/www/wwwroot/virtual-world-bt"

if [ -z "$DB_NAME" ] || [ -z "$DB_USER" ] || [ -z "$DB_PASS" ]; then
    echo "Usage: bash deploy_db.sh database_name username password"
    echo "Example: bash deploy_db.sh virtual_world vw_app MyPassword123"
    exit 1
fi

echo "=== 1. Import database ==="
sudo -u postgres psql -d "$DB_NAME" -f "$PROJECT_DIR/db_export.sql"

echo "=== 2. Permission check ==="
PGPASSWORD="$DB_PASS" psql -h 127.0.0.1 -U "$DB_USER" -d "$DB_NAME" -c "SELECT count(*) FROM system_config;" 2>&1

echo "=== 3. Grant permissions ==="
sudo -u postgres psql -d "$DB_NAME" -c "GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO \"$DB_USER\";"
sudo -u postgres psql -d "$DB_NAME" -c "GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO \"$DB_USER\";"
sudo -u postgres psql -d "$DB_NAME" -c "GRANT USAGE ON SCHEMA public TO \"$DB_USER\";"
sudo -u postgres psql -d "$DB_NAME" -c "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO \"$DB_USER\";"
sudo -u postgres psql -d "$DB_NAME" -c "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO \"$DB_USER\";"

echo "=== 4. Verify permissions ==="
PGPASSWORD="$DB_PASS" psql -h 127.0.0.1 -U "$DB_USER" -d "$DB_NAME" -c "SELECT count(*) FROM system_config;"
PGPASSWORD="$DB_PASS" psql -h 127.0.0.1 -U "$DB_USER" -d "$DB_NAME" -c "SELECT nextval('ui_controls_id_seq');"

echo "=== 5. Update .env ==="
sed -i "s|^DB_NAME=.*|DB_NAME=$DB_NAME|" "$PROJECT_DIR/.env"
sed -i "s|^DB_USER=.*|DB_USER=$DB_USER|" "$PROJECT_DIR/.env"
sed -i "s|^DB_PASSWORD=.*|DB_PASSWORD=$DB_PASS|" "$PROJECT_DIR/.env"

echo "=== 6. Restart Node ==="
pm2 restart virtual-world 2>/dev/null || (cd "$PROJECT_DIR" && pm2 start src/server.js --name virtual-world)
sleep 5

echo "=== 7. Test API ==="
curl -s http://localhost:3002/api/health
echo ""
curl -s http://localhost:3002/api/federation/info | head -c 200
echo ""

echo "=== Deployment complete ==="

VI. Best Practices

6.1 Naming Conventions (Avoid Case Issues)

Item Recommended Not Recommended Reason
Database name virtual_world VirtualWorld All lowercase, no quotes needed in SQL
Username vw_app VwApp All lowercase, avoids quoting trouble
Password Mixed case OK - Passwords are unaffected

PostgreSQL Case Rules:

  • Unquoted identifiers are automatically lowercased
  • Mixed-case role/table names must be wrapped in double quotes
  • All-lowercase naming avoids all case-related quoting issues

6.2 Permission Management Approaches

Approach Pros Cons Use Case
Use postgres superuser No permission issues ever Poor security Personal projects, development
App user + full grants Good security Needs grant steps Production (recommended)
App user + change owner One-time fix Complex operation Long-term maintained projects

6.3 Pre-Deployment Checklist

  • PostgreSQL service is running (systemctl status postgresql@version-main)
  • Database has been created
  • App user has been created
  • SQL file has been uploaded to server
  • Data has been imported (SELECT count(*) FROM information_schema.tables)
  • Permission check passed (table query + sequence query)
  • Table permissions granted (if needed)
  • Sequence permissions granted (if needed)
  • .env configuration updated
  • Node service restarted (pm2 restart)
  • API test passed (curl /api/health)
  • PM2 auto-start on boot configured (pm2 save && pm2 startup)

VII. Troubleshooting Flowchart

API returns 500/503
    ↓
Check if Node service is running → pm2 status
    ↓ ↓
    Not running → pm2 start src/server.js
    Running ↓
    ↓
Check error logs → pm2 logs --err
    ↓ ↓
    Database connection error ↓
    ↓
Test database connection → psql -h 127.0.0.1 -U username -d database_name
    ↓ ↓
    Cannot connect ↓
    ↓
Check PostgreSQL service → systemctl status postgresql@version-main
    ↓ ↓
    Not running → systemctl restart postgresql@version-main
    Can connect ↓
    ↓
Check permissions → look for "permission denied" in error logs
    ↓ ↓
    Permission error ↓
    ↓
Execute GRANT commands (see Section 3.5)
    ↓
Restart Node → pm2 restart virtual-world
    ↓
Test API → curl /api/health