Skip to Content
DocsServerDatabase

Databases

Note

This article has been initially checked against the Ubuntu 26.04 LTS April 2026 release notes. Database versions and upgrade notes still need page-by-page validation.

Databases are a core component of virtually all web applications and services. The Ubuntu official repository provides installation packages for many popular databases. This article covers how to install and configure the most commonly used databases on Ubuntu 26.04: MySQL/MariaDB, PostgreSQL, Redis, and SQLite.

MySQL / MariaDB

MySQL is one of the world’s most popular open-source relational databases, while MariaDB is a community fork of MySQL maintained by MySQL’s original developers. The two are highly compatible in usage.

Tip

The Ubuntu 26.04 official repository provides both MySQL and MariaDB. MariaDB is a fully compatible drop-in replacement for MySQL with better performance optimizations and more open community governance. If you have no specific requirements, MariaDB is recommended. The command-line tools and SQL syntax are nearly identical.

Installing MySQL

# Install MySQL Server
sudo apt update
sudo apt install mysql-server
 
# Check service status
sudo systemctl status mysql
 
# Enable auto-start on boot
sudo systemctl enable mysql

Installing MariaDB

# Install MariaDB Server
sudo apt update
sudo apt install mariadb-server
 
# Check service status
sudo systemctl status mariadb
 
# Enable auto-start on boot
sudo systemctl enable mariadb

Security Initialization

After installation, be sure to run the security initialization script to harden the database:

# Both MySQL and MariaDB use the same command
sudo mysql_secure_installation

The script will guide you through the following operations:

  1. Set root password (if not already set)
  2. Remove anonymous users (strongly recommended)
  3. Disallow root remote login (strongly recommended)
  4. Remove test database (recommended)
  5. Reload privilege tables

Basic Operations

# Log in to MySQL/MariaDB (Ubuntu defaults to unix_socket authentication)
sudo mysql
 
# Or log in with a password
mysql -u root -p

Common SQL operations:

-- List all databases
SHOW DATABASES;
 
-- Create a database
CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 
-- Use a database
USE myapp;
 
-- Create a user and set password
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'StrongPassword123!';
 
-- Grant privileges (limited to a specific database)
GRANT ALL PRIVILEGES ON myapp.* TO 'appuser'@'localhost';
 
-- Flush privileges
FLUSH PRIVILEGES;
 
-- View user privileges
SHOW GRANTS FOR 'appuser'@'localhost';
 
-- Create a table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- Insert data
INSERT INTO users (username, email) VALUES ('zhang', 'zhang@example.com');
 
-- Query data
SELECT * FROM users;
 
-- View table structure
DESCRIBE users;
 
-- Drop a user
DROP USER 'appuser'@'localhost';
 
-- Drop a database
DROP DATABASE myapp;
 
-- Exit
EXIT;

Remote Access Configuration

By default, MySQL/MariaDB only listens on localhost (127.0.0.1). To enable remote access:

# Edit MySQL configuration
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# Or MariaDB configuration
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Find the bind-address line and change it to:

# Allow connections from all IPs (in production, bind to a specific IP)
bind-address = 0.0.0.0

Then create a user that allows remote connections:

-- Create a user that can connect from any IP
CREATE USER 'remoteuser'@'%' IDENTIFIED BY 'StrongPassword123!';
GRANT ALL PRIVILEGES ON myapp.* TO 'remoteuser'@'%';
FLUSH PRIVILEGES;
 
-- Or restrict to a specific IP
CREATE USER 'remoteuser'@'192.168.1.100' IDENTIFIED BY 'StrongPassword123!';
GRANT ALL PRIVILEGES ON myapp.* TO 'remoteuser'@'192.168.1.100';
FLUSH PRIVILEGES;
# Restart the service for changes to take effect
sudo systemctl restart mysql
# Or
sudo systemctl restart mariadb
 
# If the firewall is enabled, allow the port
sudo ufw allow 3306/tcp
Warning

Exposing the database to the public internet (0.0.0.0) is extremely dangerous. If remote access is necessary, make sure to: use strong passwords, restrict allowed connection IPs, configure firewall rules, and consider using SSH tunnels or a VPN instead of directly exposing the port.

Common Configuration Optimizations

Edit /etc/mysql/mysql.conf.d/mysqld.cnf (or the corresponding MariaDB configuration file):

[mysqld]
# Character set (utf8mb4 recommended to support emoji and other characters)
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
 
# InnoDB buffer pool (recommended: 50%-70% of physical memory)
innodb_buffer_pool_size = 1G
 
# Maximum connections
max_connections = 200
 
# Slow query log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
# Restart the service after changes
sudo systemctl restart mysql

PostgreSQL

PostgreSQL (commonly abbreviated as PG) is a powerful open-source object-relational database known for its strict SQL standard compliance, rich data types, and powerful extensibility. It’s the ideal choice for complex queries, geospatial data (PostGIS), and JSON data.

Installing PostgreSQL

# Install PostgreSQL
sudo apt update
sudo apt install postgresql postgresql-client
 
# Check service status
sudo systemctl status postgresql
 
# Check installed version
psql --version

User and Database Creation

PostgreSQL uses its own user authentication system. After installation, a system user and database superuser named postgres are automatically created.

# Switch to the postgres user
sudo -u postgres psql
-- Create a new database user
CREATE USER appuser WITH PASSWORD 'StrongPassword123!';
 
-- Create a database and assign an owner
CREATE DATABASE myapp OWNER appuser;
 
-- Grant connection privileges
GRANT ALL PRIVILEGES ON DATABASE myapp TO appuser;
 
-- List all databases
\l
 
-- List all users
\du
 
-- Switch database
\c myapp
 
-- Exit
\q

You can also use command-line tools:

# Create a new user using the postgres account
sudo -u postgres createuser --interactive appuser
 
# Create a database
sudo -u postgres createdb -O appuser myapp
 
# Connect with the new user
psql -U appuser -d myapp -h localhost

Basic Operations

# Connect to a database
psql -U appuser -d myapp -h localhost
-- Create a table
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    tags TEXT[],
    metadata JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
 
-- Insert data (note: PostgreSQL supports array and JSON types)
INSERT INTO articles (title, content, tags, metadata)
VALUES (
    'Ubuntu 26.04 Released',
    'The new version brings many improvements...',
    ARRAY['ubuntu', 'linux', 'release'],
    '{"author": "admin", "views": 0}'::jsonb
);
 
-- Query data
SELECT * FROM articles;
 
-- JSON query
SELECT title FROM articles WHERE metadata->>'author' = 'admin';
 
-- Array query
SELECT title FROM articles WHERE 'ubuntu' = ANY(tags);
 
-- View table structure
\d articles
 
-- List all tables
\dt
 
-- View table sizes
\dt+

Common psql Commands

CommandDescription
\lList all databases
\c dbnameSwitch to a specific database
\dtList all tables in the current database
\d tablenameView table structure
\duList all users/roles
\diList all indexes
\dfList all functions
\timingToggle query timing on/off
\xToggle expanded display mode
\qExit

Remote Access Configuration

# Edit the main configuration file
sudo nano /etc/postgresql/18/main/postgresql.conf
# Change the listen address
listen_addresses = '*'
# Edit the client authentication configuration
sudo nano /etc/postgresql/18/main/pg_hba.conf

Add at the end of the file:

# Allow a specific subnet via password authentication
host    all    all    192.168.1.0/24    scram-sha-256

# Or allow all IPs (not recommended for production)
host    all    all    0.0.0.0/0    scram-sha-256
# Restart PostgreSQL
sudo systemctl restart postgresql
 
# Allow through firewall
sudo ufw allow 5432/tcp

Redis / Valkey

Redis / Valkey is an open-source in-memory data structure store that can be used as a database, cache, and message broker. They support various data structures such as strings, hashes, lists, and sets, and are known for their extremely fast read/write speeds.

Note

The Ubuntu 26.04 official repository promotes Valkey 9.0 (a community fork of Redis that is compatible with the Redis protocol) and has moved it into the main component. Valkey is recommended; Redis can still be installed separately — valkey-server and redis-server coexist in the repository. The examples below use Valkey, with the valkey-server / valkey-cli commands; the old redis-cli is still available as a compatibility alias.

Installing Valkey

# Install Valkey (recommended on 26.04)
sudo apt update
sudo apt install valkey-server
 
# If you need Redis, you can install it separately (coexists with Valkey; swap the redis prefix into the commands)
# sudo apt install redis-server
 
# Check service status
sudo systemctl status valkey-server
 
# Verify it is working properly
valkey-cli ping
# Should output: PONG

Basic Operations

# Connect to the Valkey CLI (redis-cli is also available as a compatibility alias)
valkey-cli
# String operations
SET name "Ubuntu Fan"
GET name

# Set a key with expiration (60 seconds)
SET session:abc123 "user_data" EX 60

# Check remaining TTL
TTL session:abc123

# Hash operations (similar to objects)
HSET user:1 name "John" email "john@example.com" age "30"
HGET user:1 name
HGETALL user:1

# List operations
LPUSH tasks "task1" "task2" "task3"
LRANGE tasks 0 -1
RPOP tasks

# Set operations
SADD online_users "user1" "user2" "user3"
SMEMBERS online_users
SISMEMBER online_users "user1"

# Sorted set (leaderboard scenario)
ZADD leaderboard 100 "player1" 200 "player2" 150 "player3"
ZREVRANGE leaderboard 0 -1 WITHSCORES

# List all keys
KEYS *

# Delete a key
DEL name

# Check key type
TYPE user:1

# View server info
INFO

# View memory usage
INFO memory

# Exit
QUIT

Persistence Configuration

Data is stored in memory by default and will be lost on restart. Valkey provides two persistence methods:

RDB (Snapshots) — Periodically saves in-memory data as snapshot files:

# Edit Valkey configuration (the Redis-compatible package uses /etc/redis/redis.conf)
sudo nano /etc/valkey/valkey.conf
# RDB persistence rules (enabled by default)
# Save if at least 1 change in 3600 seconds
save 3600 1
# Save if at least 100 changes in 300 seconds
save 300 100
# Save if at least 10000 changes in 60 seconds
save 60 10000
 
# RDB file location
dir /var/lib/valkey
dbfilename dump.rdb

AOF (Append-Only File) — Records every write operation:

# Enable AOF
appendonly yes
 
# AOF sync policy
# always: Sync on every write (safest, but slowest)
# everysec: Sync once per second (recommended, balances safety and performance)
# no: Let the OS decide (fastest, but may lose data)
appendfsync everysec
 
# AOF filename
appendfilename "appendonly.aof"
# Restart Valkey for changes to take effect
sudo systemctl restart valkey-server

Valkey Security Configuration

sudo nano /etc/valkey/valkey.conf
# Set password
requirepass YourStrongRedisPassword
 
# Bind address (defaults to local access only)
bind 127.0.0.1 ::1
 
# Disable dangerous commands
rename-command FLUSHDB ""
rename-command FLUSHALL ""
rename-command CONFIG ""
# Connect with password
valkey-cli -a YourStrongRedisPassword
 
# Or authenticate after connecting
valkey-cli
AUTH YourStrongRedisPassword

SQLite

SQLite is a lightweight embedded relational database. It doesn’t require a separate server process, and all data is stored in a single file. SQLite is ideal for small applications, prototyping, mobile apps, and embedded systems.

Installation and Usage

# Install SQLite
sudo apt install sqlite3
 
# Create/open a database file
sqlite3 mydb.sqlite
 
# Operate within the SQLite prompt
-- Create a table
CREATE TABLE notes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    content TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
 
-- Insert data
INSERT INTO notes (title, content) VALUES ('Test Note', 'This is a test entry');
 
-- Query
SELECT * FROM notes;
 
-- List all tables
.tables
 
-- View table structure
.schema notes
 
-- Display results with column names
.headers on
.mode column
 
-- Export as SQL
.dump
 
-- Export as CSV
.mode csv
.output notes.csv
SELECT * FROM notes;
.output stdout
 
-- Exit
.quit

SQLite’s strengths lie in zero configuration and zero maintenance. It’s suitable for:

  • Small web applications (e.g., personal blogs)
  • Development and testing environments
  • Local data storage for desktop applications
  • Data analysis (paired with Python’s sqlite3 module)

Database Backup Strategies

Data backup is one of the most important tasks in database management. Here are the backup methods for each database:

MySQL / MariaDB Backup

# Back up a single database
mysqldump -u root -p myapp > myapp_backup_$(date +%Y%m%d).sql
 
# Back up all databases
mysqldump -u root -p --all-databases > all_databases_$(date +%Y%m%d).sql
 
# Compressed backup
mysqldump -u root -p myapp | gzip > myapp_backup_$(date +%Y%m%d).sql.gz
 
# Restore a database
mysql -u root -p myapp < myapp_backup_20260324.sql
 
# Restore a compressed backup
gunzip < myapp_backup_20260324.sql.gz | mysql -u root -p myapp

PostgreSQL Backup

# Back up a single database
sudo -u postgres pg_dump myapp > myapp_backup_$(date +%Y%m%d).sql
 
# Back up all databases
sudo -u postgres pg_dumpall > all_databases_$(date +%Y%m%d).sql
 
# Custom format backup (supports parallel restore)
sudo -u postgres pg_dump -Fc myapp > myapp_backup_$(date +%Y%m%d).dump
 
# Restore SQL format backup
sudo -u postgres psql myapp < myapp_backup_20260324.sql
 
# Restore custom format backup
sudo -u postgres pg_restore -d myapp myapp_backup_20260324.dump

Valkey Backup

# Manually trigger RDB snapshot
valkey-cli BGSAVE
 
# Copy the RDB file
sudo cp /var/lib/valkey/dump.rdb /backup/valkey_$(date +%Y%m%d).rdb
 
# Restore: stop Valkey, replace dump.rdb, restart
sudo systemctl stop valkey-server
sudo cp /backup/valkey_20260324.rdb /var/lib/valkey/dump.rdb
sudo chown valkey:valkey /var/lib/valkey/dump.rdb
sudo systemctl start valkey-server

Automated Backup Script

Create a cron job for automatic backups:

# Create backup script
sudo nano /usr/local/bin/db-backup.sh
#!/bin/bash
BACKUP_DIR="/backup/databases"
DATE=$(date +%Y%m%d_%H%M%S)
mkdir -p "$BACKUP_DIR"
 
# MySQL/MariaDB backup
mysqldump -u root --all-databases | gzip > "$BACKUP_DIR/mysql_$DATE.sql.gz"
 
# PostgreSQL backup
sudo -u postgres pg_dumpall | gzip > "$BACKUP_DIR/postgres_$DATE.sql.gz"
 
# Valkey backup
valkey-cli BGSAVE
sleep 5
cp /var/lib/valkey/dump.rdb "$BACKUP_DIR/valkey_$DATE.rdb"
 
# Delete backups older than 30 days
find "$BACKUP_DIR" -type f -mtime +30 -delete
 
echo "Backup complete: $DATE"
# Set execute permission
sudo chmod +x /usr/local/bin/db-backup.sh
 
# Add cron job (runs daily at 3 AM)
sudo crontab -e
# Add the following line:
# 0 3 * * * /usr/local/bin/db-backup.sh >> /var/log/db-backup.log 2>&1

Database Comparison: MySQL vs PostgreSQL vs MariaDB

FeatureMySQLPostgreSQLMariaDB
DeveloperOraclePostgreSQL CommunityMariaDB Foundation
LicenseGPL v2 (dual license)PostgreSQL License (MIT-like)GPL v2
Default Port330654323306
ACID ComplianceInnoDB engine supportedFull supportInnoDB/Aria engine supported
JSON SupportJSON typeJSONB type (more powerful)JSON type
Full-Text SearchSupportedSupported (more powerful)Supported
GeospatialBasic supportPostGIS extension (industry-leading)Basic support
ReplicationPrimary-replica, group replicationStreaming replication, logical replicationPrimary-replica, Galera cluster
Storage EnginesMultiple (InnoDB, MyISAM, etc.)Single engineMultiple (InnoDB, Aria, ColumnStore, etc.)
SQL Standard ComplianceGoodBestGood
Use CasesWeb apps, read-heavy workloadsComplex queries, data analytics, GISWeb apps, MySQL replacement
Learning CurveLowMediumLow (MySQL compatible)

Recommendation Guide

  • MySQL: Largest ecosystem and most community resources; suitable for most web applications
  • PostgreSQL: Most powerful features; ideal for complex queries, JSON operations, and geospatial data
  • MariaDB: Open-source MySQL alternative with more aggressive performance optimizations and a more open community

Common Management Commands Summary

# --- MySQL / MariaDB ---
sudo systemctl start mysql        # Start
sudo systemctl stop mysql         # Stop
sudo systemctl restart mysql      # Restart
sudo systemctl status mysql       # Check status
sudo mysql                        # Log in
 
# --- PostgreSQL ---
sudo systemctl start postgresql   # Start
sudo systemctl stop postgresql    # Stop
sudo systemctl restart postgresql # Restart
sudo systemctl status postgresql  # Check status
sudo -u postgres psql             # Log in
 
# --- Valkey ---
sudo systemctl start valkey-server   # Start
sudo systemctl stop valkey-server    # Stop
sudo systemctl restart valkey-server # Restart
sudo systemctl status valkey-server  # Check status
valkey-cli                           # Log in
valkey-cli INFO                      # View info
valkey-cli DBSIZE                    # View key count

Choosing and configuring the right database is fundamental to building reliable web applications. It’s recommended to experiment with different databases in your development environment to find the best fit for your project’s needs.

Last updated on
Kubernetes