数据库教程FGMT45‑PostgreSQL数据库基础入门培训

举报
风哥数据库教程 发表于 2026/09/15 11:21:42 2026/09/15
【摘要】 数据库教程FGMT45‑PostgreSQL数据库基础入门培训 前言与内容大纲PostgreSQL是一款功能强大的开源对象‑关系型数据库，具备完善SQL标准兼容、MVCC多版本并发控制、丰富数据类型、自定义扩展能力，广泛应用于政务、金融、互联网、地理信息、企业业务系统。PostgreSQL18作为新一代重要版本，新增异步IO、uuidv7、虚拟生成列、跳过扫描索引优化等大量能力，风哥教程本文...

数据库教程FGMT45‑PostgreSQL数据库基础入门培训

前言与内容大纲

PostgreSQL是一款功能强大的开源对象‑关系型数据库，具备完善SQL标准兼容、MVCC多版本并发控制、丰富数据类型、自定义扩展能力，广泛应用于政务、金融、互联网、地理信息、企业业务系统。PostgreSQL18作为新一代重要版本，新增异步IO、uuidv7、虚拟生成列、跳过扫描索引优化等大量能力，风哥教程本文以PostgreSQL18作为案例，面向DBA、运维工程师、数据库开发、云计算工程师，完成从底层理论、环境部署、对象管理、SQL开发、权限体系、备份恢复、基础运维全套内容讲解。风哥教程本文严格按照64G内存8CPU硬件规格进行参数配置；使用两台主机fgedu‑net‑cn1作为主数据库实例主机，fgedu‑net‑cn2作为测试、备份恢复演练主机；文件根路径统一使用/fgedudb；数据库名、实例名fgedudb，业务用户名fgedu。风哥教程本文所有实战操作均建议在隔离测试环境执行，禁止直接在线上生产环境执行未验证命令。

风哥教程本文完整知识大纲：

  1. PostgreSQL数据库产品定位与PostgreSQL版本新特性
  2. PostgreSQL核心体系结构理论：集群、进程模型、存储架构、WAL预写日志
  3. MVCC多版本并发控制原理、元组、死元组与Autovacuum自动清理机制
  4. 对象逻辑概念：database、schema、角色role、表空间tablespace、模板库template0/template1
  5. PostgreSQL权限与访问控制体系，pg_hba.conf认证机制
  6. PostgreSQL基于64G内存8CPU硬件的核心参数理论说明
  7. 实战一：操作系统内核预调优、PostgreSQL源码部署与数据库集群初始化
  8. 实战二：数据库、schema、业务角色用户创建，pg_hba.conf访问权限配置
  9. 实战三：psql客户端工具完整实操，元命令、数据库对象DDL操作
  10. 实战四：表、索引、约束、视图、序列开发实战，DML增删改查事务实操
  11. 实战五：表空间tablespace创建与对象迁移实操
  12. 实战六：逻辑备份恢复pg_dump/pg_dumpall/pg_restore完整演练
  13. 实战七：物理备份pg_basebackup基础备份与WAL归档配置实操
  14. 实战八：基础运维，日志查看、会话管理、vacuum维护、简单故障排查
  15. 实战九：异地主机fgedu‑net‑cn2备份集恢复演练
  16. PostgreSQL入门阶段常见故障与排错思路

网上搜索风哥教程可以学习全套数据库教程

一、PostgreSQL基础理论知识

1.1 PostgreSQL产品定位与PostgreSQL版本核心新特性

PostgreSQL属于对象‑关系型数据库（ORDBMS），完全开源BSD协议，支持复杂数据类型JSONB、数组、范围类型、几何地理类型，支持自定义函数、存储过程、触发器、扩展插件生态。和MySQL相比，PostgreSQL对SQL标准遵从度更高，复杂查询优化能力强，适合OLTP业务、OLAP混合分析、GIS地理业务场景。

PostgreSQL重要更新点，风哥教程本文重点梳理：

  1. 异步AIO子系统，顺序扫描、位图堆扫描、vacuum清理IO性能提升；
  2. uuidv7内置函数，生成带时间戳有序UUID，业务主键场景性能优化；
  3. 虚拟生成列，计算逻辑读取时完成，不占用磁盘存储；
  4. 多列B‑tree索引支持跳过扫描，提升前缀不完整条件索引查询效率；
  5. INSERT/UPDATE/DELETE/MERGE支持RETURNING子句访问OLD与NEW元组；
  6. pg_upgrade升级工具可以保留原有优化器统计信息，升级后避免统计信息缺失引发执行计划退化；
  7. OAuth认证接入，丰富数据库身份认证方式。

需要明确概念：一个PostgreSQL实例就是一个database cluster数据库集群，一套集群管理多个database数据库；集群拥有统一一套WAL日志、共享系统目录；不同database之间默认不能直接跨库查询，必须通过dblink或者fdw外部数据封装实现跨库访问。

风哥 itpux‑com

1.2 PostgreSQL进程模型体系理论

PostgreSQL采用多进程模型，和MySQL多线程模型存在明显区别，每一条客户端连接会fork独立backend后端服务进程。

  1. postmaster主守护进程：实例总入口，监听TCP/IP与unix socket连接，管理子进程生命周期，崩溃自动重启子进程；
  2. backend后端进程：每一个客户端会话对应独立backend进程，处理该会话全部SQL解析、优化、执行；连接数越多操作系统进程开销越大，生产需要合理管控最大连接数max_connections；
  3. wal writer进程：WAL预写日志刷盘后台进程，负责把内存WAL缓冲区写入磁盘WAL文件；
  4. checkpointer检查点进程：触发检查点，将内存脏数据页批量落盘，控制崩溃恢复时间；
  5. autovacuum launcher/worker自动清理进程：回收死元组dead tuple，更新表统计信息；
  6. bgwriter后台写进程：预刷脏页，平滑IO压力；
  7. archiver归档进程：开启归档模式后，把已经完成切换的WAL文件复制到归档目录，用于时间点PITR恢复。

1.3 存储体系、WAL预写日志理论

PostgreSQL所有数据变更首先写入WAL预写日志，再刷新内存缓冲区，遵循WAL协议，保障数据库崩溃之后可以依靠WAL完成实例crash‑recovery崩溃恢复。没有落盘到数据文件的修改全部保存在WAL，实例异常断电，启动阶段回放WAL完成数据一致性修复。

集群数据目录PGDATA内部核心目录结构：

  • base目录：各个database业务数据文件；
  • pg_wal：WAL预写日志文件；
  • pg_tblspc：表空间符号链接；
  • pg_xact：事务提交状态；
  • global：集群共享系统表；
  • log：数据库运行日志（可配置）。

每一张数据表的数据文件会分割为多个segment段文件，默认单段最大1GB；大字段会自动存入TOAST溢出存储，行外存储超长文本、大二进制字段，避免行过大影响性能。

1.4 MVCC多版本并发控制、元组Tuple、Autovacuum原理

PostgreSQL MVCC实现机制和InnoDB存在本质差异。MySQL InnoDB将旧版本数据存放在undo回滚段；PostgreSQL直接在数据表堆heap内部保存多条元组tuple版本，更新操作不会覆盖原有行，而是追加写入新版本元组，旧版本保留为历史快照。每一条元组头部携带xmin（创建该元组的事务ID）、xmax（删除/更新该元组事务ID），事务依靠xmin/xmax判断元组对当前会话是否可见。

旧版本元组不再被任何活跃事务访问之后就变为dead tuple死元组，死元组不会自动释放磁盘空间，必须依靠VACUUM操作回收。autovacuum后台进程会自动扫描表，清理死元组、更新pg_statistic统计信息。

  • VACUUM：清理死元组，标记空间可复用，不会收缩磁盘文件；
  • VACUUM FULL：会重写整张表，真正释放磁盘空间，会锁表，业务高峰期禁止执行VACUUM FULL；
  • ANALYZE：采集表与索引统计信息，供查询优化器生成最优执行计划。

生产运维常见故障：autovacuum参数不合理，表死元组堆积，表体积膨胀bloat，查询性能严重退化。

风哥教程 113257174

1.5 PostgreSQL逻辑对象层级概念

  1. Cluster集群：整套PostgreSQL实例，一套集群多个database，共享WAL、系统角色；
  2. Database数据库：逻辑隔离数据库，不同database互相隔离；模板库template0干净只读模板，template1新建数据库默认模板；
  3. Schema模式：database之下的命名空间，一个database包含多个schema；public为默认schema；业务对象表、索引、视图全部归属于schema；
  4. Role角色：角色可以是用户，也可以是角色组，支持角色之间继承权限；login属性代表该角色可以登录数据库；
  5. Tablespace表空间：定义操作系统物理存储路径，可以把指定表、索引存放到不同磁盘分区，解决磁盘空间不足，冷热数据分层存储。

层级关系：Cluster → Database → Schema → Table / Index / View / Sequence。

1.6 权限访问控制体系与pg_hba.conf

PostgreSQL两层访问控制：
第一层 pg_hba.conf：主机认证配置文件，控制客户端来源IP、认证方式（trust、password、scram‑sha‑256、peer），控制哪些主机可以连接集群；
第二层 SQL对象权限：GRANT授予对象权限，select、insert、update、delete、truncate、reference，schema使用权限USAGE。

scram‑sha‑256为PostgreSQL推荐密码加密方式，旧md5已经逐步不推荐使用。注意：pg_hba配置修改之后执行pg_reload_conf()重载，不需要重启实例。

1.7 PostgreSQL 64G内存8CPU硬件核心参数理论说明

风哥教程本文基于64G物理内存，8CPU服务器规格，梳理关键参数理论含义。

  1. shared_buffers：PostgreSQL自身共享缓冲池，官方建议设置物理内存25%，64G内存设置16G；PostgreSQL会使用操作系统page cache，不会像InnoDB把全部内存接管；
  2. work_mem：单个排序、hash join操作内存，会话级别，设置过大会造成内存溢出；
  3. maintenance_work_mem：vacuum、索引创建维护操作内存；
  4. max_connections：最大客户端连接，生产不建议设置过大，一般500‑800，配合pgbouncer连接池；
  5. wal_buffers：WAL缓冲区；
  6. checkpoint_completion_target：检查点时间目标，平滑刷盘IO；
  7. wal_level=replica：开启WAL归档、流复制必备；
  8. max_wal_senders：流复制最大发送进程数量；
  9. autovacuum系列参数，控制自动清理阈值、运行开销；
  10. max_parallel_workers、max_parallel_workers_per_gather并行查询工作进程，8CPU机器合理配置并行能力。

风哥数据库教程 itpux‑com

1.8 PostgreSQL备份分类理论

  1. 逻辑备份：pg_dump/pg_dumpall，导出SQL或者自定义归档格式，导出逻辑对象定义与数据。优点跨版本迁移友好，可以单库单表恢复；缺点大库备份恢复速度慢；pg_dumpall用于导出整个集群全部数据库与角色权限。
  2. 物理备份：pg_basebackup，直接拷贝PGDATA集群物理文件，WAL归档配合实现时间点PITR恢复；适合TB级大库；物理备份必须保证主备大版本完全一致。
    WAL归档是PITR时间点恢复基础，持续归档wal段文件，可以恢复到过去任意时间点。

1.9 PostgreSQL入门阶段典型故障分类

  1. 连接访问故障：pg_hba.conf拒绝访问，密码认证失败，连接数打满；
  2. 磁盘空间故障：磁盘耗尽，WAL日志堆积，表空间磁盘满；
  3. 表膨胀Bloat：autovacuum失效，死元组堆积，表体积暴涨；
  4. 事务ID回卷风险：长时间不做vacuum，触发事务ID耗尽保护；
  5. WAL归档故障，归档命令失败实例阻塞；
  6. SQL性能问题，统计信息缺失，糟糕执行计划。

上51CTO搜索风哥可以学习全套数据库教程

二、PostgreSQL实战环境准备

2.1 主机规划

  • fgedu‑net‑cn1：PostgreSQL主实例主机，64G内存8CPU，NVMe SSD磁盘，CentOS Stream8，部署PostgreSQL实例；根路径/fgedudb，实例集群PGDATA目录/fgedudb/pg18/data；端口5432；集群名/数据库名fgedudb；业务角色fgedu
  • fgedu‑net‑cn2：测试与备份恢复演练主机，硬件规格和fgedu‑net‑cn1完全一致，用于存放备份集，执行恢复演练

2.2 fgedu‑net‑cn1操作系统内核与资源限制预调优

修改文件句柄、信号量、共享内存参数，适配PostgreSQL运行要求，两台主机fgedu‑net‑cn1、fgedu‑net‑cn2全部执行下面操作。

#修改limits.conf资源限制
cat >> /etc/security/limits.conf <<EOF
postgres soft nofile 65535
postgres hard nofile 65535
postgres soft nproc 65535
postgres hard nproc 65535
root soft nofile 65535
root hard nofile 65535
EOF

#sysctl内核参数
cat >> /etc/sysctl.conf <<EOF
kernel.shmmax=34359738368
kernel.shmall=8388608
kernel.sem=250 32000 100 128
fs.file‑max=655350
vm.swappiness=1
net.ipv4.ip_local_port_range=9000 65535
EOF
sysctl -p

创建操作系统postgres系统用户，创建目录结构，设置权限：

groupadd postgres
useradd -g postgres postgres
#创建实例目录
mkdir -p /fgedudb/pg18/{data,wal_archive,backup,scripts,tablespace}
chown -R postgres:postgres /fgedudb
chmod 700 /fgedudb/pg18/data

2.3 PostgreSQL源码编译部署（fgedu‑net‑cn1）

安装编译依赖包：

yum install -y gcc gcc‑c++ readline‑devel zlib‑devel libxml2‑devel libxslt‑devel openldap‑devel bison flex make git
#下载PostgreSQL源码包
cd /usr/local/src
wget [https://ftp.postgresql.org/pub/source/v18.0/postgresql](https://ftp.postgresql.org/pub/source/v18.0/postgresql)‑18.0.tar.gz
tar -zxvf postgresql‑18.0.tar.gz
cd postgresql‑18.0
#编译安装，安装路径/fgedudb/pg18
./configure --prefix=/fgedudb/pg18 --with‑openssl --with‑xml
make -j8
make install

配置postgres用户环境变量，编辑/home/postgres/.bash_profile

export PGHOME=/fgedudb/pg18
export PATH=$PGHOME/bin:$PATH
export PGDATA=/fgedudb/pg18/data
export PGPORT=5432

chown postgres:postgres /home/postgres/.bash_profile
su - postgres
source /home/postgres/.bash_profile

2.4 initdb初始化数据库集群（切换postgres操作系统用户）

su - postgres
initdb -D /fgedudb/pg18/data \
--encoding=UTF8 \
--locale=en_US.UTF‑8 \
--wal‑segment‑size=16MB

initdb完成，生成完整PGDATA集群目录，模板库template0 template1，postgres系统库初始化完成。

2.5 postgresql.conf参数配置，64G内存8CPU规格

编辑/fgedudb/pg18/data/postgresql.conf，核心参数修改如下：

listen_addresses = '*'
port = 5432
max_connections = 600
shared_buffers = 16GB
work_mem = 32MB
maintenance_work_mem = 2GB
wal_buffers = 64MB
wal_level = replica
max_wal_senders = 10
archive_mode = on
archive_command = 'test ! -f /fgedudb/pg18/wal_archive/%f && cp %p /fgedudb/pg18/wal_archive/%f'
checkpoint_completion_target = 0.9
max_parallel_workers = 8
max_parallel_workers_per_gather = 4
autovacuum = on
log_directory = '/fgedudb/pg18/log'
log_filename = 'postgresql‑%Y%m%d.log'
password_encryption = scram‑sha‑256

修改pg_hba.conf访问认证配置/fgedudb/pg18/data/pg_hba.conf，允许内网网段访问：

#TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             192.168.0.0/16          scram‑sha‑256
local   all             all                                     peer

2.6 启动PostgreSQL实例

su - postgres
pg_ctl start -D /fgedudb/pg18/data
#查看实例状态
pg_ctl status -D /fgedudb/pg18/data

pg_ctl为实例控制工具，start/stop/restart/reload，reload用于重载配置文件，不中断业务连接。

网上搜索风哥教程可以学习全套数据库教程

2.7 创建业务数据库fgedudb、业务角色fgedu

登录psql客户端，操作系统切换postgres用户执行psql：

su - postgres
psql

执行SQL创建业务登录角色fgedu，数据库fgedudb：

--创建登录角色fgedu
CREATE ROLE fgedu WITH LOGIN PASSWORD 'Fgedu@2026';
--创建业务数据库fgedudb，owner归属fgedu
CREATE DATABASE fgedudb OWNER fgedu ENCODING 'UTF8' LC_COLLATE 'en_US.UTF‑8' LC_CTYPE 'en_US.UTF‑8';
--授予fgedu角色业务库全部权限
GRANT ALL PRIVILEGES ON DATABASE fgedudb TO fgedu;
--连接业务库
\c fgedudb
--授予public schema使用权限
GRANT USAGE,CREATE ON SCHEMA public TO fgedu;
--查看角色 \du ;查看数据库 \l
\du
\l
\q

三、psql客户端工具实操与DDL/DML对象实战

psql是PostgreSQL自带交互式终端工具，内置大量反斜杠元命令\，不需要写SQL即可查询元数据信息。本章节全部操作在fgedu‑net‑cn1主机，部分命令演示远程从fgedu‑net‑cn2访问数据库。

3.1 psql连接方式实操

本地socket连接：

su - postgres
psql -d fgedudb

远程TCP连接，在fgedu‑net‑cn2主机执行访问fgedu‑net‑cn1数据库：

/fgedudb/pg18/bin/psql -h fgedu‑net‑cn1 -p 5432 -U fgedu -d fgedudb

常用psql元命令清单：

\l          列出所有database
\du         列出角色
\dn         列出schema
\dt         列出当前schema下面表
\d 表名      查看表结构
\di         索引列表
\dv         视图列表
\ds         序列
\q          退出psql
\x          切换扩展输出模式
\!          执行shell命令
\c dbname   切换数据库连接

3.2 schema模式对象实战，创建业务schema

登录fgedudb数据库，创建业务schema名字为biz；把schema所有权赋予fgedu角色。

\c fgedudb
CREATE SCHEMA biz OWNER fgedu;
--设置search_path会话参数，优先搜索biz模式
SET search_path TO biz,public;
\dn

search_path非常关键，控制SQL查找表对象的schema顺序，业务开发脚本建议显式带上schema名称。

3.3 数据表、约束、索引、序列实战

连接biz schema，创建业务测试表t_customer，包含主键、非空、唯一约束，使用serial序列自增，PostgreSQL支持IDENTITY自增列。

\c fgedudb
SET search_path TO biz,public;

CREATE TABLE t_customer (
    cid bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    cname varchar(128) NOT NULL,
    phone varchar(20) UNIQUE,
    register_time timestamp without time zone DEFAULT now(),
    info jsonb,
    status smallint DEFAULT 1
);

--创建普通B‑tree索引
CREATE INDEX idx_t_customer_phone ON biz.t_customer(phone);

--查看表结构
\d biz.t_customer
\di biz.idx_t_customer_phone

DML增删改查事务实操：

--开启事务
BEGIN;
INSERT INTO biz.t_customer(cname,phone,info) VALUES
('zhangsan','13800138001','{"level":1,"vip":false}'::jsonb),
('lisi','13800138002','{"level":2,"vip":true}'::jsonb);

UPDATE biz.t_customer SET status=2 WHERE cid=1;
DELETE FROM biz.t_customer WHERE cid=2;
--回滚测试
ROLLBACK;

--重新写入，提交事务
BEGIN;
INSERT INTO biz.t_customer(cname,phone,info) VALUES
('zhangsan','13800138001','{"level":1,"vip":false}'::jsonb),
('lisi','13800138002','{"level":2,"vip":true}'::jsonb);
COMMIT;

SELECT * FROM biz.t_customer;
--jsonb字段查询
SELECT * FROM biz.t_customer WHERE info ->> 'vip' = 'true';

3.4 视图创建实操

CREATE VIEW v_customer_vip AS
SELECT cid,cname,phone,register_time,info FROM biz.t_customer WHERE (info->>'vip')::boolean = true;

\dv biz.v_customer_vip
SELECT * FROM biz.v_customer_vip;

3.5 表空间tablespace完整实战

表空间可以把数据表存储到独立磁盘分区，本套风哥教程表空间路径/fgedudb/pg18/tablespace/ts_biz。

#操作系统创建目录，授权postgres
mkdir -p /fgedudb/pg18/tablespace/ts_biz
chown postgres:postgres /fgedudb/pg18/tablespace/ts_biz
chmod 700 /fgedudb/pg18/tablespace/ts_biz

psql内创建表空间：

CREATE TABLESPACE ts_biz LOCATION '/fgedudb/pg18/tablespace/ts_biz';
\db
--将现有表迁移至ts_biz表空间
ALTER TABLE biz.t_customer SET TABLESPACE ts_biz;
ALTER INDEX biz.idx_t_customer_phone SET TABLESPACE ts_biz;

风哥 itpux‑com

四、PostgreSQL备份恢复完整实战

4.1 逻辑备份pg_dump单库备份实战（fgedu‑net‑cn1主机，postgres用户执行）

pg_dump支持多种输出格式：plain文本SQL、custom自定义‑Fc、directory目录‑Fd、tar格式。custom格式最常用，支持并行恢复、选择性恢复单表。备份业务数据库fgedudb，备份存放目录/fgedudb/pg18/backup/logic。

编写备份脚本/fgedudb/pg18/scripts/pg_dump_fgedudb.sh

#!/bin/bash
BACKUP_DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/fgedudb/pg18/backup/logic"
PGHOME=/fgedudb/pg18
mkdir -p ${BACKUP_DIR}

${PGHOME}/bin/pg_dump \
-h 127.0.0.1 -p 5432 -U fgedu -d fgedudb \
-Fc -Z 6 \
-f ${BACKUP_DIR}/fgedudb_${BACKUP_DATE}.dump

RET=$?
if [ ${RET} -eq 0 ];then
    md5sum ${BACKUP_DIR}/fgedudb_${BACKUP_DATE}.dump > ${BACKUP_DIR}/fgedudb_${BACKUP_DATE}.dump.md5
    echo "backup ok ${BACKUP_DATE}" >> ${BACKUP_DIR}/backup.log
else
    echo "backup fail ${BACKUP_DATE}" >> ${BACKUP_DIR}/backup.log
    exit 1
fi
#清理7天之前旧备份
find ${BACKUP_DIR} -type f -mtime +7 -exec rm -f {} \;

赋予执行权限，postgres用户运行脚本：

chmod +x /fgedudb/pg18/scripts/pg_dump_fgedudb.sh
su - postgres
/fgedudb/pg18/scripts/pg_dump_fgedudb.sh

4.2 pg_restore逻辑恢复演练，fgedu‑net‑cn2主机执行演练

把备份文件传输至异地演练主机fgedu‑net‑cn2：

scp /fgedudb/pg18/backup/logic/* fgedu‑net‑cn2:/fgedudb/pg18/backup/logic/

fgedu‑net‑cn2主机校验md5，模拟恢复：

cd /fgedudb/pg18/backup/logic
md5sum -c fgedudb_20260915_100000.dump.md5

pg_restore恢复，首先在演练实例新建空数据库fgedudb：

su - postgres
createdb -O fgedu fgedudb
pg_restore -d fgedudb -U fgedu -j4 /fgedudb/pg18/backup/logic/fgedudb_20260915_100000.dump

参数‑j4开启4并行恢复加速；custom格式dump支持只恢复单张表，示例：

pg_restore -d fgedudb -U fgedu -t biz.t_customer /fgedudb/pg18/backup/logic/fgedudb_20260915_100000.dump

风哥教程 113257174

4.3 pg_dumpall集群全量逻辑备份

pg_dumpall导出整个集群所有database，包含角色、用户权限全部元数据，输出为plain SQL文本，适合集群整体逻辑备份。

su - postgres
/fgedudb/pg18/bin/pg_dumpall -h 127.0.0.1 -p 5432 -U postgres > /fgedudb/pg18/backup/logic/cluster_all_$(date +%Y%m%d).sql

4.4 pg_basebackup物理基础备份实战 fgedu‑net‑cn1

pg_basebackup做物理集群基线备份，用于物理灾难恢复，需要replica权限角色。首先创建复制角色：

CREATE ROLE repuser WITH REPLICATION LOGIN PASSWORD 'Rep@2026';

执行pg_basebackup，备份目标目录/fgedudb/pg18/backup/basebk

su - postgres
pg_basebackup \
-h 127.0.0.1 -p 5432 -U repuser \
-D /fgedudb/pg18/backup/basebk/full_$(date +%Y%m%d) \
‑Ft ‑z ‑P ‑c fast

参数说明：‑Ft输出tar归档格式，‑z压缩，‑P输出备份进度，‑c fast立刻触发检查点。

物理备份配合wal_archive归档目录的WAL段文件，可以完成PITR时间点恢复。将整套物理备份传输到fgedu‑net‑cn2异地主机。

4.5 fgedu‑net‑cn2物理备份恢复演练

将tar备份包解压，重建PGDATA数据目录；注意表空间需要处理tablespace‑mapping映射，恢复完成修改postgresql.conf、pg_hba.conf适配演练主机环境，之后启动实例。

su - postgres
mkdir -p /fgedudb/pg18/data
tar -zxvf full_20260915.tar -C /fgedudb/pg18/data
#编辑postgresql.conf、pg_hba.conf适配演练主机
pg_ctl start -D /fgedudb/pg18/data

风哥数据库教程 itpux‑com

五、PostgreSQL基础运维实战

5.1 会话查看、会话终止实操

登录psql查看当前所有会话pg_stat_activity视图：

SELECT pid,usename,datname,state,query,wait_event_type FROM pg_stat_activity;
--终止指定pid会话
SELECT pg_terminate_backend(12345);

5.2 vacuum维护实操

手动执行vacuum，清理指定表死元组，更新统计信息：

--普通vacuum，回收可复用空间
VACUUM biz.t_customer;
--vacuum analyze清理同时更新统计信息
VACUUM ANALYZE biz.t_customer;
--全库vacuum analyze
VACUUM ANALYZE;

提醒业务生产禁止高峰期执行VACUUM FULL，会排他锁锁表。

5.3 日志查看与基础故障排查

PostgreSQL日志路径配置/fgedudb/pg18/log，日志记录连接、错误、checkpoint、vacuum信息。

tail -f /fgedudb/pg18/log/postgresql‑20260915.log

常见排错步骤：

  1. 实例无法启动：优先查看数据库日志，权限错误、目录权限、WAL损坏、参数错误；
  2. 客户端连接报错：检查pg_hba.conf配置，密码scram‑sha‑256，防火墙端口5432；
  3. 磁盘满：排查pg_wal、wal_archive、表空间目录占用磁盘空间；
  4. SQL慢查询：开启log_statement，查看pg_stat_statements统计执行耗时。

5.4 插件扩展安装，pg_stat_statements

pg_stat_statements是官方核心扩展，用于统计SQL执行性能，postgresql.conf添加：

shared_preload_libraries = 'pg_stat_statements'

重启实例之后psql执行：

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT queryid,query,calls,total_time FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

5.5 crontab定时备份任务配置

postgres操作系统用户配置crontab定时任务，每日凌晨2点执行pg_dump逻辑备份。

crontab -u postgres -e
#内容
0 2 * * * /fgedudb/pg18/scripts/pg_dump_fgedudb.sh >> /fgedudb/pg18/backup/logic/cron.log 2>&1

上51CTO搜索风哥可以学习全套数据库教程

风哥针对本文总结

风哥教程本文完整讲解PostgreSQL数据库基础入门整套知识，分为理论体系和大规模实战两大部分。理论部分讲解PostgreSQL版本新特性、多进程模型、WAL预写日志、MVCC元组与autovacuum清理机制，梳理Cluster集群、database、schema、role角色、tablespace表空间逻辑对象层级，解析pg_hba.conf访问认证，基于64G内存8CPU硬件规格说明核心配置参数，区分逻辑备份与物理备份原理，梳理入门阶段高频故障类型。

实战部分使用两台主机fgedu‑net‑cn1、fgedu‑net‑cn2，完整完成操作系统内核调优、源码编译部署PostgreSQL、initdb集群初始化、postgresql.conf与pg_hba.conf配置；业务数据库fgedudb、业务角色fgedu创建；psql元命令实操；schema、数据表、索引、jsonb、视图DML事务开发；tablespace表空间创建与对象迁移；pg_dump逻辑备份脚本、pg_restore恢复演练；pg_dumpall集群全量备份；pg_basebackup物理备份与异地主机恢复演练；会话管理、vacuum维护、pg_stat_statements性能扩展、crontab定时备份运维操作。全部文件路径替换为/fgedudb，实例、数据库、账号统一为fgedudb/fgedudb/fgedu。

风哥教程本文强调几条入门阶段运维关键点：PostgreSQL MVCC死元组依赖autovacuum自动清理，业务环境禁止随意关闭autovacuum；VACUUM FULL会锁表，业务高峰严禁执行；pg_hba.conf修改执行pg_reload_conf()重载，不需要重启实例；逻辑备份pg_dump适合中小库、单对象恢复，物理pg_basebackup适合TB级大库，WAL归档是时间点PITR恢复的基础；所有备份必须传输异地主机并且完成恢复验证，没有验证过的备份不可信；max_connections不要配置过高，生产业务建议配合连接池中间件使用，降低进程内存开销。

本套风哥教程所有命令均可以在隔离测试环境直接复现，生产上线之前需要充分验证参数与脚本逻辑。掌握本套教程内容之后，可以继续深入学习PostgreSQL索引优化、主从流复制、分区表、pgBackRest高级备份、FDW外部数据包装器等进阶技术。

【声明】本内容来自华为云开发者社区博主，不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息，否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
PostgreSQL 数据库
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入