Home > Back-end >  Postgres connection error when deploying on Heroku
Postgres connection error when deploying on Heroku

Time：06-26

I'm trying to deploy my Django Dash app on Heroku; the build succeeds, but I get the following error:

(psycopg2.OperationalError) connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
    Is the server running locally and accepting connections on that socket?

Everything works locally.

My database settings:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'datanz',
        'USER': 'xxxx',
        'PASSWORD': 'xxxx',
        'HOST': 'localhost',
        'PORT': '5432',
    }
}

import dj_database_url
db_from_env = dj_database_url.config(conn_max_age=500, ssl_require=True)
DATABASES['default'].update(db_from_env)

And establishing the connection and getting the database configuration:

heroku_app_name = "datanz"
raw_db_url = subprocess.run(
    ["heroku", "config:get", "DATABASE_URL", "--app", heroku_app_name],
    capture_output=True, shell=True
).stdout 

db_url = raw_db_url.decode("ascii").strip()

# Convert "postgres://<db_address>"  --> "postgresql psycopg2://<db_address>" needed for SQLAlchemy
final_db_url = "postgresql psycopg2://"   db_url.lstrip("postgres://")   "?sslmode=require"

# Create SQLAlchemy engine
from sqlalchemy import create_engine
engine = create_engine(final_db_url)

df1=pd.read_sql_query(sql, con=engine)   #example of my connections

I'm using the free Heroku version. Could this be the issue?

Full traceback:

Environment:
Django Version: 3.2.3
Python Version: 3.9.12

Traceback (most recent call last):
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3280, in _wrap_pool_connect
    return fn()
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 310, in connect
    return _ConnectionFairy._checkout(self)
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 868, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 476, in checkout
    rec = pool._do_get()
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 146, in _do_get
    self._dec_overflow()
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 143, in _do_get
    return self._create_connection()
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 256, in _create_connection
    return _ConnectionRecord(self)
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 371, in __init__
    self.__connect()
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 666, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 661, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/engine/create.py", line 590, in connect
    return dialect.connect(*cargs, **cparams)
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 597, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/app/.heroku/python/lib/python3.9/site-packages/psycopg2/__init__.py", line 127, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)

The above exception (connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
    Is the server running locally and accepting connections on that socket?
) was the direct cause of the following exception:
  File "/app/.heroku/python/lib/python3.9/site-packages/django/core/handlers/exception.py", line 47, in inner
    response = get_response(request)
  File "/app/.heroku/python/lib/python3.9/site-packages/django/core/handlers/base.py", line 167, in _get_response
    callback, callback_args, callback_kwargs = self.resolve_request(request)
  File "/app/.heroku/python/lib/python3.9/site-packages/django/core/handlers/base.py", line 290, in resolve_request
    resolver_match = resolver.resolve(request.path_info)
  File "/app/.heroku/python/lib/python3.9/site-packages/django/urls/resolvers.py", line 556, in resolve
    for pattern in self.url_patterns:
  File "/app/.heroku/python/lib/python3.9/site-packages/django/utils/functional.py", line 48, in __get__
    res = instance.__dict__[self.name] = self.func(instance)
  File "/app/.heroku/python/lib/python3.9/site-packages/django/urls/resolvers.py", line 598, in url_patterns
    patterns = getattr(self.urlconf_module, "urlpatterns", self.urlconf_module)
  File "/app/.heroku/python/lib/python3.9/site-packages/django/utils/functional.py", line 48, in __get__
    res = instance.__dict__[self.name] = self.func(instance)
  File "/app/.heroku/python/lib/python3.9/site-packages/django/urls/resolvers.py", line 591, in urlconf_module
    return import_module(self.urlconf_name)
  File "/app/.heroku/python/lib/python3.9/importlib/__init__.py", line 127, in import_module
    return _bootstrap._gcd_import(name[level:], package, level)
  File "<frozen importlib._bootstrap>", line 1030, in _gcd_import
    <source code not available>
  File "<frozen importlib._bootstrap>", line 1007, in _find_and_load
    <source code not available>
  File "<frozen importlib._bootstrap>", line 986, in _find_and_load_unlocked
    <source code not available>
  File "<frozen importlib._bootstrap>", line 680, in _load_unlocked
    <source code not available>
  File "<frozen importlib._bootstrap_external>", line 850, in exec_module
    <source code not available>
  File "<frozen importlib._bootstrap>", line 228, in _call_with_frames_removed
    <source code not available>
  File "/app/datasite/urls.py", line 20, in <module>
    from . import views
  File "/app/datasite/views.py", line 2, in <module>
    from data.dash_lab import ur_latest
  File "/app/data/dash_lab.py", line 26, in <module>
    df1=pd.read_sql_query(statement_df1, con=engine)
  File "/app/.heroku/python/lib/python3.9/site-packages/pandas/io/sql.py", line 399, in read_sql_query
    return pandas_sql.read_query(
  File "/app/.heroku/python/lib/python3.9/site-packages/pandas/io/sql.py", line 1557, in read_query
    result = self.execute(*args)
  File "/app/.heroku/python/lib/python3.9/site-packages/pandas/io/sql.py", line 1402, in execute
    return self.connectable.execution_options().execute(*args, **kwargs)
  File "<string>", line 2, in execute
    <source code not available>
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/util/deprecations.py", line 401, in warned
    return fn(*args, **kwargs)
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3175, in execute
    connection = self.connect(close_with_result=True)
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3234, in connect
    return self._connection_cls(self, close_with_result=close_with_result)
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 96, in __init__
    else engine.raw_connection()
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3313, in raw_connection
    return self._wrap_pool_connect(self.pool.connect, _connection)
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3283, in _wrap_pool_connect
    Connection._handle_dbapi_exception_noconnection(
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2117, in _handle_dbapi_exception_noconnection
    util.raise_(
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3280, in _wrap_pool_connect
    return fn()
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 310, in connect
    return _ConnectionFairy._checkout(self)
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 868, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 476, in checkout
    rec = pool._do_get()
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 146, in _do_get
    self._dec_overflow()
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 143, in _do_get
    return self._create_connection()
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 256, in _create_connection
    return _ConnectionRecord(self)
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 371, in __init__
    self.__connect()
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 666, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 661, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/engine/create.py", line 590, in connect
    return dialect.connect(*cargs, **cparams)
  File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 597, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/app/.heroku/python/lib/python3.9/site-packages/psycopg2/__init__.py", line 127, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)

Exception Type: OperationalError at /
Exception Value: (psycopg2.OperationalError) connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
    Is the server running locally and accepting connections on that socket?

(Background on this error at: https://sqlalche.me/e/14/e3q8)

CodePudding user response：

@Chris was correct. The heroku config:get commands failed and therefore I couldn't get the correct database details. I solved it by using dj_database_url and then getting the necessary variables from there, like this:

import dj_database_url
db_from_env = dj_database_url.config(default=DATABASES['default'], conn_max_age=500, ssl_require=True)
DATABASES['default'].update(db_from_env)
from sqlalchemy import create_engine
db_engine = DATABASES['default']['ENGINE']
db_name = DATABASES['default']['NAME']
db_user = DATABASES['default']['USER']
db_password = DATABASES['default']['PASSWORD']
db_host = DATABASES['default']['HOST']
db_port = DATABASES['default']['PORT']
ENGINE = create_engine("postgresql psycopg2://" db_user ":" db_password "@" db_host ":" str(db_port) "/" db_name)

Page link：https//www.codepudding.com/Backend/454766.html

Prev:Unable to push migrations to Heroku from Django
Next:IntelliJ starts but cannot see it on the screen
  • Related

About Us:  Contact Us      Terms of Service       Privacy Policy

Copyright © 2010-2023，Powered By CodePudding