У меня есть экземпляр Redshift, доступный хосту-бастиону.
Я пытаюсь запустить круговое задание CI, которое изменяет схему базы данных, которая будет переименована, чтобы включить версию тега выпуска git. Мы хотим, чтобы это срабатывало, когда мы создаем выпуск git, нацеленный на master.
Рабочий процесс CI делает следующее:
1) Авторизоваться с помощью AWS
2) Запустите скрипт Python, который создает группу безопасности, прикрепляет ее к хосту-бастиону и настраивает правила входа, чтобы IP-адрес блока CI был занесен в белый список в качестве cidr.
3) Откройте ssh-туннель для пересылки команд sqlalchemy через бастион на хост Redshift.
4) Запустите другой скрипт Python, который изменяет схему БД.
Первые два шага не вызывают никаких проблем. Проблема, кажется, возникает с туннелем ssh.
туннель ssh работает в фоновом режиме и вызывает скрипт python: ssh -o StrictHostKeyChecking=no -N -L 127.0.0.1:5439:$PRODUCTION_REDSHIFT_DNS:5439 -i /home/circleci/.ssh/id_rsa_d88b40ac655a406a2c26ef75202fc144 circle_ci@$PRODUCTION_BASTION_DNS & python3 archive_schema.py
$PROD_REDSHIFT_CONNECTION_STRING: redshift+psycopg2://$USER:$PASS@localhost:5439/$DB_NAME
У меня есть простой скрипт на Python, который печатает имя первой таблицы в базе данных и завершает работу. Я успешно запустил его в бастионе с той же строкой подключения, но когда я запускаю его после настройки ssh-туннеля в фоновом режиме, все взрывается.
import os
from sqlalchemy import create_engine, inspect
engine = create_engine(os.getenv("PROD_REDSHIFT_CONNECTION_STRING"))
inspector = inspect(engine)
for table_name in inspector.get_table_names():
print(table_name)
break
Любая помощь будет принята с благодарностью. Вот ошибка, которую я получаю при запуске скрипта из Circle CI:
Traceback (most recent call last):
File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2262, in _wrap_pool_connect
return fn()
File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 303, in unique_connection
return _ConnectionFairy._checkout(self)
File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 760, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 492, in checkout
rec = pool._do_get()
File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/pool/impl.py", line 139, in _do_get
self._dec_overflow()
File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 154, in reraise
raise value
File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/pool/impl.py", line 136, in _do_get
return self._create_connection()
File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 308, in _create_connection
return _ConnectionRecord(self)
File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 437, in __init__
self.__connect(first_connect_check=True)
File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 639, in __connect
connection = pool._invoke_creator(self)
File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/engine/strategies.py", line 114, in connect
return dialect.connect(*cargs, **cparams)
File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 451, in connect
return self.dbapi.connect(*cargs, **cparams)
File "/home/circleci/repo/venv/lib/python3.6/site-packages/psycopg2/__init__.py", line 126, in connect
conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: could not connect to server: Connection refused
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 5439?
could not connect to server: Connection refused
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 5439?
could not connect to server: Cannot assign requested address
Is the server running on host "localhost" (::1) and accepting
TCP/IP connections on port 5439?
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "z.py", line 7, in <module>
inspector = inspect(engine)
File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/inspection.py", line 63, in inspect
ret = reg(subject)
File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/engine/reflection.py", line 141, in _insp
return Inspector.from_engine(bind)
File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/engine/reflection.py", line 136, in from_engine
return bind.dialect.inspector(bind)
File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/dialects/postgresql/base.py", line 2209, in __init__
reflection.Inspector.__init__(self, conn)
File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/engine/reflection.py", line 111, in __init__
bind.connect().close()
File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2193, in connect
return self._connection_cls(self, **kwargs)
File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 103, in __init__
else engine.raw_connection()
File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2293, in raw_connection
self.pool.unique_connection, _connection
File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2266, in _wrap_pool_connect
e, dialect, self
File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1536, in _handle_dbapi_exception_noconnection
util.raise_from_cause(sqlalchemy_exception, exc_info)
File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 399, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 153, in reraise
raise value.with_traceback(tb)
File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2262, in _wrap_pool_connect
return fn()
File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 303, in unique_connection
return _ConnectionFairy._checkout(self)
File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 760, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 492, in checkout
rec = pool._do_get()
File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/pool/impl.py", line 139, in _do_get
self._dec_overflow()
File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 154, in reraise
raise value
File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/pool/impl.py", line 136, in _do_get
return self._create_connection()
File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 308, in _create_connection
return _ConnectionRecord(self)
File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 437, in __init__
self.__connect(first_connect_check=True)
File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 639, in __connect
connection = pool._invoke_creator(self)
File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/engine/strategies.py", line 114, in connect
return dialect.connect(*cargs, **cparams)
File "/home/circleci/repo/venv/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 451, in connect
return self.dbapi.connect(*cargs, **cparams)
File "/home/circleci/repo/venv/lib/python3.6/site-packages/psycopg2/__init__.py", line 126, in connect
conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not connect to server: Connection refused
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 5439?
could not connect to server: Connection refused
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 5439?
could not connect to server: Cannot assign requested address
Is the server running on host "localhost" (::1) and accepting
TCP/IP connections on port 5439?
(Background on this error at: http://sqlalche.me/e/e3q8)
Exited with code 1
