Troubleshooting PostgreSQL Broken pipe connection lost

This guide was created trying to fix the errors shown below. It'll help you to get PostgreSQL DEBUG logs enabled.

2023-10-04 20:01:02.184 UTC [1] LOG:  starting PostgreSQL 13.8 (Debian 13.8-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
2023-10-04 20:01:02.185 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2023-10-04 20:01:02.185 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2023-10-04 20:01:02.188 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-10-04 20:01:02.329 UTC [13] LOG:  database system was shut down at 2023-10-04 17:36:32 UTC
2023-10-04 20:01:02.367 UTC [1] LOG:  database system is ready to accept connections
2023-10-04 20:01:12.768 UTC [32] LOG:  could not receive data from client: Connection reset by peer
2023-10-04 20:08:28.767 UTC [468] LOG:  could not receive data from client: Connection reset by peer
2023-10-04 20:08:28.772 UTC [469] LOG:  could not send data to client: Broken pipe
2023-10-04 20:08:28.772 UTC [469] FATAL:  connection to client lost

In order of getting this error fixed, you need to understand what's causing it. Most of the time it's a bad SQL query, but also can be a misconfigured Connection Pool.

Here are the steps you need to take to enable debug logs on database deployed into OpenShift:

1. Deploy a modified database config file
2. Follow log messages to understand what's happening.

However deploying a modified configuration file is not that easy, not intuitive. So here are the required steps for doing that.

Getting PostgreSQL config file

The file you need to change is called postgresql.conf. This is file is usually deployed into the data folder.

The easiest way of getting it, is by running a PostgreSQL Docker image and extract from it.

• If you have a Dockerfile, or want to run your database build, run (note that you should be in the same directory the Dockerfile is):

docker build -t custom-database .

• Run the image:

docker run -p 5432:5432 --rm \
  --name custom-database \
  -e POSTGRES_USER=postgres \
  -e POSTGRES_PASSWORD=default \
  -e POSTGRES_DB=postgres \
  -v /data:/var/lib/postgresql \
  custom-database

But if you want to run directly the postgres image:

docker run -p 5432:5432 --rm \
  --name postgres \
  -e POSTGRES_USER=postgres \
  -e POSTGRES_PASSWORD=default \
  -e POSTGRES_DB=postgres \
  -v /data:/var/lib/postgresql/spar-data \
  postgis/postgis:15-master

With the running container, you now need to connect and start a bash on it. For that you can use the container id or the container name. Let's say you named your container postgres, you can run:

docker exec -it postgres bash

If you do not know your container name or id, check with docker ps.

Once connected, you should see something like this: root@8915b30662be:/#. Now we want to extract a file from it, the file is located in /var/lib/postgresql/data and it's called postgresql.conf. Make sure the file is there before trying to copy it. Try this:

ls -l /var/lib/postgresql/data/postgresql.conf

If it's there, you can now leave the container and run the docker copy command:

docker cp postgres:/var/lib/postgresql/data/postgresql.conf .

Remember that postgres is the container name, and the dot in the end is the destiny folder.

Changing the configuration file

Once you have the file, open up with your favourite text editor and search for log_min_messages.

This line should be commented. Remove the first character (#) to enable it and update the value to debug3.

PS: I tried with debug3, but feel free to pick another value like debug4 or debug5 if you want. I haven't tried debug2 or debug1.

Do the same change, now for the log_min_error_statement.

Save and close the file.

Updating build & deployment file

Getting the file updated is kind of tricky, because we need to change the file before the init script starts. First update your database repository adding the updated postgresql.conf configuration file, into the same directory you have the Dockerfile.

Now add these two lines to your database Dockerfile:

COPY postgresql.conf /tmp/postgresql.conf
COPY updateConfig.sh /docker-entrypoint-initdb.d/_updateConfig.sh

And create this _updateConfig.sh file on the same directory your Dockefile:

#!/usr/bin/env bash

cat /tmp/postgresql.conf > /var/lib/postgresql/data/postgresql.conf

The name should start with underscore so it can run before all other scripts. And remember of making it executable with chmod +x _updateConfig.sh.

Note: this tip came from this link: https://stackoverflow.com/a/46052380

Deploying and following

Now you can deploy your application the same way you usually do. Now you should be able to see more logs and messages on your database container.

References

• StackOverflow question: https://stackoverflow.com/questions/30848670/how-to-customize-the-configuration-file-of-the-official-postgresql-docker-image/46052380
• Docker docs: https://docs.docker.com/engine/reference/commandline/cp/