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

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

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

前置条件

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

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

如果您还没有设置服务器环境，AlexHost 的 VPS Control Panels 可以让您快速轻松地配置堆栈。

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 元命令参考

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

11. 退出 PostgreSQL CLI

完成会话后，使用以下方式干净地退出：

q

这将返回到 Linux shell 提示符。

为什么在 AlexHost VPS 上运行 PostgreSQL？

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

• NVMe SSD 存储用于大型数据集上的超快速读