How to Set Up a Database Server on a VPS: MySQL, PostgreSQL, and Redis

Your application is only as reliable as the database behind it. Running a dedicated database server on a VPS gives you full control over configuration, performance tuning, access control, and backup scheduling — none of which is possible on shared hosting or restrictive managed database tiers.

This guide covers installing and configuring three of the most widely used database systems on Ubuntu VPS: MySQL/MariaDB (relational, general purpose), PostgreSQL (relational, advanced features), and Redis (in-memory, caching and sessions).

Choosing the Right Database for Your Use Case

Part 1: MySQL / MariaDB

Install MariaDB (recommended over MySQL for most use cases)

sudo apt update && sudo apt upgrade -y
sudo apt install mariadb-server mariadb-client -y
sudo systemctl enable mariadb
sudo mysql_secure_installation

Create Databases and Users

sudo mysql -u root -p

-- Create application database
CREATE DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create a dedicated user (never use root for applications)
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'SecurePassword123!';
GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'localhost';

-- Create a read-only user for reporting
CREATE USER 'readonly'@'localhost' IDENTIFIED BY 'ReadOnlyPass456!';
GRANT SELECT ON appdb.* TO 'readonly'@'localhost';

FLUSH PRIVILEGES;
EXIT;

Performance Tuning for a 4 GB RAM VPS

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

[mysqld]
innodb_buffer_pool_size     = 1G
innodb_buffer_pool_instances = 2
innodb_log_file_size        = 256M
innodb_flush_method         = O_DIRECT
innodb_flush_log_at_trx_commit = 2
query_cache_type            = 1
query_cache_size            = 64M
max_connections             = 150
tmp_table_size              = 64M
max_heap_table_size         = 64M

sudo systemctl restart mariadb

Allow Remote Connections (if needed)

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

# Change bind-address to allow remote connections
bind-address = 0.0.0.0

# Grant access from specific remote IP
GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'192.168.1.100' IDENTIFIED BY 'Password';
FLUSH PRIVILEGES;

sudo ufw allow 3306/tcp from 192.168.1.100

Part 2: PostgreSQL

Install PostgreSQL

sudo apt install postgresql postgresql-contrib -y
sudo systemctl enable postgresql
sudo systemctl start postgresql

Create Databases and Users

# Switch to postgres system user
sudo -u postgres psql

-- Create a database
CREATE DATABASE myapp_db;

-- Create a user
CREATE USER myapp_user WITH PASSWORD 'SecurePassword123!';

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO myapp_user;

-- For Django, also grant schema privileges
\c myapp_db
GRANT ALL ON SCHEMA public TO myapp_user;

\q

Configure PostgreSQL for Performance

sudo nano /etc/postgresql/16/main/postgresql.conf

# Memory — set to ~25% of RAM
shared_buffers = 1GB
effective_cache_size = 2GB
work_mem = 16MB
maintenance_work_mem = 256MB

# Connection settings
max_connections = 100

# Write-ahead log
wal_buffers = 16MB
checkpoint_completion_target = 0.9

# Query planner
random_page_cost = 1.1  # For SSD storage
effective_io_concurrency = 200

# Logging
log_min_duration_statement = 1000  # Log queries slower than 1 second

sudo systemctl restart postgresql

Allow Remote PostgreSQL Connections

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

Add a line for your remote IP:

host    myapp_db    myapp_user    192.168.1.100/32    md5

sudo nano /etc/postgresql/16/main/postgresql.conf

listen_addresses = '*'

sudo systemctl restart postgresql
sudo ufw allow 5432/tcp from 192.168.1.100

Useful PostgreSQL Commands

# List all databases
\l

# Connect to a database
\c myapp_db

# List tables
\dt

# Show running queries
SELECT pid, now() - pg_stat_activity.query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

Part 3: Redis

Install Redis

sudo apt install redis-server -y
sudo systemctl enable redis-server

Configure Redis for Production

sudo nano /etc/redis/redis.conf

# Security — bind to localhost only
bind 127.0.0.1

# Set a strong password
requirepass YourRedisPassword123!

# Max memory (adjust to ~20% of RAM)
maxmemory 512mb

# Eviction policy
maxmemory-policy allkeys-lru

# For cache-only use (no persistence)
save ""
appendonly no

# For session/queue use (needs persistence)
# save 900 1
# save 300 10
# appendonly yes

sudo systemctl restart redis-server

Test Redis Connection

redis-cli -a YourRedisPassword123!
> PING          # Returns: PONG
> SET test "hello"
> GET test      # Returns: "hello"
> exit

Redis Data Structure Examples

# String — simple key/value (sessions, flags)
SET user:1:token "abc123" EX 3600  # Expires in 1 hour

# Hash — structured data (user profiles)
HSET user:1 name "Alice" email "alice@example.com"
HGET user:1 name

# List — queues (job queues, activity feeds)
LPUSH job_queue "send_email:user1"
RPOP job_queue

# Sorted Set — leaderboards, rankings
ZADD leaderboard 1500 "player1"
ZADD leaderboard 2300 "player2"
ZRANGE leaderboard 0 -1 WITHSCORES

Database Backups

Automated MySQL/MariaDB backup

nano ~/backup-mysql.sh

#!/bin/bash
DATE=$(date +%Y-%m-%d)
BACKUP_DIR="/var/backups/mysql"
mkdir -p $BACKUP_DIR
mysqldump -u root --all-databases --single-transaction \
  | gzip > $BACKUP_DIR/all-databases-$DATE.sql.gz
find $BACKUP_DIR -name "*.sql.gz" -mtime +14 -delete
echo "MySQL backup complete: $DATE"

Automated PostgreSQL backup

nano ~/backup-postgres.sh

#!/bin/bash
DATE=$(date +%Y-%m-%d)
BACKUP_DIR="/var/backups/postgres"
mkdir -p $BACKUP_DIR
sudo -u postgres pg_dumpall | gzip > $BACKUP_DIR/all-$DATE.sql.gz
find $BACKUP_DIR -name "*.sql.gz" -mtime +14 -delete

# Schedule both at 2 AM daily
crontab -e
# 0 2 * * * /bin/bash /root/backup-mysql.sh
# 0 2 * * * /bin/bash /root/backup-postgres.sh

Final Thoughts

A well-configured database server on a VPS performs far beyond the constraints of shared hosting or entry-level managed database tiers. With MariaDB’s buffer pool, PostgreSQL’s query planner settings, and Redis handling caching and sessions, your application’s data layer will be fast, reliable, and fully under your control.

• 🇺🇸 USA VPS Plans — from $20/month
• 🇭🇰 Hong Kong VPS — CN2+BGP routing

Related articles:

• How to Speed Up Your VPS: Caching, Swap, and Kernel Tuning
• How to Set Up Automated Backups on Your VPS Using Rsync and Cron
• How to Deploy a Python Flask or Django App on a VPS