How To Log All Queries In The Official Postgres Docker Image?

Logging queries that we have executed is a useful way to monitor database activity, troubleshoot performance issues and ensure compliance with security policies. If you are running PostgreSQL using the official Docker image, you can configure query logging by modifying PostgreSQL's configuration files. In this article, we will learn step by step to enable logging of queries that we executes.

Step-By-Step Guide To Enable Query Logging in PostgreSQL Docker Container

Step 1: Start the PostgreSQL Container

First, we will start the PostgreSQL Container. By running the below command it will pull the latest PostgreSQL image from the docker registry and start & creates the container.

docker run --name my_postgres -e POSTGRES_PASSWORD=password -d postgres

To Verify, the container is started or not, you can use the below command. This command will list all the container that is currently running.

docker ps

Step 2: Copy the Configuration File to Local

We will copy the Postgres configuration (postgresql.conf) file to our local because, the container might not have vim or nano editor installed. The below command will copy the postgresql.conf file from the container to our local.

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

Step 3: Modifying the Postgres configuration file.

Open the configuration file using Vim editor or anything that you like.

vi postgresql.conf

Made the changes as below, Some lines will be commented so you have to find that specific variable and have to uncomment it and assign value as per the given below.

To search the specific text in Vim editor, you can press "/" and write what you want to search then press enter. to write in the Vim editor click the insert button on keyboard and to save click Esc button then write ":wq" or ":wq!" to save the changes.

# Enable logging
logging_collector = on

# Log all queries
log_statement = 'all'

# uncomment this
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_destination = 'stderr'

Step 4: Send the modified config file to container

Now, you need to send the modified Postgres configuration file to the docker image from where it gets copied. the below command will copy the file to the docker container.

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

Step 5: Restart the docker container.

After modifying the PostgreSQL configuration file, you need to restart the docker container. The below command will restart the docker container.

docker restart my_postgres

Step 6: Execute PostgreSQL query

Now, connect with the PostgreSQL and execute the query, so we can check the log of it. The below command will open the PostgreSQL Shell from where you can execute the query.

docker exec -it my_postgres psql -U postgres

After Opening the PostgreSQL Shell, Execute this Queries:

//Create Table
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    position VARCHAR(100)
);

//Insert the dummy records
INSERT INTO employees (name, position) VALUES ('Alice Johnson', 'Software Engineer');
INSERT INTO employees (name, position) VALUES ('Bob Smith', 'Data Scientist');
INSERT INTO employees (name, position) VALUES ('Carol White', 'Project Manager');

//Retrieve the data
SELECT * FROM employees; 

Exit from postgres shell by typing \q.

Step 7: Open the Postgres container bash to view log.

After executing query, open the Postgres container bash and locate for the log file at location var/lib/postgresql/data/log folder, where you will find the log files.

The below command will open the Postgres bash:

docker exec -it my_postgres bash

After coming inside bash, change the directory where the logs are stored

cd  /var/lib/postgresql/data/log

For listing all the log files, run the following command

ls

Now here, you can copy the log to your local or using more command view the log file.

more logFileName.log

• After Executing command to view the logs, Logs will be displayed.

Conclusion

In this article, we explored how to enable query logging in the official PostgreSQL Docker image. By following the outlined steps, you can effectively monitor database activity, troubleshoot issues, and ensure compliance with security policies. Properly configured logging enhances your ability to maintain performance and analyze queries, making it an essential practice for any PostgreSQL deployment. With the ability to view logs easily, you can gain valuable insights into your database operations.