数据库教程FGMT47‑Linux平台PostgreSQL安装配置与管理入门

数据库教程FGMT47‑Linux平台PostgreSQL安装配置与管理入门

前言与教程大纲

前言

风哥教程本文面向数据库运维工程师、DBA、后端开发人员、云计算运维人员，完整讲解Linux环境下PostgreSQL数据库从前期系统规划、软件部署、实例初始化、参数调优、权限体系、日常运维、备份恢复、基础故障排查整套技术内容。PostgreSQL作为企业级开源关系型数据库，支持复杂SQL、JSON数据类型、事务强一致性、MVCC多版本并发控制，在OLTP业务、地理信息业务、数据分析业务场景被大量使用；很多运维人员直接执行软件包安装，忽略操作系统内核、文件系统、资源限制前期规划，上线后出现内存抖动、磁盘IO瓶颈、连接报错、权限异常等各类生产故障。

本套风哥教程采用两台标准化主机fgedu‑net‑cn1、fgedu‑net‑cn2作为实操环境，硬件规格统一为64G物理内存，8物理CPU核心；数据根目录统一使用/fgedudb；数据库实例名、数据库名、业务用户名统一为fgedudb、fgedudb、fgedu。全部实操案例基于PostgreSQL版本，覆盖源码编译部署、RPM软件包部署两种主流实施方式；风哥教程本文包含底层理论原理、完整可复现的命令步骤、配置文件样例、故障模拟验证、巡检脚本开发，帮助从业者建立标准化PostgreSQL运维工作流程，理解数据库与Linux操作系统之间的资源交互逻辑。

风哥 itpux‑com

风哥教程本文内容大纲

1. PostgreSQL基础理论：数据库架构、进程模型、MVCC原理、WAL预写日志机制、集群与实例概念；64G/8C硬件环境资源分配理论；PostgreSQL版本新特性解读。
2. Linux操作系统前期规划：内核sysctl参数调优、ulimit资源限制规划、文件系统XFS挂载规划、用户目录权限规划，适配PostgreSQL生产运行要求。
3. PostgreSQL部署实战：源码编译安装流程、官方RPM包安装流程；两台主机fgedu‑net‑cn1、fgedu‑net‑cn2完整实操；实例初始化initdb操作；多实例部署方法。
4. PostgreSQL核心配置文件详解：postgresql.conf参数调优（内存、WAL、检查点、连接、日志）；pg_hba.conf访问权限控制；pg_ident.conf用户映射配置；64G内存主机参数参考值。
5. 数据库实例启停管理：pg_ctl工具完整用法；systemd服务单元配置；启动、停止、重启、重载配置、故障模式启动完整实操。

风哥教程 113257174

6. 用户、角色、权限体系实战：超级用户、普通业务角色创建；库、schema、表级权限授权回收；业务账号fgedu完整创建案例；访问控制排错。
7. 数据库对象基础管理：库、schema、表、索引操作；表空间tablespace创建管理，数据文件迁移至/fgedudb不同子目录实操。
8. PostgreSQL备份恢复实战：逻辑备份pg_dump、pg_dumpall；逻辑恢复pg_restore；物理基础备份pg_basebackup；定时备份脚本开发；误删除数据恢复演练。
9. PostgreSQL日常运维监控实战：系统视图pg_stat_activity、pg_stat_user_tables；长会话、锁等待、表膨胀定位；VACUUM垃圾回收实操；基础巡检脚本编写。
   10.基础故障排查实战：实例启动失败、远程连接失败、磁盘空间满、WAL日志异常、表膨胀故障现象、定位步骤与修复命令。
   11.风哥针对本文总结：PostgreSQL运维关键点汇总，生产环境风险清单，参数适配思路。

本套风哥教程篇幅分配规则：前言与大纲占全文5%；底层理论原理部分占全文30%；实战部署、配置、备份、故障模拟操作命令占全文60%；收尾总结占全文5%。

一、PostgreSQL基础理论

1.1 PostgreSQL整体架构与进程模型理论

PostgreSQL属于经典的多进程架构，区别于MySQL多线程模型。数据库实例启动后会生成一个主进程postgres，主进程负责接收外部连接请求；每一个客户端连接过来，操作系统会fork生成独立后端backend进程，每个会话拥有独立进程，会话之间进程相互隔离，单个会话崩溃不会直接造成整个实例宕机。

后台辅助关键进程在PostgreSQL包含：

1. checkpointer检查点进程：触发检查点，将共享缓冲区的脏数据批量持久写入磁盘数据文件；降低实例崩溃之后WAL日志回放工作量。
2. WAL writer日志写进程：负责预写日志WAL缓冲区刷入磁盘WAL段文件；所有事务提交必须等待WAL日志落盘，保障ACID事务持久性。
3. autovacuum自动垃圾回收进程：MVCC更新删除产生死亡元组dead tuple，autovacuum扫描表清理过期元组，回收存储空间，抑制表膨胀，是PostgreSQL运维中极其核心的后台进程。
4. bgwriter后台写进程：周期性将共享缓冲区shared buffers内的脏页提前刷盘，避免检查点瞬间大量IO冲击存储设备。
5. stats collector统计收集进程：采集表、索引、会话运行统计信息，供pg_stat_*系列系统视图读取，用于性能监控、故障定位。

风哥数据库教程 itpux‑com

实例（cluster）概念：一次initdb初始化生成一套完整的数据目录集合称之为数据库实例，一套实例内部可以创建多个database数据库；不同database之间schema对象互相隔离，psql客户端连接只能指定单一database，跨database不能直接访问表对象，需要使用dblink或者fdw外部数据封装扩展。

1.2 MVCC多版本并发控制理论

MVCC是PostgreSQL实现高并发事务隔离的核心机制。执行UPDATE、DELETE操作，数据库不会直接原地覆盖旧数据行，而是生成新版本元组，旧版本元组保留在原有数据块；不同事务根据快照看到对应版本的数据。旧死亡元组不会自动释放磁盘空间，必须依靠VACUUM进行清理；如果autovacuum被人为关闭或者参数设置不合理，会产生严重表膨胀，磁盘占用持续上涨，查询性能持续退化。

1.3 WAL预写日志原理

WAL（Write‑Ahead‑Logging）预写日志，核心规则：数据页修改必须先记录WAL日志落盘，再写入数据文件。事务提交的时候强制fsync把WAL缓冲区持久化磁盘，就算数据库实例崩溃，重启的时候读取WAL日志，执行崩溃恢复，重放已经提交事务、回滚未完成事务，保证数据一致性。WAL同时是物理流复制的数据源，主库产生WAL记录传递给备库实现主从同步。PostgreSQL对WAL段文件管理、检查点算法做了优化，降低高写入场景IO抖动风险。

1.4 64G内存、8CPU主机资源分配理论

针对fgedu‑net‑cn1、fgedu‑net‑cn2两台专用PostgreSQL数据库主机，整机64G物理内存，8颗CPU核心，生产环境资源分配基础原则：

1. shared_buffers共享缓冲区，设置物理内存的25%，64G内存对应设置16G，这部分是PostgreSQL自己管理内存缓存；不要设置超过整机40%，PostgreSQL大量依赖操作系统page cache文件缓存。
2. effective_cache_size设置物理内存50%，即32G，给查询优化器评估可用缓存大小，影响执行计划生成。
3. work_mem是每个排序、哈希连接操作独占内存，不能设置过大，防止并发会话内存叠加耗尽整机内存；64G主机work_mem建议16MB。
4. maintenance_work_mem，VACUUM、CREATE INDEX重建索引操作占用内存，设置1‑2G区间。
5. CPU层面，max_parallel_workers全局并行最大等于CPU核心数8；max_parallel_workers_per_gather设置4，控制单条SQL并行执行worker进程数量。
6. 操作系统层面预留内存给page cache，禁止swap频繁换入换出；数据库服务器vm.swappiness建议设置为1。

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

1.5 PostgreSQL版本关键新特性

1. 并行查询能力增强，分区表DML操作支持更多并行场景；
2. autovacuum逻辑优化，大表垃圾回收对业务IO冲击得到缓解；
3. WAL日志相关参数、检查点逻辑优化，高写入OLTP场景IO更加平稳；
4. JIT即时编译默认行为调整，复杂SQL运算性能提升；
5. 系统监控视图增加更多等待事件，更容易定位锁、IO等待类故障；
6. 增强JSONB类型处理性能，扩展数据类型能力；
7. pg_basebackup物理备份增加更多校验机制，提升备份集可靠性。

二、Linux操作系统前期规划（fgedu‑net‑cn1、fgedu‑net‑cn2）

2.1操作系统内核参数sysctl配置

两台主机硬件规格64G内存8CPU，编辑/etc/sysctl.conf，写入适配PostgreSQL内核参数，PostgreSQL默认使用mmap共享内存，旧版本System V共享内存不再强依赖，但依旧做兼容配置。

#共享内存配置
kernel.shmmax=68719476736
kernel.shmall=16777216
kernel.sem=250 32000 100 128
#文件句柄
fs.file-max=6553600
#内存策略，降低swap使用倾向
vm.swappiness=1
vm.overcommit_memory=0
vm.dirty_background_ratio=5
vm.dirty_ratio=10
#网络参数
net.ipv4.ip_local_port_range=9000 65535
net.core.rmem_max=16777216
net.core.wmem_max=16777216
net.core.somaxconn=4096

参数加载生效，root账号执行：

sysctl -p
#校验参数是否生效
sysctl kernel.shmmax vm.swappiness fs.file-max

2.2 用户资源限制limits.conf配置

PostgreSQL运行操作系统用户默认postgres，配置文件/etc/security/limits.conf，增加postgres用户资源限制，防止文件句柄、进程数耗尽。

postgres soft nofile 65535
postgres hard nofile 65535
postgres soft nproc 65535
postgres hard nproc 65535
postgres soft memlock unlimited
postgres hard memlock unlimited

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

2.3 文件系统规划

数据目录统一/fgedudb，数据库数据目录/fgedudb/fgedudb_data，WAL目录/fgedudb/fgedudb_wal，备份存放目录/fgedudb/fgedudb_backup，日志目录/fgedudb/fgedudb_log。生产环境优先XFS文件系统，挂载参数rw,noatime,nodiratime，关闭访问时间atime减少额外写IO。
查看挂载信息：

mount

创建目录，赋予postgres用户所有权：

mkdir -p /fgedudb/fgedudb_data
mkdir -p /fgedudb/fgedudb_wal
mkdir -p /fgedudb/fgedudb_backup
mkdir -p /fgedudb/fgedudb_log
groupadd postgres
useradd -g postgres postgres
chown -R postgres:postgres /fgedudb
chmod -R 700 /fgedudb

三、PostgreSQL部署实战

PostgreSQL提供两种主流部署方式：源码编译安装、官方PGDG‑YUM源RPM包安装；fgedu‑net‑cn1使用源码编译部署；fgedu‑net‑cn2使用RPM软件包部署。

3.1 fgedu‑net‑cn1：PostgreSQL源码编译安装

3.1.1安装编译依赖包，root执行

#RHEL/Rocky/AlmaLinux系列
yum install -y gcc gcc‑c++ make readline‑devel zlib‑devel openssl‑devel libxml2‑devel libxslt‑devel perl‑ExtUtils‑Embed bison flex

3.1.2下载解压源码包

cd /usr/local/src
wget [https://ftp.postgresql.org/pub/source/v18.1/postgresql](https://ftp.postgresql.org/pub/source/v18.1/postgresql)‑18.1.tar.bz2
tar -xjvf postgresql‑18.1.tar.bz2
cd postgresql‑18.1

3.1.3 configure配置编译选项

./configure --prefix=/fgedudb/pgsql18 --with‑openssl --with‑xml --with‑xslt

--prefix=/fgedudb/pgsql18指定软件安装根目录。

3.1.4编译与安装

make world
make install‑world

make world把数据库、contrib扩展组件、文档全部编译安装完成。

3.1.5配置环境变量（postgres用户）

切换操作系统postgres用户，编辑家目录.bash_profile

su - postgres
vi ~/.bash_profile

写入内容：

export PGHOME=/fgedudb/pgsql18
export PATH=$PGHOME/bin:$PATH
export PGDATA=/fgedudb/fgedudb_data
export PGLOG=/fgedudb/fgedudb_log/pgsql.log

生效环境变量：

source ~/.bash_profile
#校验版本
psql --version

3.1.6 initdb初始化数据库实例

postgres操作系统用户执行initdb，指定数据目录，指定WAL目录，设置数据库区域编码。

initdb -D /fgedudb/fgedudb_data -X /fgedudb/fgedudb_wal -E UTF8 --locale=en_US.UTF‑8

initdb完成之后，/fgedudb/fgedudb_data目录生成postgresql.conf、pg_hba.conf、pg_ident.conf核心配置文件；目录权限自动设置700，不允许其他用户访问。

3.2 fgedu‑net‑cn2：PostgreSQL RPM包安装实战

主机fgedu‑net‑cn2使用官方pgdg yum源安装PostgreSQL，root账号执行。

#添加PGDG官方yum仓库
rpm -Uvh [https://download.postgresql.org/pub/repos/yum/reporpms/EL](https://download.postgresql.org/pub/repos/yum/reporpms/EL)‑9‑x86_64/pgdg‑redhat‑repo‑latest.noarch.rpm
yum clean all
yum makecache
#安装服务端、客户端、contrib扩展包
yum install -y PostgreSQL‑server PostgreSQL PostgreSQL‑contrib

RPM包软件默认路径：二进制程序/usr/pgsql‑18/bin；我们修改数据目录指向/fgedudb/fgedudb_data，修改系统service文件，修改环境变量。
初始化数据库：

/usr/pgsql‑18/bin/postgresql‑setup --initdb -D /fgedudb/fgedudb_data -X /fgedudb/fgedudb_wal
chown -R postgres:postgres /fgedudb

风哥 itpux‑com

3.3 pg_ctl工具初始化启停数据库实例（fgedu‑net‑cn1源码版本）

postgres操作系统用户执行pg_ctl，pg_ctl是PostgreSQL自带实例控制工具。

#启动实例
pg_ctl -D /fgedudb/fgedudb_data -l /fgedudb/fgedudb_log/pgsql.log start
#查看实例状态
pg_ctl -D /fgedudb/fgedudb_data status
#重载配置（不中断业务，加载postgresql.conf/pg_hba.conf修改）
pg_ctl -D /fgedudb/fgedudb_data reload
#正常停止（等待会话完成，干净关闭）
pg_ctl -D /fgedudb/fgedudb_data stop -m smart
#快速停止，断开现有会话
pg_ctl -D /fgedudb/fgedudb_data stop -m fast
#紧急停止，立刻终止进程（故障场景，尽量避免生产频繁使用）
pg_ctl -D /fgedudb/fgedudb_data stop -m immediate

3.4 systemd服务单元配置实战

生产环境推荐systemd托管PostgreSQL实例，编写/etc/systemd/system/postgresql‑fgedudb.service单元文件。

[Unit]
Description=PostgreSQL 18 Database Instance fgedudb
Documentation=man:postgres(1)
After=network.target

[Service]
Type=notify
User=postgres
Group=postgres
ExecStart=/fgedudb/pgsql18/bin/postgres -D /fgedudb/fgedudb_data
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
TimeoutSec=600
LimitNOFILE=65535
LimitNPROC=65535

[Install]
WantedBy=multi‑user.target

root执行重载systemd，设置开机自启，启停实例：

systemctl daemon‑reload
systemctl enable postgresql‑fgedudb.service
systemctl start postgresql‑fgedudb.service
systemctl status postgresql‑fgedudb.service

四、PostgreSQL核心配置文件调优（64G内存8CPU）

4.1 postgresql.conf核心参数配置示例

数据目录/fgedudb/fgedudb_data/postgresql.conf，针对64G内存8CPU主机关键参数，只列出生产必须调整项，其余保持默认。

#内存参数
shared_buffers=16GB
effective_cache_size=32GB
work_mem=16MB
maintenance_work_mem=1GB

#CPU并行参数
max_parallel_workers=8
max_parallel_workers_per_gather=4

#连接参数
max_connections=300
superuser_reserved_connections=10

#WAL与检查点
wal_level=replica
max_wal_size=8GB
min_wal_size=2GB
checkpoint_timeout=15min
checkpoint_completion_target=0.9
wal_buffers=16MB

#autovacuum垃圾回收
autovacuum=on
autovacuum_max_workers=4
autovacuum_vacuum_scale_factor=0.1
autovacuum_analyze_scale_factor=0.05

#日志配置
logging_collector=on
log_directory='/fgedudb/fgedudb_log'
log_filename='postgresql‑%Y%m%d_%H%M%S.log'
log_rotation_size=100MB
log_min_duration_statement=200
log_checkpoints=on
log_connections=on
log_disconnections=on

#监听地址
listen_addresses='*'
port=5432

参数修改完成，执行reload重载配置；部分内存类参数需要重启实例才会生效。

风哥教程 113257174

4.2 pg_hba.conf访问控制配置

pg_hba.conf控制客户端访问认证策略，决定哪些IP可以访问实例，使用什么认证方式；格式：TYPE DATABASE USER ADDRESS METHOD。
示例配置/fgedudb/fgedudb_data/pg_hba.conf

#本地socket访问
local   all             all                                     trust
#本机回环IP
host    all             all             127.0.0.1/32            scram‑sha‑256
#业务内网网段允许访问
host    all             all             192.168.10.0/24         scram‑sha‑256

修改pg_hba.conf后，执行pg_ctl reload，不需要重启数据库实例。常见故障：远程连接报no entry in pg_hba.conf，绝大多数是没有添加对应客户端网段策略。

4.3 pg_ident.conf用户映射

pg_ident.conf用于操作系统用户与数据库角色之间身份映射，主要用于本地peer认证场景，大多数业务场景保持基础默认配置即可。

五、数据库角色、用户、权限体系实战

PostgreSQL中，USER本质是带LOGIN属性的ROLE角色；超级用户默认postgres。我们创建业务数据库fgedudb，业务登录角色fgedu。

5.1登录数据库

本地unix socket登录，postgres操作系统用户执行：

psql
#或者指定数据库
psql -d postgres

5.2创建业务数据库fgedudb

CREATE DATABASE fgedudb ENCODING 'UTF8' LC_COLLATE 'en_US.UTF‑8' LC_CTYPE 'en_US.UTF‑8';

5.3创建业务角色fgedu

--创建登录角色，设置密码
CREATE ROLE fgedu LOGIN PASSWORD 'FgEdU@2026';
--授予数据库连接权限
GRANT CONNECT ON DATABASE fgedudb TO fgedu;
--授予public schema全部操作权限
\c fgedudb
GRANT USAGE,CREATE ON SCHEMA public TO fgedu;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO fgedu;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO fgedu;
--设置后续新建表自动授权给fgedu
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO fgedu;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO fgedu;

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

5.4权限回收、角色属性修改示例

--修改角色密码
ALTER ROLE fgedu PASSWORD 'FgEdU@NewPass123';
--回收表权限
REVOKE DELETE ON ALL TABLES IN SCHEMA public FROM fgedu;
--查看角色列表
\du
--切换连接到fgedudb库，使用fgedu账号验证登录
\q
psql -U fgedu -d fgedudb -h 127.0.0.1 -p 5432

六、数据库对象与表空间管理实战

6.1 schema、基础表索引操作

登录fgedudb数据库：

--创建schema
CREATE SCHEMA fg_schema;
--创建测试业务表
CREATE TABLE fg_t1(id bigint primary key, info text, create_time timestamp);
--创建索引
CREATE INDEX idx_fg_t1_ctime ON fg_t1(create_time);
--插入测试数据
INSERT INTO fg_t1(id,info,create_time) SELECT generate_series(1,10000),md5(random()::text),now();
--查看表信息
\d fg_t1

6.2 Tablespace表空间实战（把数据文件迁移至/fgedudb不同目录）

表空间允许把不同表、索引放置到不同磁盘目录，适合冷热数据分离场景。
创建表空间目录，操作系统层面：

mkdir -p /fgedudb/tbs_fgdata
chown postgres:postgres /fgedudb/tbs_fgdata
chmod 700 /fgedudb/tbs_fgdata

psql内部执行，创建表空间tbs_fgdata

CREATE TABLESPACE tbs_fgdata LOCATION '/fgedudb/tbs_fgdata';
--建表指定存储到该表空间
CREATE TABLE fg_t2(id int) TABLESPACE tbs_fgdata;
--移动已有表到新表空间
ALTER TABLE fg_t1 SET TABLESPACE tbs_fgdata;
--移动索引
ALTER INDEX idx_fg_t1_ctime SET TABLESPACE tbs_fgdata;
--查看全部表空间
\db

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

七、PostgreSQL备份恢复完整实战

PostgreSQL备份分为两大类：逻辑备份（pg_dump/pg_dumpall）、物理基础备份pg_basebackup。逻辑备份输出SQL或者自定义归档格式，与硬件平台无关，可以单库、单表粒度备份恢复；物理备份直接拷贝整个实例数据目录，用于完整实例灾难恢复，配合WAL日志可以实现时间点PITR恢复。备份文件统一存放目录/fgedudb/fgedudb_backup。

7.1逻辑备份pg_dump（单库fgedudb）

postgres操作系统用户执行，自定义压缩格式‑Fc，推荐生产使用，支持并行恢复。

#单库备份，自定义压缩格式
pg_dump -U postgres -d fgedudb -Fc -Z 6 -f /fgedudb/fgedudb_backup/fgedudb_$(date +%Y%m%d).dump
#单库备份纯SQL文本格式
pg_dump -U postgres -d fgedudb -f /fgedudb/fgedudb_backup/fgedudb_$(date +%Y%m%d).sql
#只备份指定schema
pg_dump -U postgres -d fgedudb -n fg_schema -Fc -f /fgedudb/fgedudb_backup/fg_schema.dump
#只备份单张表
pg_dump -U postgres -d fgedudb -t fg_t1 -Fc -f /fgedudb/fgedudb_backup/fg_t1.dump

7.2逻辑恢复pg_restore

模拟误删表故障演练，删除fg_t1表，再从备份文件恢复。

psql -U postgres -d fgedudb -c "DROP TABLE fg_t1;"
#pg_restore恢复自定义格式dump文件
pg_restore -U postgres -d fgedudb /fgedudb/fgedudb_backup/fgedudb_20260915.dump
#并行恢复，‑j开启多线程
pg_restore -j 4 -U postgres -d fgedudb /fgedudb/fgedudb_backup/fgedudb_20260915.dump

7.3 pg_dumpall全实例逻辑备份（含角色、表空间全局对象）

pg_dump备份单库不会导出角色用户、表空间这些集群全局对象；pg_dumpall用于导出整个实例所有数据库、角色、表空间信息。

pg_dumpall -U postgres -f /fgedudb/fgedudb_backup/all_cluster_$(date +%Y%m%d).sql

7.4物理备份pg_basebackup实战

pg_basebackup制作实例完整物理基础备份，用于实例整体故障重建。

pg_basebackup -D /fgedudb/fgedudb_backup/base_$(date +%Y%m%d) -Ft -z -P -X fetch

参数说明：‑D目标目录；‑Ft输出tar包格式；‑z开启压缩；‑P输出备份进度；‑X fetch把需要的WAL日志一并打包到备份集。

7.5编写定时备份shell脚本pg_backup.sh

脚本路径/fgedudb/script/pg_backup.sh，自动备份，自动清理7天之前旧备份。

#!/bin/bash
BACKUP_BASE=/fgedudb/fgedudb_backup
DATE_STR=$(date +%Y%m%d_%H%M%S)
RETENTION_DAY=7
mkdir -p ${BACKUP_BASE}
#逻辑备份业务库fgedudb
pg_dump -U postgres -d fgedudb -Fc -Z 6 -f ${BACKUP_BASE}/fgedudb_${DATE_STR}.dump
#全集群全局对象备份
pg_dumpall -U postgres -f ${BACKUP_BASE}/cluster_all_${DATE_STR}.sql
#清理过期备份
find ${BACKUP_BASE} -type f -mtime +${RETENTION_DAY} -delete
echo "backup finish ${DATE_STR}" >> ${BACKUP_BASE}/backup_log.txt

赋予执行权限，配置crontab定时任务，每天凌晨2点执行备份。

mkdir -p /fgedudb/script
mv pg_backup.sh /fgedudb/script/
chmod +x /fgedudb/script/pg_backup.sh
#配置crontab定时
crontab -e
#写入定时任务
0 2 * * * /fgedudb/script/pg_backup.sh

八、PostgreSQL日常运维监控实战

8.1会话与锁等待查询

登录psql，查看当前全部后端会话，识别长连接、空闲事务会话。

--查看全部会话
SELECT pid,usename,datname,state,query,wait_event_type,wait_event,backend_start,state_change
FROM pg_stat_activity;

--查找空闲事务长时间挂起会话
SELECT pid,usename,datname,state,backend_xact_start,query
FROM pg_stat_activity WHERE state='idle in transaction';

--终止问题会话
SELECT pg_terminate_backend(pid);

8.2表膨胀定位查询

MVCC产生死亡元组，查看表死亡元组数量，判断表膨胀风险。

SELECT relname,n_live_tup,n_dead_tup,last_vacuum,last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup>1000
ORDER BY n_dead_tup DESC;

8.3手动VACUUM垃圾回收实操

autovacuum后台自动执行，遇到大表膨胀可以手动执行vacuum；VACUUM ANALYZE清理同时更新统计信息。

--普通vacuum，清理死亡元组
VACUUM fg_t1;
--vacuum analyze，清理+更新统计信息
VACUUM ANALYZE fg_t1;
--VACUUM FULL，会锁表，重建表，回收磁盘碎片，业务高峰期禁止执行
VACUUM FULL fg_t1;

8.4基础巡检脚本pg_check.sh

脚本输出实例运行状态、连接数、表膨胀、磁盘占用，放置/fgedudb/script/pg_check.sh

#!/bin/bash
echo "========PostgreSQL实例巡检 $(date)========"
echo "1.实例运行状态"
systemctl status postgresql‑fgedudb | head -20
echo -e "\n2.数据库连接统计"
psql -U postgres -d postgres -t -c "SELECT state,count(*) FROM pg_stat_activity GROUP BY state;"
echo -e "\n3.表膨胀TOP10"
psql -U postgres -d fgedudb -t -c "SELECT relname,n_live_tup,n_dead_tup FROM pg_stat_user_tables ORDER BY n_dead_tup desc limit 10;"
echo -e "\n4.磁盘空间占用 /fgedudb目录"
df -h /fgedudb
du -sh /fgedudb/*
echo "========巡检结束========"

赋予权限执行：

chmod +x /fgedudb/script/pg_check.sh
/fgedudb/script/pg_check.sh

九、基础故障排查实战

9.1实例启动失败故障排查

现象：systemctl start启动失败。排查步骤：

1. 查看数据库日志/fgedudb/fgedudb_log/pgsql.log，绝大多数启动报错信息记录在这里。
2. 检查数据目录权限，必须为postgres用户，权限700，不能777。
3. 磁盘空间是否满，df‑h检查/fgedudb挂载点。
4. postgresql.conf参数语法错误，可以使用下面命令校验配置文件：

postgres -D /fgedudb/fgedudb_data -C

5. 端口5432是否被其他进程占用

ss -tlnp | grep 5432

9.2远程客户端无法连接数据库故障

常见根因：

1. listen_addresses没有设置’*'，只监听本地回环127.0.0.1；修改postgresql.conf，重启实例。
2. pg_hba.conf没有添加客户端IP网段访问策略，执行pg_ctl reload。
3. Linux防火墙firewalld开放5432端口，或者关闭防火墙。

firewall‑cmd --add‑port=5432/tcp --permanent
firewall‑cmd --reload

4. 客户端账号密码错误，scram‑sha‑256密码认证失败。

9.3磁盘空间耗尽故障

磁盘写满数据库实例会进入拒绝写入状态；优先清理旧备份、旧日志文件；不要直接手动删除data目录内部文件，会直接损坏实例。清理完磁盘空间之后执行VACUUM回收表空间。

风哥针对本文总结

风哥教程本文完整讲解Linux平台PostgreSQL安装配置与管理入门整套技术，分为理论原理与大量可复现实战操作。PostgreSQL运维工作，操作系统前期规划优先级高于数据库内部参数调整；sysctl内核参数、ulimit资源限制、XFS文件系统挂载、目录权限是数据库稳定运行底座，如果底座配置错误，实例运行会出现各类诡异故障。

两台标准化主机fgedu‑net‑cn1、fgedu‑net‑cn2分别演示源码编译、RPM包两种部署模式，64G内存8CPU硬件规格下，shared_buffers设置整机内存25%，effective_cache_size设置整机内存50%是通用基线配置，业务上线后再根据实际业务读写压力微调参数。

PostgreSQL运维几个核心风险点需要重点记忆：

1. MVCC机制带来表膨胀风险，autovacuum禁止人为关闭；定期巡检pg_stat_user_tables视图监控死亡元组数量，业务高峰期严禁执行VACUUM FULL，该操作会持有排他锁阻塞业务DML。
2. pg_hba.conf访问控制文件修改执行reload即可生效，不需要重启实例；远程连接故障优先排查该配置与防火墙策略。
3. 区分pg_dump逻辑备份与pg_basebackup物理备份，逻辑备份适合单库单表粒度误操作恢复；物理备份适合整个实例灾难重建，生产环境建议两种备份方式同时部署。
4. 角色、schema、数据库对象权限严格管控业务账号，业务账号不建议直接使用超级用户postgres执行业务SQL。
5. 故障排查优先读取数据库日志文件，绝大多数启动异常、连接报错、SQL报错信息全部记录在日志；不要直接手动删除data目录内部任何文件，会造成实例不可逆损坏。
6. 生产环境配置crontab定时备份任务，备份完成必须定期做恢复演练，只备份不做恢复演练等于没有备份。

本套风哥教程全部命令、配置基于PostgreSQL版本，硬件规格64G内存8CPU；读者落地实施时，需要结合自己业务硬件规格、业务读写压力做参数适配调整。