notified on shutdown

[sgoudelis]

This is not a bug report. I have a question.

How can my app get notified when postgres goes offline or crashes ? Is there any such functionality? From what I can understand so far, I can have some callbacks when connections are opened and acquired (init and setup) but I cannot know when postgres goes offline.

Thank you

[ljluestc]

import psycopg2
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart

# PostgreSQL Database Connection Parameters
db_params = {
    'dbname': 'your_database',
    'user': 'your_user',
    'password': 'your_password',
    'host': 'your_postgresql_host',
    'port': 'your_postgresql_port',
}

# Email Configuration
smtp_server = 'your_smtp_server'
smtp_port = 587
smtp_username = 'your_smtp_username'
smtp_password = 'your_smtp_password'
sender_email = 'your_sender_email'
receiver_email = 'your_receiver_email'

def send_email(subject, message):
    try:
        # Create a connection to the SMTP server
        server = smtplib.SMTP(smtp_server, smtp_port)
        server.starttls()
        server.login(smtp_username, smtp_password)

        # Create the email message
        msg = MIMEMultipart()
        msg['From'] = sender_email
        msg['To'] = receiver_email
        msg['Subject'] = subject
        msg.attach(MIMEText(message, 'plain'))

        # Send the email
        server.sendmail(sender_email, receiver_email, msg.as_string())

        # Close the SMTP server connection
        server.quit()
    except Exception as e:
        print(f"Error sending email: {e}")

def check_database_status():
    try:
        # Attempt to connect to the database
        connection = psycopg2.connect(**db_params)
        cursor = connection.cursor()

        # Execute a simple query (e.g., SELECT 1) to check database availability
        cursor.execute("SELECT 1")
        result = cursor.fetchone()

        # Database is online
        print("Database is online")
    except psycopg2.OperationalError as e:
        # Database is offline or inaccessible
        print("Database is offline or inaccessible")
        # Send an email notification
        send_email("PostgreSQL Database Alert", "The PostgreSQL database is offline or inaccessible.")
    finally:
        if connection:
            cursor.close()
            connection.close()

if __name__ == '__main__':
    check_database_status()