Database Setup

Install and configure PostgreSQL or MySQL for Refugio

Overview

Refugio supports both PostgreSQL and MySQL databases. This guide covers installation, schema creation, and initial data setup for both systems.

PostgreSQL is recommended for production due to better support for ENUM types and concurrent connections.

PostgreSQL Setup

Installation

Ubuntu/Debian

# Update package list
sudo apt update

# Install PostgreSQL
sudo apt install postgresql postgresql-contrib

# Start PostgreSQL service
sudo systemctl start postgresql
sudo systemctl enable postgresql

# Check status
sudo systemctl status postgresql

CentOS/RHEL

# Install PostgreSQL repository
sudo yum install postgresql-server postgresql-contrib

# Initialize database
sudo postgresql-setup initdb

# Start and enable service
sudo systemctl start postgresql
sudo systemctl enable postgresql

Docker

# Run PostgreSQL container
docker run -d \
  --name refugio-postgres \
  -e POSTGRES_PASSWORD=refugio_password \
  -e POSTGRES_USER=refugio \
  -e POSTGRES_DB=refugio \
  -p 5432:5432 \
  -v refugio-data:/var/lib/postgresql/data \
  postgres:14

# Check container status
docker ps

Create Database and User

1. Access PostgreSQL

   sudo -u postgres psql
   
2. Create Database

   CREATE DATABASE refugio;
   
3. Create User

   CREATE USER refugio_user WITH ENCRYPTED PASSWORD 'your_secure_password';
   GRANT ALL PRIVILEGES ON DATABASE refugio TO refugio_user;
   
4. Exit PostgreSQL

   \q
   

Create Schema

You need to create the database schema based on the structure documented in the Database Schema page. The schema includes 6 tables:

Quick Schema Creation

Create the following tables in order:

1. usuarios - User accounts with roles (admin/user)
2. habitaciones - Rooms in the refuge
3. camas - Individual beds
4. reservas - Reservation records
5. reservas_camas - Junction table for bed assignments
6. acompanantes - Companion information

See the Database Schema documentation for complete table definitions, field types, and relationships.

If your repository includes sql/refugio.sql, you can import it directly:

psql -U refugio_user -d refugio -f sql/refugio.sql

Otherwise, refer to the Database Schema documentation to create the tables manually.

Configure PostgreSQL Authentication

Edit the PostgreSQL configuration to allow password authentication:

sudo nano /etc/postgresql/14/main/pg_hba.conf

Add or modify:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     peer
host    refugio         refugio_user    127.0.0.1/32           md5
host    refugio         refugio_user    ::1/128                md5

Restart PostgreSQL:

sudo systemctl restart postgresql

MySQL Setup

Installation

Ubuntu/Debian

# Install MySQL server
sudo apt update
sudo apt install mysql-server

# Secure installation
sudo mysql_secure_installation

# Start MySQL
sudo systemctl start mysql
sudo systemctl enable mysql

CentOS/RHEL

# Install MySQL
sudo yum install mysql-server

# Start and enable
sudo systemctl start mysqld
sudo systemctl enable mysqld

# Get temporary root password
sudo grep 'temporary password' /var/log/mysqld.log

# Secure installation
sudo mysql_secure_installation

Docker

# Run MySQL container
docker run -d \
  --name refugio-mysql \
  -e MYSQL_ROOT_PASSWORD=root_password \
  -e MYSQL_DATABASE=refugio \
  -e MYSQL_USER=refugio_user \
  -e MYSQL_PASSWORD=refugio_password \
  -p 3306:3306 \
  -v refugio-data:/var/lib/mysql \
  mysql:8.0

Create Database and User

1. Access MySQL

   sudo mysql -u root -p
   
2. Create Database

   CREATE DATABASE refugio CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
   
3. Create User

   CREATE USER 'refugio_user'@'localhost' IDENTIFIED BY 'your_secure_password';
   GRANT ALL PRIVILEGES ON refugio.* TO 'refugio_user'@'localhost';
   FLUSH PRIVILEGES;
   
4. Exit MySQL

   EXIT;
   

Create Schema

Create the database schema based on the structure documented in the Database Schema page.

If your repository includes sql/refugio_mysql.sql, you can import it directly:

mysql -u refugio_user -p refugio < sql/refugio_mysql.sql

Otherwise, refer to the Database Schema documentation to create the tables manually.

Verify Installation

Test Database Connection

PostgreSQL

# Test connection
psql -U refugio_user -d refugio -c "SELECT version();"

# List tables
psql -U refugio_user -d refugio -c "\dt"

# Count users
psql -U refugio_user -d refugio -c "SELECT COUNT(*) FROM usuarios;"

MySQL

# Test connection
mysql -u refugio_user -p -e "SELECT VERSION();"

# List tables
mysql -u refugio_user -p refugio -e "SHOW TABLES;"

# Count users
mysql -u refugio_user -p refugio -e "SELECT COUNT(*) FROM usuarios;"

Use Verification Script

Refugio includes a verification script:

php verificar_mysql.php

This script will:

• Test database connectivity
• Verify all tables exist
• Check for default admin account
• Validate schema structure

Default Test Data

The schema includes three default user accounts:

Role	Email	Password	Member #
Admin	[e-mail]	admin123	A001
User	[e-mail]	user123	U001
User	[e-mail]	user123	U002

CRITICAL: Change these passwords immediately after first login, especially in production!

Database Backup

PostgreSQL Backup

# Create backup
pg_dump -U refugio_user -d refugio > refugio_backup_$(date +%Y%m%d).sql

# Restore backup
psql -U refugio_user -d refugio < refugio_backup_20260304.sql

MySQL Backup

# Create backup
mysqldump -u refugio_user -p refugio > refugio_backup_$(date +%Y%m%d).sql

# Restore backup
mysql -u refugio_user -p refugio < refugio_backup_20260304.sql

Automated Backups

Create a cron job for daily backups:

# Edit crontab
crontab -e

# Add daily backup at 2 AM (PostgreSQL)
0 2 * * * pg_dump -U refugio_user refugio > /backups/refugio_$(date +\%Y\%m\%d).sql

# Or for MySQL
0 2 * * * mysqldump -u refugio_user -pPASSWORD refugio > /backups/refugio_$(date +\%Y\%m\%d).sql

Performance Tuning

PostgreSQL

# Edit postgresql.conf
sudo nano /etc/postgresql/14/main/postgresql.conf

Optimize these settings based on available RAM:

# For 2GB RAM server
shared_buffers = 512MB
effective_cache_size = 1536MB
maintenance_work_mem = 128MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 2MB
min_wal_size = 1GB
max_wal_size = 4GB

MySQL

# Edit my.cnf
sudo nano /etc/mysql/my.cnf

Optimize for InnoDB:

[mysqld]
innodb_buffer_pool_size = 512M
innodb_log_file_size = 128M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
max_connections = 100
query_cache_size = 64M
query_cache_type = 1

Troubleshooting

Connection Refused

PostgreSQL:

# Check if PostgreSQL is running
sudo systemctl status postgresql

# Check listening port
sudo netstat -plnt | grep 5432

# Review logs
sudo tail -f /var/log/postgresql/postgresql-14-main.log

MySQL:

# Check if MySQL is running
sudo systemctl status mysql

# Check listening port
sudo netstat -plnt | grep 3306

# Review logs
sudo tail -f /var/log/mysql/error.log

Authentication Failed

• Verify username and password
• Check pg_hba.conf (PostgreSQL) or user grants (MySQL)
• Ensure user has correct privileges
• Try resetting the password

Schema Import Errors

• Ensure database is empty before import
• Check file encoding (should be UTF-8)
• Verify PostgreSQL/MySQL version compatibility
• Review error messages for specific table issues

Next Steps

Configuration

Configure Refugio to connect to your database

Database Schema

Learn about the database structure