psycopg2.OperationalError: server closed the connection unexpectedly

[gatiszeiris]

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_dbapi_exception
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_dbapi_exception
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