如何在 PostgreSQL 中管理数据库：Linux VPS 用户完整指南

PostgreSQL（通常称为 Postgres）是当今最强大、功能最丰富的开源关系型数据库管理系统之一。受到初创公司和企业团队的信任，它在处理复杂查询、大型数据集和可扩展应用架构方面表现出色。无论您是在构建 SaaS 平台、电子商务商店还是数据密集型 API 后端，PostgreSQL 都能提供您的项目所需的可靠性和性能。

在高性能 VPS Hosting 环境中运行 PostgreSQL — 配备 NVMe SSD 存储、完整的 root 访问权限和内置 DDoS 防护 — 让您能够完全控制数据库基础设施，而无需承担托管云解决方案的开销。本指南将引导您完成每项基本的 PostgreSQL 管理任务，从初始访问和数据库创建到用户权限、备份和安全加固。

前置条件

在继续之前，请确保您拥有：

• 运行 Ubuntu、Debian 或 CentOS 的 Linux VPS，已安装 PostgreSQL
• 对服务器的 root 或 sudo 访问权限
• 对 Linux 命令行的基本熟悉程度

如果您还没有设置服务器环境，AlexHost 的 VPS 控制面板可以帮助您快速配置堆栈。

1. 访问 PostgreSQL 命令行界面

所有 PostgreSQL 管理任务都从命令行界面 (CLI) 开始，也称为 psql。默认情况下，PostgreSQL 在安装期间创建一个称为 postgres 的系统用户，该账户用于与数据库引擎进行身份验证。

步骤 1：切换到 PostgreSQL 系统用户

sudo -i -u postgres

步骤 2：启动 PostgreSQL CLI

psql

连接后，您将看到交互式提示符：

postgres=#

这确认您已进入 PostgreSQL 环境，可以执行 SQL 命令和元命令。

> 专业提示：您也可以从常规 shell 会话中运行 sudo -u postgres psql 来直接连接，而无需切换用户。

2. 创建新数据库

数据库是所有表、索引和存储数据的顶级容器。使用 CREATE DATABASE 语句来配置新数据库。

语法

CREATE DATABASE database_name;

示例

CREATE DATABASE my_database;

这会创建一个名为 my_database 的数据库，由当前活跃的 PostgreSQL 角色拥有。

验证数据库是否已创建

使用 l 元命令列出服务器上的所有数据库：

l

您将看到一个表格，显示数据库名称、所有者、编码和访问权限。

3. 创建和管理数据库用户

正确的用户管理对数据库安全至关重要。与其在 postgres 超级用户账户下授予所有应用程序访问权限，不如创建具有作用域权限的专用用户。

创建新用户

CREATE USER username WITH PASSWORD 'your_secure_password';

示例

CREATE USER dbuser WITH PASSWORD 'StrongP@ssword123';

授予对特定数据库的完全访问权限

GRANT ALL PRIVILEGES ON DATABASE database_name TO username;

示例

GRANT ALL PRIVILEGES ON DATABASE my_database TO dbuser;

这授予 dbuser 对 my_database 的完全读写访问权限，包括创建和删除表的能力。

列出所有用户

要查看所有现有角色和用户：

du

4. 连接到数据库

数据库存在后，您需要先连接到它，然后才能创建表或对其运行查询。

切换到数据库

c my_database

提示符将更新以反映活动数据库：

my_database=#

您现在在 my_database 中运行，可以对其执行所有 DDL 和 DML 语句。

5. 创建和管理表

表是任何关系数据库的核心结构单元。每个表定义一个模式——一组具有特定数据类型和约束的命名列。

创建表

CREATE TABLE table_name (
    column1 data_type PRIMARY KEY,
    column2 data_type,
    column3 data_type
);

实际示例：员工记录表

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    department  VARCHAR(50),
    salary      NUMERIC(10, 2)
);

这里，SERIAL 在每个新行上自动递增 employee_id，而 NOT NULL 强制每个员工都必须有一个名字。

6. 插入、查询、更新和删除数据

插入记录

INSERT INTO employees (name, department, salary)
VALUES ('Jane Smith', 'Engineering', 72000);

查询所有记录

SELECT * FROM employees;

使用 WHERE 子句筛选结果

SELECT name, salary FROM employees WHERE department = 'Engineering';

更新现有记录

UPDATE employees
SET salary = 78000
WHERE name = 'Jane Smith';

删除特定记录

DELETE FROM employees
WHERE name = 'Jane Smith';

> 最佳实践：始终在 WHERE 子句中使用 UPDATE 和 DELETE 语句。省略它将影响表中的每一行。

7. 管理数据库访问和安全性

安全性是任何生产 PostgreSQL 部署中的首要关注点。最小权限原则——仅授予用户实际需要的权限——可以大幅减少您的攻击面。

撤销用户的所有权限

REVOKE ALL PRIVILEGES ON DATABASE my_database FROM dbuser;

授予对特定表的只读访问权限

GRANT SELECT ON TABLE employees TO dbuser;

这允许 dbuser 查询 employees 表，但防止任何插入、更新或删除操作。

授予特定的 DML 权限

GRANT SELECT, INSERT, UPDATE ON TABLE employees TO dbuser;

其他安全建议

• 为每个数据库用户使用强密码和唯一密码
• 在 pg_hba.conf 中禁用 postgres 超级用户的远程访问
• 启用 SSL 连接以加密传输中的数据——将其与服务器上的受信任 SSL 证书配对
• 定期审计用户权限使用 du 和 dp
• 保持 PostgreSQL 更新以修补已知漏洞

8. 数据库备份和恢复

定期备份对任何生产数据库都是必不可少的。PostgreSQL 提供 pg_dump 和 psql 实用程序，用于直接的备份和恢复工作流。

将数据库备份到 SQL 文件

pg_dump my_database > my_database_backup.sql

这会将整个数据库架构和数据导出为纯文本 SQL 脚本。

以压缩格式备份（推荐用于大型数据库）

pg_dump -Fc my_database > my_database_backup.dump

自定义格式 (-Fc) 生成压缩的二进制文件，并支持并行恢复。

从 SQL 备份恢复数据库

psql my_database < my_database_backup.sql

从自定义格式备份恢复

pg_restore -d my_database my_database_backup.dump

使用 Cron 作业自动化备份

通过添加 cron 条目来计划每日备份：

crontab -e

0 2 * * * pg_dump my_database > /var/backups/postgres/my_database_$(date +%F).sql

这会在每天凌晨 2:00 运行备份，并在文件名中附加日期以便于版本控制。

9. 删除表和数据库

当不再需要表或数据库时，使用 DROP 命令永久删除它。

删除表

DROP TABLE table_name;

示例

DROP TABLE employees;

仅在表存在时删除表（更安全的语法）

DROP TABLE IF EXISTS employees;

删除数据库

删除目标数据库之前必须断开连接。首先切换到默认 postgres 数据库：

c postgres

然后删除目标：

DROP DATABASE my_database;

> 警告： DROP DATABASE 是不可逆的。在生产环境中执行此命令之前，始终验证您有当前备份。

10. 有用的 PostgreSQL 元命令参考

PostgreSQL CLI 包含一套丰富的元命令（以 为前缀），可简化导航和检查：

11. 退出 PostgreSQL CLI

当您完成会话时，使用以下命令干净地退出：

q

这会将您返回到 Linux shell 提示符。

为什么在 AlexHost VPS 上运行 PostgreSQL？

性能和控制是在专用 VPS 上自托管 PostgreSQL 而不是依赖共享或托管数据库服务的两个最大原因。使用 AlexHost 的基础设施，您可以获得：

• NVMe SSD 存储，用于大型数据集的超快速读/写操作
• 完全 root 访问权限，可以根据您的应用程序要求精确配置 PostgreSQL
• DDoS 防护，在恶劣网络条件下保持数据库服务器可用
• 可扩展资源 — 随着数据增长升级您的 CPU、RAM 和存储

对于管理多个应用程序或客户端项目的团队，专用服务器提供更大的隔离、原始性能和数据库密集型工作负载的可预测延迟。

如果您在 PostgreSQL 数据库旁边托管 Web 应用程序，将您的 VPS 与共享 Web 托管配对以用于静态资产或前端交付可以进一步优化您的架构并降低成本。

结论

在 Linux VPS 上掌握 PostgreSQL 数据库管理让您完全拥有数据基础设施的所有权——从模式设计和用户权限到自动备份和安全加固。本指南涵盖的命令和工作流代表了每个开发人员和系统管理员需要在生产环境中自信地操作 PostgreSQL 的核心技能集。

快速参考总结：

• 使用 CREATE DATABASE 和 CREATE USER 来配置新资源
• 使用 GRANT 和 REVOKE 应用最小权限原则
• 在进行破坏性更改之前，始终使用 pg_dump 进行备份
• 使用 l、dt 和 du 一目了然地检查您的环境
• 通过 cron 计划自动备份以防止数据丢失

准备好将这些技能付诸实践了吗？在 AlexHost VPS 主机计划上启动完全托管的 Linux 环境，立即完全控制您的 PostgreSQL 部署。