PostgreSQL教程：安装、配置与使用

2025-3-24

PostgreSQL 教程：安装、配置与使用

PostgreSQL，通常简称为 Postgres，是一个功能强大、开源的对象关系型数据库管理系统 (ORDBMS)。它以其可靠性、数据完整性、强大的功能集以及对 SQL 标准的高度兼容性而闻名。PostgreSQL 适用于各种规模的项目，从小型个人项目到大型企业级应用程序。

本教程将引导您完成 PostgreSQL 的安装、配置和基本使用过程。我们将涵盖以下主题：

PostgreSQL 简介
安装 PostgreSQL
- Windows 上的安装
- macOS 上的安装
- Linux (Ubuntu/Debian) 上的安装
- 使用 Docker 安装
配置 PostgreSQL
- postgresql.conf 文件
- pg_hba.conf 文件
- 常用配置参数
使用 PostgreSQL
- 连接到数据库
- psql 命令行工具
- 创建数据库
- 创建用户和角色
- 授权
- 创建表
- 数据类型
- 插入数据
- 查询数据 (SELECT)
- 更新数据 (UPDATE)
- 删除数据 (DELETE)
- 基本 SQL 语法
- 使用图形化界面工具 (pgAdmin)
备份与恢复
- pg_dump 和 pg_dumpall
- pg_restore
高级主题 (简要介绍)
- 索引
- 事务
- 视图
- 存储过程
- 触发器
- 扩展
常见问题解答 (FAQ)
总结

1. PostgreSQL 简介

PostgreSQL 起源于 1986 年加州大学伯克利分校的 POSTGRES 项目。它旨在成为 Ingres 数据库的继任者，并引入了许多现代数据库系统中的概念。PostgreSQL 强调可扩展性和符合标准。其主要特点包括：

开源和免费： PostgreSQL 采用 PostgreSQL 许可证，这是一个非常宽松的开源许可证。
ACID 兼容性： PostgreSQL 完全支持 ACID 属性（原子性、一致性、隔离性、持久性），确保数据可靠性。
丰富的特性集： 支持各种数据类型（包括 JSON、XML、数组、范围类型等）、事务、视图、存储过程、触发器、外键、子查询等。
可扩展性： 可以通过创建自定义数据类型、函数、操作符等来扩展 PostgreSQL 的功能。
强大的社区支持： 拥有活跃的社区，提供文档、教程和支持。
多平台支持： 可在各种操作系统上运行，包括 Windows、macOS、Linux、FreeBSD 等。
地理空间支持： 通过 PostGIS 扩展，PostgreSQL 成为强大的地理信息系统 (GIS) 数据库。

2. 安装 PostgreSQL

PostgreSQL 的安装过程因操作系统而异。以下是在常见操作系统上的安装步骤：

2.1 Windows 上的安装

下载安装程序： 访问 PostgreSQL 官方网站 (https://www.postgresql.org/download/windows/) 下载适用于您 Windows 版本的安装程序。
运行安装程序： 双击下载的安装程序文件。按照安装向导的指示进行操作。
设置超级用户密码： 在安装过程中，系统会提示您为 PostgreSQL 超级用户 (通常为 postgres) 设置密码。请务必记住此密码。
选择端口： 默认端口为 5432，通常不需要更改。
选择区域设置： 选择适当的区域设置。
完成安装： 安装完成后，可以选择启动 Stack Builder，这是一个用于安装附加工具和驱动程序的工具。

2.2 macOS 上的安装

有多种方法可以在 macOS 上安装 PostgreSQL：

Homebrew (推荐)：
1. 如果尚未安装 Homebrew，请打开终端并运行以下命令：
  bash /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"
2. 安装 PostgreSQL：
  bash brew install postgresql
3. 启动PostgreSQL服务
  bash brew services start postgresql
Postgres.app：
1. 访问 Postgres.app 网站 (https://postgresapp.com/) 下载应用程序。
2. 将 Postgres.app 拖到 "Applications" 文件夹中。
3. 双击 Postgres.app 启动 PostgreSQL。
EnterpriseDB 安装程序： 与 Windows 类似，您可以从 EnterpriseDB 网站下载 macOS 安装程序。

2.3 Linux (Ubuntu/Debian) 上的安装

更新软件包列表：
bash sudo apt update
安装 PostgreSQL 软件包：
bash sudo apt install postgresql postgresql-contrib
验证安装：
bash sudo -u postgres psql -c "SELECT version();"
如果安装成功，将显示 PostgreSQL 版本信息。
(可选)为了设置postgres用户密码
bash sudo passwd postgres

2.4 使用 Docker 安装

Docker 提供了一种便捷的方式来运行 PostgreSQL，而无需在主机操作系统上进行本地安装。

安装 Docker： 如果尚未安装 Docker，请访问 Docker 官方网站 (https://www.docker.com/get-started) 下载并安装适用于您操作系统的 Docker Desktop。
拉取 PostgreSQL 镜像：
bash docker pull postgres
运行 PostgreSQL 容器：
bash docker run --name my-postgres -e POSTGRES_PASSWORD=mysecretpassword -p 5432:5432 -d postgres
- --name my-postgres：为容器指定一个名称。
- -e POSTGRES_PASSWORD=mysecretpassword：设置 postgres 用户的密码。
- -p 5432:5432：将容器的 5432 端口映射到主机的 5432 端口。
- -d：在后台运行容器。

3. 配置 PostgreSQL

PostgreSQL 的主要配置文件是 postgresql.conf 和 pg_hba.conf。

postgresql.conf： 控制服务器的行为，例如监听地址、端口、内存分配等。
pg_hba.conf： 控制客户端身份验证 (Client Authentication)，决定哪些用户可以从哪些主机连接到哪些数据库。

这些文件的位置因操作系统而异：

Windows： 通常位于 PostgreSQL 数据目录中 (例如，C:\Program Files\PostgreSQL\15\data)。
macOS (Homebrew)： /usr/local/var/postgres/
macOS (Postgres.app)： Postgres.app 的数据目录内。
Linux： /etc/postgresql/<version>/main/ (例如，/etc/postgresql/15/main/)

3.1 `postgresql.conf` 文件

以下是一些常用的 postgresql.conf 配置参数：

listen_addresses：指定 PostgreSQL 监听的 IP 地址。默认情况下，它只监听本地连接 (localhost)。要允许远程连接，可以将其设置为 '*' 或特定的 IP 地址。
port：指定 PostgreSQL 监听的端口。默认端口为 5432。
max_connections：设置允许的最大并发客户端连接数。
shared_buffers：设置 PostgreSQL 用于共享内存缓冲区的内存量。通常设置为系统总内存的 25%。
work_mem：设置用于排序和哈希操作的内存量。
maintenance_work_mem：设置用于维护操作 (例如，VACUUM、CREATE INDEX) 的内存量。
log_destination：设置日志输出目标,如stderr, csvlog, 和 syslog等
logging_collector：启用日志收集器，它是一个后台进程，负责捕获日志消息并将其写入日志文件。
log_directory：设置日志文件存储目录。
log_filename：设置日志文件名格式。

修改 postgresql.conf 后，需要重启 PostgreSQL 服务才能使更改生效。

3.2 `pg_hba.conf` 文件

pg_hba.conf 文件控制客户端身份验证。每行代表一个身份验证规则，格式如下：

TYPE DATABASE USER ADDRESS METHOD

TYPE：连接类型。
- local：Unix 域套接字连接。
- host：TCP/IP 连接 (包括 SSL 和非 SSL 连接)。
- hostssl：仅限 SSL 连接。
- hostnossl：仅限非 SSL 连接。
DATABASE：匹配的数据库名称。可以使用 all 表示所有数据库，也可以使用逗号分隔的列表。
USER：匹配的用户名。可以使用 all 表示所有用户，也可以使用逗号分隔的列表。
ADDRESS：匹配的客户端 IP 地址或主机名。可以使用 all 表示所有地址，也可以使用 CIDR 表示法 (例如，192.168.1.0/24)。
METHOD：身份验证方法。
- trust：无条件允许连接。
- reject：无条件拒绝连接。
- md5：要求客户端提供 MD5 加密的密码。
- password：要求客户端提供明文密码 (不推荐)。
- scram-sha-256: 使用更安全的SHA-256加密密码（推荐）
- ident：使用客户端操作系统用户名进行身份验证 (仅适用于 local 连接)。
- peer：获取客户端操作系统用户名 (仅适用于 local 连接)。

示例：

允许本地所有用户使用 MD5 密码连接到所有数据库：

local all all md5
允许来自 192.168.1.0/24 网络的所有用户使用 MD5 密码连接到所有数据库：

host all all 192.168.1.0/24 md5
允许用户 dbuser 从任何主机使用 scram-sha-256 密码连接到 mydatabase 数据库：

host mydatabase dbuser all scram-sha-256
* 拒绝所有其他连接
host all all all reject
修改 pg_hba.conf 后，需要重新加载 PostgreSQL 配置才能使更改生效 (无需重启服务)。可以使用以下命令：

bash sudo -u postgres psql -c "SELECT pg_reload_conf();"

3.3 常用配置参数

除了 postgresql.conf 和 pg_hba.conf 中的参数外，还可以通过其他方式配置 PostgreSQL：

环境变量： 可以使用环境变量来设置一些 PostgreSQL 客户端程序的默认值，例如 PGHOST、PGPORT、PGUSER、PGDATABASE 等。
命令行选项： PostgreSQL 的一些命令行工具 (例如 psql、createdb、dropdb) 允许您通过命令行选项来指定连接参数和其他设置。

4. 使用 PostgreSQL

4.1 连接到数据库

有多种方式可以连接到 PostgreSQL 数据库：

psql 命令行工具： psql 是 PostgreSQL 的交互式终端，允许您执行 SQL 命令、管理数据库对象等。
图形化界面工具： 有许多图形化界面 (GUI) 工具可用于连接和管理 PostgreSQL 数据库，例如 pgAdmin、DBeaver、DataGrip 等。
编程语言连接库： 各种编程语言 (例如 Python、Java、PHP、Ruby、Node.js 等) 都有用于连接和操作 PostgreSQL 数据库的库。

4.2 `psql` 命令行工具

psql 是 PostgreSQL 的标准命令行客户端。以下是一些常用的 psql 命令：

连接到数据库：

bash psql -h <host> -p <port> -U <username> -d <database>
* -h：主机名 (默认是 localhost)。
* -p：端口号 (默认是 5432)。
* -U：用户名 (默认是当前操作系统用户)。
* -d：数据库名称 (默认是与用户名同名的数据库)。
* -W: 强制psql在连接到数据库之前提示输入密码。

例如，要以 postgres 用户连接到本地的 mydatabase 数据库：

bash psql -U postgres -d mydatabase -W
* 列出所有数据库： \l 或 \list
* 切换数据库： \c <database_name>
* 列出当前数据库中的所有表： \dt
* 列出所有用户： \du
* 查看表结构： \d <table_name>
* 执行 SQL 查询： 直接输入 SQL 语句，以分号 (;) 结尾。
* 退出 psql： \q
* 获取帮助： \? (列出psql命令) 或 help (SQL 语法帮助)

4.3 创建数据库

使用 CREATE DATABASE 语句创建数据库：

sql CREATE DATABASE mydatabase;
使用psql创建:
sql createdb mydatabase

4.4 创建用户和角色

在 PostgreSQL 中，用户和角色是类似的概念。角色可以拥有数据库对象，并且可以被授予权限。用户是具有登录权限的角色。

使用 CREATE USER 语句创建用户：

sql CREATE USER dbuser WITH PASSWORD 'mypassword';
使用psql创建
sql createuser dbuser

使用 CREATE ROLE 语句创建角色：

sql CREATE ROLE myrole;

4.5 授权

使用 GRANT 语句向用户或角色授予权限：

```sql
-- 授予 dbuser 对 mydatabase 数据库的所有权限
GRANT ALL PRIVILEGES ON DATABASE mydatabase TO dbuser;

-- 授予 dbuser 对 mytable 表的 SELECT 权限
GRANT SELECT ON mytable TO dbuser;

-- 授予 myrole 对 myschema 模式中所有表的 INSERT 权限
GRANT INSERT ON ALL TABLES IN SCHEMA myschema TO myrole;
```

常见的权限包括：

SELECT：允许查询数据。
INSERT：允许插入数据。
UPDATE：允许更新数据。
DELETE：允许删除数据。
CREATE：允许创建对象 (例如，表、视图、函数)。
CONNECT：允许连接到数据库。
TEMPORARY (或 TEMP)：允许创建临时表。
ALL PRIVILEGES：授予所有权限。

4.6 创建表

使用 CREATE TABLE 语句创建表：

sql CREATE TABLE mytable ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, age INTEGER, email VARCHAR(255) UNIQUE );

SERIAL：自增整数类型，通常用作主键。
PRIMARY KEY：主键约束，确保列的唯一性和非空性。
VARCHAR(n)：可变长度字符串，最多 n 个字符。
INTEGER：整数类型。
NOT NULL：非空约束，确保列不包含空值。
UNIQUE：唯一约束，确保列中的值是唯一的。

4.7 数据类型

PostgreSQL 支持多种数据类型，包括：

数值类型： SMALLINT、INTEGER、BIGINT、REAL、DOUBLE PRECISION、NUMERIC、DECIMAL
字符串类型： CHAR、VARCHAR、TEXT
日期/时间类型： DATE、TIME、TIMESTAMP、INTERVAL
布尔类型： BOOLEAN
二进制类型： BYTEA
数组类型： INTEGER[]、VARCHAR[] 等
JSON 类型： JSON、JSONB
XML 类型： XML
UUID 类型： UUID
几何类型： POINT、LINE、POLYGON (需要 PostGIS 扩展)
网络地址类型： INET、CIDR、MACADDR

4.8 插入数据

使用 INSERT 语句插入数据：

sql INSERT INTO mytable (name, age, email) VALUES ('John Doe', 30, '[email protected]'); INSERT INTO mytable (name, age) VALUES ('Jane Smith', 25);

4.9 查询数据 (SELECT)

使用 SELECT 语句查询数据：

```sql
-- 查询所有列
SELECT * FROM mytable;

-- 查询特定列
SELECT name, age FROM mytable;

-- 使用 WHERE 子句过滤数据
SELECT * FROM mytable WHERE age > 28;

-- 使用 ORDER BY 子句排序数据
SELECT * FROM mytable ORDER BY name ASC; -- 升序
SELECT * FROM mytable ORDER BY age DESC; -- 降序

-- 使用 LIMIT 子句限制返回的行数
SELECT * FROM mytable LIMIT 10;

-- 使用 OFFSET 子句跳过前面的行
SELECT * FROM mytable LIMIT 5 OFFSET 5;

-- 使用 JOIN 连接多个表
SELECT *
FROM orders
JOIN customers ON orders.customer_id = customers.id;
```

4.10 更新数据 (UPDATE)

使用 UPDATE 语句更新数据：

sql UPDATE mytable SET age = 31 WHERE id = 1; UPDATE mytable SET email = '[email protected]' WHERE name = 'Jane Smith';

4.11 删除数据 (DELETE)

使用 DELETE 语句删除数据：

sql DELETE FROM mytable WHERE id = 2; DELETE FROM mytable WHERE age < 20;

4.12 基本 SQL 语法

注释： 单行注释以 -- 开头，多行注释使用 /* ... */。
大小写： SQL 关键字通常不区分大小写 (例如，SELECT、select、SeLeCt 都是等效的)，但字符串值和标识符 (例如，表名、列名) 可能区分大小写 (取决于数据库配置)。
分号： 每个 SQL 语句通常以分号 (;) 结尾。

4.13 使用图形化界面工具 (pgAdmin)

pgAdmin 是 PostgreSQL 最流行的图形化管理工具。它提供了直观的界面来管理数据库对象、执行 SQL 查询、查看服务器状态等。

下载和安装： 从 pgAdmin 官方网站 (https://www.pgadmin.org/download/) 下载并安装适用于您操作系统的 pgAdmin。
启动 pgAdmin： 打开 pgAdmin 应用程序。
添加服务器： 在 pgAdmin 中，右键单击 "Servers"，选择 "Register" -> "Server"。
填写连接信息： 输入服务器名称、主机名、端口、用户名和密码。
连接到数据库： 展开服务器节点，然后展开 "Databases" 节点。您可以浏览数据库对象、执行查询等。

5. 备份与恢复

定期备份数据库非常重要，以防止数据丢失。PostgreSQL 提供了 pg_dump、pg_dumpall 和 pg_restore 工具来执行备份和恢复操作。

5.1 `pg_dump` 和 `pg_dumpall`

pg_dump： 用于备份单个数据库。
pg_dumpall： 用于备份整个 PostgreSQL 集群 (所有数据库)。

pg_dump 示例：

bash pg_dump -U postgres -h localhost -p 5432 -F c -b -v -f mydatabase_backup.dump mydatabase

-U：用户名。
-h：主机名。
-p：端口号。
-F：输出格式 (c = 自定义格式，p = 纯文本 SQL 脚本，t = tar 归档)。
-b：包括大对象 (blobs)。
-v：详细模式 (显示更多信息)。
-f：输出文件名。
mydatabase: 要备份的数据库名

pg_dumpall 示例：

bash pg_dumpall -U postgres -h localhost -p 5432 -f all_databases_backup.sql

5.2 `pg_restore`

pg_restore 用于从 pg_dump 创建的自定义格式或 tar 格式备份文件中恢复数据库。

pg_restore 示例：

bash pg_restore -U postgres -h localhost -p 5432 -d newdatabase -v mydatabase_backup.dump

-d：要恢复到的数据库名称 (如果数据库不存在，需要先创建)。

如果要从纯文本 SQL 脚本恢复，可以直接使用 psql：

bash psql -U postgres -h localhost -p 5432 -d newdatabase -f all_databases_backup.sql

6. 高级主题 (简要介绍)

索引： 索引可以加速查询。PostgreSQL 支持多种索引类型，例如 B-tree、Hash、GiST、GIN 等。
事务： 事务允许您将一系列 SQL 操作组合成一个原子单元。
视图： 视图是虚拟表，其内容由查询定义。
存储过程： 存储过程是预编译的 SQL 代码块，可以在服务器端执行。
触发器： 触发器是在特定事件 (例如，INSERT、UPDATE、DELETE) 发生时自动执行的函数。
扩展： PostgreSQL 具有可扩展性，允许您通过扩展来添加新功能，例如 PostGIS (地理空间支持)、pg_trgm (模糊字符串匹配) 等。

7. 常见问题解答 (FAQ)

如何重置 postgres 用户的密码？
1. 以 postgres 用户身份连接到 psql：
  bash sudo -u postgres psql
2. 使用 ALTER USER 语句更改密码：
  sql ALTER USER postgres WITH PASSWORD 'newpassword';
3. 如何查看 PostgreSQL 日志？
  日志文件的位置取决于 log_directory 配置参数。通常可以在 PostgreSQL 数据目录中找到日志文件。
4. 如何优化 PostgreSQL 性能？
  优化 PostgreSQL 性能的方法有很多，包括：
5. 调整 postgresql.conf 中的参数 (例如，shared_buffers、work_mem)。
6. 创建适当的索引。
7. 使用 EXPLAIN 分析查询计划。
8. 定期运行 VACUUM 和 ANALYZE。
9. 使用连接池。
10. 升级硬件。
11. 如何开启远程连接？
12. 修改postgresql.conf文件中的listen_addresses为'*'
13. 修改pg_hba.conf文件，添加允许远程连接的规则
14. 重启PostgreSQL 服务
15. 如何使用PostgreSQL的全文搜索功能？
  PostgreSQL 提供了内置的全文搜索功能，允许您对文本数据进行高效的搜索。
16. 创建一个包含 tsvector 列的表。
17. 使用 to_tsvector() 函数将文本数据转换为 tsvector 类型。
18. 使用 to_tsquery() 函数将搜索查询转换为 tsquery 类型。
19. 使用 @@ 操作符执行全文搜索。
20. 可以创建 GIN 索引来加速全文搜索。

8. 总结

本教程详细介绍了 PostgreSQL 的安装、配置和使用。我们涵盖了从基本概念到高级主题的广泛内容。希望本教程能帮助您入门 PostgreSQL，并为您进一步探索其强大功能奠定基础。记住，实践是学习的关键，请尝试使用不同的命令和 SQL 语句来加深理解。如果您有任何问题，PostgreSQL 社区和文档资源将为您提供支持。

作者：admin

链接：https://hostlocvps.com/2025/03/24/postgresql%e6%95%99%e7%a8%8b%ef%bc%9a%e5%ae%89%e8%a3%85%e3%80%81%e9%85%8d%e7%bd%ae%e4%b8%8e%e4%bd%bf%e7%94%a8/

文章版权归作者所有，未经允许请勿转载。

THE END

如何用DeepL提升你的翻译效率？

<<上一篇

Ubuntu LTS 版本解析：稳定性和长期支持

下一篇>>