Docker环境下Scala Spark无法连接本地PostgreSQL问题

问题背景 #

• 本地非Docker环境下，Scala Spark可正常连接PostgreSQL，已将postgresql-42.7.5.jar放入Spark jar目录，pgAdmin4也能正常访问数据库，数据库本身无连接问题。
• 将Spark和Jupyter部署到Docker容器后，无法连接运行在macOS本地的PostgreSQL，报错连接被拒绝。

报错信息 #

org.postgresql.util.PSQLException: Connection to localhost:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.
  at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:352)
  at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:54)
  at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:273)
  at org.postgresql.Driver.makeConnection(Driver.java:446)
  at org.postgresql.Driver.connect(Driver.java:298)
  at org.apache.spark.sql.execution.datasources.jdbc.connection.BasicConnectionProvider.getConnection(BasicConnectionProvider.scala:49)
  at org.apache.spark.sql.execution.datasources.jdbc.connection.ConnectionProviderBase.create(ConnectionProvider.scala:102)
  at org.apache.spark.sql.jdbc.JdbcDialect.$anonfun$createConnectionFactory$1(JdbcDialects.scala:161)
  at org.apache.spark.sql.jdbc.JdbcDialect.$anonfun$createConnectionFactory$1$adapted(JdbcDialects.scala:157)
  at org.apache.spark.sql.execution.datasources.jdbc.JDBCRDD$.getQueryOutputSchema(JDBCRDD.scala:63)
  at org.apache.spark.sql.execution.datasources.jdbc.JDBCRDD$.resolveTable(JDBCRDD.scala:58)
  at org.apache.spark.sql.execution.datasources.jdbc.JDBCRelation$.getSchema(JDBCRelation.scala:241)
  at org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:37)
  at org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:346)
  at org.apache.spark.sql.DataFrameReader.loadV1Source(DataFrameReader.scala:229)
  at org.apache.spark.sql.DataFrameReader.$anonfun$load$2(DataFrameReader.scala:211)
  at scala.Option.getOrElse(Option.scala:189)
  at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:211)
  at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:172)
  ... 49 elided
Caused by: java.net.ConnectException: Connection refused (Connection refused)
  at java.base/java.net.PlainSocketImpl.socketConnect(Native Method)
  at

解决方案 #

1. 替换连接地址中的localhost为宿主IP #

Docker容器内的localhost指向容器自身，而非macOS宿主。需将JDBC连接URL中的localhost替换为宿主的实际IP地址：

• 获取macOS宿主IP：执行ipconfig getifaddr en0（无线网卡）或ifconfig查看对应网卡的IP。
• 修改JDBC连接URL：将jdbc:postgresql://localhost:5432/your_database改为jdbc:postgresql://192.168.x.x:5432/your_database（替换为你的宿主IP）。

2. 配置PostgreSQL允许远程连接 #

• 修改postgresql.conf：找到listen_addresses参数，设置为listen_addresses = '*'，允许所有IP访问。
• 修改pg_hba.conf：添加一条规则允许Docker网段的IP访问，例如：

  host    all             all             172.17.0.0/16            scram-sha-256
  

  （172.17.0.0/16是Docker默认桥接网络的网段，若使用自定义网络需调整对应网段）
• 重启PostgreSQL服务使配置生效。

3. 验证容器网络连通性 #

• 进入Docker容器：docker exec -it <container_name> bash
• 测试端口连通性：telnet 192.168.x.x 5432或nc -zv 192.168.x.x 5432
• 若不通，检查macOS防火墙是否阻止了5432端口的入站连接，需添加允许规则。

内容的提问来源于stack exchange，提问作者Ultra