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

PostgreSQL 是一个功能强大、功能丰富的开源关系数据库管理系统 (RDBMS)，因其可靠性、灵活性和高性能而享有坚实的声誉。从轻量级网络应用到复杂的企业级系统，PostgreSQL 是全球开发人员、数据工程师和数据库管理员的首选。其强大的架构、先进的 SQL 合规性和卓越的可扩展性使其成为当今最值得信赖的数据库平台之一。

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

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

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 安装中，是系统管理员和高级用户的首选工具。

步骤 1：打开您的终端或命令提示符

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

步骤 2：使用 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 — 您要连接的数据库名称。

步骤 3：运行实际连接示例

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

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

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

mydb=#

步骤 4：在 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

步骤 5：退出 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连接

步骤1：下载并安装pgAdmin

从官方pgAdmin网站下载最新版本。Windows、macOS和Linux都有安装包。

步骤2：启动pgAdmin

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

步骤3：创建新的服务器连接

1. 在左侧边栏中，右键单击服务器。
2. 选择创建 → 服务器…

步骤4：配置常规选项卡

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

步骤5：配置连接选项卡

填写以下字段：

可选地，勾选保存密码以在开发环境中方便使用。

步骤6：保存并连接

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

步骤7：运行查询

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

3.2 DBeaver — 通用数据库客户端

DBeaver是一个免费的开源跨平台数据库管理工具，支持80多个数据库系统，包括PostgreSQL。它在需要使用多种数据库类型并需要高级数据可视化、ER图生成和数据导出功能的开发人员中特别受欢迎。

如何使用DBeaver连接

步骤1：下载并安装DBeaver

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

步骤2：创建新的数据库连接

1. 打开DBeaver。
2. 点击新建数据库连接按钮（顶部工具栏中的插头图标），或转到数据库 → 新建数据库连接。

步骤3：选择PostgreSQL作为数据库类型

从支持的数据库列表中选择PostgreSQL并点击下一步。

步骤4：输入您的连接详情

填写连接表单：

步骤5：测试连接

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

步骤6：完成并开始管理您的数据库

点击完成。您的PostgreSQL连接将出现在左侧的数据库导航器面板中。您现在可以浏览表、在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.")

使用 psycopg2 的连接 URI

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

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

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