Administration, Reliability, and Cloud Operations

Introduction

A production database is not merely a SQL engine. It is a stateful service whose data, configuration, logs, security controls, dependencies, and recovery procedures must remain dependable under normal operation and failure. Database administration therefore combines systems engineering, information security, performance analysis, software release management, and business continuity planning.

This chapter uses MySQL 8.0+ and PostgreSQL 15+ examples. Commands assume Linux, administrative privileges, and a test environment unless explicitly stated. Distribution packages, paths, service names, and cloud interfaces vary; always verify version-specific documentation before changing production systems.

Learning objectives

By the end of this chapter, you should be able to:

1. Install and validate MySQL and PostgreSQL.
2. Configure secure access, logging, memory, connections, and durability.
3. Monitor availability, performance, capacity, and replication.
4. Perform logical and physical backup, point-in-time recovery (PITR), and restore validation.
5. Explain MySQL binary-log replication and PostgreSQL streaming/WAL replication.
6. Plan upgrades, disaster recovery, service-level objectives (SLOs), and incident response.
7. Evaluate managed cloud databases, automation, observability, and cost controls.

1. The operational model

Database reliability is commonly evaluated through four related properties:

• Availability: whether clients can successfully use the service.
• Durability: whether committed data survives faults.
• Recoverability: whether service and data can be restored within required limits.
• Integrity: whether constraints, transactions, permissions, and replication preserve correctness.

A useful reliability model distinguishes the primary database, replicas, backup storage, monitoring, automation, and people. Replication improves availability and read capacity, but it is not a backup: an accidental DELETE can replicate immediately, and corrupted data can be copied to every replica.

Figure: A reliability architecture separates serving, replication, backup, and observability functions.

Service objectives and recovery measures

An SLO is a target, such as 99.9% monthly availability. In a 30-day month there are 43,200 minutes. A 99.9% target permits:

This is an allowance, not a guarantee. Define the measurement window, excluded maintenance, monitoring method, and user-visible failure condition.

Two recovery objectives are central:

• RPO (Recovery Point Objective): maximum acceptable data loss measured in time.
• RTO (Recovery Time Objective): maximum acceptable time from outage declaration to service restoration.

If backups run every six hours, an unreplicated failure can theoretically lose nearly six hours of committed data; the expected loss under uniformly distributed failure time is approximately three hours, but planning must use the worst permitted loss, not the average. PITR can reduce RPO if logs are continuously archived.

2. Installation and initial validation

MySQL on Debian/Ubuntu

sudo apt update
sudo apt install mysql-server
sudo systemctl enable --now mysql
sudo mysql_secure_installation
sudo systemctl status mysql
mysql --version
sudo mysql -e "SELECT VERSION(), @@datadir, @@log_bin;"

Run the security wizard deliberately: remove anonymous users and the test database, restrict remote administrative access, and set an appropriate authentication policy. Do not expose port 3306 directly to the public internet.

PostgreSQL on Debian/Ubuntu

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl enable --now postgresql
sudo -u postgres psql -c "SELECT version();"
pg_lsclusters
sudo -u postgres createdb appdb
sudo -u postgres psql -d appdb -c "SELECT current_database(), current_user;"

PostgreSQL commonly stores cluster configuration under /etc/postgresql/<version>/main/, while the data directory is often /var/lib/postgresql/<version>/main/. Obtain authoritative paths with:

SHOW data_directory;
SHOW config_file;
SHOW hba_file;

Create a least-privilege role rather than using postgres in an application:

CREATE ROLE app_user LOGIN PASSWORD 'use-a-secret-manager';
GRANT CONNECT ON DATABASE appdb TO app_user;
\c appdb
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

In real deployments, use a secret manager and preferably short-lived or rotated credentials. PostgreSQL client authentication is controlled by pg_hba.conf; after changes, validate and reload:

sudo -u postgres pg_ctlcluster 15 main reload
sudo -u postgres psql -c "SELECT pg_reload_conf();"

3. Configuration for correctness and performance

Configuration is workload-dependent. Change one controlled parameter at a time, record the reason, and measure before and after.

MySQL

A typical configuration file is /etc/mysql/mysql.conf.d/mysqld.cnf:

[mysqld]
bind-address = 10.0.1.10
max_connections = 200
innodb_buffer_pool_size = 4G
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
server_id = 101
slow_query_log = ON
long_query_time = 1

innodb_flush_log_at_trx_commit=1 and sync_binlog=1 favor durability, subject to operating-system and storage guarantees. They can reduce throughput compared with relaxed settings. ROW binlogging generally makes replication less dependent on nondeterministic statement behavior. Verify settings:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES WHERE Variable_name IN ('log_bin','binlog_format','server_id');
SHOW GLOBAL STATUS LIKE 'Threads_connected';

Restart only when required:

sudo systemctl restart mysql
sudo journalctl -u mysql -n 50 --no-pager

PostgreSQL

Important settings in postgresql.conf include:

listen_addresses = '10.0.1.10'
max_connections = 200
shared_buffers = 1GB
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /backup/wal/%f && cp %p /backup/wal/%f'
logging_collector = on
log_min_duration_statement = 1000

The archive command must be reliable and must return success only when the WAL file is safely copied. Never use a command that silently discards files. archive_mode requires a restart to enable; many other settings require only reload. Check:

SHOW shared_buffers;
SHOW wal_level;
SHOW archive_mode;
SELECT pg_reload_conf();

A production archive normally copies to durable, access-controlled object storage using a tested utility rather than an unencrypted local directory. PostgreSQL shared_buffers is not a universal percentage formula; operating-system cache, workload, and memory pressure must be measured.

4. Backups and point-in-time recovery

A backup strategy has at least three properties: an independent copy, a defined retention period, and a tested restore. Apply the 3-2-1 principle where appropriate: at least three copies, on two media or failure domains, with at least one off-site or separately protected copy. Encrypt backups in transit and at rest, restrict access, and test their readability.

MySQL logical backup and restore

mysqldump is portable and useful for smaller databases or object-level recovery:

mysqldump --single-transaction --routines --events --triggers \
  --all-databases --hex-blob --source-data=2 > full.sql
mysql < full.sql

--single-transaction provides a consistent snapshot for transactional InnoDB tables without a long write lock; it does not make nontransactional tables consistent. For large systems, MySQL Enterprise Backup or a filesystem/storage snapshot coordinated with MySQL may be more suitable.

MySQL conventional PITR uses a full backup plus archived binary logs, not archived InnoDB redo logs. First identify the binary-log position or GTID recorded with the backup:

mysqlbinlog --read-from-remote-server --host=db1 \
  --raw --stop-never --result-file=/backup/binlog/ mysql-bin.000001

After restoring the full backup to an isolated server, replay binary logs up to a time:

mysql < full.sql
mysqlbinlog --stop-datetime="2025-03-08 14:32:00" \
  /backup/binlog/mysql-bin.000001 /backup/binlog/mysql-bin.000002 | mysql

Use GTID or recorded positions to avoid duplicate replay. Confirm application consistency and inspect the recovered data before redirecting traffic.

PostgreSQL logical and physical backup

Logical backup:

pg_dump -Fc -d appdb -f appdb.dump
createdb appdb_restored
pg_restore --clean --if-exists --dbname=appdb_restored appdb.dump
pg_dumpall --globals-only > globals.sql

For a large cluster, physical base backup plus WAL archiving is the usual PITR approach:

sudo -u postgres pg_basebackup -D /backup/base/$(date +%F) \
  -Fp -Xs -P -c fast

To recover, use a separate data directory, restore the base backup, create recovery.signal, and configure a restore command. For PostgreSQL 12+:

restore_command = 'cp /backup/wal/%f %p'
recovery_target_time = '2025-03-08 14:32:00+00'

Start PostgreSQL and inspect the log. PostgreSQL replays archived WAL (Write-Ahead Log) records. It does not use MySQL binary logs. After recovery, validate row counts, constraints, application checks, and the target recovery timestamp.

A backup job is incomplete until restoration is periodically demonstrated. Record backup duration, size, age of the newest backup/log, restore duration, and checksum or verification results.

5. Replication and high availability

MySQL asynchronous replication

On the source, configure unique server_id, binary logging, and a replication account:

CREATE USER 'repl'@'10.0.2.%' IDENTIFIED BY 'strong-secret';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'10.0.2.%';
FLUSH PRIVILEGES;
SHOW MASTER STATUS;

On a fresh replica, configure server_id=102, then, for a position-based setup:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='10.0.1.10', SOURCE_USER='repl', SOURCE_PASSWORD='strong-secret',
  SOURCE_LOG_FILE='mysql-bin.000001', SOURCE_LOG_POS=12345;
START REPLICA;
SHOW REPLICA STATUS\G

Prefer GTID-based replication in new designs, with TLS and a documented failover process. Key health fields include Replica_IO_Running, Replica_SQL_Running, and Seconds_Behind_Source; the last is not a perfect measure of true data freshness, especially during idle periods or clock anomalies.

PostgreSQL streaming replication

On the primary, set wal_level=replica, a suitable max_wal_senders, and (if using replication slots) max_replication_slots. Add a narrowly scoped entry to pg_hba.conf, such as:

host replication repl 10.0.2.20/32 scram-sha-256

Create the role:

CREATE ROLE repl WITH REPLICATION LOGIN PASSWORD 'strong-secret';

Initialize the standby:

pg_basebackup -h 10.0.1.10 -U repl -D /var/lib/postgresql/15/standby \
  -Fp -Xs -P -R

The -R option writes connection settings and creates standby signaling for supported versions. On the primary, inspect:

SELECT client_addr, state, sync_state, sent_lsn, write_lsn, flush_lsn, replay_lsn
FROM pg_stat_replication;

On the standby:

SELECT pg_is_in_recovery(), pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();

Synchronous replication can reduce acknowledged-write loss but increases commit latency and depends on correctly chosen synchronous standbys. Asynchronous replication offers lower latency but may lose recent commits during primary failure. A replication slot prevents removal of WAL needed by a lagging consumer, but an abandoned slot can fill disk; monitor pg_replication_slots.

6. Monitoring, observability, and capacity

Observability combines:

• Metrics: CPU, memory, disk latency, free space, connections, transactions, cache hit behavior, replication lag, and backup age.
• Logs: authentication failures, slow statements, checkpoints, deadlocks, crashes, and archival errors.
• Traces: request-level timing across application and database boundaries.

Useful MySQL queries include:

SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

PostgreSQL examples include:

SELECT * FROM pg_stat_activity;
SELECT * FROM pg_stat_database;
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

Enable pg_stat_statements according to the installation method and version. Monitor locks and long transactions because they can prevent cleanup and cause table or WAL growth.

Capacity planning should connect demand to resources. If average transaction arrival is λ transactions/second and average service time is S seconds, utilization is:

This simple relationship assumes a stable workload and consistent service time; it is not a complete sizing model. For example, λ = 80 transactions/s and S = 0.005 s gives U = 0.4, or 40%. Bursty workloads, I/O waits, locks, and multiple resource pools can make observed latency much worse. Track trends, test peak load, and reserve headroom.

For storage, estimate:

Define alert thresholds with time to exhaustion, not arbitrary percentages alone. A disk at 70% may be urgent if it grows 10% per hour; a disk at 90% may be manageable if growth is negligible and expansion is automated.

7. Upgrades and change management

A major-version upgrade can change SQL behavior, authentication, extensions, optimizer plans, replication compatibility, and backup tooling. The safe sequence is:

1. Inventory versions, extensions, collations, schemas, jobs, integrations, and data volume.
2. Read release notes and incompatibility documentation.
3. Test restore and upgrade on a representative clone.
4. Capture performance baselines and application regression tests.
5. Rehearse rollback and verify backups.
6. Announce a maintenance window or use a tested rolling/blue-green strategy.
7. Upgrade, run smoke tests, inspect logs and metrics, and retain the old environment until acceptance.

For PostgreSQL major versions, pg_upgrade or logical replication may be used; pg_upgrade --check validates many prerequisites without performing the upgrade. PostgreSQL minor releases are generally in-place binary updates, but backups and testing remain essential. MySQL upgrades require checking reserved words, authentication plugins, data dictionary changes, and replication compatibility. Never treat a package-manager upgrade as a complete migration plan.

8. Managed cloud databases and cost control

Managed services such as Amazon RDS/Aurora, Google Cloud SQL/AlloyDB, and Azure Database services provide automated patching, backups, monitoring, and failover mechanisms. They reduce undifferentiated operational work but do not remove responsibility for schema design, access control, query quality, recovery objectives, or cost.

Evaluate:

• supported engine versions and extensions;
• backup retention and cross-region recovery;
• maintenance and failover behavior;
• network latency and egress charges;
• encryption keys and audit logs;
• maximum connections, storage scaling, and read replicas;
• export and exit strategy.

Cost controls include right-sizing, scheduled nonproduction shutdown, storage lifecycle policies, connection pooling, query/index optimization, and budgets with alerts. A cheaper instance that produces timeouts can cost more through lost revenue and support effort. Compare total cost with availability, performance, compliance, and recovery requirements.

Automation should be declarative and reviewable: infrastructure as code, configuration management, migration tooling, backup jobs, health checks, and runbooks stored in version control. Secrets must not be committed to repositories or embedded in shell history.

9. Incident response and governance

A database incident process should identify severity, incident commander, communications owner, technical lead, and decision log. The initial sequence is:

1. Detect and acknowledge the symptom.
2. Protect evidence and prevent harmful changes.
3. Assess scope: availability, integrity, confidentiality, and replication state.
4. Stabilize, for example by stopping a runaway job or routing reads to a healthy replica.
5. Recover using the least destructive tested action.
6. Validate service and data correctness.
7. Communicate status and customer impact.
8. Conduct a blameless post-incident review with corrective actions.

Do not promote a replica merely because it is reachable: check freshness, transaction integrity, application compatibility, and split-brain risk. In security incidents, preserve logs, rotate credentials, restrict access, and follow legal and regulatory notification procedures. Personal data in backups requires retention, deletion, encryption, and access policies consistent with applicable law.

10. Illustrative case: an online retailer

Background. An online retailer runs PostgreSQL for orders and inventory. It requires RPO ≤ 5 minutes and RTO ≤ 60 minutes during business hours.

Problem. A reporting query consumes I/O, the primary becomes slow, and a failed disk later causes an outage. The team has a replica but has never tested restoration.

Analysis. The replica is asynchronous and currently 18 minutes behind, so promoting it violates the RPO. WAL archiving is enabled but the archive destination has not been monitored. There is no tested recovery-time measurement.

Approach. The team first cancels the runaway report, adds query governance, and uses connection pooling. It repairs WAL archiving, alerts when archive age exceeds five minutes, and takes encrypted base backups. A standby is rebuilt and monitored through pg_stat_replication. Quarterly exercises restore a base backup plus WAL to an isolated environment; the measured restore takes 38 minutes. A documented promotion procedure checks replay LSN, fencing, DNS, credentials, and application smoke tests.

Outcome. The retailer meets the stated objectives in drills, although synchronous replication is rejected for the reporting region because its network latency harms checkout performance. Management accepts the residual asynchronous-loss risk and funds a second, closer standby for critical periods.

Learning points. Objectives must be measurable; a replica is not automatically an adequate DR system; archive health and restore drills are as important as backup creation; and availability decisions require explicit business trade-offs.

Challenges and limitations

No design eliminates all failure modes. Backups can be encrypted by ransomware, replicas can reproduce logical errors, cloud control planes can fail, and monitoring can be misconfigured. Synchronous replication improves durability only for failures covered by the quorum and network assumptions. PITR can restore database state but may not restore external payments, files, queues, or application side effects. Recovery plans must therefore include dependencies, time synchronization, DNS, credentials, infrastructure, and human decision-making.

Chapter Summary

Reliable MySQL and PostgreSQL operations require more than installation. Administrators configure secure, durable services; monitor workload, storage, logs, and replication; maintain independent and tested backups; and rehearse recovery against explicit RPO and RTO targets. MySQL conventional PITR replays archived binary logs after restoration of a full backup, whereas PostgreSQL PITR replays archived WAL. MySQL replication uses binary logging and source/replica configuration; PostgreSQL commonly uses WAL streaming and standby recovery. Upgrades require compatibility analysis, rehearsal, rollback planning, and validation. Cloud services automate infrastructure tasks but do not transfer responsibility for data governance, cost, performance, or recovery. Effective operations combine technical controls, automation, observability, incident response, and continual testing.

Key Terms

Availability: Probability or proportion of time a service is usable.

Binary log: MySQL log of changes used for replication and PITR.

Disaster recovery: Processes for restoring service after a major failure.

Durability: Persistence of committed data despite defined failures.

Failover: Transition from a failed or unsuitable primary to another server.

GTID: Globally unique transaction identifier used in MySQL replication.

High availability: Design that reduces service interruption through redundancy and procedures.

Logical backup: Export of database objects and data in logical form.

PITR: Recovery to a selected time using a base backup and change logs.

PostgreSQL WAL: Write-Ahead Log containing records required for crash recovery and replication.

RPO: Maximum acceptable amount of data loss measured in time.

RTO: Maximum acceptable time to restore service.

Replication lag: Difference between a source’s applied changes and a replica’s state.

SLO: Quantified reliability target for a service.

Streaming replication: Continuous transfer and replay of database change records.

Observability: Ability to infer internal system state from metrics, logs, and traces.

Replication slot: PostgreSQL mechanism retaining WAL for a consumer.

Runbook: Step-by-step operational procedure for a known task or incident.

Review Questions

Short Answer Questions

1. Distinguish RPO from RTO.
2. Why is replication not a substitute for backup?
3. What is the purpose of pg_hba.conf?
4. Name two indicators of replica health.
5. Why should backup restoration be tested?

Long Answer Questions

1. Compare MySQL binary-log PITR with PostgreSQL WAL-based PITR, including the major operational steps.
2. Explain how you would design a secure and durable database installation.
3. Compare asynchronous and synchronous replication for an order-processing system.
4. Develop a major-version upgrade plan for either DBMS.
5. Explain how observability supports capacity planning and incident response.

Analytical / Application Questions

1. A service has a 99.95% monthly availability SLO. Calculate the maximum downtime in a 30-day month and discuss whether planned maintenance should be included.
2. A PostgreSQL standby is 12 minutes behind while the business RPO is 5 minutes. Should it be promoted? Analyze alternatives.
3. A database disk is 82% full and growing 6% of capacity per day. Design an immediate response and a sustainable capacity plan.

Multiple Choice Questions with Answers

Question 1

Which log does conventional MySQL point-in-time recovery replay after restoring a full backup?

A. Archived InnoDB redo logs

B. Archived binary logs

C. PostgreSQL WAL files

D. The slow-query log

Correct Answer: B. Archived binary logs

Question 2

Which PostgreSQL feature retains WAL for a specific replication consumer?

A. A replication slot

B. A foreign key

C. A materialized view

D. The query planner

Correct Answer: A. A replication slot

Question 3

What is the main limitation of mysqldump --single-transaction?

A. It cannot export table data

B. It requires PostgreSQL

C. It does not guarantee consistency for nontransactional tables

D. It disables all indexes permanently

Correct Answer: C. It does not guarantee consistency for nontransactional tables

Question 4

A 30-day month has 43,200 minutes. A 99.9% availability SLO permits approximately how much downtime?

A. 4.32 minutes

B. 43.2 minutes

C. 432 minutes

D. 0.432 minutes

Correct Answer: B. 43.2 minutes

Question 5

Which PostgreSQL view is especially useful for inspecting WAL sender and standby state on a primary?

A. pg_stat_replication

B. pg_roles

C. pg_tablespace

D. pg_indexes

Correct Answer: A. pg_stat_replication

Question 6

What is the principal purpose of pg_basebackup?

A. To create a physical base backup

B. To analyze a slow SQL statement

C. To change a user password

D. To remove archived WAL

Correct Answer: A. To create a physical base backup

Question 7

If transaction arrival rate λ is 80 per second and mean service time S is 0.005 seconds, what is U = λS?

A. 0.004

B. 0.04

C. 0.4

D. 4.0

Correct Answer: C. 0.4

Question 8

Which statement best describes RPO?

A. Maximum acceptable restoration duration

B. Maximum acceptable data loss measured in time

C. Percentage of CPU reserved for backups

D. Number of replicas required

Correct Answer: B. Maximum acceptable data loss measured in time

Question 9

Why can an abandoned PostgreSQL replication slot create a storage risk?

A. It forces every query to become read-only

B. It prevents needed WAL from being removed

C. It deletes the primary data directory

D. It disables authentication

Correct Answer: B. It prevents needed WAL from being removed

Question 10

Which action is most appropriate before promoting a database replica?

A. Promote it solely because its process is running

B. Delete all monitoring data

C. Check freshness, integrity, fencing, and application compatibility

D. Disable backups permanently

Correct Answer: C. Check freshness, integrity, fencing, and application compatibility