Church-Music/legacy-site/documentation/md-files/POSTGRESQL_QUICK_START.md

Quick Start - PostgreSQL Migration to Ubuntu Server

What's Changed

✅ Port: Changed from 5000 to 5100
✅ Database: Switched from MongoDB to PostgreSQL
✅ Server: Configured for 192.168.10.130

Files Created/Updated

New Files

• backend/postgresql_models.py - PostgreSQL database models
• backend/migrate_to_postgresql.py - Migration script
• POSTGRESQL_DEPLOYMENT_GUIDE.md - Complete deployment guide
• ubuntu-setup-postgresql.sh - Automated setup script

Updated Files

• backend/requirements.txt - Now uses SQLAlchemy and psycopg2-binary
• backend/.env - Updated for PostgreSQL and port 5100
• backend/.env.example - Updated template
• backend/.env.ubuntu - Ubuntu deployment config
• backend/app.py - Updated imports (need to complete routes conversion)
• frontend/package.json - Proxy updated to port 5100

---

🚀 Quick Deployment Steps

1. SSH to Ubuntu Server

# From Windows
ssh username@192.168.10.130

2. Transfer Files

# From Windows PowerShell (in another window)
scp -r "E:\Documents\Website Projects\Church_SongLyric" username@192.168.10.130:/tmp/

3. Run Setup Script on Ubuntu

# On Ubuntu server
sudo mv /tmp/Church_SongLyric /var/www/church-songlyric
cd /var/www/church-songlyric
chmod +x ubuntu-setup-postgresql.sh
./ubuntu-setup-postgresql.sh

The script will:

• Install PostgreSQL
• Create database and user
• Install all dependencies
• Migrate your data
• Configure services
• Start everything

4. Access Application

Open browser to: http://192.168.10.130

---

📋 Before You Start

On Windows (Local Machine)

1. Update Python dependencies:

cd "E:\Documents\Website Projects\Church_SongLyric\backend"
.\venv\Scripts\activate
pip install -r requirements.txt

2. Test migration locally (optional):

# Install PostgreSQL on Windows first, then:
python migrate_to_postgresql.py

3. Test backend locally:

python app.py
# Should start on port 5100

4. Update frontend:

cd ..\frontend
npm install
npm start
# Should proxy to port 5100

---

🔧 Manual Setup (if not using script)

See POSTGRESQL_DEPLOYMENT_GUIDE.md for step-by-step manual installation.

---

⚠️ Important Notes

PostgreSQL Connection String Format

postgresql://username:password@host:port/database

Example:

POSTGRESQL_URI=postgresql://songlyric_user:MySecurePass123@192.168.10.130:5432/church_songlyric

Port 5100 Usage

The backend now runs on port 5100 instead of 5000. Update any:

• Firewall rules
• API endpoint references
• Mobile device configurations
• External access configurations

Database Migration

Your existing data from data.json will be automatically migrated to PostgreSQL during setup.

---

🛠️ Troubleshooting

Can't connect to PostgreSQL

# Test connection
psql -h 192.168.10.130 -U songlyric_user -d church_songlyric

# Check if PostgreSQL is listening
sudo netstat -tulpn | grep 5432

Backend won't start

# Check logs
sudo journalctl -u church-songlyric-backend -n 50

# Test manually
cd /var/www/church-songlyric/backend
source venv/bin/activate
python app.py

Port 5100 not accessible

# Check if running
sudo netstat -tulpn | grep 5100

# Check firewall
sudo ufw status
sudo ufw allow 5100/tcp

---

📝 Management Commands

# Service management
sudo systemctl status church-songlyric-backend
sudo systemctl restart church-songlyric-backend
sudo systemctl stop church-songlyric-backend

# View logs
sudo journalctl -u church-songlyric-backend -f

# Database access
sudo -u postgres psql
\c church_songlyric
\dt  # List tables

---

🔐 Security Checklist

• Change PostgreSQL password from default
• Update SECRET_KEY in .env
• Configure firewall rules
• Backup database regularly
• Use strong passwords

---

📊 Database Backup

# Backup
pg_dump -h 192.168.10.130 -U songlyric_user church_songlyric > backup_$(date +%Y%m%d).sql

# Restore
psql -h 192.168.10.130 -U songlyric_user -d church_songlyric < backup_20241207.sql

---

Next Steps After Deployment

1. ✅ Test all features (add songs, create profiles, etc.)
2. ✅ Verify data was migrated correctly
3. ✅ Test from mobile devices on same network
4. ✅ Setup regular database backups
5. ✅ Configure SSL (optional, for HTTPS)

---

Need Help? See POSTGRESQL_DEPLOYMENT_GUIDE.md for detailed instructions!