How to Install, Configure, and Use pgBadger for PostgreSQL 16 on Ubuntu 24.04 LTS

[email protected] ? 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:

https://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.