Postgres Troubleshooting Scenarios

Scenario: "Bucharest": Connecting to Postgres

Level: Easy

Type: Fix

Access: Email

Description: A web application relies on the PostgreSQL database on this server. After a client-authentication change, that connection fails.

The application connects over TCP to 127.0.0.1, database app1, user app1user, password app1user. The role and password are already set. Do not replace the database or the account; restore this connection. It must still work after a reboot.

Test: This command succeeds (exits 0, no error):
PGPASSWORD=app1user psql -h 127.0.0.1 -d app1 -U app1user -c '\q'

The "Check My Solution" button runs the script /home/admin/agent/check.sh, which you can see and execute.

Time to Solve: 15 minutes.

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.

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.

Scenario: "Luxor": PostgreSQL analytics queries crawl

Level: Medium

Type: Fix

Access: Email

Description: Our analytics API serves customer sales counts from a PostgreSQL database. Requests to the customer lookup endpoint take several seconds and often time out, even though the database server looks healthy — no obvious CPU, memory, or disk exhaustion.

The API runs as systemd service sad-analytics-api on port 9090. Application code lives under /opt/sad/. Database credentials for debugging: connect as saduser to database analytics_db (password: sadpassword). The main table is sales_data.

Find why customer lookups are slow and restore acceptable API response times.

Test: This API request must complete quickly (total response time under 500 ms):
curl -s -w "\nTotal time: %{time_total}s\n" http://127.0.0.1:9090/api/customers/1234/sales/count

The "Check My Solution" button runs the script /home/admin/agent/check.sh, which you can see and execute.

Time to Solve: 20 minutes.

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: 40 minutes.

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: 60 minutes.