如何连接到 PostgreSQL 数据库：所有方法的完整指南

PostgreSQL 是一个功能强大、特性丰富的开源关系型数据库管理系统（RDBMS），以其可靠性、灵活性和高性能赢得了坚如磐石的声誉。从轻量级 Web 应用程序到复杂的企业级系统，PostgreSQL 是全球开发人员、数据工程师和数据库管理员的首选。其强大的架构、先进的 SQL 合规性和卓越的可扩展性使其成为当今最受信赖的数据库平台之一。

无论您是在 VPS 托管环境中搭建新项目，还是在独立服务器上管理生产数据库，了解如何连接 PostgreSQL 数据库都是一项绝对基础的技能。正确配置的连接可确保安全访问、最佳性能和高效的数据库管理——这是运行查询、导入或导出数据、管理用户角色或将数据库与应用程序集成之前必不可少的第一步。

本综合指南涵盖了连接 PostgreSQL 的所有主要方法：命令行界面（CLI）、图形化 GUI 工具，以及使用 Python 和 Node.js 进行编程连接。

目录

1. 连接 PostgreSQL 的前提条件
2. 通过命令行界面（psql）连接
3. 使用图形化工具连接（pgAdmin 和 DBeaver）
4. 以编程方式连接 PostgreSQL
5. 常见连接错误及解决方法
6. PostgreSQL 连接安全最佳实践

1. 连接 PostgreSQL 的前提条件

在尝试任何连接之前，请确认以下前提条件已满足：

PostgreSQL 已安装并正在运行

确保 PostgreSQL 已安装在您的本地计算机上，或者您可以通过网络访问远程 PostgreSQL 服务器。您可以使用以下命令验证服务是否正在运行：

# On Linux (systemd-based)
sudo systemctl status postgresql

# On macOS (Homebrew)
brew services list | grep postgresql

# On Windows
sc query postgresql

所需访问凭据

本指南中涵盖的每种连接方法都需要以下详细信息：

网络和防火墙访问

对于远程连接，请确保：

• 端口 5432 在服务器防火墙规则中已开放。
• PostgreSQL 配置文件 pg_hba.conf 允许来自您 IP 地址的连接。
• postgresql.conf 文件中的 listen_addresses 已正确设置（例如，'*' 表示所有接口，或指定特定 IP）。

2. 通过命令行界面（CLI）连接 PostgreSQL

psql 命令行工具是与 PostgreSQL 交互最直接、最通用的方法。它随每个标准 PostgreSQL 安装预装，是系统管理员和高级用户的首选工具。

第一步：打开终端或命令提示符

• Linux / macOS：打开终端应用程序。
• Windows：打开命令提示符、PowerShell 或 Windows Terminal。确保 PostgreSQL bin 目录已添加到系统 PATH 中。

第二步：使用 psql 连接语法

标准 psql 连接语法为：

psql -h host -p port -U username -d database

参数说明：

• -h host — 服务器的主机名或 IP 地址（例如，本地使用 localhost，或远程 IP 如 192.168.1.100）。
• -p port — PostgreSQL 监听的端口（默认：5432）。
• -U username — 用于身份验证的 PostgreSQL 用户名。
• -d database — 您要连接的数据库名称。

第三步：运行实际连接示例

以 postgres 超级用户身份连接到本地计算机上名为 mydb 的数据库：

psql -h localhost -p 5432 -U postgres -d mydb

系统将提示您输入密码。身份验证成功后，您将看到 psql shell 提示符：

mydb=#

第四步：在 psql Shell 中执行查询

进入 psql shell 后，您可以直接运行任何 SQL 查询：

-- Check the PostgreSQL server version
SELECT version();

-- List all databases
l

-- List all tables in the current database
dt

-- Run a query
SELECT * FROM my_table;

-- Describe a table's structure
d my_table

第五步：退出 psql Shell

要关闭连接并退出 psql：

q

使用连接字符串（URI 格式）

PostgreSQL 还支持通过 URI 字符串进行连接，这对于脚本编写非常方便：

psql "postgresql://postgres:your_password@localhost:5432/mydb"

使用 PGPASSWORD 环境变量

在自动化脚本中避免密码提示：

export PGPASSWORD='your_password'
psql -h localhost -p 5432 -U postgres -d mydb

> 安全提示：在 shell 脚本中使用 PGPASSWORD 可能会在进程列表中暴露凭据。在生产环境中，请改用 .pgpass 文件（详见安全最佳实践部分）。

3. 使用图形化工具连接 PostgreSQL

对于偏好可视化界面的用户，多款优秀的 GUI 客户端可让您无需记忆命令行语法，即可轻松连接、查询和管理 PostgreSQL 数据库。

3.1 pgAdmin — 官方 PostgreSQL GUI

pgAdmin 是 PostgreSQL 官方的开源管理工具。它提供了全面的基于 Web 和桌面的界面，用于执行 SQL 查询、管理数据库对象、监控性能和可视化数据。

#### 如何使用 pgAdmin 连接

第一步：下载并安装 pgAdmin

从 pgAdmin 官方网站下载最新版本。提供适用于 Windows、macOS 和 Linux 的安装包。

第二步：启动 pgAdmin

打开 pgAdmin。它将在您的默认 Web 浏览器中启动（桌面版）或作为独立应用程序运行。

第三步：创建新的服务器连接

1. 在左侧边栏中，右键单击 Servers。
2. 选择 Create → Server…

第四步：配置 General 选项卡

• Name：为此连接输入一个描述性名称（例如，Production DB 或 Local Development）。

第五步：配置 Connection 选项卡

填写以下字段：

在开发环境中，可选择勾选 Save password 以方便使用。

第六步：保存并连接

点击 Save。pgAdmin 将立即尝试连接。连接成功后，您的服务器将出现在左侧边栏中，您可以展开它以浏览数据库、模式、表等内容。

第七步：运行查询

右键单击任意数据库，选择 Query Tool 打开 SQL 编辑器。您可以直接在界面中编写和执行查询、查看结果并导出数据。

3.2 DBeaver — 通用数据库客户端

DBeaver 是一款免费、开源、跨平台的数据库管理工具，支持超过 80 种数据库系统，包括 PostgreSQL。它在需要处理多种数据库类型并需要高级数据可视化、ER 图生成和数据导出功能的开发人员中尤为流行。

#### 如何使用 DBeaver 连接

第一步：下载并安装 DBeaver

从 DBeaver 官方网站下载 DBeaver 社区版（免费）。适用于 Windows、macOS 和 Linux。

第二步：创建新的数据库连接

1. 打开 DBeaver。
2. 点击 New Database Connection 按钮（顶部工具栏中的插头图标），或前往 Database → New Database Connection。

第三步：选择 PostgreSQL 作为数据库类型

从支持的数据库列表中选择 PostgreSQL，然后点击 Next。

第四步：输入连接详细信息

填写连接表单：

第五步：测试连接

点击 Test Connection。DBeaver 将尝试连接并显示成功或错误消息。如果您是第一次在 DBeaver 中使用 PostgreSQL，系统可能会提示您下载所需的 JDBC 驱动程序——点击 Download 自动完成下载。

第六步：完成并开始管理数据库

点击 Finish。您的 PostgreSQL 连接将出现在左侧的 Database Navigator 面板中。您现在可以浏览表、在 SQL 编辑器中运行 SQL 查询、导入/导出数据以及生成 ER 图。

4. 以编程方式连接 PostgreSQL

从应用程序代码连接 PostgreSQL 是最常见的实际使用场景之一。以下是两种最流行语言的生产就绪示例：Python 和 Node.js。

4.1 Python — 使用 psycopg2

psycopg2 是 Python 中使用最广泛的 PostgreSQL 适配器。它速度快、线程安全，并完全符合 Python DB-API 2.0 规范。

#### 安装 psycopg2

pip install psycopg2

对于不希望从源代码编译的环境，请使用二进制包：

pip install psycopg2-binary

#### 使用 psycopg2 连接 PostgreSQL

import psycopg2
from psycopg2 import OperationalError

def create_connection():
    connection = None
    try:
        connection = psycopg2.connect(
            user="postgres",
            password="your_password",
            host="127.0.0.1",
            port="5432",
            database="mydb"
        )
        print("Connection to PostgreSQL successful.")
    except OperationalError as e:
        print(f"The error '{e}' occurred.")
    return connection

def execute_query(connection, query):
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        connection.commit()
        print("Query executed successfully.")
    except Exception as e:
        print(f"The error '{e}' occurred.")
    finally:
        cursor.close()

# Establish the connection
conn = create_connection()

# Execute a sample query
if conn:
    execute_query(conn, "SELECT version();")

    # Fetch and display results
    cursor = conn.cursor()
    cursor.execute("SELECT version();")
    record = cursor.fetchone()
    print(f"PostgreSQL server version: {record[0]}")

    # Close the connection
    cursor.close()
    conn.close()
    print("PostgreSQL connection closed.")

#### 使用连接 URI 与 psycopg2

import psycopg2

DATABASE_URL = "postgresql://postgres:your_password@127.0.0.1:5432/mydb"

connection = psycopg2.connect(DATABASE_URL)
cursor = connection.cursor()
cursor.execute("SELECT current_database();")
print(cursor.fetchone())
connection.close()

4.2 Node.js — 使用 pg 包

node-postgres（pg）包是 Node.js 的标准 PostgreSQL 客户端。它支持基于回调和 async/await 两种模式，并通过 pg.Pool 提供连接池支持。

#### 安装 pg 包

npm install pg

#### 使用单个客户端连接（async/await）

const { Client } = require('pg');

const client = new Client({
  host: 'localhost',
  port: 5432,
  user: 'postgres',
  password: 'your_password',
  database: 'mydb',
});

async function connectAndQuery() {
  try {
    await client.connect();
    console.log('Connected to PostgreSQL successfully.');

    const result = await client.query('SELECT version()');
    console.log('PostgreSQL version:', result.rows[0].version);

    const tableResult = await client.query('SELECT * FROM my_table LIMIT 10');
    console.log('Query results:', tableResult.rows);

  } catch (err) {
    console.error('Connection error:', err.message);
  } finally {
    await client.end();
    console.log('PostgreSQL connection closed.');
  }
}

connectAndQuery();

#### 使用连接池连接（生产环境推荐）

在生产应用程序中，始终使用连接池来高效管理多个并发数据库连接：

const { Pool } = require('pg');

const pool = new Pool({
  host: 'localhost',
  port: 5432,
  user: 'postgres',
  password: 'your_password',
  database: 'mydb',
  max: 20,               // Maximum number of connections in the pool
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

async function queryDatabase(sql, params = []) {
  const client = await pool.connect();
  try {
    const result = await client.query(sql, params);
    return result.rows;
  } catch (err) {
    console.error('Query error:', err.message);
    throw err;
  } finally {
    client.release(); // Always release the client back to the pool
  }
}

// Example usage
(async () => {
  const rows = await queryDatabase('SELECT * FROM my_table WHERE id = $1', [1]);
  console.log(rows);
})();

#### 在 Node.js 中使用连接字符串

const { Client } = require('pg');

const client = new Client({
  connectionString: 'postgresql://postgres:your_password@localhost:5432/mydb',
});

client.connect()
  .then(() => client.query('SELECT NOW()'))
  .then(res => console.log('Current time:', res.rows[0]))
  .catch(err => console.error('Error:', err))
  .finally(() => client.end());

5. 常见 PostgreSQL 连接错误及解决方法

即使是经验丰富的管理员也会遇到连接问题。以下是最常见的错误及其解决方案：

错误：FATAL: role "postgres" does not exist

原因：服务器上不存在指定的 PostgreSQL 用户。

解决方法：

# Create the user via the system's postgres account
sudo -u postgres createuser --superuser postgres

错误：could not connect to server: Connection refused

原因：PostgreSQL 未运行，或未在预期的主机/端口上监听。

解决方法：

# Start PostgreSQL
sudo systemctl start postgresql

# Verify it is listening on port 5432
sudo ss -tlnp | grep 5432

同时检查 postgresql.conf，确保 listen_addresses 已正确配置。

错误：FATAL: pg_hba.conf rejects connection

原因：pg_hba.conf 文件中没有允许您连接的条目。

解决方法：编辑 /etc/postgresql/<version>/main/pg_hba.conf 并添加适当的规则：

# Allow local connections with password authentication
host    all             all             127.0.0.1/32            md5

# Allow connections from a specific remote subnet
host    all             all             192.168.1.0/24          md5

编辑后，重新加载 PostgreSQL：

sudo systemctl reload postgresql

错误：FATAL: password authentication failed

原因：指定用户的密码不正确。

解决方法：在 PostgreSQL 内部重置密码：

ALTER USER postgres WITH PASSWORD 'new_secure_password';

错误：SSL connection required

原因：服务器要求 SSL/TLS 加密连接。

解决方法：在连接字符串中添加 sslmode=require，或正确配置 SSL。如果您需要为服务器提供受信任的 SSL 证书，可以考虑使用 SSL 证书来端到端保护您的数据库连接。

6. PostgreSQL 连接安全最佳实践

保护 PostgreSQL 连接的安全与建立连接同样重要。在每个环境中都应遵循以下最佳实践：

使用强且唯一的密码

始终为所有 PostgreSQL 用户设置强密码。避免将默认的 postgres 超级用户用于应用程序连接——应创建具有最小所需权限的专用用户。

-- Create a dedicated application user with limited privileges
CREATE USER app_user WITH PASSWORD 'StrongP@ssw0rd!2024';
GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

使用 .pgpass 文件代替环境变量

将凭据安全存储在 .pgpass 文件中，以避免在 shell 历史记录或进程列表中暴露密码：

# Create the file
echo "localhost:5432:mydb:postgres:your_password" >> ~/.pgpass

# Set correct permissions (required by PostgreSQL)
chmod 600 ~/.pgpass

始终对远程连接使用 SSL/TLS

切勿通过未加密的连接传输数据库凭据或查询数据。配置 PostgreSQL 以要求 SSL：

# In postgresql.conf
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

# In pg_hba.conf — require SSL for all remote connections
hostssl    all    all    0.0.0.0/0    md5

通过 pg_hba.conf 限制访问

对网络访问应用最小权限原则。仅允许来自已知可信 IP 地址或子网的连接。

在生产环境中使用连接池

使用 PgBouncer 或 pg.Pool（Node.js）内置连接池等工具，可减少建立新连接的开销，并防止连接耗尽攻击。

保持 PostgreSQL 更新

始终运行最新稳定版本的 PostgreSQL，以获得安全补丁和性能改进。

为 PostgreSQL 选择合适的托管环境

PostgreSQL 数据库的性能和可靠性在很大程度上取决于底层基础设施。以下是值得考虑的最佳托管选项：

• VPS 托管 — 非常适合开发环境、中小型生产数据库，以及需要完整 root 访问权限以按需配置 PostgreSQL 的团队。
• 独立服务器 — 适合高流量、资源密集型 PostgreSQL 部署的最佳选择，可在无资源共享的情况下实现最大 CPU、RAM 和 I/O 性能。
• 带 cPanel 的 VPS — 如果您希望将 VPS 的强大功能与易于使用的控制面板相结合，通过图形界面管理数据库、用户和服务器设置，这是一个绝佳选择。

结论

连接 PostgreSQL 数据库是每位开发人员、数据工程师和系统管理员都需要掌握的基础技能。本指南详细介绍了所有主要连接方法：

• 使用 psql 的 CLI — 快速、强大，在安装了 PostgreSQL 的任何地方均可使用。
• pgAdmin — 用于可视化数据库管理和管理的官方 GUI。
• DBeaver — 支持多种数据库系统的多功能跨平台 GUI 客户端。
• Python（psycopg2） — Python 应用程序的标准 PostgreSQL 适配器。
• Node.js（pg） — JavaScript/Node.js 环境中 PostgreSQL 连接的首选包。

通过将正确的连接方法与强大的安全实践和可靠的托管基础设施相结合，您将拥有一个稳固、安全且高性能的 PostgreSQL 配置，可应对任何工作负载——从个人项目到企业级应用程序。