Postgres Troubleshooting Scenarios

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.

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: "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.

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.