Monitoring PostgreSQL Replication Slots and Sending Email Alerts using Python

Photo by Brett Jordan on Unsplash

Introduction:

In the world of managing databases, PostgreSQL is a popular and powerful tool for organizing and accessing data. It has a feature called replication, which allows data to be copied and synchronized across different parts of the system, making sure everything stays up-to-date.

However, to keep things running smoothly, we need to keep an eye on the replication process. One important aspect is monitoring the size of something called “replication slots.” These slots are like storage areas that hold data waiting to be copied and synchronized.

Problem Statement:

The primary concern while utilizing replication in PostgreSQL is monitoring the replication slots. A replication slot represents an allocation of disk space used to hold changes yet to be replicated. When a replication slot’s size exceeds a certain threshold, it could lead to replication delays or even system failures. The goal is to fetch the size of each replication slot, compare it against a predefined threshold (e.g., 65 MB), and send an email alert if the threshold is breached.

Solution Overview:

To address the problem statement, we will develop a Python script that connects to the PostgreSQL server, fetches the size of each replication slot, and creates an email with the relevant information. The script will be responsible for sending this email alert to specified recipients.

Step 1: Importing Necessary Modules

We begin by importing the required Python modules, such as psycopg2 for PostgreSQL connection, pandas for data manipulation, smtplib and related email modules for sending email alerts.

import psycopg2
import pandas as pd
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from mconfig import DB_HOST, DB_PORT, DB_NAME, DB_USER, DB_PASSWORD, SENDER_EMAIL, SENDER_PASSWORD, RECEIVER_EMAILS

Step 2: Writing the PostgreSQL Query

The script will execute a query on the PostgreSQL server to fetch the relevant information about the replication slots. The query calculates the size of each replication slot in megabytes and returns the results.

def execute_postgres_query_and_send_email():
    # PostgreSQL query
    query = "SELECT slot_name, round((redo_lsn - restart_lsn) / 1024 / 1024, 2) AS MB_behind FROM pg_control_checkpoint(), pg_replication_slots;"

    # Establish a connection to PostgreSQL
    try:
        connection = psycopg2.connect(host=DB_HOST, port=DB_PORT, database=DB_NAME, user=DB_USER, password=DB_PASSWORD)
        cursor = connection.cursor()

        # Execute the query and fetch the result
        cursor.execute(query)
        result = cursor.fetchall()

        # Convert the result to a pandas DataFrame
        columns = ['slot_name', 'MB_behind']
        df = pd.DataFrame(result, columns=columns)

        # Close the cursor and connection
        cursor.close()
        connection.close()
    except Exception as e:
        print(f"Error occurred while connecting to the database or executing the query: {e}")
        return

Step 3: Processing the Data and Generating HTML Table

The script will convert the fetched data into a pandas DataFrame for easy manipulation. We will then generate an HTML table from the DataFrame, highlighting the rows where the replication slot size exceeds the predefined threshold.

def create_html_table(df):
    # Create an HTML table from the DataFrame
    html_table = df.to_html(index=False)

    # Highlight rows where MB_behind is greater than 65 MB
    highlighted_rows = []
    for _, row in df.iterrows():
        if row['MB_behind'] > 65:
            highlighted_rows.append('<tr style="background-color: #ffcccc;">')
        else:
            highlighted_rows.append('<tr>')

    html_table = html_table.replace('<table>', '<table style="border-collapse: collapse;">')
    html_table = html_table.replace('<th>', '<th style="border: 1px solid black; padding: 8px; background-color: #f2f2f2;">')
    html_table = html_table.replace('<td>', '<td style="border: 1px solid black; padding: 8px;">')
    html_table = html_table.replace('<tr>', '\n'.join(highlighted_rows))

    return html_table

Step 4: Preparing and Sending the Email

The script will construct an email message containing the HTML table and other relevant information. It will establish a connection to an SMTP server (such as Outlook/Office 365), authenticate the sender’s email account, and send the email to the specified recipients.

    # Prepare the email content with the HTML table
    email_subject = 'Replication Slot Size Result'
    html_table = create_html_table(df)
    email_body = f"<html><body><h2>{email_subject}</h2>{html_table}</body></html>"

    # Create the email message
    msg = MIMEMultipart()
    msg['From'] = SENDER_EMAIL
    msg['To'] = ', '.join(RECEIVER_EMAILS)  # Join the list of email addresses into a comma-separated string
    msg['Subject'] = email_subject

    # Attach the HTML table to the email
    msg.attach(MIMEText(email_body, 'html'))

    # Establish the SMTP connection and send the email
    try:
        smtp_server = 'smtp.office365.com'  # Change this to your Outlook SMTP server
        smtp_port = 587  # Change this to the appropriate port

        server = smtplib.SMTP(smtp_server, smtp_port)
        server.starttls()  # Upgrade the connection to a secure SSL/TLS connection
        server.login(SENDER_EMAIL, SENDER_PASSWORD)

        # Send the email
        server.sendmail(SENDER_EMAIL, RECEIVER_EMAILS, msg.as_string())

        # Close the connection
        server.quit()
        print("Email sent successfully!")
    except Exception as e:
        print(f"Error occurred while sending email: {e}")

if __name__ == "__main__":
    execute_postgres_query_and_send_email()

Step 5: Scheduling the Script

To automate the process, we can schedule the script to run at regular intervals (e.g., daily) using tools like Cron (Unix) or Task Scheduler (Windows).

How the mconfig.py file should look like:

# mconfig.py

# PostgreSQL database credentials
DB_HOST = 'your_postgres_host'
DB_PORT = 'your_postgres_port'
DB_NAME = 'your_database_name'
DB_USER = 'your_database_user'
DB_PASSWORD = 'your_database_password'

# Outlook email credentials
SENDER_EMAIL = 'your_email@example.com'
SENDER_PASSWORD = 'your_email_password'
RECEIVER_EMAILS = ['recipient1@example.com', 'recipient2@example.com', 'recipient3@example.com']

GitHub Link: Access the Complete Code:

GitHub - commityourdream/postgres-replication-slot-monitoring

Conclusion:

In conclusion, monitoring PostgreSQL replication slots is essential to ensure a robust and reliable database replication system. By leveraging Python and automation, we can simplify the process of monitoring and sending timely email alerts, enabling database administrators to proactively address replication issues and maintain a smoothly functioning PostgreSQL environment. Implementing the approach outlined in this blog will enhance the stability and performance of your PostgreSQL replication setup.

Thank you for reading, and happy coding!

If you have any questions, feedback, or suggestions, please don’t hesitate to reach out.