从零玩转 PostgreSQL：安装管理｜SQL 实战｜权限调优一站式学习
·
PostgreSQL 数据库
PostgreSQL 介绍与特性
官方首页：PostgreSQL: The world’s most advanced open source database
官方软件下载页：PostgreSQL: Downloads
PostgreSQL 介绍
PostgreSQL 是当前功能最强大的开源关系型数据库系统，支持跨平台的多种操作系统，基于 C 语言开发，通常简称为 PG 或 PGSQL。
PostgreSQL 宣称自己是世界上最先进的开源数据库。PostgreSQL 的忠实用户认为其性能与 Oracle 不相上下，且没有高昂的成本负担。
PostgreSQL 拥有悠久的历史，可以追溯到 1985 年加州大学伯克利分校的 POSTGRES 项目，它是 1977 年由数据库科学家 Michael Stonebraker 领导的 Ingres 项目的衍生品。为了专注于数据库理论的研究，伯克利分校在版本 4.2 时正式终止了 POSTGRES 项目。
1994 年，来自中国香港的两名伯克利研究生 Andrew Yu 和 Jolly Chen 向 POSTGRES 中增加了 SQL 语言解释器，将 Postgres 更名为 Postgres95，并将其源代码发布到互联网上，使其成为一个开源的数据库管理系统。
1996 年，Postgres95 这一名称已不合时宜，被更改为 PostgreSQL，表示其支持查询语言标准，同时版本号也重新从 6.0 开始计数。自版本 6.0 之后，涌现了众多后续发行版本。
PostgreSQL 是 100% 社区驱动的开源项目，由全球范围内上千名社区贡献者共同维护。PostgreSQL 提供功能完整的单一版本，而不像 MySQL 那样提供多个不同版本（如社区版、商业版及企业版）。
PostgreSQL 的开源协议采用自由的 BSD、MIT 类型，这类开源协议允许任何人在保留版权声明的前提下使用、复制、修改或分享代码。
可靠性是 PostgreSQL 最优先关注的特性。业界普遍认为 PostgreSQL 坚如磐石且设计精密，能够支持事务处理和关键任务应用。PostgreSQL 提供一流的文档服务，包括全面的免费在线手册以及旧版本手册的存档。社区支持非常出色，同时也有独立厂商提供商业支持。
数据一致性和完整性同样是 PostgreSQL 的高度优先事项。PostgreSQL 是完全符合 ACID 原则（原子性、一致性、隔离性、持久性）的数据库。PostgreSQL 对数据库访问提供强大的安全控制，不仅能够利用企业安全工具（如 Kerberos 和 OpenSSL 等），还可以根据自身业务规则自定义校验方法，以确保数据质量。数据库管理员最喜爱的功能之一是时间点恢复（point-in-time recovery，简称 PITR），它具备灵活性和高可用性特征，能够打造快速故障切换的热备份服务器，以及快照和恢复到特定时间点等能力。但这还不是全部，该项目提供了多种方法来管理 PostgreSQL，使其具备高可用性、负载均衡和同步功能，因此可以利用这些功能来满足特定需求。
PostgreSQL 与 MySQL 对比
核心定位与设计理念
MySQL
- 主打易用、轻量、读写快、运维简单，是互联网中小业务、建站、APP 后端的主流选择
- 架构偏简单高效，优先牺牲部分高级特性以换取速度
- 社区庞大，生态工具和云厂商支持全面（阿里云、腾讯云、AWS 等）
- 主流存储引擎：InnoDB（支持事务）、MyISAM（无事务，已淘汰）
PostgreSQL（PG）
- 标准企业级全功能关系型数据库，严格遵循 SQL 标准，学术出身，功能完整度极高
- 设计优先数据完整性、复杂查询、扩展能力、高级 SQL 特性
- 适合复杂业务、数据分析、GIS、金融、政企、多类型混合数据等场景
- 单一存储引擎（内置统一存储层），无引擎切换成本
核心功能对比表
| 对比维度 | MySQL(InnoDB) | PostgreSQL |
|---|---|---|
| SQL 标准兼容 | 部分兼容，存在大量私有语法，许多标准特性缺失 | 高度兼容 SQL:2003/2016，几乎完整支持标准语法 |
| 事务 / ACID | 完整支持，MVCC | 完整支持 MVCC，隔离级别更丰富 |
| 事务隔离级别 | 读未提交、读已提交、可重复读（默认）、串行化 | 多一级：可序列化快照隔离（Snapshot），无幻读问题 |
| 数据类型 | 以基础类型为主，扩展能力弱 | 极丰富：数组、JSONB、地理 GIS、范围类型、自定义类型、UUID、IP、树形 ltree |
| JSON 支持 | JSON/JSON5，索引性能一般 | JSONB（二进制 JSON，支持 GIN/GIST 高效索引，原生 JSON 操作能力强） |
| 窗口函数 | 8.0 之后支持，功能有限 | 完整全套窗口函数，支持复杂分析 |
| CTE 递归 | 8.0 支持递归 CTE | 原生完善递归 CTE，树形层级查询能力极强 |
| 存储过程 / 函数 | 语法简陋，仅支持 SQL / 简单逻辑 | 支持 PL/pgSQL、Python、Java、JS 等多语言存储过程 |
| 自定义扩展 | 扩展生态较少 | 海量开源扩展：pg_stat_statements、pg_cron、pg_vector、pg_graphql、TimescaleDB 时序库 |
| 全文检索 | 内置简易版本，能力较弱，依赖 ES | 原生高性能全文检索，支持分词、权重、模糊匹配 |
| 地理空间 GIS | 简单 Point，能力弱，需第三方插件 | 内置完整 PostGIS，专业 GIS 数据库标准，地图/轨迹首选 |
| 并行查询 | 有限并行，仅支持简单查询 | 完善并行：聚合、JOIN、扫描、分区全部可并行 |
| 分区表 | 分区类型少，性能一般，限制多 | 范围/列表/哈希/复合分区，分区裁剪高效，支持分区触发器 |
| 约束完整性 | 外键性能差，大量外键会拖慢写入 | 外键、检查约束、排他约束强，性能损耗低 |
| 向量数据库 | 无原生向量，依赖第三方 | 原生向量类型 vector，pg_vector 扩展实现 AI 向量检索 |
| 多版本并发 MVCC | 回滚日志 undo，事务 ID 复用，长事务易膨胀 | 元组多版本，vacuum 清理，长事务影响可控 |
性能差异
简单 CRUD、高并发写入（互联网短事务）
- MySQL 优势：轻量、内存开销更小，简单单表插入/更新/查询吞吐量更高
- 适用场景：高并发短请求、商品订单简单表、小程序、CMS 网站
复杂查询、多表 JOIN、聚合、报表、大数据量分析
- PostgreSQL 优势明显：优化器更智能，支持并行、哈希 JOIN、完善的统计信息
- 千万级多表关联、分组统计、子查询、递归树形数据场景下，PG 速度远快于 MySQL
读写混合、大量复杂约束/外键
PG 性能衰减更小，InnoDB 在外键较多时写入会出现明显卡顿。
大数据批量导入
PG 的 copy 批量加载性能远超 MySQL 的 load data。
并发与锁机制
- MySQL InnoDB
- 行锁基于索引，无索引时会降级为表锁
- 锁粒度较粗，更新范围大时极易触发表级意向锁冲突
- 默认可重复读隔离级别，存在幻读问题
- PostgreSQL
- MVCC 无共享锁，读写完全不阻塞
- 支持行级、页级、表级多种锁，粒度精细
- 快照隔离彻底解决幻读，适合高并发读写混合场景
扩展性
MySQL
- 横向扩展成熟：分库分表（Sharding-JDBC、MyCat）
- 主从复制简单，读写分离方案普及
- 垂直扩展上限较低，复杂单库上亿数据容易出现瓶颈
PostgreSQL
- 垂直扩展能力极强
- 支持自定义数据类型、操作符、索引方法、聚合函数
- 插件化架构，无需修改内核即可新增功能（时序、向量、图数据库）
- 复制：流复制、逻辑复制、多副本、级联复制
- 分库分表生态不如 MySQL 成熟，但 Citus 扩展可实现分布式 PG 集群
运维与成本
MySQL
- 学习门槛低，DBA 人才多，排错资料丰富
- 资源占用低，低配服务器即可运行
- 备份、监控、迁移工具生态极其完善
- 缺点：版本间语法差异大，升级容易踩坑
PostgreSQL
- 功能复杂，学习曲线陡峭，资深 DBA 相对较少
- 内存配置要求更高，大数据场景需要更大内存
- 版本升级平滑，SQL 标准统一，升级改动小
- 自带监控、日志、性能诊断工具完善
适用场景推荐
优先选 MySQL
- 中小型互联网业务，简单 CRUD、高并发短事务（电商订单、小程序、CMS、博客）
- 团队 MySQL 技术栈成熟，人手充足
- 需要快速分库分表实现水平扩容
- 硬件资源有限，需在低配服务器上部署
- 仅存储结构化简单数据，无复杂查询和分析需求
优先选 PostgreSQL
- 复杂业务系统：ERP、金融、政务、制造，强数据一致性、多外键约束
- 报表、BI、数据分析、多表复杂 JOIN、批量统计
- 存储混合数据：JSON 业务配置、地理轨迹 GIS、数组、树形结构
- AI 向量检索、时序数据（搭配 TimescaleDB）、图查询
- 需要递归查询、树形组织（菜单、组织架构、层级审批）
- 对 SQL 标准、数据完整性要求极高，不接受数据库自动隐式类型转换等不规范行为
一句话总结
- MySQL：轻量化、高吞吐简单业务首选，生态成熟易上手，适合互联网高并发短事务
- PostgreSQL：全能企业级数据库，SQL 标准完整、复杂查询/多类型数据/GIS/向量能力拉满，适合重业务、分析、混合数据场景
各类数据库性能比较
性能对比简表
| 负载场景 | 最优 | 中等 | 短板选手 |
|---|---|---|---|
| 简单单表高并发读写 | MySQL | PG、SQL Server | Oracle 单机 |
| 多表复杂 JOIN、报表 | PostgreSQL | Oracle | MySQL |
| 批量导入/大批量插入 | PostgreSQL(COPY) | MySQL | SQL Server |
| 海量分布式事务 | OceanBase | Oracle RAC | MySQL 分库分表 |
| JSON/JSONB 检索、数组索引 | PostgreSQL | MySQL | SQL Server |
| GIS、树形递归查询 | PostgreSQL | Oracle | MySQL |
PostgreSQL 各版本特性矩阵
https://www.postgresql.org/about/featurematrix/
pg_ctl 命令管理 PostgreSQL
pg_ctl 是一个实用的命令行工具，具备以下常见功能：
- 初始化 PostgreSQL 数据库实例
- 启动、终止或重启 PostgreSQL 数据库服务
- 查看 PostgreSQL 数据库服务的状态
- 让数据库实例重新读取配置文件，允许向指定的 PostgreSQL 进程发送信号
- 控制 standby 服务器切换为可读写模式
- 在 Windows 平台下允许为数据库实例注册或取消系统服务
pg_ctl 命令格式：
[root@localhost ~]# echo PATH=$PATH:/usr/pgsql-18/bin/ >> /etc/profile
[root@localhost ~]# source /etc/profile
[root@localhost ~]# su - postgres
[postgres@localhost ~]$ pg_ctl --help
pg_ctl is a utility to initialize, start, stop, or control a PostgreSQL server.
Usage:
pg_ctl init[db] [-D DATADIR] [-s] [-o OPTIONS]
pg_ctl start [-D DATADIR] [-l FILENAME] [-W] [-t SECS] [-s]
[-o OPTIONS] [-p PATH] [-c]
pg_ctl stop [-D DATADIR] [-m SHUTDOWN-MODE] [-W] [-t SECS] [-s]
pg_ctl restart [-D DATADIR] [-m SHUTDOWN-MODE] [-W] [-t SECS] [-s]
[-o OPTIONS] [-c]
pg_ctl reload [-D DATADIR] [-s]
pg_ctl status [-D DATADIR]
pg_ctl promote [-D DATADIR] [-W] [-t SECS] [-s]
pg_ctl logrotate [-D DATADIR] [-s]
pg_ctl kill SIGNALNAME PID
Common options:
-D, --pgdata=DATADIR location of the database storage area
-s, --silent only print errors, no informational messages
-t, --timeout=SECS seconds to wait when using -w option
-V, --version output version information, then exit
-w, --wait wait until operation completes (default)
-W, --no-wait do not wait until operation completes
-?, --help show this help, then exit
If the -D option is omitted, the environment variable PGDATA is used.
Options for start or restart:
-c, --core-files allow postgres to produce core files
-l, --log=FILENAME write (or append) server log to FILENAME
-o, --options=OPTIONS command line options to pass to postgres
(PostgreSQL server executable) or initdb
-p PATH-TO-POSTGRES normally not necessary
Options for stop or restart:
-m, --mode=MODE MODE can be "smart", "fast", or "immediate"
Shutdown modes are:
smart quit after all clients have disconnected
fast quit directly, with proper shutdown (default)
immediate quit without complete shutdown; will lead to recovery on restart
Allowed signal names for kill:
ABRT HUP INT KILL QUIT TERM USR1 USR2
Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>
初始化实例
初始化 PostgreSQL 数据库实例的命令如下：
# 先切换用户
[root@localhost ~]# su - postgres
# 两种方式
initdb [OPTION]... [DATADIR]
pg_ctl init[db] [-D DATADIR] [-s] [-o OPTIONS]
#pg_ctl命令调用initdb命令创建了一个新的PostgresQL数据库实例，参数说明如下。
-s #只打印错误和警告信息，不打印提示性信息。
-D DATADIR#指定数据库实例的数据目录。如果没有指定DATADIR，使用环境变量PGDATA指定的路径
-o options#为直接传递给initdb命令的参数
范例：创建新的数据库实例。
[postgres@localhost ~]$ pg_ctl init -D /var/lib/pgsql/18/data2
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are enabled.
creating directory /var/lib/pgsql/18/data2 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default "max_connections" ... 100
selecting default "shared_buffers" ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
/usr/pgsql-18/bin/pg_ctl -D /var/lib/pgsql/18/data2 -l logfile start
[postgres@localhost ~]$ ls /var/lib/pgsql/18/
backups data data2 initdb.log
[postgres@localhost ~]$ ls /var/lib/pgsql/18/data2
base pg_dynshmem pg_logical pg_replslot pg_stat pg_tblspc pg_wal postgresql.conf
global pg_hba.conf pg_multixact pg_serial pg_stat_tmp pg_twophase pg_xact
pg_commit_ts pg_ident.conf pg_notify pg_snapshots pg_subtrans PG_VERSION postgresql.auto.conf
服务管理
查看服务状态
查询数据库实例状态的命令如下：
pg_ctl status [-D DATADIR]
范例：
[postgres@localhost ~]$ pg_ctl status
pg_ctl: server is running (PID: 1253)
/usr/pgsql-18/bin/postgres "-D" "/var/lib/pgsql/18/data/"
[postgres@localhost ~]$ pg_ctl status -D /var/lib/pgsql/18/data2
pg_ctl: no server running
启动服务
启动 PostgreSQL 服务的命令：
pg_ctl start [-D DATADIR] [-l FILENAME] [-W] [-t SECS] [-s]
#参数说明如下。
start #启动数据库实例
-w #等待启动完成
-t #等待启动完成的等待秒数，默认为60秒
-s #只打印错误和警告信息，不打印提示性信息
-D datadir #指定数据库实例的数据目录
-l #服务器日志输出附加在"fi1ename"文件上，如果该文件不存在则创建它
-o options #声明要直接传递给postgres的选项，具体可见postgres命令的帮助
-p path#指定postgres可执行文件的位置。默认情况下postgres可执行文件来自和pg_ctl相同的目录，不必使用该选项。除非要进行一些不同寻常的操作，或者产生了postgres执行文件找不到的错误
-c #提高服务器的软限制(ulimit-c），尝试允许数据库实例在有异常时产生一个coredump文件，以便于问题定位和故障分析
范例：
# 无法启动
[postgres@localhost ~]$ pg_ctl start -D /var/lib/pgsql/18/data2
waiting for server to start....2026-07-14 21:23:43.140 CST [2042] LOG: redirecting log output to logging collector process
2026-07-14 21:23:43.140 CST [2042] HINT: Future log output will appear in directory "log".
stopped waiting
pg_ctl: could not start server
Examine the log output.
# 查看原因，默认端口被占用了
[postgres@localhost ~]$ cat /var/lib/pgsql/18/data2/log/postgresql-Tue.log
2026-07-14 21:24:01.326 CST [2048] LOG: could not bind IPv6 address "::1": Address already in use
2026-07-14 21:24:01.326 CST [2048] HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2026-07-14 21:24:01.326 CST [2048] LOG: could not bind IPv4 address "127.0.0.1": Address already in use
2026-07-14 21:24:01.326 CST [2048] HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2026-07-14 21:24:01.326 CST [2048] WARNING: could not create listen socket for "localhost"
2026-07-14 21:24:01.326 CST [2048] FATAL: could not create any TCP/IP sockets
2026-07-14 21:24:01.326 CST [2048] LOG: database system is shut down
# 修改端口
[postgres@localhost ~]$ vi /var/lib/pgsql/18/data2/postgresql.conf
64 port = 5433
# 正常启动
[postgres@localhost ~]$ pg_ctl start -D /var/lib/pgsql/18/data2
waiting for server to start....2026-07-14 21:33:19.585 CST [2098] LOG: redirecting log output to logging collector process
2026-07-14 21:33:19.585 CST [2098] HINT: Future log output will appear in directory "log".
done
server started
# 验证服务已经启动
[postgres@localhost ~]$ pg_ctl status -D /var/lib/pgsql/18/data2
pg_ctl: server is running (PID: 1973)
/usr/pgsql-18/bin/postgres "-D" "/var/lib/pgsql/18/data2"
停止服务
停止 PostgreSQL 数据库的命令如下：
pg_ctl stop [-D DATADIR] [-m SHUTDOWN-MODE] [-W] [-t SECS] [-s]
# 参数说明如下。
-W #不等待数据库停下来，命令就返回。
-m #指定停止的模式。前面已叙述过停止的几种模式了。
# 其它未说明的参数，其含义与启动数据库命令中的参数相同。
范例：
[postgres@localhost ~]$ pg_ctl stop -D /var/lib/pgsql/18/data2
waiting for server to shut down.... done
server stopped
[postgres@localhost ~]$ pg_ctl status -D /var/lib/pgsql/18/data2
pg_ctl: no server running
重启服务
重启 PostgreSQL 数据库的命令如下：
pg_ctl restart [-D DATADIR] [-m SHUTDOWN-MODE] [-W] [-t SECS] [-s]
范例：
[postgres@localhost ~]$ pg_ctl restart -D /var/lib/pgsql/18/data2
pg_ctl: PID file "/var/lib/pgsql/18/data2/postmaster.pid" does not exist
Is server running?
trying to start server anyway
waiting for server to start....2026-07-14 21:41:53.033 CST [2140] LOG: redirecting log output to logging collector process
2026-07-14 21:41:53.033 CST [2140] HINT: Future log output will appear in directory "log".
done
server started
[postgres@localhost ~]$ pg_ctl status -D /var/lib/pgsql/18/data2
pg_ctl: server is running (PID: 2140)
/usr/pgsql-18/bin/postgres "-D" "/var/lib/pgsql/18/data2"
加载配置
在配置文件中修改参数后，需要使用以下命令使参数生效。
# 修改配置文件postgresql.conf后，让修改生效的方法有两种
# 方法一：在操作系统使用下面命令
pg_ctl reload [-D DATADIR] [-s]
# 方法二：在 psql 中使用如下命令
postgres=# select pg_reload_conf();
#注意：加载配置操作只针对一些配置的修改生效，有些配置需要重新启动服务才能生效
范例：
[postgres@localhost ~]$ pg_ctl reload -D /var/lib/pgsql/18/data2
server signaled
# 注意：修改端口不支持reload，只能restart
[postgres@localhost ~]$ vi /var/lib/pgsql/18/data2/postgresql.conf
60 listen_addresses = '*'
[postgres@localhost ~]$ pg_ctl reload -D /var/lib/pgsql/18/data2
server signaled
[postgres@localhost ~]$ ss -nlt | grep 5433
LISTEN 0 200 127.0.0.1:5433 0.0.0.0:*
LISTEN 0 200 [::1]:5433 [::]:*
[postgres@localhost ~]$ pg_ctl restart -D /var/lib/pgsql/18/data2
waiting for server to shut down.... done
server stopped
waiting for server to start....2026-07-14 21:50:32.529 CST [2189] LOG: redirecting log output to logging collector process
2026-07-14 21:50:32.529 CST [2189] HINT: Future log output will appear in directory "log".
done
server started
[postgres@localhost ~]$ ss -nlt | grep 5433
LISTEN 0 200 0.0.0.0:5433 0.0.0.0:*
LISTEN 0 200 [::]:5433 [::]:*
[postgres@localhost ~]$
PostgreSQL 管理
配置文件介绍
PostgreSQL 使用环境变量 PGDATA 指向的目录作为数据存放目录。该目录在安装时指定，因此安装时需要指定一个合适的目录作为数据目录的根目录，而且每个 PG 数据库实例都需要这样一个目录。此数据目录的初始化通过 initdb 命令完成。
初始化完成后，PGDATA 数据目录下会生成三个配置文件。
postgresql.conf #数据库实例的主配置文件，基本上所有的配置参数都在此文件中。
pg_hba.conf #认证配置文件，配置了允许哪些IP的主机访问数据库，认证的方法是什么等信息。
pg_ident.conf #认证方式ident的用户映射文件。
数据库相关概念
数据库的结构组织
在一个 PostgreSQL 数据库系统中，数据的组织结构可以分为以下五层：
- 实例：一个 PostgreSQL 对应一个安装的数据目录 $PGDATA，即一个 instance 实例。
- 数据库：一个 PostgreSQL 数据库服务下可以管理多个数据库，当应用连接到一个数据库时，一般只能访问该数据库中的数据，而不能访问其他数据库中的内容。
- 默认情况下初始实例只有三个数据库：postgres、template0、template1。
- 模式：一个数据库可以创建多个不同的命名空间即 Schema，用于分隔不同的业务数据。
- 表和索引：一个数据库可以有多个表和索引。在 PostgreSQL 中表的术语称为 Relation，而在其他数据库中通常称为 Table。
- 行和列：每张表中包含大量列和行数据。在 PostgreSQL 中行的术语一般为"Tuple"，而在其他数据库中则称为"Row"。
PostgreSQL 中的术语
PostgreSQL 有一些术语与其他数据库不同，了解这些术语的含义，有助于更好地阅读 PostgreSQL 文档。
与其他数据库不同的术语如下：
- Relation：表示表 table 或索引 index，具体表示 Table 还是 Index 需视具体情况而定。
- Tuple：表示表中的行，在其他数据库中使用 Row 表示。
- Segment：每个表和索引都单独对应一个文件，即为 segment，如果文件大小超过 1GB，会创建多个名称相同但后缀不同的文件。
- Page：表示磁盘中的数据块。文件中以块为单位存放数据，默认值为 8KB，最大可为 32KB。
- Buffer：表示内存中的数据块。
模板数据库 template0 和 template1
template1 和 template0 是 PostgreSQL 的模板数据库。所谓模板数据库，就是在创建新 database 时，PostgreSQL 会基于模板数据库制作一份副本，其中包含所有的数据库设置和数据文件。
PostgreSQL 安装完成后会默认附带两个模板数据库：template1 和 template0。
默认模板库为 template1，也可以指定使用 template0。
例如：create database db1 template template0
不要对 template0 模板数据库进行任何修改，因为它是原始的干净模板。
如果其他模板数据库被损坏，基于该数据库制作一份副本即可恢复。
如果希望定制自己的模板数据库，请基于 template1 进行修改，或者另外创建一个模板数据库再修改。
template1 和 template0 的区别主要有两点：
- template1 可以连接，template0 不可以连接。
- 使用 template1 模板库建库时不可指定新的 encoding 和 locale，而 template0 可以。
注意：template0 和 template1 都不能被删除。
模式 schema
模式 schema 是数据库中的一个概念，可以将其理解为一个命名空间。不同的模式下可以有相同名称的表、函数等对象且互不冲突。提出模式的概念是为了便于管理，只要有权限，每个模式（schema）的对象可以互相调用。
在 PostgreSQL 中，一个数据库包含一个或多个模式，一个模式中又包含表、函数及操作符等数据库对象。
在 PostgreSQL 中，不能同时访问不同数据库中的对象，当要访问另一个数据库中的表或其他对象时，需要重新连接到该数据库，而模式没有此限制。用户在连接到一个数据库后，就可以同时访问该数据库中多个模式的对象。
通常情况下，创建和访问表时都不需要指定模式，实际上此时访问的都是 public 模式。每当创建一个新的数据库时，PostgreSQL 都会自动创建一个名为 public 的模式。当登录到该数据库时，如果没有特殊指定，都是以该 public 模式操作各种数据对象。
使用模式主要有以下几个原因：
- 允许多个用户在使用同一个数据库时彼此互不干扰。
- 将数据库对象放在不同的模式下，然后组织成逻辑组，使其更便于管理。
- 第三方应用可以放在不同的模式中，从而避免与其他对象的名称冲突。
#创建模弐
create schema schema_name;
#删除模式
drop schema schema_name;
#查看模式
\dn
要访问指定模式中的对象，需要先指定一个包含模式名及表名的名称，模式和表之间用一个"点"分隔，
如下：
schema_name.table_name
psql 工具介绍和基本用法
psql 是 PostgreSQL 中的一个命令行交互式客户端工具，类似 MySQL 的 mysql 和 Oracle 中的命令行工具 sqlplus，它允许交互地输入 SQL 或命令，然后将其发送给 PostgreSQL 服务器，再显示 SQL 或命令的执行结果。而且，输入的内容还可以来自于文件。此外，它还提供了一些命令和多种类似 shell 的特性来实现脚本编写，从而实现对大量任务的自动化处理。
虽然也可以使用 PostgreSQL 的图形化客户端工具（如 pgAdmin）来实现上述功能，但如果掌握了 psql 的使用方法，将会体会到它的便捷之处。因为 psql 是一个字符界面工具，没有图形化工具使用上的一些限制。psql 与 pgAdmin 之间的关系类似于 vi 与某些图形化工具的关系。
psql 的历史命令与补全功能
- 可以使用上下键调出以前使用过的命令或 SQL 语句。
- 连续按两次 tab 键可以补全命令或给出输入提示。
psql 命令格式
psql-h <hostname or ip> -p<端口> [数据库名称] -U [用户名称]
-h #指定要连接的数据库主机名或IP地址，默认localsocket登录（由配置项unix_socket_directories指定)
-p #指定连接的数据库端口
#最后两个参数是数据库名和用户名
#这些连接参数也可以用环境变量指定，比如：
export PGDATABASE=testdb
export PGHOST=192.168.108.200
export PGPORT=5432
export PGUSER=postgres
#然后运行psql即可，其效果与运行psql -h 192.168.108.200 -p 5432 testdb postgres相同。
范例：psql 本地登录 PGSQL
[root@localhost ~]# su - postgres
[postgres@localhost ~]$ psql
psql (18.4)
Type "help" for help.
postgres=#
范例：远程登录
#注意：默认PostgreSQL不支持远程登录，需要修改配置和授权才可以
#如果不指定he11odb数据库，默认连接和用户名同名的数据库
[postgres@localhost ~]$ psql -h 192.168.108.200 -p 5432 hellodb postgres
范例：psql 命令中直接执行 SQL
[postgres@localhost ~]$ psql -d postgres -c "select current_time"
current_time
--------------------
17:08:25.507614+08
(1 row)
范例：psql 命令中执行文件中的 SQL
[postgres@localhost ~]$ vim test.sql
select current_time;
\du
[postgres@localhost ~]$ psql
psql (18.4)
Type "help" for help.
# \i <文件名> 执行存储在外部文件中的sql语句或命令
postgres=# \i test.sql
current_time
--------------------
17:12:25.837359+08
(1 row)
List of roles
Role name | Attributes
-----------+------------------------------------------------------------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
连接管理
访问控制配置文件介绍
在 PostgreSQL 中，带有网络防火墙功能的文件 pg_hba.conf 可以控制允许哪些 IP 地址的机器访问数据库服务器。
HBA 的含义是 host-based authentication，即基于主机的认证，也就是实现 PostgreSQL 防火墙功能。
initdb 初始化数据目录时，会生成一个默认的 pg_hba.conf 文件。
pg_hba.conf 文件的格式由多条记录组成，每条记录占一行。
以 # 开头的注释行及空白行会被忽略。
一条记录由若干个以空格或制表符分隔的字段组成，如果字段用引号包围，则可以包含空格。
每条记录声明一种连接类型、一个客户端 IP 地址范围（如果与连接类型相关）、一个数据库名、一个用户名，以及对匹配这些参数的连接所使用的认证方法。
第一条匹配连接类型、客户端地址、连接请求的数据库名和用户名的记录将用于执行认证。如果选择了一条记录且认证失败，将不再考虑后续记录；如果没有匹配的记录，访问将被拒绝。即从上向下匹配，一旦匹配则不再向下检查。
每条记录可以是以下五种格式之一：
# local DATABASE USER METHOD [OPTIONS]
# host DATABASE USER ADDRESS METHOD [OPTIONS]
# hostssl DATABASE USER ADDRESS METHOD [OPTIONS]
# hostnossl DATABASE USER ADDRESS METHOD [OPTIONS]
# hostgssenc DATABASE USER ADDRESS METHOD [OPTIONS]
# hostnogssenc DATABASE USER ADDRESS METHOD [OPTIONS]
pg_hba.conf 文件是 PG 实例的防火墙配置文件。配置文件格式分为 5 个部分：
TYPE DATABASE USER ADDRESS METHOD
- 第 1 个字段只能取以下值。
- local：该记录匹配通过 UNIX 域套接字的连接认证。没有这种类型的记录，就不允许 UNIX 域套接字连接。当 psql 后面不指定主机名或 IP 地址时，即使用 UNIX 域套接字方式连接数据库。
- host：该记录匹配通过 TCP/IP 进行的连接，包括 SSL 和非 SSL 连接。
- hostssl：该记录匹配使用 TCP/IP 的 SSL 连接。必须是使用 SSL 加密的连接，且要使用该选项，编译服务器时必须开启 SSL 支持，启动服务器时必须开启 SSL 配置选项。
- hostnossl：该记录与 hostssl 相反，只匹配在 TCP/IP 上不使用 SSL 的连接请求。
- 第 2 个字段用于设置数据库名称，如果设置为 all，表示可以匹配任何数据库。注意：如果设置为 replication 时比较特殊，表示允许流复制连接，而不是允许连接到名为"replication"的数据库。
- 第 3 个字段用于设置用户名，如果设置为 all，表示可以匹配任何用户。
- 第 4 个字段 <ip/masklen> 表示允许哪些 IP 地址访问此服务器，如 192.168.1.10/32 表示只允许 192.168.1.10 这台主机访问数据库，192.168.1.0/24 表示 IP 地址前缀为 192.168.1.X 的主机都允许访问数据库服务器。
- 第 5 个字段表示认证方法，PostgreSQL 支持的认证配置方式很多，最常用的认证方法是 trust、reject、md5 和 ident。
#METHOD有如下值可选
md5：执行SCRAM-SHA-256或MD5身份验证加密密码来验证，是推荐使用的安全验证的方法
peer：从操作系统获取客户端的操作系统用户名，并检查它是否与请求的数据库用户名匹配。这仅适用于本地socket连接。
trust：允许无条件连接，允许任何PostgresQL用户身份登录，而无需密码或任何其他身份验证。
reject：拒绝任何条件连接，这对于从组中"过滤掉"某些主机非常有用。
sCram-sha-256：执行SCRAM-SHA-256身份验证以验证用户的密码。
password：要提供未加密的密码以进行身份验证。由于密码是通过网络以明文形式发送的，因此不建议使用
gss：使用GSSAPI对用户进行身份验证，这仅适用于TCP/IP连接。
sspi：使用sSPI对用户进行身份验证，这仅适用于windows。
ident：允许客户端上的特定操作系统用户连接到数据库。这种认证方式的使用场景是，客户端是主机上的某个操作系统用户，已经通过了操作系统的身份认证，是数据库服务器可以信任的用户，不需要在数据库层面再次检测身份。比如，如果配置了这种认证方式（配置中允许的用户名为dba）、这时在操作系统用户dba下，就能以数据库用户dba的身份连接到数据库。服务器为了确定接收到的连接请求确实是客户端机器上的dba用户发起的，而不是这台机器上其他用户发起的假冒请求，会向客户端机器上的ident服务发起请求，让ident服务查看此TCP连接是否是dba用户发起的，如果不是，说明是假冒，则认证失败。如果客户端通过本地连接到服务器，因为客户端与服务器在一台机器上，数据库服务器可以直接检查客户端用户的操作系统用户身份，就不需要向ident服务发送请求进行判断了。
1dap：使用LDAP服务器进行身份验证。
radius：使用RADIUS服务器进行身份验证。
cert：使用ssL客户端证书进行身份验证。
pam：使用操作系统提供的可插入身份验证模块（PAM）服务进行身份验证。
bsd：使用操作系统提供的BSD身份验证服务进行身份验证。
范例：
#如果一台机器只给数据库使用，而没有其他用途，则可以在pg_hba.conf中加上下面一行配置：
local all all trust
#该配置表示在这台机器上，任何操作系统的用户都可以使用任何数据库用户（包括数据库超级用户）连接到数据库而不需要任何密码。因为这台主机只供数据库使用，可以把不用的操作系统用户都禁止掉，以保证安全性。
#如果数据库中有一个用户"dba"，操作系统中也有一个用户"dba"
#在操作系统"dba"用户下连接数据库不需要密码验证的设置方法：
local all dba ident
#如果想在数据库主机上使用密码验证，可以使用下面的配置项：
local all all md5
#如果想让其他主机的连接都使用md5密码验证，则使用如下配置：
host all all 0.0.0.0/0 md5
#允许用户通过10.0.0.0/24的远程主机进行md5验证登录
# TYPE DATABASE USER ADDRESS METHOD
host all all 10.0.0.0/24 md5
#允许用户test通过任意远程主机进行md5验证登录test数据库
# TYPE DATABASE USER ADDRESS METHOD
host all test 0.0.0.0/24 md5
打开远程连接
默认安装完成的 PG 只能监听 local，如果需要远程连接，需要监听对外提供服务的 IP 地址。
范例：实现远程连接
#修改用户postgres密码
[postgres@localhost ~]$ psql
psql (18.4)
Type "help" for help.
postgres=# alter user postgres with password '123456';
#查看监听地址和端口，默认为127.0.0.1:5432
[postgres@localhost ~]$ ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 200 127.0.0.1:5432 0.0.0.0:*
[postgres@localhost ~]$ vim /var/lib/pgsql/18/data/postgresql.conf
60 listen_addresses = '*' #修改此行中的localhost为*，或者修改为0.0.0.0
#最后面加一行
[root@localhost ~]# vim /var/lib/pgsql/18/data/pg_hba.conf
123 host all all 0.0.0.0/0 md5
#重启服务生效
[postgres@localhost ~]$ /usr/pgsql-18/bin/pg_ctl restart -mf
#查看监听地址和端口
[postgres@localhost ~]$ ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
测试：使用笔记本进行测试。
常用操作
查看 psql 帮助
#列出psql帮助用法
help
#列出以\开头的命令，即psql的命令
\?
#列出所有SQL命令的帮助，注意：SQL语句必须以；结束
\h
#查看指定SQL的帮助
\h create database
\help create user
范例：
postgres=# help
You are using psql, the command-line interface to PostgreSQL.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
postgres=# \?
General
\copyright show PostgreSQL usage and distribution terms
\crosstabview [COLUMNS] execute query and display results in crosstab
\errverbose show most recent error message at maximum verbosity
\g [FILE] or ; execute query (and send results to file or |pipe)
\gexec execute query, then execute each value in its result
\gset [PREFIX] execute query and store results in psql variables
\gx [FILE] as \g, but forces expanded output mode
\q quit psql
\watch [SEC] execute query every SEC seconds
postgres=# \h
Available help:
ABORT CLUSTER CREATE VIEW DROP USER MAPPING
ALTER AGGREGATE COMMENT DEALLOCATE DROP VIEW
ALTER COLLATION COMMIT DECLARE END
ALTER CONVERSION COMMIT PREPARED DELETE EXECUTE
postgres=# \h create user
Command: CREATE USER
Description: define a new database role
Syntax:
CREATE USER name [ [ WITH ] option [ ... ] ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT connlimit
| [ ENCRYPTED ] PASSWORD 'password'
| VALID UNTIL 'timestamp'
| IN ROLE role_name [, ...]
| IN GROUP role_name [, ...]
| ROLE role_name [, ...]
| ADMIN role_name [, ...]
| USER role_name [, ...]
| SYSID uid
设置显示信息的格式
#后续查询将坚着显示，类似于MySQL中的\G
\x
#开启命令执行时长提示
\timing on
#显示详细的信息，可以打印出报出问题的源代码位置
\set VERBOSITY verbose
范例：
postgres=# \x
Expanded display is on.
postgres=# \l
List of databases
-[ RECORD 1 ]-----+----------------------
Name | postgres
Owner | postgres
Encoding | UTF8
Collate | en_US.UTF-8
Ctype | en_US.UTF-8
Access privileges |
-[ RECORD 2 ]-----+----------------------
Name | template0
Owner | postgres
Encoding | UTF8
Collate | en_US.UTF-8
Ctype | en_US.UTF-8
Access privileges | =c/postgres +
| postgres=CTc/postgres
-[ RECORD 3 ]-----+----------------------
Name | template1
Owner | postgres
Encoding | UTF8
Collate | en_US.UTF-8
Ctype | en_US.UTF-8
Access privileges | =c/postgres +
| postgres=CTc/postgres
postgres=# select pg_sleep(3);
-[ RECORD 1 ]
pg_sleep |
范例：
postgres=# \timing on
Timing is on.
postgres=# select pg_sleep(3);
-[ RECORD 1 ]
pg_sleep |
Time: 3004.184 ms (00:03.004)
范例：查看出错对应的源代码位置
postgres=# \set VERBOSITY verbose
postgres=# select gao;
ERROR: 42703: column "gao" does not exist
LINE 1: select gao;
^
LOCATION: errorMissingColumn, parse_relation.c:3827
#说明：错误对应的是parse_relation.c文件中的3827行中errorMissingColumn函数
数据库的创建和删除
创建数据库可以使用 SQL 语句 create database 实现，也可以利用 createdb 命令创建数据库。
createdb 是 SQL 命令 CREATE DATABASE 的封装。
createdb 命令语法格式如下：
[postgres@localhost ~]$ createdb --help
createdb creates a PostgreSQL database.
Usage:
createdb [OPTION]... [DBNAME] [DESCRIPTION]
Options:
-D, --tablespace=TABLESPACE default tablespace for the database #指定数据库默认表空间
-e, --echo show the commands being sent to the server #将createdb生成的命令发送到服务端
-E, --encoding=ENCODING encoding for the database #指定数据库编码
-l, --locale=LOCALE locale settings for the database #指定数据库的语言环境
--lc-collate=LOCALE LC_COLLATE setting for the database
--lc-ctype=LOCALE LC_CTYPE setting for the database
--builtin-locale=LOCALE builtin locale setting for the database
--icu-locale=LOCALE ICU locale setting for the database
--icu-rules=RULES ICU rules setting for the database
--locale-provider={builtin|libc|icu}
locale provider for the database's default collation
-O, --owner=OWNER database user to own the new database
-S, --strategy=STRATEGY database creation strategy wal_log or file_copy
-T, --template=TEMPLATE template database to copy #指定创建此数据库的模板
-V, --version output version information, then exit
-?, --help show this help, then exit # 显示createdb命令的帮助信息
Connection options:
-h, --host=HOSTNAME database server host or socket directory #指定服务器的主机明
-p, --port=PORT database server port #指定服务器监听的端口
-U, --username=USERNAME user name to connect as #连接数据库的用户名
-w, --no-password never prompt for password #忽略输入密码
-W, --password force password prompt #连接是强制要求输入密码
--maintenance-db=DBNAME alternate maintenance database
By default, a database with the same name as the current user is created.
Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>
删除数据库可以使用 SQL 语句 drop database 实现。
范例：创建数据库
#方法1
[postgres@localhost ~]$ createdb -h localhost -p 5432 -U postgres testdb
Password:123456
#方法2
[postgres@localhost ~]$ psql
psql (18.4)
Type "help" for help.
postgres=# create database testdb;
范例：删除数据库
[postgres@localhost ~]$ psql
psql (18.4)
Type "help" for help.
postgres=# drop database testdb;
DROP DATABASE
范例：查看数据库存放目录的路径
postgres=# select oid,datname from pg_database;
oid | datname
-----+-----------
5 | postgres
1 | template1
4 | template0
(3 rows)
[postgres@localhost ~]$ ls /var/lib/pgsql/18/data/base/
1 4 5
管理和查看模式
一个数据库包含一个或多个已命名的模式，模式又包含表。模式还可以包含其他对象，包括数据类型、函数、操作符等。同一个对象名可以在不同的模式里使用而不会导致冲突；例如，schema1 和 schema2 都可以包含一个名为 test 的表。
#创建模式语法
create schema schema_name;
#删除模式语法
drop schema schema_name;
#列出所有schema
postgres=# \dn
List of schemas
Name | Owner
--------+-------------------
public | pg_database_owner
(1 row)
# 创建schema
postgres=# create schema sch;
CREATE SCHEMA
postgres=# \dn
List of schemas
Name | Owner
--------+-------------------
public | pg_database_owner
sch | postgres
(2 rows)
# 创建t1表，默认在public schema
postgres=# create table t1(id int);
CREATE TABLE
# 因为public schema已经有t1 无法创建
postgres=# create table t1(id int);
ERROR: relation "t1" already exists
# 在其他schema能够创建t1表
postgres=# create table sch.t1(id int);
CREATE TABLE
# 默认查看public schema
postgres=# \dt
List of tables
Schema | Name | Type | Owner
--------+------+-------+----------
public | t1 | table | postgres
(1 row)
# 查看指定schema的表
postgres=# \dt sch.t1
List of tables
Schema | Name | Type | Owner
--------+------+-------+----------
sch | t1 | table | postgres
(1 row)
查看和连接数据库
# 列出所有数据库名，相当于MySQL中的show databases;
postgres-# \l
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges
-----------+----------+----------+-----------------+-------------+-------------+--------+-----------+-----------------------
postgres | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | |
template0 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
(3 rows)
# 创建一个数据库，观察下变化
postgres=# create database testdb;
CREATE DATABASE
# 再次查看
postgres=# \l
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges
-----------+----------+----------+-----------------+-------------+-------------+--------+-----------+-----------------------
postgres | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | |
template0 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
`testdb` | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | |
(4 rows)
# 显示数据库详细信息，比如大小
postgres=# \l+
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges | Size | Tablespace | Desc
ription
-----------+----------+----------+-----------------+-------------+-------------+--------+-----------+-----------------------+---------+------------+--------------------
------------------------
postgres | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | | 7646 kB | pg_default | default administrat
ive connection database
template0 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | =c/postgres +| 7489 kB | pg_default | unmodifiable empty
database
| | | | | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | =c/postgres +| 7718 kB | pg_default | default template fo
r new databases
| | | | | | | | postgres=CTc/postgres | | |
testdb | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | | 7561 kB | pg_default |
(4 rows)
# 查看当前连接信息
postgres-# \c
You are now connected to database "postgres" as user "postgres".
# 连接数据库，相当于use
postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
# 查看当前连接详细信息
testdb=# \conninfo
Connection Information
Parameter | Value
----------------------+-----------------
Database | testdb
Client User | postgres
Socket Directory | /run/postgresql
Server Port | 5432
Options |
Protocol Version | 3.0
Password Used | false
GSSAPI Authenticated | false
Backend PID | 1972
SSL Connection | false
Superuser | on
Hot Standby | off
(12 rows)
管理表
PostgreSQL 支持多种数据类型来实现表结构的创建。
范例：查看支持的数据类型
testdb=# select typname from pg_type;
typname
----------------------------------------
bool
bytea
char
name
int8
int2
int2vector
int4
regproc
text
oid
tid
xid
cid
oidvector
pg_type
pg_attribute
pg_proc
pg_class
json
xml
......
(621 rows)
范例：管理表
testdb=# \c testdb
You are now connected to database "testdb" as user "postgres".
testdb=# create table tb1 (id serial primary key,name text);
CREATE TABLE
testdb=# insert into tb1 (name) select (md5(random()::text)) from generate_series (2,10);
INSERT 0 9
testdb=# select * from tb1;
id | name
----+----------------------------------
1 | 4534485a0d2517117a8848ad869b15bb
2 | b206a46cd9beca36fdad1dd651723d65
3 | a7126c510f91184d694d8e3f06bd0c3a
4 | 1b5e08e3046cea04f2157caec1594724
5 | 99d882abb4823d4fdd5d7f9081382532
6 | 3d5cc2b18c8aacf0170e674ed20cb249
7 | 5f8909d5ce6241f4fe01734bd140f044
8 | de397cee8c6746b7b61f1956e5597a52
9 | ec41e0a9c28e51af59c1ace7635b9471
(9 rows)
#PostgreSQL中插入100万条记录观察世间
testdb=# \timing on
Timing is on.
testdb=# insert into tb1 (name) select (md5(random()::text)) from generate_series (1,1000000);
INSERT 0 1000000
Time: 13892.772 ms (00:13.893) #13秒
# 复制表结构，不复制数据
testdb=# create table tb2 (like tb1);
CREATE TABLE
testdb=# \d tb2
Table "public.tb2"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
name | text | | |
testdb=# select * from tb2;
id | name
----+------
(0 rows)
Time: 1.141 ms
testdb=# drop table tb2;
DROP TABLE
查看表和表信息
#列出所有表，视图，序列
testdb=# \d
List of relations
Schema | Name | Type | Owner
--------+------------+----------+----------
public | tb1 | table | postgres
public | tb1_id_seq | sequence | postgres
(2 rows)
#列出public的schema中所有的表名，相当于show tables;
testdb=# \dt
List of tables
Schema | Name | Type | Owner
--------+------+-------+----------
public | tb1 | table | postgres
(1 row)
#查看tb1的表信息
testdb=# \dt tb1
List of tables
Schema | Name | Type | Owner
--------+------+-------+----------
public | tb1 | table | postgres
(1 row)
#支持通配符*和？，以下显示所有t开头的表
testdb=# \dt t*
List of tables
Schema | Name | Type | Owner
--------+------+-------+----------
public | tb1 | table | postgres
(1 row)
#列出sch模式的表结构
testdb=# create schema sch;
testdb=# create table sch.t1(id int);
testdb=# \dt sch.*
List of tables
Schema | Name | Type | Owner
--------+------+-------+----------
sch | t1 | table | postgres
(1 row)
#查看t1的表结构，相当于desc
testdb=# \d tb1
Table "public.tb1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------------------------------
id | integer | | not null | nextval('tb1_id_seq'::regclass)
name | text | | |
Indexes:
"tb1_pkey" PRIMARY KEY, btree (id)
#列出所有表信息，包括大小
testdb=# \dt+
List of tables
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+------+-------+----------+-------------+---------------+-------+-------------
public | tb1 | table | postgres | permanent | heap | 65 MB |
(1 row)
#列出tb1表信息
testdb=# \dt tb1
List of tables
Schema | Name | Type | Owner
--------+------+-------+----------
public | tb1 | table | postgres
(1 row)
# 列出tb1表信息的大小信息
testdb=# \dt+ tb1
List of tables
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+------+-------+----------+-------------+---------------+-------+-------------
public | tb1 | table | postgres | permanent | heap | 65 MB |
(1 row)
#查看所有表
testdb=# select * from pg_tables;
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
--------------------+--------------------------+------------+------------+------------+----------+-------------+-------------
pg_catalog | pg_statistic | postgres | | t | f | f | f
pg_catalog | pg_type | postgres | | t | f | f | f
public | tb1 | postgres | | t | f | f | f
pg_catalog | pg_foreign_table | postgres | | t | f | f | f
pg_catalog | pg_authid | postgres | pg_global | t | f | f | f
sch | t1 | postgres | | f | f | f | f
pg_catalog | pg_statistic_ext_data | postgres | | t | f | f | f
pg_catalog | pg_user_mapping | postgres | | t | f | f | f
......
#查看表大小
testdb=# select pg_total_relation_size('tb1');
pg_total_relation_size
------------------------
90816512
(1 row)
testdb=# select pg_total_relation_size('tb1')/1024/1024||'MB';
?column?
----------
86MB
(1 row)
范例：查看表对应的文件路径
postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
testdb=# select oid,datname from pg_database where datname = 'testdb';
oid | datname
-------+---------
16402 | testdb
(1 row)
testdb=# select relid from pg_stat_all_tables where relname='t1';
relid
-------
16431
(1 row)
testdb=# \q
[postgres@localhost ~]$ ll /var/lib/pgsql/18/data/base/16402/16431
-rw------- 1 postgres postgres 0 Jul 26 11:14 /var/lib/pgsql/18/data/base/16402/16431
表的 CRUD
SQL 的 CRUD，即 Insert、update、delete、select 四条语句范例：
# 清空环境
postgres=# \c testdb;
You are now connected to database "testdb" as user "postgres".
testdb=# drop table tb1;
DROP TABLE
# 创建表tb1
testdb=# create table tb1 (id serial,name varchar(10));
CREATE TABLE
# 查看有哪些表
testdb=# \dt
List of tables
Schema | Name | Type | Owner
--------+------+-------+----------
public | tb1 | table | postgres
(1 row)
# 查看tb1表结构；
testdb=# \d tb1;
Table "public.tb1"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------------------------------
id | integer | | not null | nextval('tb1_id_seq'::regclass)
name | character varying(10) | | |
# 向tb1表插入数据
testdb=# insert into tb1(name)values('gao');
INSERT 0 1
testdb=# insert into tb1(name)values('zhang');
INSERT 0 1
#验证插入的数据
testdb=# select * from tb1;
id | name
----+-------
1 | gao
2 | zhang
# 更新tb1表中id为2的name为ma
testdb=# update tb1 set name='ma' where id=2;
UPDATE 1
#验证改了没
testdb=# select * from tb1;
id | name
----+------
1 | gao
2 | ma
(2 rows)
# 删除tb1表中id为2的数据
testdb=# delete from tb1 where id=2;
DELETE 1
#验证删除了没
testdb=# select * from tb1;
id | name
----+------
1 | gao
(1 row)
#清空表
testdb=# truncate tb1;
TRUNCATE TABLE
testdb=# truncate table tb1;
TRUNCATE TABLE
testdb=# select * from tb1;
id | name
----+------
(0 rows)
索引管理
范例：创建和删除索引
# 还原环境
testdb=# drop table tb1;
# 创建表
testdb=# create table tb1(id int,info text,crt_time timestamp);
CREATE TABLE
testdb=# insert into tb1 select generate_series(1,1000000),md5(random()::text),clock_timestamp();
INSERT 0 1000000
testdb=# select * from tb1 limit 3;
id | info | crt_time
----+----------------------------------+----------------------------
1 | 67d6285a42ba94dedd86048a0008683d | 2026-07-26 20:34:26.141821
2 | c59be31fca89ae637ed6ce4cff13e141 | 2026-07-26 20:34:26.142127
3 | 60bfbd9412a4b788901d70edff428255 | 2026-07-26 20:34:26.142133
(3 rows)
# 创建索引
testdb=# create index idx_tb1_id on tb1(id);
CREATE INDEX
testdb=# \d tb1
Table "public.tb1"
Column | Type | Collation | Nullable | Default
----------+-----------------------------+-----------+----------+---------
id | integer | | |
info | text | | |
crt_time | timestamp without time zone | | |
Indexes:
"idx_tb1_id" btree (id)
#删除索引
testdb=# drop index idx_tb1_id;
DROP INDEX
testdb=# \d tb1
Table "public.tb1"
Column | Type | Collation | Nullable | Default
----------+-----------------------------+-----------+----------+---------
id | integer | | |
info | text | | |
crt_time | timestamp without time zone | | |
范例：使用索引
#打开时间
testdb=# \timing on
Timing is on.
# 创建索引
testdb=# create index idx_tb1_id on tb1(id);
# 查询条件是索引列
testdb=# explain analyze select * from tb1 where id = 99999;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Index Scan using idx_tb1_id on tb1 (cost=0.42..8.44 rows=1 width=45) (actual time=0.030..0.032 rows=1.00 loops=1)
Index Cond: (id = 99999)
Index Searches: 1
Buffers: shared hit=4
Planning Time: 0.099 ms
Execution Time: 0.054 ms
(6 rows)
Time: 0.777 ms
#查询条件不是索引列
testdb=# explain analyze select * from tb1 where info ='67d6285a42ba94dedd86048a0008683d';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..15554.43 rows=1 width=45) (actual time=0.283..31.611 rows=1.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=9346
-> Parallel Seq Scan on tb1 (cost=0.00..14554.33 rows=1 width=45) (actual time=15.751..25.377 rows=0.33 loops=3)
Filter: (info = '67d6285a42ba94dedd86048a0008683d'::text)
Rows Removed by Filter: 333333
Buffers: shared hit=9346
Planning Time: 0.079 ms
Execution Time: 31.634 ms
(10 rows)
Time: 32.308 ms
#关闭索引
testdb=# set enable_indexscan=off;
SET
Time: 0.487 ms
testdb=# set enable_bitmapscan=off;
SET
Time: 0.363 ms
# 再次查询全表扫描
testdb=# explain analyze select * from tb1 where id = 99999;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..15554.43 rows=1 width=45) (actual time=18.017..20.143 rows=1.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=9346
-> Parallel Seq Scan on tb1 (cost=0.00..14554.33 rows=1 width=45) (actual time=10.684..15.061 rows=0.33 loops=3)
Filter: (id = 99999)
Rows Removed by Filter: 333333
Buffers: shared hit=9346
Planning Time: 0.087 ms
Execution Time: 20.167 ms
(10 rows)
Time: 21.092 ms
表空间
#列出所有表空间，实际上PostgresQL中的表空间就是对应一个目录，放在这个表空间的表，就是把表的数据文件放到这个表空间下。
testdb=# \db
List of tablespaces
Name | Owner | Location
------------+----------+----------
pg_default | postgres |
pg_global | postgres |
(2 rows)
testdb=# select * from tb1;
id | info | crt_time
---------+----------------------------------+----------------------------
1 | 67d6285a42ba94dedd86048a0008683d | 2026-07-26 20:34:26.141821
2 | c59be31fca89ae637ed6ce4cff13e141 | 2026-07-26 20:34:26.142127
3 | 60bfbd9412a4b788901d70edff428255 | 2026-07-26 20:34:26.142133
4 | 9b20c15670378a9a52e44e57bbcccc3a | 2026-07-26 20:34:26.142142
......
testdb=# copy tb1 to '/tmp/tb1.txt';
COPY 1000000
Time: 299.741 ms
[postgres@localhost ~]$ cat /tmp/tb1.txt | wc -l
1000000
范例：表空间 pg_tblspc 目录
[postgres@localhost ~]$ mkdir ts1
testdb=# create tablespace ts1 location '/var/lib/pgsql/ts1';
CREATE TABLESPACE
testdb=# \db
List of tablespaces
Name | Owner | Location
------------+----------+--------------------
pg_default | postgres |
pg_global | postgres |
ts1 | postgres | /var/lib/pgsql/ts1
(3 rows)
[postgres@localhost ~]$ readlink /var/lib/pgsql/18/data/pg_tblspc/32781
/var/lib/pgsql/ts1
查看系统信息
可以通过系统函数查看系统信息，也可以通过 show/set 查看和修改配置。
# 查看版本信息
testdb=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 18.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-28), 64-bit
(1 row)
#查看数据库启动时间
testdb=# select pg_postmaster_start_time();
pg_postmaster_start_time
-------------------------------
2026-07-26 20:26:47.209345+08
(1 row)
#查看加载配置文件时间
[postgres@localhost ~]$ pg_ctl reload
server signaled
[postgres@localhost ~]$ psql
psql (18.4)
Type "help" for help.
postgres=# select pg_conf_load_time();
pg_conf_load_time
-------------------------------
2026-07-26 21:30:34.496232+08
(1 row)
#查看时区和时间
postgres=# show timezone;
TimeZone
---------------
Asia/Shanghai
(1 row)
#临时修改时区
postgres=# set timezone='Asia/Shanghai';
SET
#永久修改时区
[postgres@localhost ~]$ vim /var/lib/pgsql/18/data/postgresql.conf
783 timezone = 'Asia/Shanghai'
[postgres@localhost ~]$ pg_ctl reload
server signaled
[postgres@localhost ~]$ psql
psql (18.4)
Type "help" for help.
postgres=# select now();
now
-------------------------------
2026-07-26 21:34:15.716666+08
(1 row)
#查看当前用户
postgres=# select user;
user
----------
postgres
(1 row)
postgres=# select current_user;
current_user
--------------
postgres
(1 row)
postgres=# select session_user;
session_user
--------------
postgres
(1 row)
#查看当前数据库
postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
testdb=# select current_database();
current_database
------------------
testdb
(1 row)
#查看指定的当前参数配置
testdb=# show port;
port
------
5432
(1 row)
testdb=# show archive_mode;
archive_mode
--------------
off
(1 row)
#查看参数
SHOW NAME;
SHOW ALL;
postgres=# show all;
name | setting |
description
---------------------------------------------+----------------------------------------+-------------------------------------------------------
------------------------------------------------------------------------------------
allow_alter_system | on | Allows running the ALTER SYSTEM command.
allow_in_place_tablespaces | off | Allows tablespaces directly inside pg_tblspc, for test
ing.
allow_system_table_mods | off | Allows modifications of the structure of system tables
.
application_name | psql | Sets the application name to be reported in statistics
and logs.
archive_cleanup_command | | Sets the shell command that will be executed at every
restart point.
archive_command | (disabled) | Sets the shell command that will be called to archive
a WAL file.
#修改配置
postgres=# set maintenance_work_mem to '128MB';
SET
postgres=# show maintenance_work_mem;
maintenance_work_mem
----------------------
128MB
(1 row)
范例：explain 可以查看 SQL 执行计划。
explain：只输出执行计划，不会真实执行 SQL；
explain analyze：真实执行语句，获取真实执行时间与 buffer 统计。
#explain可以查看SQL执行的计划
postgres=# explain select * from t1;
QUERY PLAN
------------------------------------------------------
Seq Scan on t1 (cost=0.00..35.50 rows=2550 width=4)
(1 row)
postgres=# explain analyze select * from t1;
QUERY PLAN
---------------------------------------------------------------------------------------------------
Seq Scan on t1 (cost=0.00..35.50 rows=2550 width=45) (actual time=0.008..0.008 rows=0.00 loops=1)
Planning Time: 0.045 ms
Execution Time: 0.022 ms
(3 rows)
postgres=# explain analyze verbose select * from t1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Seq Scan on public.t1 (cost=0.00..35.50 rows=2550 width=4) (actual time=0.009..0.009 rows=0.00 loops=1)
Output: id
Planning Time: 0.045 ms
Execution Time: 0.027 ms
(4 rows)
openEuler 是由开放原子开源基金会孵化的全场景开源操作系统项目，面向数字基础设施四大核心场景（服务器、云计算、边缘计算、嵌入式），全面支持 ARM、x86、RISC-V、loongArch、PowerPC、SW-64 等多样性计算架构
更多推荐
2
0- 0
已为社区贡献8条内容
所有评论(0)