Debian Database Server Installation: MySQL/MariaDB & PostgreSQL Guide

Database servers are the backbone of modern applications. This comprehensive guide covers installation, configuration, security, replication, and optimization of both MySQL/MariaDB and PostgreSQL database servers on Debian systems.

MySQL/MariaDB Installation and Setup

Installing MariaDB

MariaDB is a drop-in replacement for MySQL with enhanced features:

# Update package index
sudo apt update

# Install MariaDB server and client
sudo apt install mariadb-server mariadb-client

# Secure the installation
sudo mysql_secure_installation

During secure installation: - Set root password - Remove anonymous users - Disallow root login remotely - Remove test database - Reload privilege tables

Basic MariaDB Configuration

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

Essential configuration settings:

[mysqld]
# Basic Settings
user = mysql
pid-file = /run/mysqld/mysqld.pid
socket = /run/mysqld/mysqld.sock
port = 3306
datadir = /var/lib/mysql

# Character Set
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# Connection Settings
max_connections = 500
connect_timeout = 10
wait_timeout = 600
max_allowed_packet = 64M
thread_cache_size = 128
thread_stack = 256K

# Table Settings
table_open_cache = 4000
table_definition_cache = 2000

# Query Cache (deprecated in MySQL 8.0+)
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 2M

# InnoDB Settings
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 4
innodb_log_file_size = 512M
innodb_log_buffer_size = 64M
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50
innodb_read_io_threads = 64
innodb_write_io_threads = 64

# Logging
log_error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# Binary Logging for Replication
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 100M

# Performance Schema
performance_schema = ON

# Security
local_infile = 0
skip_name_resolve = 1
sql_mode = STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

Creating Database Users and Permissions

# Connect to MariaDB
sudo mysql -u root -p

# Create database
CREATE DATABASE myapp_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# Create user with specific privileges
CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'strong_password';
GRANT ALL PRIVILEGES ON myapp_db.* TO 'myapp_user'@'localhost';

# Create user for remote access
CREATE USER 'myapp_user'@'%' IDENTIFIED BY 'strong_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_db.* TO 'myapp_user'@'%';

# Create read-only user
CREATE USER 'readonly_user'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT ON myapp_db.* TO 'readonly_user'@'localhost';

# Create backup user
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'backup_password';
GRANT SELECT, LOCK TABLES, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER ON *.* TO 'backup_user'@'localhost';

# Apply privileges
FLUSH PRIVILEGES;

PostgreSQL Installation and Setup

Installing PostgreSQL

# Install PostgreSQL
sudo apt install postgresql postgresql-contrib

# Check PostgreSQL status
sudo systemctl status postgresql

# Switch to postgres user
sudo -u postgres psql

Basic PostgreSQL Configuration

# Main configuration file
sudo nano /etc/postgresql/15/main/postgresql.conf

Key configuration settings:

# Connection Settings
listen_addresses = 'localhost'
port = 5432
max_connections = 200
superuser_reserved_connections = 3

# Memory Settings
shared_buffers = 2GB
effective_cache_size = 6GB
maintenance_work_mem = 512MB
work_mem = 16MB
wal_buffers = 16MB

# Checkpoint Settings
checkpoint_completion_target = 0.9
checkpoint_timeout = 10min
max_wal_size = 4GB
min_wal_size = 1GB

# Write Ahead Log
wal_level = replica
fsync = on
synchronous_commit = on
wal_sync_method = fdatasync
full_page_writes = on
wal_compression = on

# Query Tuning
random_page_cost = 1.1
cpu_tuple_cost = 0.01
cpu_index_tuple_cost = 0.005
cpu_operator_cost = 0.0025
effective_io_concurrency = 200

# Logging
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_file_mode = 0600
log_truncate_on_rotation = off
log_rotation_age = 1d
log_rotation_size = 100MB
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_checkpoints = on
log_connections = on
log_disconnections = on
log_duration = off
log_error_verbosity = default
log_hostname = on
log_statement = 'all'
log_timezone = 'UTC'

# Statistics
track_activities = on
track_counts = on
track_io_timing = on
track_functions = all
track_activity_query_size = 1024
stats_temp_directory = 'pg_stat_tmp'

# Autovacuum
autovacuum = on
autovacuum_max_workers = 4
autovacuum_naptime = 30s
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05

PostgreSQL Authentication Configuration

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

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256
host    all             all             192.168.1.0/24          scram-sha-256

Creating PostgreSQL Users and Databases

# Connect as postgres user
sudo -u postgres psql

-- Create database
CREATE DATABASE myapp_db
    WITH 
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;

-- Create user
CREATE USER myapp_user WITH ENCRYPTED PASSWORD 'strong_password';
GRANT CONNECT ON DATABASE myapp_db TO myapp_user;
GRANT USAGE ON SCHEMA public TO myapp_user;
GRANT CREATE ON SCHEMA public TO myapp_user;
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO myapp_user;

-- Create read-only user
CREATE USER readonly_user WITH ENCRYPTED PASSWORD 'password';
GRANT CONNECT ON DATABASE myapp_db TO readonly_user;
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;

-- Create backup user
CREATE USER backup_user WITH ENCRYPTED PASSWORD 'backup_password' REPLICATION;

Database Replication

MariaDB Master-Slave Replication

Master Server Configuration

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

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = myapp_db
binlog_format = ROW

Create replication user:

CREATE USER 'repl_user'@'%' IDENTIFIED BY 'replication_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
FLUSH PRIVILEGES;

-- Get master status
SHOW MASTER STATUS;

Slave Server Configuration

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

[mysqld]
server-id = 2
relay_log = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = myapp_db
read_only = 1

Configure slave:

STOP SLAVE;

CHANGE MASTER TO
    MASTER_HOST='master_ip',
    MASTER_USER='repl_user',
    MASTER_PASSWORD='replication_password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=1234;

START SLAVE;
SHOW SLAVE STATUS\G

PostgreSQL Streaming Replication

Primary Server Configuration

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

wal_level = replica
max_wal_senders = 3
wal_keep_size = 1GB
hot_standby = on

Create replication user:

CREATE USER repl_user WITH REPLICATION ENCRYPTED PASSWORD 'replication_password';

Update pg_hba.conf:

host    replication     repl_user       slave_ip/32             scram-sha-256

Standby Server Setup

# Stop PostgreSQL
sudo systemctl stop postgresql

# Clear data directory
sudo rm -rf /var/lib/postgresql/15/main/*

# Create base backup
sudo -u postgres pg_basebackup -h primary_ip -D /var/lib/postgresql/15/main -U repl_user -v -P -W

# Create standby signal file
sudo -u postgres touch /var/lib/postgresql/15/main/standby.signal

# Configure recovery
sudo nano /var/lib/postgresql/15/main/postgresql.auto.conf

Add:

primary_conninfo = 'host=primary_ip port=5432 user=repl_user password=replication_password'
restore_command = 'cp /var/lib/postgresql/15/archive/%f %p'

Database Backup Strategies

Automated MariaDB Backup Script

sudo nano /usr/local/bin/mariadb-backup.sh

#!/bin/bash

# MariaDB Backup Script with Rotation

# Configuration
MYSQL_USER="backup_user"
MYSQL_PASS="backup_password"
BACKUP_DIR="/backup/mysql"
RETENTION_DAYS=7
DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE="/var/log/mysql-backup.log"

# Create backup directory
mkdir -p "$BACKUP_DIR"

# Function to log messages
log_message() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}

# Start backup
log_message "Starting MariaDB backup"

# Get list of databases
DATABASES=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|mysql|sys)")

# Backup each database
for db in $DATABASES; do
    log_message "Backing up database: $db"

    # Create database backup with compression
    mysqldump -u$MYSQL_USER -p$MYSQL_PASS \
        --single-transaction \
        --routines \
        --triggers \
        --events \
        --add-drop-database \
        --databases $db | gzip > "$BACKUP_DIR/${db}_${DATE}.sql.gz"

    # Check backup status
    if [ $? -eq 0 ]; then
        log_message "Successfully backed up $db"
    else
        log_message "ERROR: Failed to backup $db"
    fi
done

# Create full backup
log_message "Creating full database backup"
mysqldump -u$MYSQL_USER -p$MYSQL_PASS \
    --all-databases \
    --single-transaction \
    --routines \
    --triggers \
    --events | gzip > "$BACKUP_DIR/all_databases_${DATE}.sql.gz"

# Binary backup using mariabackup
log_message "Creating binary backup"
mariabackup --backup \
    --target-dir="$BACKUP_DIR/binary_${DATE}" \
    --user=$MYSQL_USER \
    --password=$MYSQL_PASS

# Compress binary backup
tar -czf "$BACKUP_DIR/binary_${DATE}.tar.gz" -C "$BACKUP_DIR" "binary_${DATE}"
rm -rf "$BACKUP_DIR/binary_${DATE}"

# Remove old backups
log_message "Removing backups older than $RETENTION_DAYS days"
find "$BACKUP_DIR" -name "*.sql.gz" -o -name "*.tar.gz" -mtime +$RETENTION_DAYS -delete

# Verify backups
log_message "Verifying backup integrity"
for backup in "$BACKUP_DIR"/*_${DATE}.sql.gz; do
    if gzip -t "$backup" 2>/dev/null; then
        log_message "Backup $backup is valid"
    else
        log_message "ERROR: Backup $backup is corrupted"
    fi
done

log_message "MariaDB backup completed"

PostgreSQL Backup Automation

sudo nano /usr/local/bin/postgresql-backup.sh

#!/bin/bash

# PostgreSQL Backup Script

# Configuration
BACKUP_DIR="/backup/postgresql"
RETENTION_DAYS=7
DATE=$(date +%Y%m%d_%H%M%S)
PGUSER="backup_user"
LOG_FILE="/var/log/postgresql-backup.log"

# Create backup directory
mkdir -p "$BACKUP_DIR"

# Function to log messages
log_message() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}

# Start backup
log_message "Starting PostgreSQL backup"

# Get list of databases
DATABASES=$(sudo -u postgres psql -t -c "SELECT datname FROM pg_database WHERE datistemplate = false AND datname != 'postgres';")

# Backup each database
for db in $DATABASES; do
    db=$(echo $db | tr -d ' ')
    log_message "Backing up database: $db"

    # Custom format backup (recommended)
    sudo -u postgres pg_dump \
        --format=custom \
        --verbose \
        --blobs \
        --file="$BACKUP_DIR/${db}_${DATE}.dump" \
        "$db"

    # Plain SQL backup for portability
    sudo -u postgres pg_dump \
        --format=plain \
        --verbose \
        --file="$BACKUP_DIR/${db}_${DATE}.sql" \
        "$db"

    # Compress SQL backup
    gzip "$BACKUP_DIR/${db}_${DATE}.sql"
done

# Full cluster backup
log_message "Creating full cluster backup"
sudo -u postgres pg_dumpall | gzip > "$BACKUP_DIR/all_databases_${DATE}.sql.gz"

# Physical backup using pg_basebackup
log_message "Creating physical backup"
sudo -u postgres pg_basebackup \
    -D "$BACKUP_DIR/physical_${DATE}" \
    -Ft \
    -z \
    -P \
    -Xs

# WAL archiving setup
if [ -d "/var/lib/postgresql/15/archive" ]; then
    log_message "Archiving WAL files"
    find /var/lib/postgresql/15/archive -name "*.done" -mtime +1 -delete
fi

# Remove old backups
log_message "Removing backups older than $RETENTION_DAYS days"
find "$BACKUP_DIR" -name "*.dump" -o -name "*.sql.gz" -o -name "*.tar" -mtime +$RETENTION_DAYS -delete
find "$BACKUP_DIR" -type d -name "physical_*" -mtime +$RETENTION_DAYS -exec rm -rf {} +

log_message "PostgreSQL backup completed"

Performance Monitoring and Tuning

Database Performance Monitoring Script

sudo nano /usr/local/bin/db-performance-monitor.sh

#!/bin/bash

# Database Performance Monitoring Script

LOG_DIR="/var/log/db-performance"
DATE=$(date +%Y-%m-%d_%H-%M-%S)
REPORT="$LOG_DIR/performance_${DATE}.log"

mkdir -p "$LOG_DIR"

echo "Database Performance Report - $DATE" > "$REPORT"
echo "=====================================" >> "$REPORT"

# MariaDB Performance Metrics
if systemctl is-active --quiet mariadb; then
    echo -e "\n=== MariaDB Performance ===" >> "$REPORT"

    mysql -e "
    -- Connection statistics
    SHOW STATUS LIKE 'Threads_connected';
    SHOW STATUS LIKE 'Max_used_connections';

    -- Query cache statistics
    SHOW STATUS LIKE 'Qcache%';

    -- InnoDB buffer pool statistics
    SHOW STATUS LIKE 'Innodb_buffer_pool%';

    -- Slow queries
    SHOW STATUS LIKE 'Slow_queries';

    -- Table lock statistics
    SHOW STATUS LIKE 'Table_locks%';

    -- Current processlist
    SHOW PROCESSLIST;
    " >> "$REPORT" 2>&1

    # Top queries
    echo -e "\n--- Top Queries ---" >> "$REPORT"
    mysql -e "
    SELECT 
        digest_text,
        count_star,
        sum_timer_wait/1000000000000 as total_latency_sec,
        avg_timer_wait/1000000000000 as avg_latency_sec
    FROM performance_schema.events_statements_summary_by_digest
    ORDER BY sum_timer_wait DESC
    LIMIT 10;
    " >> "$REPORT" 2>&1
fi

# PostgreSQL Performance Metrics
if systemctl is-active --quiet postgresql; then
    echo -e "\n=== PostgreSQL Performance ===" >> "$REPORT"

    sudo -u postgres psql -c "
    -- Connection statistics
    SELECT count(*) as total_connections FROM pg_stat_activity;

    -- Database statistics
    SELECT datname, numbackends, xact_commit, xact_rollback, 
           blks_read, blks_hit, tup_returned, tup_fetched, tup_inserted,
           tup_updated, tup_deleted
    FROM pg_stat_database
    WHERE datname NOT IN ('template0', 'template1', 'postgres')
    ORDER BY numbackends DESC;

    -- Cache hit ratio
    SELECT 
        sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as cache_hit_ratio
    FROM pg_statio_user_tables;

    -- Long running queries
    SELECT pid, now() - pg_stat_activity.query_start AS duration, query 
    FROM pg_stat_activity 
    WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'
    AND state != 'idle'
    ORDER BY duration DESC;

    -- Table sizes
    SELECT 
        schemaname,
        tablename,
        pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
    FROM pg_tables
    WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
    ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
    LIMIT 10;
    " >> "$REPORT" 2>&1
fi

# System resource usage
echo -e "\n=== System Resources ===" >> "$REPORT"
echo "CPU Usage:" >> "$REPORT"
top -bn1 | grep "Cpu(s)" >> "$REPORT"
echo -e "\nMemory Usage:" >> "$REPORT"
free -h >> "$REPORT"
echo -e "\nDisk I/O:" >> "$REPORT"
iostat -x 1 3 >> "$REPORT"

# Check for issues and send alert
if grep -q "Too many connections\|deadlock\|out of memory" "$REPORT"; then
    mail -s "Database Performance Alert - $(hostname)" admin@example.com < "$REPORT"
fi

Query Optimization Tips

MariaDB Query Optimization

-- Enable query profiling
SET profiling = 1;

-- Run your query
SELECT * FROM large_table WHERE column = 'value';

-- View profile
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

-- Analyze query execution
EXPLAIN SELECT * FROM large_table WHERE column = 'value';
EXPLAIN EXTENDED SELECT * FROM large_table WHERE column = 'value';

-- Create indexes for better performance
CREATE INDEX idx_column ON large_table(column);

-- Analyze table statistics
ANALYZE TABLE large_table;

-- Optimize table
OPTIMIZE TABLE large_table;

PostgreSQL Query Optimization

-- Enable timing
\timing

-- Analyze query execution
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM large_table WHERE column = 'value';

-- Create indexes
CREATE INDEX idx_column ON large_table(column);
CREATE INDEX idx_composite ON large_table(column1, column2);

-- Partial index for specific conditions
CREATE INDEX idx_partial ON large_table(column) WHERE status = 'active';

-- Update table statistics
ANALYZE large_table;

-- Vacuum table
VACUUM ANALYZE large_table;

-- Check for missing indexes
SELECT 
    schemaname,
    tablename,
    attname,
    n_distinct,
    most_common_vals
FROM pg_stats
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
AND n_distinct > 100
AND attname NOT IN (
    SELECT a.attname
    FROM pg_index i
    JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey)
);

Security Best Practices

Database Security Hardening

sudo nano /usr/local/bin/db-security-audit.sh

#!/bin/bash

# Database Security Audit Script

AUDIT_LOG="/var/log/db-security-audit.log"
DATE=$(date +%Y-%m-%d_%H-%M-%S)

echo "Database Security Audit - $DATE" > "$AUDIT_LOG"
echo "===================================" >> "$AUDIT_LOG"

# MariaDB Security Checks
if systemctl is-active --quiet mariadb; then
    echo -e "\n=== MariaDB Security Audit ===" >> "$AUDIT_LOG"

    mysql -e "
    -- Check for users without passwords
    SELECT User, Host FROM mysql.user WHERE authentication_string = '';

    -- Check for users with broad host permissions
    SELECT User, Host FROM mysql.user WHERE Host = '%';

    -- Check for users with all privileges
    SELECT User, Host FROM mysql.user WHERE Super_priv = 'Y';

    -- Check for anonymous users
    SELECT User, Host FROM mysql.user WHERE User = '';

    -- List all user privileges
    SELECT User, Host, Db, Select_priv, Insert_priv, Update_priv, Delete_priv 
    FROM mysql.db ORDER BY User, Host, Db;
    " >> "$AUDIT_LOG" 2>&1
fi

# PostgreSQL Security Checks
if systemctl is-active --quiet postgresql; then
    echo -e "\n=== PostgreSQL Security Audit ===" >> "$AUDIT_LOG"

    sudo -u postgres psql -c "
    -- Check for users with superuser privileges
    SELECT usename, usesuper, usecreatedb, usecreaterole 
    FROM pg_user 
    WHERE usesuper = true;

    -- Check password encryption
    SHOW password_encryption;

    -- List all user permissions
    SELECT 
        grantee, 
        table_catalog, 
        table_schema, 
        table_name, 
        privilege_type
    FROM information_schema.role_table_grants
    WHERE grantee NOT IN ('postgres', 'PUBLIC')
    ORDER BY grantee, table_schema, table_name;

    -- Check for unencrypted connections
    SELECT 
        datname,
        usename,
        client_addr,
        ssl
    FROM pg_stat_ssl
    JOIN pg_stat_activity ON pg_stat_ssl.pid = pg_stat_activity.pid;
    " >> "$AUDIT_LOG" 2>&1
fi

# Check file permissions
echo -e "\n=== File Permission Audit ===" >> "$AUDIT_LOG"
ls -la /var/lib/mysql/ >> "$AUDIT_LOG" 2>&1
ls -la /var/lib/postgresql/ >> "$AUDIT_LOG" 2>&1

# Email audit results
mail -s "Database Security Audit - $(hostname)" admin@example.com < "$AUDIT_LOG"

High Availability Setup

MariaDB Galera Cluster Configuration

# Install Galera
sudo apt install galera-4 mariadb-server

# Configure first node
sudo nano /etc/mysql/mariadb.conf.d/galera.cnf

[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so

# Galera Cluster Configuration
wsrep_cluster_name="galera_cluster"
wsrep_cluster_address="gcomm://node1_ip,node2_ip,node3_ip"

# Galera Synchronization Configuration
wsrep_sst_method=rsync

# Galera Node Configuration
wsrep_node_address="this_node_ip"
wsrep_node_name="node1"

PostgreSQL High Availability with Patroni

# Install Patroni
sudo apt install python3-pip
sudo pip3 install patroni[etcd]

# Create Patroni configuration
sudo nano /etc/patroni/patroni.yml

scope: postgres-cluster
namespace: /service/
name: node1

restapi:
  listen: 0.0.0.0:8008
  connect_address: node1_ip:8008

etcd:
  hosts: etcd1:2379,etcd2:2379,etcd3:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      parameters:
        max_connections: 200
        shared_buffers: 2GB
        effective_cache_size: 6GB

postgresql:
  listen: 0.0.0.0:5432
  connect_address: node1_ip:5432
  data_dir: /var/lib/postgresql/15/main
  pgpass: /tmp/pgpass
  authentication:
    replication:
      username: replicator
      password: rep_password
    superuser:
      username: postgres
      password: postgres_password
  parameters:
    unix_socket_directories: '/var/run/postgresql'
    wal_level: replica
    hot_standby: "on"
    wal_log_hints: "on"
    max_wal_senders: 10
    max_replication_slots: 10

tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
    nosync: false

Conclusion

Proper database server setup and maintenance are crucial for application performance and data integrity. This guide provides comprehensive coverage of both MariaDB/MySQL and PostgreSQL installation, configuration, security, replication, and performance optimization on Debian servers.

Key takeaways: - Always secure your database installation immediately - Implement regular automated backups with testing - Monitor performance metrics continuously - Use replication for high availability - Apply security best practices and regular audits - Optimize queries and maintain indexes - Document your configuration for disaster recovery

Regular maintenance, monitoring, and optimization will ensure your database servers remain performant and reliable.

Debian Database Server Installation: MySQL/MariaDB & PostgreSQL Guide

Need Professional Debian Server Support?

Debian Database Server Installation: MySQL/MariaDB & PostgreSQL Guide

MySQL/MariaDB Installation and Setup

Installing MariaDB

Basic MariaDB Configuration

Creating Database Users and Permissions

PostgreSQL Installation and Setup

Installing PostgreSQL

Basic PostgreSQL Configuration

PostgreSQL Authentication Configuration

Creating PostgreSQL Users and Databases

Database Replication

MariaDB Master-Slave Replication

Master Server Configuration

Slave Server Configuration

PostgreSQL Streaming Replication

Primary Server Configuration

Standby Server Setup

Database Backup Strategies

Automated MariaDB Backup Script

PostgreSQL Backup Automation

Performance Monitoring and Tuning

Database Performance Monitoring Script

Query Optimization Tips

MariaDB Query Optimization

PostgreSQL Query Optimization

Security Best Practices

Database Security Hardening

High Availability Setup

MariaDB Galera Cluster Configuration

PostgreSQL High Availability with Patroni

Conclusion