部署至VPS后，Sequelize+Node.js连接PostgreSQL失败

错误信息 #

SequelizeConnectionRefusedError: connect ECONNREFUSED 127.0.0.1:5432
ConnectionRefusedError [SequelizeConnectionRefusedError]: connect ECONNREFUSED 127.0.0.1:5432
    at Client._connectionCallback (/root/server/mern-cloud-disk-server/node_modules/sequelize/lib/dialects/postgres/connection-manager.js:131:24)
    at Client._handleErrorWhileConnecting (/root/server/mern-cloud-disk-server/node_modules/pg/lib/client.js:318:19)
    at Client._handleErrorEvent (/root/server/mern-cloud-disk-server/node_modules/pg/lib/client.js:328:19)
    at Connection.emit (events.js:400:28)
    at Socket.reportStreamError (/root/server/mern-cloud-disk-server/node_modules/pg/lib/connection.js:57:12)
    at Socket.emit (events.js:400:28)
    at emitErrorNT (internal/streams/destroy.js:106:8)
    at emitErrorCloseNT (internal/streams/destroy.js:74:3)
    at processTicksAndRejections (internal/process/task_queues.js:82:21) {
  parent: Error: connect ECONNREFUSED 127.0.0.1:5432
      at TCPConnectWrap.afterConnect [as oncomplete] (net.js:1159:16) {
    errno: -111,
    code: 'ECONNREFUSED',
    syscall: 'connect',
    address: '127.0.0.1',
    port: 5432
  },
  original: Error: connect ECONNREFUSED 127.0.0.1:5432
      at TCPConnectWrap.afterConnect [as oncomplete] (net.js:1159:16) {
    errno: -111,
    code: 'ECONNREFUSED',
    syscall: 'connect',
    address: '127.0.0.1',
    port: 5432
  }
}

相关代码文件 #

db.js #

const { Sequelize } = require('sequelize')

module.exports = new Sequelize(
    process.env.DB_NAME, // 数据库名称
    process.env.DB_USER, // 用户
    process.env.DB_PASSWORD, // 密码
    {
        dialect: 'postgres',
        host: process.env.DB_HOST,
        port: process.env.DB_PORT
    }
)

.env #

PORT=5000
DB_NAME=auto-service
DB_USER=postgres
DB_PASSWORD=password
DB_HOST=localhost
DB_PORT=5432
SECRET_KEY=secret

index.js #

require('dotenv').config()
const express = require('express')
const sequelize = require('./db')
const models = require('./models/models')
const cors = require('cors')
const fileUpload = require('express-fileupload')
const router = require('./routes/index')
const path = require('path')

const PORT = process.env.PORT || 5000

const app = express()
app.use(cors());
app.use(express.json())
app.use(express.static(path.resolve(__dirname, 'static')))
app.use(fileUpload({}))
app.use('/api', router)

const start = async () => {
  try {
    await sequelize.authenticate()
    await sequelize.sync()
    app.listen(PORT, () => console.log(`Server started on port ${PORT}`))
  } catch (e) {
    console.log(e)
  }
}

start()

解决步骤 #

• 确认PostgreSQL服务状态
  执行命令检查服务是否运行：

  sudo systemctl status postgresql
  

  如果未运行，启动服务并设置开机自启：

  sudo systemctl start postgresql
  sudo systemctl enable postgresql
  

• 检查PostgreSQL监听配置
  编辑PostgreSQL配置文件（路径通常为/etc/postgresql/<你的PostgreSQL版本>/main/postgresql.conf），找到listen_addresses项，设置为'localhost'（确保允许本地连接）：

  listen_addresses = 'localhost'
  

  修改后重启服务：

  sudo systemctl restart postgresql
  

• 验证数据库和用户权限

  1. 登录PostgreSQL控制台：

     sudo -u postgres psql
     

  2. 检查目标数据库是否存在：

     SELECT datname FROM pg_database;
     

     如果没有auto-service数据库，执行创建命令：

     CREATE DATABASE "auto-service";
     

  3. 确保postgres用户拥有该数据库的全部权限：

     GRANT ALL PRIVILEGES ON DATABASE "auto-service" TO postgres;
     

  4. 退出控制台：\q

• 检查防火墙设置
  如果VPS启用了防火墙（如ufw），确认5432端口对本地连接开放：

  sudo ufw allow from 127.0.0.1 to any port 5432
  sudo ufw reload
  

• 确认.env配置正确性
  确保.env文件中的DB_HOST为localhost，DB_PORT为5432，且数据库名称、用户名、密码与PostgreSQL中的配置完全一致。

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