We face with the following error using datapusher service. Its come unavailable after running some time of period. Only think how to solve problem is restart datapusher service. PostgreSQL server installed on other server and datapusher connect via local network to DB. Also I dont see option to configure DB connection with pool_pre_ping: True.
We use PostgreSQL for datapusher jobs with following configuration:
[pid: 1700741|app: 0|req: 1/9] 10.55.175.2 () {32 vars in 501 bytes} [Mon Apr 8 07:42:18 2024] GET /job/67655590-4e28-428b-a2b7-e2f9bd953165 => generated 3627 bytes in 40 msecs (HTTP/1.1 200) 3 headers in 87 bytes (1 switches on core 0)
Traceback (most recent call last):
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
self.dialect.do_execute(
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
cursor.execute(statement, parameters)
psycopg2.OperationalError: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/flask/app.py", line 2213, in call
return self.wsgi_app(environ, start_response)
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/werkzeug/middleware/proxy_fix.py", line 182, in call
return self.app(environ, start_response)
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/flask/app.py", line 2193, in wsgi_app
response = self.handle_exception(e)
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/flask/app.py", line 2190, in wsgi_app
response = self.full_dispatch_request()
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/flask/app.py", line 1486, in full_dispatch_request
rv = self.handle_user_exception(e)
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/flask/app.py", line 1484, in full_dispatch_request
rv = self.dispatch_request()
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/flask/app.py", line 1469, in dispatch_request
return self.ensure_sync(self.view_functions[rule.endpoint])(*view_args)
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/ckanserviceprovider/web.py", line 687, in job
return run_asynchronous_job(asynchronous_job, job_id, job_key, input)
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/ckanserviceprovider/web.py", line 731, in run_asynchronous_job
aps_job = scheduler.add_job(job, trigger, [job_id, input], None)
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/apscheduler/schedulers/base.py", line 447, in add_job
self._real_add_job(job, jobstore, replace_existing)
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/apscheduler/schedulers/base.py", line 871, in _real_add_job
store.add_job(job)
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/apscheduler/jobstores/sqlalchemy.py", line 98, in add_job
self.engine.execute(insert)
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 2235, in execute
return connection.execute(statement, multiparams, **params)
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
return meth(self, multiparams, params)
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1124, in _execute_clauseelement
ret = self._execute_context(
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context
self._handle_dbapi_exception(
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapiexception
util.raise(
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
raise exception
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
self.dialect.do_execute(
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
[SQL: INSERT INTO apscheduler_jobs (id, next_run_time, job_state) VALUES (%(id)s, %(next_run_time)s, %(job_state)s)]
[parameters: {'id': '883d96ab758145bb823d40bd32192713', 'next_run_time': 1712551340.916334, 'job_state': <psycopg2.extensions.Binary object at 0x7f4e5a85cd20>}]
(Background on this error at: http://sqlalche.me/e/13/e3q8)
[pid: 1700743|app: 0|req: 6/10] 10.55.175.2 () {32 vars in 391 bytes} [Mon Apr 8 07:42:20 2024] POST /job => generated 0 bytes in 46 msecs (HTTP/1.1 500) 0 headers in 0 bytes (0 switches on core 2)
Traceback (most recent call last):
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
self.dialect.do_execute(
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
cursor.execute(statement, parameters)
psycopg2.OperationalError: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/flask/app.py", line 2213, in call
return self.wsgi_app(environ, start_response)
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/werkzeug/middleware/proxy_fix.py", line 182, in call
return self.app(environ, start_response)
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/flask/app.py", line 2193, in wsgi_app
response = self.handle_exception(e)
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/flask/app.py", line 2190, in wsgi_app
response = self.full_dispatch_request()
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/flask/app.py", line 1486, in full_dispatch_request
rv = self.handle_user_exception(e)
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/flask/app.py", line 1484, in full_dispatch_request
rv = self.dispatch_request()
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/flask/app.py", line 1469, in dispatch_request
return self.ensure_sync(self.view_functions[rule.endpoint])(*view_args)
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/ckanserviceprovider/web.py", line 687, in job
return run_asynchronous_job(asynchronous_job, job_id, job_key, input)
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/ckanserviceprovider/web.py", line 731, in run_asynchronous_job
aps_job = scheduler.add_job(job, trigger, [job_id, input], None)
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/apscheduler/schedulers/base.py", line 447, in add_job
self._real_add_job(job, jobstore, replace_existing)
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/apscheduler/schedulers/base.py", line 871, in _real_add_job
store.add_job(job)
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/apscheduler/jobstores/sqlalchemy.py", line 98, in add_job
self.engine.execute(insert)
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 2235, in execute
return connection.execute(statement, multiparams, **params)
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
return meth(self, multiparams, params)
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1124, in _execute_clauseelement
ret = self._execute_context(
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context
self._handle_dbapi_exception(
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapiexception
util.raise(
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
raise exception
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
self.dialect.do_execute(
File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
[SQL: INSERT INTO apscheduler_jobs (id, next_run_time, job_state) VALUES (%(id)s, %(next_run_time)s, %(job_state)s)]
[parameters: {'id': 'd3006b134f5e46e99627548e03141bf6', 'next_run_time': 1712555265.585745, 'job_state': <psycopg2.extensions.Binary object at 0x7f4e5b8d16c0>}]
(Background on this error at: http://sqlalche.me/e/13/e3q8)
CKAN 2.10.1 Python v. 3.8 PostgreSQL 15
We face with the following error using datapusher service. Its come unavailable after running some time of period. Only think how to solve problem is restart datapusher service. PostgreSQL server installed on other server and datapusher connect via local network to DB. Also I dont see option to configure DB connection with pool_pre_ping: True.
We use PostgreSQL for datapusher jobs with following configuration:
import os import uuid
DEBUG = True TESTING = True SECRET_KEY = str(uuid.uuid4()) USERNAME = str(uuid.uuid4()) PASSWORD = str(uuid.uuid4())
NAME = 'datapusher'
Webserver host and port
HOST = os.environ.get('DATAPUSHER_HOST', '0.0.0.0') PORT = os.environ.get('DATAPUSHER_PORT', 8800)
Database
SQLALCHEMY_DATABASE_URI = 'postgresql://datapusher_jobs:pass@loca-network-ip/datapusher_jobs'
Download and streaming settings
MAX_CONTENT_LENGTH = int(os.environ.get('DATAPUSHER_MAX_CONTENT_LENGTH', '221375671')) CHUNK_SIZE = int(os.environ.get('DATAPUSHER_CHUNK_SIZE', '16384')) CHUNK_INSERT_ROWS = int(os.environ.get('DATAPUSHER_CHUNK_INSERT_ROWS', '450')) DOWNLOAD_TIMEOUT = int(os.environ.get('DATAPUSHER_DOWNLOAD_TIMEOUT', '120'))
Verify SSL
SSL_VERIFY = os.environ.get('DATAPUSHER_SSL_VERIFY', False)
logging
LOG_FILE = os.environ.get('DATAPUSHER_LOG_FILE', '/tmp/ckan_service.log') STDERR = bool(int(os.environ.get('DATAPUSHER_STDERR', '1')))
There is datapusher log file.
[pid: 1700741|app: 0|req: 1/9] 10.55.175.2 () {32 vars in 501 bytes} [Mon Apr 8 07:42:18 2024] GET /job/67655590-4e28-428b-a2b7-e2f9bd953165 => generated 3627 bytes in 40 msecs (HTTP/1.1 200) 3 headers in 87 bytes (1 switches on core 0) Traceback (most recent call last): File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context self.dialect.do_execute( File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute cursor.execute(statement, parameters) psycopg2.OperationalError: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.
The above exception was the direct cause of the following exception:
Traceback (most recent call last): File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/flask/app.py", line 2213, in call return self.wsgi_app(environ, start_response) File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/werkzeug/middleware/proxy_fix.py", line 182, in call return self.app(environ, start_response) File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/flask/app.py", line 2193, in wsgi_app response = self.handle_exception(e) File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/flask/app.py", line 2190, in wsgi_app response = self.full_dispatch_request() File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/flask/app.py", line 1486, in full_dispatch_request rv = self.handle_user_exception(e) File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/flask/app.py", line 1484, in full_dispatch_request rv = self.dispatch_request() File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/flask/app.py", line 1469, in dispatch_request return self.ensure_sync(self.view_functions[rule.endpoint])(*view_args) File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/ckanserviceprovider/web.py", line 687, in job return run_asynchronous_job(asynchronous_job, job_id, job_key, input) File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/ckanserviceprovider/web.py", line 731, in run_asynchronous_job aps_job = scheduler.add_job(job, trigger, [job_id, input], None) File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/apscheduler/schedulers/base.py", line 447, in add_job self._real_add_job(job, jobstore, replace_existing) File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/apscheduler/schedulers/base.py", line 871, in _real_add_job store.add_job(job) File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/apscheduler/jobstores/sqlalchemy.py", line 98, in add_job self.engine.execute(insert) File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 2235, in execute return connection.execute(statement, multiparams, **params) File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1011, in execute return meth(self, multiparams, params) File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params) File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1124, in _execute_clauseelement ret = self._execute_context( File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context self._handle_dbapi_exception( File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapiexception util.raise( File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_ raise exception File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context self.dialect.do_execute( File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.
[SQL: INSERT INTO apscheduler_jobs (id, next_run_time, job_state) VALUES (%(id)s, %(next_run_time)s, %(job_state)s)] [parameters: {'id': '883d96ab758145bb823d40bd32192713', 'next_run_time': 1712551340.916334, 'job_state': <psycopg2.extensions.Binary object at 0x7f4e5a85cd20>}] (Background on this error at: http://sqlalche.me/e/13/e3q8) [pid: 1700743|app: 0|req: 6/10] 10.55.175.2 () {32 vars in 391 bytes} [Mon Apr 8 07:42:20 2024] POST /job => generated 0 bytes in 46 msecs (HTTP/1.1 500) 0 headers in 0 bytes (0 switches on core 2) Traceback (most recent call last): File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context self.dialect.do_execute( File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute cursor.execute(statement, parameters) psycopg2.OperationalError: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.
The above exception was the direct cause of the following exception:
Traceback (most recent call last): File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/flask/app.py", line 2213, in call return self.wsgi_app(environ, start_response) File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/werkzeug/middleware/proxy_fix.py", line 182, in call return self.app(environ, start_response) File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/flask/app.py", line 2193, in wsgi_app response = self.handle_exception(e) File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/flask/app.py", line 2190, in wsgi_app response = self.full_dispatch_request() File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/flask/app.py", line 1486, in full_dispatch_request rv = self.handle_user_exception(e) File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/flask/app.py", line 1484, in full_dispatch_request rv = self.dispatch_request() File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/flask/app.py", line 1469, in dispatch_request return self.ensure_sync(self.view_functions[rule.endpoint])(*view_args) File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/ckanserviceprovider/web.py", line 687, in job return run_asynchronous_job(asynchronous_job, job_id, job_key, input) File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/ckanserviceprovider/web.py", line 731, in run_asynchronous_job aps_job = scheduler.add_job(job, trigger, [job_id, input], None) File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/apscheduler/schedulers/base.py", line 447, in add_job self._real_add_job(job, jobstore, replace_existing) File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/apscheduler/schedulers/base.py", line 871, in _real_add_job store.add_job(job) File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/apscheduler/jobstores/sqlalchemy.py", line 98, in add_job self.engine.execute(insert) File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 2235, in execute return connection.execute(statement, multiparams, **params) File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1011, in execute return meth(self, multiparams, params) File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params) File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1124, in _execute_clauseelement ret = self._execute_context( File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context self._handle_dbapi_exception( File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapiexception util.raise( File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_ raise exception File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context self.dialect.do_execute( File "/usr/lib/ckan/datapusher/lib64/python3.8/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.
[SQL: INSERT INTO apscheduler_jobs (id, next_run_time, job_state) VALUES (%(id)s, %(next_run_time)s, %(job_state)s)] [parameters: {'id': 'd3006b134f5e46e99627548e03141bf6', 'next_run_time': 1712555265.585745, 'job_state': <psycopg2.extensions.Binary object at 0x7f4e5b8d16c0>}] (Background on this error at: http://sqlalche.me/e/13/e3q8)
uwsgi setpu
[uwsgi]
http-socket = 0.0.0.0:8800 uid = ckan-user gid = ckan-user wsgi-file = /usr/lib/ckan/datapusher/src/datapusher/deployment/datapusher.wsgi virtualenv = /usr/lib/ckan/datapusher master = true pidfile = /tmp/%n.pid harakiri = 14400 max-requests = 5000 vacuum = true callable = application buffer-size = 32768 strict = true
see High Availability Setup
workers = 6 threads = 3 lazy-apps = true