Database Ops Basics
Why This Matters
At 3am, the on-call page goes off: "Database connection errors." You are the Linux admin, not the DBA. But the DBA is on vacation. The application team is panicking. You need to figure out whether the database server is healthy, whether it is accepting connections, why it is slow, and possibly restore from a backup -- all things that require basic database knowledge.
Every significant application stores data in a database. As a Linux administrator, you will install databases, manage their services, configure authentication, set up backups, monitor performance, and troubleshoot failures. You do not need to write complex SQL queries or design schemas, but you absolutely need to know how to keep database services running.
This chapter covers the two most popular open-source databases from an operations perspective: PostgreSQL and MariaDB (the open-source MySQL fork).
Try This Right Now
Check if you have any databases installed:
# Check for PostgreSQL
$ which psql && psql --version
psql (PostgreSQL) 15.5
# Check for MariaDB/MySQL
$ which mysql && mysql --version
mysql Ver 15.1 Distrib 10.11.4-MariaDB
# Check for running database processes
$ systemctl list-units --type=service | grep -iE "postgres|mysql|mariadb"
# Check which ports databases typically use
$ ss -tlnp | grep -E ':5432|:3306'
LISTEN 0 128 0.0.0.0:5432 0.0.0.0:* users:(("postgres",pid=1234,fd=6))
LISTEN 0 80 0.0.0.0:3306 0.0.0.0:* users:(("mariadbd",pid=5678,fd=22))
If nothing is installed, do not worry -- we will install them next.
PostgreSQL: Installation and Basic Administration
PostgreSQL (often called "Postgres") is the most advanced open-source relational database. It is known for data integrity, standards compliance, and extensibility.
Installing PostgreSQL
# Debian/Ubuntu
$ sudo apt update
$ sudo apt install -y postgresql postgresql-client
# Fedora
$ sudo dnf install -y postgresql-server postgresql
$ sudo postgresql-setup --initdb # Initialize data directory
# RHEL/AlmaLinux/Rocky
$ sudo dnf install -y postgresql-server postgresql
$ sudo postgresql-setup --initdb
# Start and enable
$ sudo systemctl enable --now postgresql
Distro Note: On Debian/Ubuntu, PostgreSQL is initialized automatically during package installation. On RHEL-family systems, you must run
postgresql-setup --initdbmanually before starting the service.
Verify Installation
$ sudo systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
Active: active (running)
$ sudo -u postgres psql -c "SELECT version();"
version
------------------------------------------------------------
PostgreSQL 15.5 on x86_64-pc-linux-gnu, compiled by gcc...
The postgres User
PostgreSQL creates a system user called postgres. By default, authentication uses "peer" mode -- the Linux username must match the PostgreSQL username:
# Switch to the postgres user to administer the database
$ sudo -u postgres psql
postgres=#
You are now in the PostgreSQL interactive shell. Type \q to exit.
Creating Databases and Users
# Method 1: Using command-line tools
$ sudo -u postgres createuser --interactive myappuser
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n
$ sudo -u postgres createdb --owner=myappuser myappdb
# Method 2: Using SQL
$ sudo -u postgres psql
postgres=# CREATE USER myappuser WITH PASSWORD 'SecurePassword123!';
CREATE ROLE
postgres=# CREATE DATABASE myappdb OWNER myappuser;
CREATE DATABASE
postgres=# GRANT ALL PRIVILEGES ON DATABASE myappdb TO myappuser;
GRANT
postgres=# \q
Useful psql Commands
$ sudo -u postgres psql
-- List all databases
\l
-- Connect to a database
\c myappdb
-- List all tables in current database
\dt
-- Describe a table's structure
\d tablename
-- List all users/roles
\du
-- Show current connection info
\conninfo
-- Run a query
SELECT datname, pg_size_pretty(pg_database_size(datname))
FROM pg_database;
-- Show running queries
SELECT pid, now() - query_start AS duration, query, state
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;
-- Exit
\q
Configuring PostgreSQL Authentication: pg_hba.conf
pg_hba.conf (Host-Based Authentication) controls who can connect and how they authenticate:
# Find the file
$ sudo -u postgres psql -c "SHOW hba_file;"
hba_file
-------------------------------------
/etc/postgresql/15/main/pg_hba.conf
$ sudo cat /etc/postgresql/15/main/pg_hba.conf
Key lines:
# 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 10.0.0.0/8 scram-sha-256
┌──────────────────────────────────────────────────────────────┐
│ pg_hba.conf AUTHENTICATION METHODS │
│ │
│ METHOD DESCRIPTION │
│ ────── ─────────── │
│ peer Match Linux username to PostgreSQL role │
│ (local connections only) │
│ scram-sha-256 Password authentication (most secure) │
│ md5 Password auth (older, less secure) │
│ trust No authentication (NEVER in production!) │
│ reject Deny connection │
│ │
│ TYPE DESCRIPTION │
│ ──── ─────────── │
│ local Unix socket connection │
│ host TCP/IP connection (with or without SSL) │
│ hostssl TCP/IP with SSL required │
│ │
└──────────────────────────────────────────────────────────────┘
After changing pg_hba.conf, reload PostgreSQL:
$ sudo systemctl reload postgresql
Safety Warning: Never set a production database to use
trustauthentication. This allows anyone who can reach the database to connect without a password. Always usescram-sha-256for password-based connections.
Configuring PostgreSQL: postgresql.conf
The main configuration file controls performance and behavior:
$ sudo -u postgres psql -c "SHOW config_file;"
config_file
-------------------------------------------
/etc/postgresql/15/main/postgresql.conf
Key settings:
# Listen on all interfaces (default: localhost only)
listen_addresses = '*' # Or specific IP: '10.0.0.5'
# Maximum connections
max_connections = 200
# Memory settings
shared_buffers = 2GB # 25% of total RAM
effective_cache_size = 6GB # 75% of total RAM
work_mem = 16MB # Per-operation memory
maintenance_work_mem = 512MB # For VACUUM, CREATE INDEX
# Logging
log_directory = 'log'
log_min_duration_statement = 1000 # Log queries slower than 1 second
Think About It: Why would setting
max_connectionstoo high actually hurt performance? Think about memory usage per connection and CPU context switching.
MariaDB/MySQL: Installation and Basic Administration
MariaDB is a community-maintained fork of MySQL, created by MySQL's original developer. It is drop-in compatible with MySQL but developed independently.
Installing MariaDB
# Debian/Ubuntu
$ sudo apt update
$ sudo apt install -y mariadb-server mariadb-client
# Fedora
$ sudo dnf install -y mariadb-server mariadb
# RHEL/AlmaLinux/Rocky
$ sudo dnf install -y mariadb-server mariadb
# Start and enable
$ sudo systemctl enable --now mariadb
Secure the Installation
MariaDB ships with insecure defaults. Always run the security script first:
$ sudo mysql_secure_installation
This script:
- Sets a root password (or switches to unix_socket authentication)
- Removes anonymous users
- Disables remote root login
- Removes the test database
- Reloads privilege tables
Answer Y to all prompts for a secure installation.
Connecting to MariaDB
# Connect as root (uses unix socket authentication on modern installs)
$ sudo mysql
MariaDB [(none)]>
# Or with password
$ mysql -u root -p
Enter password:
Creating Databases and Users
-- Create a database
CREATE DATABASE myappdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Create a user
CREATE USER 'myappuser'@'localhost' IDENTIFIED BY 'SecurePassword123!';
-- Grant privileges
GRANT ALL PRIVILEGES ON myappdb.* TO 'myappuser'@'localhost';
-- For remote access (specific IP only)
CREATE USER 'myappuser'@'10.0.0.%' IDENTIFIED BY 'SecurePassword123!';
GRANT ALL PRIVILEGES ON myappdb.* TO 'myappuser'@'10.0.0.%';
-- Apply changes
FLUSH PRIVILEGES;
-- Verify
SHOW GRANTS FOR 'myappuser'@'localhost';
Useful MySQL/MariaDB Commands
-- Show all databases
SHOW DATABASES;
-- Use a database
USE myappdb;
-- Show all tables
SHOW TABLES;
-- Describe a table
DESCRIBE tablename;
-- Show all users
SELECT user, host FROM mysql.user;
-- Show running queries
SHOW PROCESSLIST;
-- Show database sizes
SELECT table_schema AS "Database",
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.tables
GROUP BY table_schema;
-- Show server status
SHOW STATUS LIKE 'Conn%';
SHOW VARIABLES LIKE 'max_connections';
Backup and Restore
Backups are not optional. If you manage a database and do not have tested backups, you are one hardware failure away from disaster.
PostgreSQL Backup
# Dump a single database
$ sudo -u postgres pg_dump myappdb > myappdb_backup.sql
# Dump with compression
$ sudo -u postgres pg_dump myappdb | gzip > myappdb_$(date +%Y%m%d).sql.gz
# Dump all databases
$ sudo -u postgres pg_dumpall > all_databases.sql
# Custom format (supports parallel restore)
$ sudo -u postgres pg_dump -Fc myappdb > myappdb.dump
PostgreSQL Restore
# Restore from SQL dump
$ sudo -u postgres psql myappdb < myappdb_backup.sql
# Restore from custom format
$ sudo -u postgres pg_restore -d myappdb myappdb.dump
# Create database and restore
$ sudo -u postgres createdb myappdb_restored
$ sudo -u postgres pg_restore -d myappdb_restored myappdb.dump
MariaDB Backup
# Dump a single database
$ sudo mysqldump myappdb > myappdb_backup.sql
# Dump with compression
$ sudo mysqldump myappdb | gzip > myappdb_$(date +%Y%m%d).sql.gz
# Dump all databases
$ sudo mysqldump --all-databases > all_databases.sql
# Dump with routines and triggers
$ sudo mysqldump --routines --triggers myappdb > myappdb_full.sql
MariaDB Restore
# Restore from SQL dump
$ sudo mysql myappdb < myappdb_backup.sql
# Or within the MySQL shell
MariaDB [(none)]> SOURCE /path/to/myappdb_backup.sql;
Automated Backup Script
#!/bin/bash
# /opt/scripts/db-backup.sh
BACKUP_DIR="/opt/backups/databases"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=30
mkdir -p "$BACKUP_DIR"
# PostgreSQL backups
for db in $(sudo -u postgres psql -At -c "SELECT datname FROM pg_database WHERE datistemplate = false AND datname != 'postgres'"); do
echo "Backing up PostgreSQL database: $db"
sudo -u postgres pg_dump -Fc "$db" > "${BACKUP_DIR}/pg_${db}_${DATE}.dump"
done
# MariaDB backups
for db in $(sudo mysql -BNe "SHOW DATABASES" | grep -v -E "^(information_schema|performance_schema|mysql|sys)$"); do
echo "Backing up MariaDB database: $db"
sudo mysqldump "$db" | gzip > "${BACKUP_DIR}/mysql_${db}_${DATE}.sql.gz"
done
# Remove old backups
find "$BACKUP_DIR" -type f -mtime +${RETENTION_DAYS} -delete
echo "Backup completed: $(date)"
Schedule it with cron (see Chapter 24):
$ sudo crontab -e
# Daily at 2am
0 2 * * * /opt/scripts/db-backup.sh >> /var/log/db-backup.log 2>&1
Safety Warning: Always test your backups by restoring them to a test database. A backup you have never restored is a backup you cannot trust. Schedule restore tests at least monthly.
Monitoring Database Processes
PostgreSQL Monitoring
# Check active connections
$ sudo -u postgres psql -c "SELECT count(*) FROM pg_stat_activity;"
# Find long-running queries
$ sudo -u postgres psql -c "
SELECT pid, now() - query_start AS duration, state, query
FROM pg_stat_activity
WHERE state != 'idle'
AND query_start < now() - interval '5 seconds'
ORDER BY duration DESC;"
# Check database sizes
$ sudo -u postgres psql -c "
SELECT datname,
pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;"
# Check for locks
$ sudo -u postgres psql -c "
SELECT blocked.pid AS blocked_pid,
blocked.query AS blocked_query,
blocking.pid AS blocking_pid,
blocking.query AS blocking_query
FROM pg_stat_activity AS blocked
JOIN pg_locks AS blocked_locks ON blocked.pid = blocked_locks.pid
JOIN pg_locks AS blocking_locks ON blocked_locks.locktype = blocking_locks.locktype
AND blocked_locks.relation = blocking_locks.relation
JOIN pg_stat_activity AS blocking ON blocking_locks.pid = blocking.pid
WHERE NOT blocked_locks.granted
AND blocked.pid != blocking.pid;"
# Kill a runaway query
$ sudo -u postgres psql -c "SELECT pg_terminate_backend(12345);"
MariaDB Monitoring
# Check active connections
$ sudo mysql -e "SHOW STATUS LIKE 'Threads_connected';"
# Show running queries
$ sudo mysql -e "SHOW FULL PROCESSLIST;"
# Check database sizes
$ sudo mysql -e "
SELECT table_schema AS db,
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;"
# Kill a runaway query
$ sudo mysql -e "KILL 12345;"
# Show InnoDB status (detailed engine info)
$ sudo mysql -e "SHOW ENGINE INNODB STATUS\G" | head -50
Hands-On: Set Up a Database from Scratch
Let us install PostgreSQL and create a working database.
Step 1: Install and start PostgreSQL:
$ sudo apt install -y postgresql postgresql-client
$ sudo systemctl enable --now postgresql
Step 2: Create a user and database:
$ sudo -u postgres psql << 'SQL'
CREATE USER webapp WITH PASSWORD 'WebApp2024!';
CREATE DATABASE webapp_production OWNER webapp;
\c webapp_production
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO users (username, email) VALUES
('alice', 'alice@example.com'),
('bob', 'bob@example.com'),
('carol', 'carol@example.com');
GRANT ALL ON ALL TABLES IN SCHEMA public TO webapp;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO webapp;
SQL
Step 3: Test the connection:
$ PGPASSWORD='WebApp2024!' psql -U webapp -d webapp_production -h localhost -c "SELECT * FROM users;"
id | username | email | created_at
----+----------+--------------------+----------------------------
1 | alice | alice@example.com | 2025-06-15 10:30:00.000000
2 | bob | bob@example.com | 2025-06-15 10:30:00.000000
3 | carol | carol@example.com | 2025-06-15 10:30:00.000000
Step 4: Take a backup and restore:
# Backup
$ sudo -u postgres pg_dump -Fc webapp_production > /tmp/webapp_backup.dump
# Create a test database and restore
$ sudo -u postgres createdb webapp_test
$ sudo -u postgres pg_restore -d webapp_test /tmp/webapp_backup.dump
# Verify
$ sudo -u postgres psql -d webapp_test -c "SELECT count(*) FROM users;"
count
-------
3
Log Configuration
Database logs are critical for troubleshooting. Configure them properly from day one.
PostgreSQL Logging
Edit postgresql.conf:
# Log destination
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d.log'
log_rotation_age = 1d
log_rotation_size = 100MB
# What to log
log_min_duration_statement = 500 # Log queries taking > 500ms
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0 # Log all temp file usage
# Log format
log_line_prefix = '%t [%p] %u@%d ' # timestamp [pid] user@database
MariaDB Logging
Edit /etc/mysql/mariadb.conf.d/50-server.cnf:
[mysqld]
# Error log
log_error = /var/log/mysql/error.log
# Slow query log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 1 # Log queries taking > 1 second
# General query log (WARNING: very verbose, use only for debugging)
# general_log = 1
# general_log_file = /var/log/mysql/general.log
Connection Pooling and Replication Overview
Connection Pooling
Database connections are expensive. Each connection consumes memory and CPU. Connection poolers sit between applications and databases, reusing connections efficiently.
┌──────────────────────────────────────────────────────────────┐
│ CONNECTION POOLING │
│ │
│ WITHOUT POOLER: │
│ App Instance 1 ──── 20 connections ──┐ │
│ App Instance 2 ──── 20 connections ──├──► Database │
│ App Instance 3 ──── 20 connections ──┘ (60 connections) │
│ │
│ WITH POOLER (PgBouncer): │
│ App Instance 1 ──── 20 connections ──┐ │
│ App Instance 2 ──── 20 connections ──├──► PgBouncer ──► DB │
│ App Instance 3 ──── 20 connections ──┘ (10 actual conn.) │
│ │
│ 60 app connections share 10 real database connections. │
│ Result: much lower database resource usage. │
│ │
└──────────────────────────────────────────────────────────────┘
For PostgreSQL, PgBouncer is the standard connection pooler:
$ sudo apt install -y pgbouncer
Replication Overview
Replication copies data from one database server to others for redundancy and read scaling:
┌──────────────────────────────────────────────────────────────┐
│ REPLICATION │
│ │
│ PRIMARY (read-write) │
│ │ │
│ ├──── Streaming replication ──► REPLICA 1 (read-only) │
│ │ │
│ └──── Streaming replication ──► REPLICA 2 (read-only) │
│ │
│ Writes go to PRIMARY only. │
│ Reads can be served by any replica. │
│ If PRIMARY fails, a replica can be promoted. │
│ │
└──────────────────────────────────────────────────────────────┘
Setting up replication is beyond the scope of this basics chapter, but knowing the concept is essential for understanding production database architectures.
Debug This
A developer reports: "The application cannot connect to the database." You are the Linux admin. Walk through the diagnosis:
# 1. Is the database service running?
$ sudo systemctl status postgresql
# or
$ sudo systemctl status mariadb
# 2. Is it listening on the expected port?
$ ss -tlnp | grep -E ':5432|:3306'
# 3. Can you connect locally?
$ sudo -u postgres psql -c "SELECT 1;"
# or
$ sudo mysql -e "SELECT 1;"
# 4. Is the firewall blocking connections?
$ sudo iptables -L -n | grep -E '5432|3306'
# 5. Does the authentication config allow the connection?
# PostgreSQL: check pg_hba.conf
# MariaDB: check user host permissions
# 6. Is the database out of connections?
$ sudo -u postgres psql -c "SELECT count(*) FROM pg_stat_activity;"
$ sudo -u postgres psql -c "SHOW max_connections;"
# 7. Check the database logs
$ sudo tail -50 /var/log/postgresql/postgresql-15-main.log
# or
$ sudo tail -50 /var/log/mysql/error.log
Common causes:
- Service not running (crash, OOM kill)
- Listening only on localhost but application connects from another host
pg_hba.confor MySQL user host restrictions blocking the connection- Firewall blocking the port
max_connectionsreached- Wrong password or user does not exist
What Just Happened?
┌──────────────────────────────────────────────────────────────┐
│ CHAPTER 74 RECAP │
│──────────────────────────────────────────────────────────────│
│ │
│ Database ops basics every Linux admin needs: │
│ │
│ PostgreSQL: │
│ • Default port: 5432 │
│ • Config: postgresql.conf + pg_hba.conf │
│ • Tools: psql, createdb, createuser, pg_dump, pg_restore │
│ • Auth: peer (local), scram-sha-256 (network) │
│ │
│ MariaDB/MySQL: │
│ • Default port: 3306 │
│ • First step: mysql_secure_installation │
│ • Tools: mysql, mysqldump │
│ • Auth: unix_socket (local), password (network) │
│ │
│ Critical practices: │
│ • Automated daily backups with retention policy │
│ • TEST your backups by restoring them │
│ • Monitor connections, slow queries, disk usage │
│ • Configure logging for troubleshooting │
│ • Never use trust/no-password auth in production │
│ │
└──────────────────────────────────────────────────────────────┘
Try This
Exercise 1: PostgreSQL Setup
Install PostgreSQL, create a user and database, insert sample data, and take a backup. Restore the backup to a different database name and verify the data is intact.
Exercise 2: MariaDB Setup
Install MariaDB, run mysql_secure_installation, create a user with limited privileges (only SELECT and INSERT on a specific database), and verify the restrictions work.
Exercise 3: Monitoring Script
Write a shell script that checks:
- Is the database service running?
- How many active connections are there?
- What is the database disk usage?
- Are there any queries running longer than 60 seconds?
Run this script via cron every 5 minutes and log the output.
Exercise 4: Backup Automation
Set up the automated backup script from this chapter. Verify it creates dated backups and cleans up old ones. Test a restore from the most recent backup.
Bonus Challenge
Set up both PostgreSQL and MariaDB on the same server. Create a simple benchmark: insert 100,000 rows into a test table in each database and compare the time. Configure slow query logging on both and find any queries that exceed your threshold.