PostgreSQL 完整指南：安装、配置和核心功能

PostgreSQL 是当今最强大的开源关系型数据库管理系统 (RDBMS) 之一。因其稳定性、可扩展性和对 SQL 标准的合规性而享誉盛名，它被全球开发人员、数据工程师和企业信任，用于自信地管理大型复杂数据集。无论您是在构建 Web 应用程序、数据仓库还是微服务后端，PostgreSQL 都能提供您的项目所需的性能和可靠性。

本综合指南涵盖您需要了解的所有内容：PostgreSQL 是什么、其突出特性、如何在 Ubuntu 上安装它，以及如何执行基本数据库操作以快速启动和运行。

1. PostgreSQL 是什么？

PostgreSQL — 通常称为 "Postgres" — 是一个对象关系数据库管理系统 (ORDBMS)，扩展了传统关系数据库的功能。与更简单的 SQL 引擎不同，PostgreSQL 支持高级数据类型（包括 JSON、数组和 hstore）、过程语言和用户定义函数，使其适用于从简单网络应用到复杂分析工作负载的广泛用例。

PostgreSQL 首次发布于 1996 年，由充满活力的开源社区支持，已成熟为生产级数据库引擎，可直接与 Oracle 和 Microsoft SQL Server 等商业解决方案竞争 — 且无需任何许可费用。

为什么选择 PostgreSQL 而不是其他数据库？

2. PostgreSQL 的关键特性

了解是什么使 PostgreSQL 与众不同将帮助您在基础设施中充分发挥其潜力。

2.1. 高级数据类型

PostgreSQL 支持非常广泛的原生数据类型，远超大多数数据库提供的范围：

• 原始类型: INTEGER, NUMERIC, VARCHAR, BOOLEAN, DATE, TIMESTAMP
• 结构化类型: 数组、复合类型、范围类型
• 文档类型: JSON 和 JSONB（用于更快查询的二进制 JSON）
• 网络类型: INET, CIDR, MACADDR — 适合网络相关应用
• 几何类型: POINT, LINE, POLYGON — 适用于 GIS 应用
• UUID: 对通用唯一标识符的原生支持
• 全文搜索 (FTS): 内置 tsvector 和 tsquery 类型支持强大的、语言感知的全文搜索，无需外部工具

这种多功能性意味着您可以直接在数据库中对几乎任何现实世界的数据结构进行建模。

2.2. 可扩展性

PostgreSQL 被设计为可扩展的。您可以自定义和扩展其功能，而无需修改核心引擎：

• 自定义函数和存储过程: 使用 PL/pgSQL、PL/Perl、PL/Python、PL/Tcl 或甚至 PL/V8 (JavaScript) 直接在数据库中编写业务逻辑
• 自定义运算符和聚合: 定义针对您的数据类型定制的运算符
• 扩展: PostgreSQL 扩展生态系统非常庞大。流行的扩展包括：

PostGIS — 高级地理空间数据支持
pg_stat_statements — 查询性能监控
pgcrypto — 密码学函数
uuid-ossp — UUID 生成
TimescaleDB — 时间序列数据优化

2.3. 并发性和事务控制
PostgreSQL 通过多版本并发控制 (MVCC) 优雅地处理并发访问：

MVCC: PostgreSQL 不是在读取期间锁定行，而是为每个事务创建数据快照。这允许读者和写者同时操作而不会相互阻塞，在高并发下大幅提高性能。
ACID 合规性: PostgreSQL 中的每个事务都完全符合 ACID：
原子性 — 事务要么完全完成，要么根本不完成
一致性 — 数据始终从一个有效状态移动到另一个有效状态
隔离性 — 并发事务彼此不干扰
持久性 — 已提交的数据通过预写日志 (WAL) 在系统崩溃中幸存
保存点: 单个事务块内的细粒度事务控制
两阶段提交 (2PC): 支持跨多个数据库节点的分布式事务

2.4. 安全特性
PostgreSQL 包含企业级安全功能：

基于角色的访问控制 (RBAC) 具有细粒度权限管理
行级安全 (RLS) — 按用户限制行级数据访问
SSL/TLS 加密 用于传输中的连接
SCRAM-SHA-256 和 MD5 身份验证
pg_hba.conf — 灵活的基于主机的身份验证配置

2.5. 高可用性和复制

流复制: 实时主到副本复制
逻辑复制: 选择性地复制特定表或发布
时间点恢复 (PITR): 使用 WAL 存档将数据库恢复到任何特定时刻
故障转移支持: 与 Patroni、repmgr 和 pgBouncer 等工具兼容，用于连接池

3. 在 Ubuntu 上安装 PostgreSQL
本部分将引导您在 Ubuntu 22.04 LTS 上完成生产就绪的 PostgreSQL 安装。相同的步骤也适用于 Ubuntu 20.04，只需进行轻微调整。
> 前置条件：运行 Ubuntu 22.04 LTS 的服务器，具有 sudo 权限。如果您需要可靠的服务器环境，请考虑来自 AlexHost 的 VPS 主机 — 非常适合运行数据库工作负载，具有保证的资源和完全的 root 访问权限。
步骤 1：更新软件包索引
始终首先刷新您的软件包列表，以确保安装最新的可用版本：
sudo apt update && sudo apt upgrade -y
步骤 2：安装 PostgreSQL
安装 PostgreSQL 以及 postgresql-contrib 软件包，其中包括其他实用程序和扩展：
sudo apt install postgresql postgresql-contrib -y
这将安装 PostgreSQL 14（或您的 Ubuntu 存储库中的最新版本）。要从官方 PostgreSQL APT 存储库安装特定版本（例如 PostgreSQL 16），请使用以下命令：
# Add the PostgreSQL APT repository
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# Import the signing key
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg

# Update and install
sudo apt update
sudo apt install postgresql-16 -y
步骤 3：启动并启用 PostgreSQL 服务
安装后，启动服务并将其配置为在系统启动时自动启动：
sudo systemctl start postgresql
sudo systemctl enable postgresql
步骤 4：验证安装
确认 PostgreSQL 正确运行：
sudo systemctl status postgresql
预期输出：
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled)
     Active: active (running) since ...
您也可以检查已安装的版本：
psql --version
# Output: psql (PostgreSQL) 16.x
步骤 5：配置 PostgreSQL 以进行远程访问（可选）
默认情况下，PostgreSQL 仅在 localhost 上侦听。要允许远程连接（例如从您的应用程序服务器），请编辑主配置文件：
sudo nano /etc/postgresql/16/main/postgresql.conf
找到并修改此行：
listen_addresses = 'localhost'
将其更改为：
listen_addresses = '*'
然后更新基于主机的身份验证文件：
sudo nano /etc/postgresql/16/main/pg_hba.conf
添加以下行以允许特定的 IP 范围（替换为您的实际 IP 范围）：
host    all             all             192.168.1.0/24          scram-sha-256
重启 PostgreSQL 以应用更改：
sudo systemctl restart postgresql
> 安全说明：始终将远程访问限制为已知的 IP 地址，并确保您的防火墙（UFW 或 iptables）配置正确。将您的数据库服务器与 SSL 证书 配对，以加密所有传输中的数据。
4. PostgreSQL 基本用法
现在 PostgreSQL 已安装，让我们逐步了解每个管理员和开发人员需要了解的基本操作。
步骤 1：访问 PostgreSQL Shell
PostgreSQL 在安装期间创建了一个名为 postgres 的默认系统用户。切换到此用户并打开交互式 shell：
sudo -i -u postgres
psql
您应该看到 PostgreSQL 提示符：
postgres=#
或者，您可以直接访问 shell，无需切换用户：
sudo -u postgres psql
步骤 2：创建数据库
为您的应用程序创建一个新数据库：
CREATE DATABASE mydatabase;
验证它已创建：
l
这将列出服务器上的所有数据库。
步骤 3：创建用户（角色）
创建一个具有安全密码的专用数据库用户：
CREATE USER myuser WITH PASSWORD 'StrongP@ssw0rd!';
最佳实践：避免为应用程序连接使用默认的 postgres 超级用户。始终创建具有最小权限的专用角色。
步骤 4：授予权限
授予新用户对数据库的完全访问权限：
GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;
为了更精细的控制，您可以在单个架构和表上授予特定权限：
-- Connect to the database first
c mydatabase

-- Grant usage on the public schema
GRANT USAGE ON SCHEMA public TO myuser;

-- Grant privileges on all existing tables
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myuser;

-- Ensure future tables are also accessible
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO myuser;
步骤 5：以新用户身份连接到数据库
退出当前会话并以新用户身份重新连接：
q
然后直接连接：
psql -U myuser -d mydatabase -h localhost
步骤 6：创建表和插入数据
连接后，创建您的第一个表：
CREATE TABLE employees (
    id          SERIAL PRIMARY KEY,
    first_name  VARCHAR(50) NOT NULL,
    last_name   VARCHAR(50) NOT NULL,
    email       VARCHAR(100) UNIQUE NOT NULL,
    department  VARCHAR(50),
    salary      NUMERIC(10, 2),
    hired_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
插入一些记录：
INSERT INTO employees (first_name, last_name, email, department, salary)
VALUES
    ('Alice', 'Johnson', 'alice@example.com', 'Engineering', 85000.00),
    ('Bob', 'Smith', 'bob@example.com', 'Marketing', 72000.00),
    ('Carol', 'Williams', 'carol@example.com', 'Engineering', 91000.00);
查询数据：
SELECT first_name, last_name, department, salary
FROM employees
WHERE department = 'Engineering'
ORDER BY salary DESC;
步骤 7：基本 psql 命令参考




命令
描述




l
列出所有数据库


c dbname
连接到数据库


dt
列出当前数据库中的所有表


d tablename
描述表结构


du
列出所有用户/角色


i file.sql
从文件执行 SQL


timing
切换查询执行时间显示


q
退出 psql


?
psql 命令帮助


h
SQL 命令帮助




5. PostgreSQL 性能调优要点
默认的 PostgreSQL 安装在资源使用上是保守的。对于生产环境，调整配置可以显著提高性能。
postgresql.conf 中的关键参数
sudo nano /etc/postgresql/16/main/postgresql.conf




参数
默认值
推荐值（8GB RAM 服务器）




shared_buffers
128MB
2GB（RAM 的 25%）


effective_cache_size
4GB
6GB（RAM 的 75%）


work_mem
4MB
64MB


maintenance_work_mem
64MB
512MB


max_connections
100
200（使用 pgBouncer 获得更多）


wal_buffers
-1（自动）
64MB


checkpoint_completion_target
0.9
0.9




通过重启服务来应用更改：
sudo systemctl restart postgresql
索引最佳实践
索引对查询性能至关重要：
-- B-tree index (default, for equality and range queries)
CREATE INDEX idx_employees_department ON employees(department);

-- Partial index (index only a subset of rows)
CREATE INDEX idx_high_earners ON employees(salary) WHERE salary > 80000;

-- Composite index (for multi-column queries)
CREATE INDEX idx_dept_salary ON employees(department, salary DESC);

-- GIN index (for full-text search and JSONB)
CREATE INDEX idx_fts ON articles USING GIN(to_tsvector('english', content));
6. 备份和恢复
数据保护是不可协商的。PostgreSQL 提供了强大的内置备份和恢复工具。
使用 pg_dump 进行逻辑备份
# Backup a single database
pg_dump -U postgres -d mydatabase -F c -f /backups/mydatabase_$(date +%Y%m%d).dump

# Backup all databases
pg_dumpall -U postgres > /backups/all_databases_$(date +%Y%m%d).sql
从备份恢复
# Restore a custom-format dump
pg_restore -U postgres -d mydatabase -F c /backups/mydatabase_20240101.dump

# Restore from SQL file
psql -U postgres -d mydatabase < /backups/all_databases_20240101.sql
自动备份脚本
#!/bin/bash
BACKUP_DIR="/var/backups/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="mydatabase"

mkdir -p "$BACKUP_DIR"
pg_dump -U postgres -F c -d "$DB_NAME" -f "$BACKUP_DIR/${DB_NAME}_${DATE}.dump"

# Retain only the last 7 days of backups
find "$BACKUP_DIR" -name "*.dump" -mtime +7 -delete

echo "Backup completed: ${DB_NAME}_${DATE}.dump"
将其添加到 cron 以进行每日自动备份：
crontab -e
# Add: 0 2 * * * /usr/local/bin/pg_backup.sh >> /var/log/pg_backup.log 2>&1
7. 托管PostgreSQL：选择正确的基础设施
PostgreSQL部署的性能和可靠性在很大程度上取决于底层基础设施。以下是根据您的工作负载选择的最佳托管选项：
用于开发和小型应用程序
共享Web托管为小型项目提供了经济实惠的入门选择。但是，对于数据库密集型应用程序，强烈建议使用专用资源。
用于生产Web应用程序
VPS托管计划为您提供专用CPU和RAM、完全root访问权限以及调整PostgreSQL配置参数的能力——这对于生产级数据库性能至关重要。AlexHost VPS计划配备NVMe SSD存储，可大幅降低PostgreSQL I/O延迟。
如果您更喜欢托管控制面板体验，带cPanel的VPS可简化服务器管理，同时仍然为您提供对PostgreSQL实例的访问权限。
用于高流量和企业工作负载
AlexHost的专用服务器提供最大性能，无资源共享。这是处理每天数百万笔交易、复杂分析查询或高可用性复制设置的大型PostgreSQL部署的理想选择。
用于AI和机器学习工作负载
如果您将PostgreSQL与机器学习管道一起使用（例如，使用pgvector进行向量相似性搜索），AlexHost的GPU托管提供AI驱动数据处理所需的计算能力。
8. 安全加固清单
在生产环境中部署 PostgreSQL 之前，请完成以下安全清单：

[ ] 更改默认 postgres 密码: ALTER USER postgres WITH PASSWORD 'NewStrongPassword!';

结论

PostgreSQL 是一个世界级的开源数据库管理系统，结合了企业软件的可靠性和开放平台的灵活性。从其先进的数据类型和 MVCC 并发模型到其丰富的扩展生态系统和强大的安全功能，PostgreSQL 旨在处理最苛刻的数据管理挑战。

通过遵循本指南，您已经学会了如何：

• 在 Ubuntu 上安装和配置 PostgreSQL
• 创建数据库、用户和管理权限
• 执行基本的 CRUD 操作
• 为生产工作负载调整性能
• 实施可靠的备份和恢复策略
• 保护您的 PostgreSQL 实例免受常见威胁

下一步是选择合适的基础设施来托管您的数据库。无论您需要经济高效的 VPS 主机解决方案还是用于企业工作负载的高性能专用服务器，AlexHost 都提供您的 PostgreSQL 部署所需的可靠、高性能基础设施。