SadServers: Linux, DevOps & SRE Labs | Job Interview Prep

Troubleshooting Scenarios: Databases

Linux & Bash

- Linux commands, Bash scripting
- Systemd
- Networking, DNS
- Storage
- SSH, Firewall
- Libraries
- Cron and more...

Web Servers

- Nginx
- Apache
- HAProxy
- Caddy
- Gunicorn
- uWSGI
- HTTPS/TLS

Databases

- PostgreSQL
- MySQL
- SQLite
- Redis
- ClickHouse
- MongoDB
- etcd

Data Processing

- CSV
- JSON
- SQL queries

Docker

- Building images
- Multi-stage builds
- Volumes
- Networks
- Docker Compose
- Podman

Kubernetes

- kubectl
- Helm
- K8S Roles & Permissions
- Services
- Namespaces
- Deployments, StatefulSets
- ConfigMaps, Secrets

Tooling / Applications

- Git
- Rabbitmq
- Envoy
- Vault
- Harbor
- Prometheus
- Jenkins

Hacking

- Capture the Flag (CTF) Challenges
- Code Vulnerabilities
- Privilege Escalation

Languages

- Python
- Golang
- PHP
- Java
- Node.js
- C

clickhouse etcd mongodb mysql postgres redis sqlite

realistic / interviews new pro

Easy

#	Name	Time	Type
1	"Bucharest": Connecting to Postgres	10 m	Fix	"Bucharest": Connecting to Postgres Scenario: "Bucharest": Connecting to Postgres Level: Easy Type: Fix Access: Email Description: A web application relies on the PostgreSQL 13 database present on this server. However, the connection to the database is not working. Your task is to identify and resolve the issue causing this connection failure. The application connects to a database named app1 with the user app1user and the password app1user. Credit PykPyky Test: Running `PGPASSWORD=app1user psql -h 127.0.0.1 -d app1 -U app1user -c '\q'` succeeds (does not return an error). Time to Solve: 10 minutes.

Medium

#	Name	Time	Type
1	"Manhattan": can't write data into database.	20 m	Fix No Registration	"Manhattan": can't write data into database. Scenario: "Manhattan": can't write data into database. Level: Medium Type: Fix Access: Public Description: Your objective is to be able to insert a row in an existing Postgres database. The issue is not specific to Postgres and you don't need to know details about it (although it may help). Helpful Postgres information: it's a service that listens to a port (:5432) and writes to disk in a data directory, the location of which is defined in the data_directory parameter of the configuration file /etc/postgresql/14/main/postgresql.conf. In our case Postgres is managed by systemd as a unit with name postgresql. Test: (from default admin user) `sudo -u postgres psql -c "insert into persons(name) values ('jane smith');" -d dt` Should return:`INSERT 0 1` Time to Solve: 20 minutes.
2	"Lisbon": etcd SSL cert troubles	20 m	Fix	"Lisbon": etcd SSL cert troubles Scenario: "Lisbon": etcd SSL cert troubles Level: Medium Type: Fix Access: Email Description: There's an etcd server running on https://localhost:2379 , get the value for the key "foo", ie `etcdctl get foo` or `curl https://localhost:2379/v2/keys/foo` Test: `etcdctl get foo` returns `bar`. Time to Solve: 20 minutes.
3	"Rosario": Restore a MySQL database	15 m	Fix	"Rosario": Restore a MySQL database Scenario: "Rosario": Restore a MySQL database Level: Medium Type: Fix Access: Email Description: A developer created a database named 'main' but now some data is missing in the database. You need to restore the database using the the dump "/home/admin/backup.sql". The issue is that the developer forgot the root password for the MariaDB server. If you encounter an issue while restoring the database, fix it. Credit: Sebastian Segovia Test: The database, once restored, has a table named "solution". The "Check My Solution" button runs the script /home/admin/agent/check.sh, which you can see and execute. Time to Solve: 15 minutes.
4	"Abaokoro": Restore MySQL Databases Spooked by a Ghost	20 m	Fix Pro	"Abaokoro": Restore MySQL Databases Spooked by a Ghost Scenario: "Abaokoro": Restore MySQL Databases Spooked by a Ghost Level: Medium Type: Fix Access: Paid Description: There are three databases that need to be restored. You need to create three databases called "first", "second" and "third" and restore the databases using the file "/home/admin/dbs_to_restore.zip". If you encounter an issue while restoring the database, fix it. Credit: Sebastian Segovia Test: All databases, once restored, have a table named "foo". The "Check My Solution" button runs the script /home/admin/agent/check.sh, which you can see and execute. Time to Solve: 20 minutes.
5	"Helsingør": The first walls of postgres physical replication	20 m	Fix	"Helsingør": The first walls of postgres physical replication Scenario: "Helsingør": The first walls of postgres physical replication Level: Medium Type: Fix Access: Email Description: You're setting up a PostgreSQL database with replication, you decided to use Docker along with Docker Compose to make it easier to manage and test, after a few hours of work you finished the job and the master database is up and running, but you're having trouble with the replica. You need to figure out what's wrong with the replica and fix it. Since you are using Docker Compose, you can check the status of the running containers using `docker compose ps` or `docker ps` will do the job too). You may also want to check the logs of the containers. All definition for the containers are inside the docker-compose.yml file. You can stand up the environment by running `docker compose up -d` and set it down by running `docker compose down`. If you make any change to the docker-compose.yml file, you can restart the containers by running `docker compose up -d --force-recreate`. Test: Postgres replica container works. The "Check My Solution" button runs the script /home/admin/agent/check.sh, which you can see and execute. Time to Solve: 20 minutes.
6	"Atrani": Modify a SQlite3 Database	15 m	Fix Pro	"Atrani": Modify a SQlite3 Database Scenario: "Atrani": Modify a SQlite3 Database Level: Medium Type: Fix Access: Paid Description: A developer created a script /home/admin/readdb.py that tests access to a database. Without modifying the readdb.py file, change the database so that running the script returns the string "John Karmack". Test: Running /home/admin/readdb.py returns "John Karmack". The "Check My Solution" button runs the script /home/admin/agent/check.sh, which you can see and execute. Time to Solve: 15 minutes.
7	"Bizerte": The Slow Application	15 m	Fix	"Bizerte": The Slow Application Scenario: "Bizerte": The Slow Application Level: Medium Type: Fix Access: Email Description: A Python web application running on port 5000 from the /opt directory is experiencing severe performance issues; every request takes more than 5 seconds to complete. The application is supposed to use the redis-server cache service for speed. Your mission is to diagnose the performance bottleneck and restore the application to its normal, fast response time. Do not change the Python application file slow_app.py. Test: `curl localhost:5000` returns Data from FAST cache! The "Check My Solution" button runs the script /home/admin/agent/check.sh, which you can see and execute. Time to Solve: 15 minutes.
8	"Solanea": ClickHouse mad house	20 m	Do Pro	"Solanea": ClickHouse mad house Scenario: "Solanea": ClickHouse mad house Level: Medium Type: Do Access: Paid Description: You have a ClickHouse installation CHI running on a Kubernetes cluster and a set of requests (located at ~/data/requests.csv) that you must populate into the http_requests table in the monitoring database (table may not exist in all pod instances). Do this insert in all pod instances of the database. The user and password to connect to the database are default. The keeper pods provide clickhouse replication services. Test: You are able to query the database and see the data: `clickhouse-client -h --password default -q 'SELECT COUNT() FROM monitoring.http_requests'` The "Check My Solution" button runs the script /home/admin/agent/check.sh, which you can see and execute. Time to Solve:* 20 minutes.
9	"Tunis": Redis Replication Problem	20 m	Fix Pro	"Tunis": Redis Replication Problem Scenario: "Tunis": Redis Replication Problem Level: Medium Type: Fix Access: Paid Description: A Redis master-replica setup is running on this server, with the master on port 6379 and the replica on port 6380. Both instances show as "connected" when you check their status, but data synchronization has silently broken. Recent writes to the master don't appear on the replica, even though there are no obvious errors in the logs and both Redis instances appear healthy. Fix the replication issues so that data written to the master (port 6379) immediately appears on the replica (port 6380) without data loss. Master: localhost:6379 Replica: localhost:6380 Password: masterpass123 A helper test script is available at /home/admin/test_replication.sh Test: The solution will be validated by writing a test key to the master and verifying it appears on the replica within 2 seconds. The "Check My Solution" button runs the script /home/admin/agent/check.sh, which you can see and execute. Time to Solve: 20 minutes.
10	"Suzhou": MongoDB replicas!	20 m	Fix New	"Suzhou": MongoDB replicas! Scenario: "Suzhou": MongoDB replicas! Level: Medium Type: Fix Access: Email Description: A new MongoDB replica set has been setup in the development environment trough `/home/admin/app/rs0.js`, however, a variety or errors are showing up when trying to bring it up. You should bring up all the replica servers, get them communicating to each other and make sure the replica set is working as it should. The status of the first replica can be checked via `systemctl status mongo1` same for the replicas `mongo2` and `mongo3`. The logs are also in a separate file for each replica under the directory `/var/log/mongodb`. To initilize the replica set again: `mongosh --file app/rs0.js` Note: The default configuration file `/etc/mongo.conf` is not the problem. Test: `mongosh --eval "rs.status()" \| grep health` returns the status of all the replicas health: 1, health: 1, health: 1, The "Check My Solution" button runs the script /home/admin/agent/check.sh, which you can see and execute. Time to Solve: 20 minutes.

Hard

#	Name	Time	Type
1	"Hong-Kong": can't write data into database.	20 m	Fix Pro	"Hong-Kong": can't write data into database. Scenario: "Hong-Kong": can't write data into database. Level: Hard Type: Fix Access: Paid Description: (Similar to "Manhattan" scenario but harder). Your objective is to be able to insert a row in an existing Postgres database. The issue is not specific to Postgres and you don't need to know details about it (although it may help). Postgres information: it's a service that listens to a port (:5432) and writes to disk in a data directory, the location of which is defined in the data_directory parameter of the configuration file /etc/postgresql/14/main/postgresql.conf. In our case Postgres is managed by systemd as a unit with name postgresql. Test: `sudo -u postgres psql -c "insert into persons(name) values ('jane smith');" -d dt` Should return:`INSERT 0 1` Time to Solve: 20 minutes.
2	"Florence": Database Migration Hell	30 m	Fix Pro	"Florence": Database Migration Hell Scenario: "Florence": Database Migration Hell Level: Hard Type: Fix Access: Paid Description: You are working as a DevOps Engineer in a company and another team member left the company and left the docker-compose.yml of a database-backed web application unfinished. Generally, the problem revolves around the database migration and docker compose. Additionally on front of the application there is an Nginx server and you need to fix the proper access to it as well. The source of code is in /home/admin/app Credit Kamil Błaż Test: `curl --cacert /etc/nginx/certs/sadserver.crt https://sadserver.local` returns a message containing "ready to serve requests" The "Check My Solution" button runs the script /home/admin/agent/check.sh, which you can see and execute Time to Solve: 30 minutes.