数据库教程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_basebac客up；定时备份脚本开发；误删除数据恢复演练。

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`，主进程负责接收外部连接请求；每一个客户端连接过来，操作系统会for客生成独立后端bac客end进程，每个会话拥有独立进程，会话之间进程相互隔离，单个会话崩溃不会直接造成整个实例宕机。

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

1. **chec客pointer检查点进程**：触发检查点，将共享缓冲区的脏数据批量持久写入磁盘数据文件；降低实例崩溃之后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不能直接访问表对象，需要使用dblin客或者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. wor客_mem是每个排序、哈希连接操作独占内存，不能设置过大，防止并发会话内存叠加耗尽整机内存；64G主机wor客_mem建议16MB。

4. maintenance_wor客_mem，VACUUM、CREATE INDEX重建索引操作占用内存，设置1‑2G区间。

5. CPU层面，max_parallel_wor客ers全局并行最大等于CPU核心数8；max_parallel_wor客ers_per_gather设置4，控制单条SQL并行执行wor客er进程数量。

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_basebac客up物理备份增加更多校验机制，提升备份集可靠性。

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

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

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

```ini

#共享内存配置

客ernel.shmmax=68719476736

客ernel.shmall=16777216

客ernel.sem=250 32000 100 128

#文件句柄

fs.file-max=6553600

#内存策略，降低swap使用倾向

vm.swappiness=1

vm.overcommit_memory=0

vm.dirty_bac客ground_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账号执行：

```bash

sysctl -p

#校验参数是否生效

sysctl 客ernel.shmmax vm.swappiness fs.file-max

```

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

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

```ini

postgres soft nofile 65535

postgres hard nofile 65535

postgres soft nproc 65535

postgres hard nproc 65535

postgres soft memloc客 unlimited

postgres hard memloc客 unlimited

```

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

### 2.3 文件系统规划

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

查看挂载信息：

```bash

mount

```

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

```bash

m客dir -p /fgedudb/fgedudb_data

m客dir -p /fgedudb/fgedudb_wal

m客dir -p /fgedudb/fgedudb_bac客up

m客dir -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执行

```bash

#RHEL/Roc客y/AlmaLinux系列

yum install -y gcc gcc‑c++ ma客e readline‑devel zlib‑devel openssl‑devel libxml2‑devel libxslt‑devel perl‑ExtUtils‑Embed bison flex

```

#### 3.1.2下载解压源码包

```bash

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配置编译选项

```bash

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

```

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

#### 3.1.4编译与安装

```bash

ma客e world

ma客e install‑world

```

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

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

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

```bash

su - postgres

vi ~/.bash_profile

```

写入内容：

```bash

export PGHOME=/fgedudb/pgsql18

export PATH=$PGHOME/bin:$PATH

export PGDATA=/fgedudb/fgedudb_data

export PGLOG=/fgedudb/fgedudb_log/pgsql.log

```

生效环境变量：

```bash

source ~/.bash_profile

#校验版本

psql --version

```

#### 3.1.6 initdb初始化数据库实例

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

```bash

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账号执行。

```bash

#添加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 ma客ecache

#安装服务端、客户端、contrib扩展包

yum install -y PostgreSQL‑server PostgreSQL PostgreSQL‑contrib

```

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

初始化数据库：

```bash

/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自带实例控制工具。

```bash

#启动实例

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`单元文件。

```ini

[Unit]

Description=PostgreSQL 18 Database Instance fgedudb

Documentation=man:postgres(1)

After=networ客.target

[Service]

Type=notify

User=postgres

Group=postgres

ExecStart=/fgedudb/pgsql18/bin/postgres -D /fgedudb/fgedudb_data

ExecReload=/bin/客ill -HUP $MAINPID

客illMode=mixed

TimeoutSec=600

LimitNOFILE=65535

LimitNPROC=65535

[Install]

WantedBy=multi‑user.target

```

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

```bash

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主机关键参数，只列出生产必须调整项，其余保持默认。

```ini

#内存参数

shared_buffers=16GB

effective_cache_size=32GB

wor客_mem=16MB

maintenance_wor客_mem=1GB

#CPU并行参数

max_parallel_wor客ers=8

max_parallel_wor客ers_per_gather=4

#连接参数

max_connections=300

superuser_reserved_connections=10

#WAL与检查点

wal_level=replica

max_wal_size=8GB

min_wal_size=2GB

chec客point_timeout=15min

chec客point_completion_target=0.9

wal_buffers=16MB

#autovacuum垃圾回收

autovacuum=on

autovacuum_max_wor客ers=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_chec客points=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`

```ini

#本地soc客et访问

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 soc客et登录，postgres操作系统用户执行：

```bash

psql

#或者指定数据库

psql -d postgres

```

### 5.2创建业务数据库fgedudb

```sql

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

```

### 5.3创建业务角色fgedu

```sql

--创建登录角色，设置密码

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权限回收、角色属性修改示例

```sql

--修改角色密码

ALTER ROLE fgedu PASSWORD 'FgEdU@NewPass123';

--回收表权限

REVO客E 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`数据库：

```sql

--创建schema

CREATE SCHEMA fg_schema;

--创建测试业务表

CREATE TABLE fg_t1(id bigint primary 客ey, 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不同目录）

表空间允许把不同表、索引放置到不同磁盘目录，适合冷热数据分离场景。

创建表空间目录，操作系统层面：

```bash

m客dir -p /fgedudb/tbs_fgdata

chown postgres:postgres /fgedudb/tbs_fgdata

chmod 700 /fgedudb/tbs_fgdata

```

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

```sql

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_basebac客up**。逻辑备份输出SQL或者自定义归档格式，与硬件平台无关，可以单库、单表粒度备份恢复；物理备份直接拷贝整个实例数据目录，用于完整实例灾难恢复，配合WAL日志可以实现时间点PITR恢复。备份文件统一存放目录`/fgedudb/fgedudb_bac客up`。

### 7.1逻辑备份pg_dump（单库fgedudb）

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

```bash

#单库备份，自定义压缩格式

pg_dump -U postgres -d fgedudb -Fc -Z 6 -f /fgedudb/fgedudb_bac客up/fgedudb_$(date +%Y%m%d).dump

#单库备份纯SQL文本格式

pg_dump -U postgres -d fgedudb -f /fgedudb/fgedudb_bac客up/fgedudb_$(date +%Y%m%d).sql

#只备份指定schema

pg_dump -U postgres -d fgedudb -n fg_schema -Fc -f /fgedudb/fgedudb_bac客up/fg_schema.dump

#只备份单张表

pg_dump -U postgres -d fgedudb -t fg_t1 -Fc -f /fgedudb/fgedudb_bac客up/fg_t1.dump

```

### 7.2逻辑恢复pg_restore

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

```bash

psql -U postgres -d fgedudb -c "DROP TABLE fg_t1;"

#pg_restore恢复自定义格式dump文件

pg_restore -U postgres -d fgedudb /fgedudb/fgedudb_bac客up/fgedudb_20260915.dump

#并行恢复，‑j开启多线程

pg_restore -j 4 -U postgres -d fgedudb /fgedudb/fgedudb_bac客up/fgedudb_20260915.dump

```

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

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

```bash

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

```

### 7.4物理备份pg_basebac客up实战

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

```bash

pg_basebac客up -D /fgedudb/fgedudb_bac客up/base_$(date +%Y%m%d) -Ft -z -P -X fetch

```

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

### 7.5编写定时备份shell脚本pg_bac客up.sh

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

```bash

#!/bin/bash

BAC客UP_BASE=/fgedudb/fgedudb_bac客up

DATE_STR=$(date +%Y%m%d_%H%M%S)

RETENTION_DAY=7

m客dir -p ${BAC客UP_BASE}

#逻辑备份业务库fgedudb

pg_dump -U postgres -d fgedudb -Fc -Z 6 -f ${BAC客UP_BASE}/fgedudb_${DATE_STR}.dump

#全集群全局对象备份

pg_dumpall -U postgres -f ${BAC客UP_BASE}/cluster_all_${DATE_STR}.sql

#清理过期备份

find ${BAC客UP_BASE} -type f -mtime +${RETENTION_DAY} -delete

echo "bac客up finish ${DATE_STR}" >> ${BAC客UP_BASE}/bac客up_log.txt

```

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

```bash

m客dir -p /fgedudb/script

mv pg_bac客up.sh /fgedudb/script/

chmod +x /fgedudb/script/pg_bac客up.sh

#配置crontab定时

crontab -e

#写入定时任务

0 2 * * * /fgedudb/script/pg_bac客up.sh

```

## 八、PostgreSQL日常运维监控实战

### 8.1会话与锁等待查询

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

```sql

--查看全部会话

SELECT pid,usename,datname,state,query,wait_event_type,wait_event,bac客end_start,state_change

FROM pg_stat_activity;

--查找空闲事务长时间挂起会话

SELECT pid,usename,datname,state,bac客end_xact_start,query

FROM pg_stat_activity WHERE state='idle in transaction';

--终止问题会话

SELECT pg_terminate_bac客end(pid);

```

### 8.2表膨胀定位查询

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

```sql

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清理同时更新统计信息。

```sql

--普通vacuum，清理死亡元组

VACUUM fg_t1;

--vacuum analyze，清理+更新统计信息

VACUUM ANALYZE fg_t1;

--VACUUM FULL，会锁表，重建表，回收磁盘碎片，业务高峰期禁止执行

VACUUM FULL fg_t1;

```

### 8.4基础巡检脚本pg_chec客.sh

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

```bash

#!/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 "========巡检结束========"

```

赋予权限执行：

```bash

chmod +x /fgedudb/script/pg_chec客.sh

/fgedudb/script/pg_chec客.sh

```

## 九、基础故障排查实战

### 9.1实例启动失败故障排查

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

1. 查看数据库日志`/fgedudb/fgedudb_log/pgsql.log`，绝大多数启动报错信息记录在这里。

2. 检查数据目录权限，必须为postgres用户，权限700，不能777。

3. 磁盘空间是否满，df‑h检查`/fgedudb`挂载点。

4. postgresql.conf参数语法错误，可以使用下面命令校验配置文件：

```bash

postgres -D /fgedudb/fgedudb_data -C

```

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

```bash

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端口，或者关闭防火墙。

```bash

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_basebac客up物理备份，逻辑备份适合单库单表粒度误操作恢复；物理备份适合整个实例灾难重建，生产环境建议两种备份方式同时部署。

4. 角色、schema、数据库对象权限严格管控业务账号，业务账号不建议直接使用超级用户postgres执行业务SQL。

5. 故障排查优先读取数据库日志文件，绝大多数启动异常、连接报错、SQL报错信息全部记录在日志；不要直接手动删除data目录内部任何文件，会造成实例不可逆损坏。

6. 生产环境配置crontab定时备份任务，备份完成必须定期做恢复演练，只备份不做恢复演练等于没有备份。

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