Sqlalchemy / psycopg2 error when trying to connect to CloudSQL postgres

12/12/2019

I am spinning up redash using helm on GKE with master/node version 1.12.10-gke.17 and istio 1.1.15 I use as database a GCP CloudSQL with postgres 9.6

When trying to execute the create_db script from the redash image (7.0.0.b18042) I encounter the following error

Traceback (most recent call last):
   File "/app/manage.py", line 9, in <module>
     manager()
   File "/usr/local/lib/python2.7/dist-packages/click/core.py", line 716, in __call__
     return self.main(*args, **kwargs)
   File "/usr/local/lib/python2.7/dist-packages/flask/cli.py", line 345, in main
     return AppGroup.main(self, *args, **kwargs)
   File "/usr/local/lib/python2.7/dist-packages/click/core.py", line 696, in main
     rv = self.invoke(ctx)
   File "/usr/local/lib/python2.7/dist-packages/click/core.py", line 1060, in invoke
     return _process_result(sub_ctx.command.invoke(sub_ctx))
   File "/usr/local/lib/python2.7/dist-packages/click/core.py", line 1060, in invoke
     return _process_result(sub_ctx.command.invoke(sub_ctx))
   File "/usr/local/lib/python2.7/dist-packages/click/core.py", line 889, in invoke
     return ctx.invoke(self.callback, **ctx.params)
   File "/usr/local/lib/python2.7/dist-packages/click/core.py", line 534, in invoke
     return callback(*args, **kwargs)
   File "/usr/local/lib/python2.7/dist-packages/click/decorators.py", line 17, in new_func
     return f(get_current_context(), *args, **kwargs)
   File "/usr/local/lib/python2.7/dist-packages/flask/cli.py", line 229, in decorator
     return __ctx.invoke(f, *args, **kwargs)
   File "/usr/local/lib/python2.7/dist-packages/click/core.py", line 534, in invoke
     return callback(*args, **kwargs)
   File "/app/redash/cli/database.py", line 31, in create_tables
     db.create_all()
   File "/usr/local/lib/python2.7/dist-packages/flask_sqlalchemy/__init__.py", line 963, in create_all
     self._execute_for_all_tables(app, bind, 'create_all')
   File "/usr/local/lib/python2.7/dist-packages/flask_sqlalchemy/__init__.py", line 955, in _execute_for_all_tables
     op(bind=self.get_engine(app, bind), **extra)
   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/schema.py", line 4005, in create_all
     tables=tables)
   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1939, in _run_visitor
     with self._optional_conn_ctx_manager(connection) as conn:
   File "/usr/lib/python2.7/contextlib.py", line 17, in __enter__
     return self.gen.next()
   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1932, in _optional_conn_ctx_manager
     with self.contextual_connect() as conn:
   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 2123, in contextual_connect
     self._wrap_pool_connect(self.pool.connect, None),
   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 2162, in _wrap_pool_connect
     e, dialect, self)
   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1476, in _handle_dbapi_exception_noconnection
     exc_info
   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
     reraise(type(exception), exception, tb=exc_tb, cause=cause)
   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 2158, in _wrap_pool_connect
     return fn()
   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 400, in connect
     return _ConnectionFairy._checkout(self)
   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 788, in _checkout
     fairy = _ConnectionRecord.checkout(pool)
   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 529, in checkout
     rec = pool._do_get()
   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 1193, in _do_get
     self._dec_overflow()
   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
     compat.reraise(exc_type, exc_value, exc_tb)
   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 1190, in _do_get
     return self._create_connection()
   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 347, in _create_connection
     return _ConnectionRecord(self)
   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 474, in __init__
     self.__connect(first_connect_check=True)
   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 671, in __connect
     connection = pool._invoke_creator(self)
   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/strategies.py", line 106, in connect
     return dialect.connect(*cargs, **cparams)
   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 412, in connect
     return self.dbapi.connect(*cargs, **cparams)
   File "/usr/local/lib/python2.7/dist-packages/psycopg2/__init__.py", line 130, in connect
     conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
 sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
  (Background on this error at: http://sqlalche.me/e/e3q8)

I cannot figure out what is causing this, i.e. whether it is the CloudSQL's issue, or some istio misconfiguration or sth else...

The same error (with smaller stack trace) is reproduced when I exec into a container and try to manually connect to CloudSQL

>>> import psycopg2
>>> conn = psycopg2.connect(host="192.44.33.1",database="postgres", user="postgres", password="mypass")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python2.7/dist-packages/psycopg2/__init__.py", line 130, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
-- pkaramol
istio
kubernetes
python
redash
sqlalchemy

1 Answer

12/12/2019

Have you enabled a ServiceEntry within the Istio mesh to allow egress traffic to CloudSQL?

apiVersion: networking.istio.io/v1alpha3
kind: ServiceEntry
metadata:
  name: www.googleapis.com
spec:
  hosts:
  - www.googleapis.com
  - oauth2.googleapis.com
  ports:
  - number: 443
    name: https
    protocol: HTTPS
  resolution: DNS
  location: MESH_EXTERNAL

Further reading here: https://github.com/istio/istio/issues/6593

-- JuanJSebGarcia
Source: StackOverflow