SQLAlchemy在Unix套接字或IPv4之前尝试通过IPv6连接PostgreSQL连接

Question

如何确保与PostgreSQL的SQLAlchemy连接是通过Unix套接字或甚至IPv4？它只会通过IPv6连接。

我将create_engine配置为@localhost，但在底部注意到主机IPv6主机“:: 1”失败。我有意删除了pg_hba.conf中的IPv6信任（见下文）以显示错误。如果我取消注释该行，它将通过IPv6成功连接并跳过Unix和IPv4。

>>> from sqlalchemy import * 
db = create_engine('postgresql://[email protected]:5432/mydb', echo=True) 
>>> metadata = MetaData(db) 
>>> users = Table('users', metadata, 
... Column('user_id', Integer, primary_key=True), 
... Column('name', String(40)), 
... Column('age', Integer), 
... Column('password', String), 
...) 
>>> users.create() 
Traceback (most recent call last): 
    File "/usr/local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 1044, in _do_get 
    return self._pool.get(wait, self._timeout) 
    File "/usr/local/lib/python3.5/site-packages/sqlalchemy/util/queue.py", line 145, in get 
    raise Empty 
sqlalchemy.util.queue.Empty 

During handling of the above exception, another exception occurred: 

Traceback (most recent call last): 
    File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 2074, in _wrap_pool_connect 
    return fn() 
    File "/usr/local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 376, in connect 
    return _ConnectionFairy._checkout(self) 
    File "/usr/local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 713, in _checkout 
    fairy = _ConnectionRecord.checkout(pool) 
    File "/usr/local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 480, in checkout 
    rec = pool._do_get() 
    File "/usr/local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 1060, in _do_get 
    self._dec_overflow() 
    File "/usr/local/lib/python3.5/site-packages/sqlalchemy/util/langhelpers.py", line 60, in __exit__ 
    compat.reraise(exc_type, exc_value, exc_tb) 
    File "/usr/local/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 186, in reraise 
    raise value 
    File "/usr/local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 1057, in _do_get 
    return self._create_connection() 
    File "/usr/local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 323, in _create_connection 
    return _ConnectionRecord(self) 
    File "/usr/local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 449, in __init__ 
    self.connection = self.__connect() 
    File "/usr/local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 607, in __connect 
    connection = self.__pool._invoke_creator(self) 
    File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/strategies.py", line 97, in connect 
    return dialect.connect(*cargs, **cparams) 
    File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 385, in connect 
    return self.dbapi.connect(*cargs, **cparams) 
    File "/usr/local/lib/python3.5/site-packages/psycopg2/__init__.py", line 164, in connect 
    conn = _connect(dsn, connection_factory=connection_factory, async=async) 
psycopg2.OperationalError: FATAL: no pg_hba.conf entry for host "::1", user "vaderade", database "mydb", SSL off 

从/var/lib/pgsql/9.5/data/pg_hba.conf

# TYPE DATABASE  USER   ADDRESS     METHOD 

# "local" is for Unix domain socket connections only 
local all    all          trust 
# IPv4 local connections: 
host all    all    127.0.0.1/32   trust 
# IPv6 local connections: 
#host all    all    ::1/128     trust 

相关信息从/var/lib/pgsql/9.5/data/postgresql.conf

相关信息

#------------------------------------------------------------------------------ 
# CONNECTIONS AND AUTHENTICATION 
#------------------------------------------------------------------------------ 

# - Connection Settings - 

listen_addresses = 'localhost'   # what IP address(es) to listen on; 
             # comma-separated list of addresses; 
             # defaults to 'localhost'; use '*' for all 
             # (change requires restart) 
port = 5432        # (change requires restart) 

具体细节：

• 红帽7.2
• 的PostgreSQL 9.5.4
• 的Python 3.5.2
• 的SQLAlchemy == 1.0.14
• psycopg2 == 2.6.2

Answer 1

当您使用localhost它会使用一个TCP连接槽是什么在您的系统中配置为响应localhost。在Linux上，它通常映射在/etc/hosts上，通常是IPv4 127.0.0.1或IPv6 ::1（因为它可能是您的情况）。

现在，如果你想在本地连接你的选择：

• postgresql://[email protected]:5432/mydb：通过TCP本地主机连接（如IPv4或IPv6取决于你的系统）
• postgresql://[email protected]:5432/mydb：通过TCP的IPv4本地主机
连接
• postgresql://[email protected][::1]:5432/mydb：连接通过TCP IPv6的本地主机
• postgresql://[email protected]:5432/mydb：通过默认的Unix域套接字连接的位置（即答案你是厕所王为）
• postgresql://[email protected]:5432/mydb?host=/path/to/socket：在/path/to/socket