inchirags@gmail.com Chirag's PostgreSQL DBA Tutorial https://www.chirags.in
*****************************************************************************************
*How to Install, Configure, and Use pgBadger for PostgreSQL 16 on Ubuntu 24.04 LTS*
*****************************************************************************************
YouTube Video Link:
PostgreSQL server IP:
Server IP: 192.168.224.129
pgBadger is a powerful log analyzer for PostgreSQL that generates detailed reports from your PostgreSQL log files. Here's how to install, configure, and use pgBadger on Ubuntu 24.04 LTS with PostgreSQL 16.
Step 1: Install PostgreSQL 16
If you haven't already installed PostgreSQL 16, start by doing so.
1. Update your package list and install PostgreSQL 16:
sudo apt update
sudo apt upgrade -y
sudo apt install postgresql postgresql-contrib
2. Start and enable PostgreSQL:
sudo systemctl start postgresql
sudo systemctl enable postgresql
Step 2: Install pgBadger
pgBadger is not part of the PostgreSQL package, so you’ll need to install it separately.
1. Install dependencies: pgBadger is written in Perl, so you need to install Perl and a few modules:
sudo apt update
sudo apt install build-essential libjson-perl libtext-csv-xs-perl libdbi-perl libdbd-pg-perl libdatetime-perl
2. install pgBadger:
sudo apt install pgbadger
3. Verify installation: Run the following command to confirm that pgBadger is installed:
pgbadger --version
You should see output like:
pgBadger version 13.0
Step 3: Configure PostgreSQL Logging
For pgBadger to generate reports, PostgreSQL needs to be configured to log enough information.
1. Edit the PostgreSQL configuration file: Open the postgresql.conf file for your PostgreSQL instance:
sudo nano /etc/postgresql/16/main/postgresql.conf
2. Update the following parameters:
# Enable Logging
logging_collector = on
# Log Directory
log_directory = '/var/log/postgresql'
# Log Filename
log_filename = 'postgresql-%a.log'
# Log Rotation Settings
log_rotation_age = 1d
log_rotation_size = 0
# Log Min Duration for Queries
log_min_duration_statement = 200
# Detailed Log Line Prefix
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
# Log Statements
log_statement = 'all'
# Log Checkpoints and Connections
log_checkpoints = on
log_connections = on
log_disconnections = on
These settings ensure that PostgreSQL generates detailed log files suitable for analysis by pgBadger.
3. Restart PostgreSQL to apply the changes:
sudo systemctl restart postgresql
Step 4: Test Logging of CREATE DATABASE and CREATE TABLE
1. Access PostgreSQL Shell
sudo -u postgres psql
2. Run Test Statements
CREATE DATABASE testdb;
\c testdb
CREATE TABLE test_table (id SERIAL PRIMARY KEY, name TEXT);
INSERT INTO test_table (name) VALUES ('Chirag');
SELECT * FROM test_table;
DROP TABLE test_table;
DROP DATABASE testdb;
\q
3. Check PostgreSQL Logs
Check Recent Log Entries
sudo tail -f /var/log/postgresql/postgresql-*.log
Step 5: Generate a pgBadger Report
required:
Install apache2:
sudo apt install apache2 -y
Once Apache is installed, you need to start the service and ensure that it starts on boot.
sudo systemctl start apache2
sudo systemctl enable apache2
1. Run pgBadger on your PostgreSQL logs: Now that PostgreSQL is logging data, you can use pgBadger to analyze the logs and generate a report:
sudo pgbadger /var/log/postgresql/postgresql-*.log -o /var/www/html/pgbadger_report.html
This command generates a report from the PostgreSQL log files and saves it to /var/www/html/pgbadger_report.html. Adjust the log path as needed.
2. View the report: Open the report in your web browser:
http://192.168.224.129/pgbadger_report.html
You’ll see a detailed visual report of your PostgreSQL performance and activity.
Step 6: Automate pgBadger Report Generation (Optional)
You can automate the generation of pgBadger reports by adding a cron job.
1. Open the cron configuration:
crontab -e
Add a cron job to generate a daily report:
0 6 * * * /usr/bin/pgbadger /var/log/postgresql/postgresql-*.log -o /var/www/html/pgbadger_report.html
This cron job runs at 6 AM every day and generates a new report.
Restart Cron
sudo systemctl restart cron
Conclusion
You’ve now installed, configured, and run pgBadger for PostgreSQL 16 on Ubuntu 24.04 LTS. You can use pgBadger reports to analyze and optimize your PostgreSQL performance.
For any doubts and query, please write on YouTube video comments section.
Note : Flow the Process shown in video.
Please, Subscribe and like for more videos:
https://youtube.com/@chiragstutorial
Don't forget to, Follow, Like, Share &, Comment
Thanks & Regards,
Chitt Ranjan Mahto "Chirag"
_________________________________________________________________________________________
Note: All scripts used in this demo will be available in our website.
Link will be available in description.
