diff -uNr a/logotron/MANIFEST.TXT b/logotron/MANIFEST.TXT --- a/logotron/MANIFEST.TXT 0b96586a2510cca44fd5be14d95ba57c0bd29a0e33b8563b512502aa8ace23cafb9b2bcef463f42a8820e20c52da3672367b3abad3e724fe8e4af44a2d3e31e2 +++ b/logotron/MANIFEST.TXT 5840d3419c171f8ce22cf0061e01d0557cc08ef11af683061a9874d17b5f1cccbd13f7a14af8090c2fc0f978b5eb0f7a20de6e04f61fdc50c9b1bf4e70e45b9b @@ -26,3 +26,4 @@ 697872 classic_css_tweaks billymg "Use list-based chan nav, fix highlight current chan bug from 684804, wrap chans when they overflow." 700821 fix_multiln_hlite billymg "Fix bug from 684804 where the highlighting of multiple selected log lines is not rendered properly." 719633 fix_bot_recursion billymg "Prevent bots from speaking to each other. Also add a flag for handling pest log lines." +719639 add_pg_reconnect billymg "Ping postgres db before inserting lines and auto reconnect if the connection has been lost." diff -uNr a/logotron/bot.py b/logotron/bot.py --- a/logotron/bot.py fa93d94c684695b4ac20f57b9580d2e1faf9e82e83145eaca7a9ff2d1130d5e296c0e1007d771ba7cd17b4dde33d4da2c6975af8d9e3a29412dcd3f5d013f25e +++ b/logotron/bot.py b342c4ebec9489f7af7f92d131d3eb7ad35c45c8d95d2f54e8bf1757cfb91faabe5d91de857f5e649ff3b54d10625d5855487b2cd442f23dfe6e3e8afcec0327 @@ -14,7 +14,7 @@ ############################################################################## # Version. If changing this program, always set this to same # as in MANIFEST -Ver = 719633 +Ver = 719639 ## As of version 689331, this program will NOT work with Freenode !!! @@ -72,10 +72,14 @@ Discon_TO = int(cfg.get("irc", "disc_t")) Prefix = cfg.get("control", "prefix") Bots = [x.strip() for x in cfg.get("control", "bots").split(',')] + # DBism: - DB_Name = cfg.get("db", "db_name") - DB_User = cfg.get("db", "db_user") - DB_DEBUG = cfg.get("db", "db_debug") + DB_Name = cfg.get("db", "db_name") + DB_User = cfg.get("db", "db_user") + DB_DEBUG = cfg.get("db", "db_debug") + DB_Reconn_Tries = int(cfg.get("db", "db_reconnect_max_tries")) + DB_Reconn_Delay = int(cfg.get("db", "db_reconnect_delay")) + # Logism: Base_URL = cfg.get("logotron", "base_url") App_Root = cfg.get("logotron", "app_root") @@ -89,21 +93,54 @@ ############################################################################## -# Connect to the given DB -try: - db = psycopg2.connect("dbname=%s user=%s" % (DB_Name, DB_User)) -except Exception: - print "Could not connect to DB!" - logging.error("Could not connect to DB!") - exit(1) -else: - logging.info("Connected to DB!") +db = None + +def conn_db(): + global db + + tries = DB_Reconn_Tries + + while True: + # Connect to the given DB + try: + db = psycopg2.connect("dbname=%s user=%s" % (DB_Name, DB_User)) + except Exception: + print "Could not connect to DB!" + logging.error("Could not connect to DB!") + if tries > 0 or DB_Reconn_Tries == -1: + tries = tries - 1 + time.sleep(DB_Reconn_Delay) + continue + else: + exit(1) + else: + logging.info("Connected to DB!") + break + +conn_db() ############################################################################## def close_db(): db.close() +def ensure_db_is_alive(): + # Ping the db to ensure it's alive and connected + logging.debug("Checking DB connection status...") + try: + cur = db.cursor() + cur.execute('SELECT 1') + except (psycopg2.OperationalError, psycopg2.InterfaceError) as e: + pass + + # If connection is alive db.closed will equal 0 + if db.closed == 0: + return True + + # Otherwise, attempt to reconnect + logging.debug("No DB Connection!") + conn_db() + def exec_db(query, args=()): cur = db.cursor(cursor_factory=psycopg2.extras.RealDictCursor) if (DB_DEBUG): logging.debug("query: '{0}'".format(query)) @@ -488,6 +525,8 @@ def save_line(time, chan, speaker, action, payload): ## Put in DB: try: + ensure_db_is_alive() + # Get index of THIS new line to be saved last_idx = query_db( '''select idx from loglines where chan=%s diff -uNr a/logotron/nsabot.conf b/logotron/nsabot.conf --- a/logotron/nsabot.conf ad5f570234ff7f7afa4e2d43669c36e502c820a6645c5f241a255e608ce897133ca31caf2d26269204d63ed7b49c793589987581aba503dad6c3a64a502849cd +++ b/logotron/nsabot.conf 9b5adeb3cfd47aac6ae34e9854aeb2541bb7fd07b7219761878ea8da4f5d88afc3acf798d7ff9d2685bd9df3d97e444da8871f97055b102c7d57d747fb9c6490 @@ -94,3 +94,9 @@ # Verbose barf of DB transactions db_debug = 0 + +# Auto-reconnect config +# Max reconnect attempts, set to -1 for unlimited +db_reconnect_max_tries = 60 +# Seconds to wait between reconnect attempts +db_reconnect_delay = 5 diff -uNr a/logotron/release_notes.txt b/logotron/release_notes.txt --- a/logotron/release_notes.txt 7680df3082e7221fbc218f95a8be4cb8403d105f39f4ccf1dca840d31f79fe798b8b7b00db9c40eab4e5fe5192b5f8ec8dbd27cba8f0ce65fa18aabfc9224e71 +++ b/logotron/release_notes.txt 235668848e5a9cf7f4ede3e62892bc72f2d521526cbc4ba3fa2c6db2ad37e7b523940250af2f91eabc8a12941e8f1e03792d48249c7c83381376336e1bdf74db @@ -1,6 +1,16 @@ ################### 2022 Jan 20 Update: ################### +--------------------------------------------------------------------------------------------------------------------------------- +719639 add_pg_reconnect billymg "Ping postgres db before inserting lines and auto reconnect if the connection has been lost." +--------------------------------------------------------------------------------------------------------------------------------- +(1) Verify the postgres connection before inserting lines, if the connection has been lost try reconnecting until successful or out of tries. +(2) Add flags 'db_reconnect_max_tries' and 'db_reconnect_delay' to control the reconnect behavior. + + +################### +2022 Jan 20 Update: +################### ---------------------------------------------------------------------------------------------------------------------------- 719633 fix_bot_recursion billymg "Prevent bots from speaking to each other. Also add a flag for handling pest log lines." ----------------------------------------------------------------------------------------------------------------------------