Tornado + momoko does not handle the connection

Question

I use tornado (4.2.1) + momoko (2.2.0) + psycopg2 (2.6.1) for a small web application and it works fine until the PostgreSQL server is shut down. Then, after each db.execute () command, an error message appears:

Traceback (most recent call last): File "C:\Python27\lib\site-packages\tornado-4.2.1-py2.7-win32.egg\tornado\web.py", line 1415, in _execute result = yield result File "C:\Python27\lib\site-packages\tornado-4.2.1-py2.7-win32.egg\tornado\gen.py", line 870, in run value = future.result() File "C:\Python27\lib\site-packages\tornado-4.2.1-py2.7-win32.egg\tornado\concurrent.py", line 215, in result raise_exc_info(self._exc_info) File "C:\Python27\lib\site-packages\tornado-4.2.1-py2.7-win32.egg\tornado\gen.py", line 876, in run yielded = self.gen.throw(*exc_info) File "server.py", line 63, in get cursor = yield self.db.execute(query) File "C:\Python27\lib\site-packages\tornado-4.2.1-py2.7-win32.egg\tornado\gen.py", line 870, in run value = future.result() File "C:\Python27\lib\site-packages\tornado-4.2.1-py2.7-win32.egg\tornado\concurrent.py", line 215, in result raise_exc_info(self._exc_info) File "D:\work\program-stat\momoko\connection.py", line 453, in when_available future_or_result = method(conn, *args, **kwargs) File "D:\work\program-stat\momoko\connection.py", line 743, in execute cursor.execute(operation, parameters) File "C:\Python27\lib\site-packages\psycopg2\extras.py", line 288, in execute return super(NamedTupleCursor, self).execute(query, vars) OperationalError: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. 

Here is the code:

 import os import tornado.httpserver import tornado.ioloop import tornado.options import tornado.web import momoko from tornadotools.route import Route from psycopg2.extras import NamedTupleCursor import environments as env tornado.options.define("port", default=9999, help="run on the given port", type=int) tornado.options.define("pgsql_host", default=env.DB_HOST, help="database host") tornado.options.define("pgsql_database", default=env.DB_DATABASE, help="database name") tornado.options.define("pgsql_user", default=env.DB_LOGIN, help="database user") tornado.options.define("pgsql_password", default=env.DB_PASSWORD, help="database password") class Application(tornado.web.Application): def __init__(self): handlers = Route.routes() settings = dict( template_path=os.path.join(os.path.dirname(__file__), "templates"), static_path=os.path.join(os.path.dirname(__file__), "static"), debug=True, ) tornado.web.Application.__init__(self, handlers, **settings) # Have one global connection to DB across all handlers self.db = momoko.Pool( dsn='dbname=%s user=%s password=%s ' 'host=%s port=5432' % ( tornado.options.options.pgsql_database, tornado.options.options.pgsql_user, tornado.options.options.pgsql_password, tornado.options.options.pgsql_host), cursor_factory=NamedTupleCursor, ) self.db.connect() class BaseHandler(tornado.web.RequestHandler): @property def db(self): return self.application.db @Route(r"/") class HomeHandler(BaseHandler): def get(self): self.write("<ul>") self.write("<li><a href='/test'>Test page</a>") self.write("</ul>") self.finish() @Route(r"/test") class MogrifyHandler(BaseHandler): @tornado.web.asynchronous @tornado.gen.coroutine def get(self): cursor = yield self.db.execute("SELECT %s as t;", (1,)) self.render("test.html", result=cursor.fetchall()) def main(): tornado.options.parse_command_line() http_server = tornado.httpserver.HTTPServer(Application()) http_server.listen(tornado.options.options.port) tornado.ioloop.IOLoop.instance().start() if __name__ == "__main__": main() 

How can I handle this exception and reconnect to db without restarting the application?

Answer 1

you can add parameter:

 raise_connect_errors=False, 

When you create the connection pool:

 self.db = momoko.Pool( dsn='dbname=%s user=%s password=%s ' 'host=%s port=5432' % ( tornado.options.options.pgsql_database, tornado.options.options.pgsql_user, tornado.options.options.pgsql_password, tornado.options.options.pgsql_host), cursor_factory=NamedTupleCursor, raise_connect_errors=False, ) 

But this is not always useful.

Answer 2

Tornado / Momoko / Auto Reload

Problem:

If the connection to the Postgres database was broken - momoko cannot recreate the connection pool. To solve this problem, you need to restart the application. To automate this process, I wrote a small script that uses the built-in tornado update mechanism.

Decision:

Check every 1 second that the database connection is alive. Add this check before starting ioloop.

import reload check_connect = tornado.ioloop.PeriodicCallback (lambda: reload.reload_if_db_pool_is_dead (application.db), 1000) check_connect.start ()

https://gist.github.com/MaxRV/d7180e63d6b5396d904d446653aa7e88