Postgresql快速入门
·
快速开始
安装Posgresql
编译安装
参考：
- https://blog.csdn.net/weixin_44821805/article/details/145676477
- https://www.postgresql.org/download/linux/
- 下载安装包并解压postgresql-14.8.tar.gz并进入目录，编译安装。
wget https://ftp.postgresql.org/pub/source/v14.8/postgresql-14.8.tar.gz
提示：需要准备gcc、g++等编译工具 libreadline-dev gcc
[root@pro31 postgresql-14.8]# mkdir -p /usr/local/pg14
[root@pro31 postgresql-14.8]# pwd
/tgz/postgresql-14.8
[root@pro31 postgresql-14.8]# ll
total 780
-rw-r--r-- 1 1107 1107 445 May 9 2023 aclocal.m4
drwxrwxrwx 2 1107 1107 4096 May 9 2023 config/
-rwxr-xr-x 1 1107 1107 594901 May 9 2023 configure*
-rw-r--r-- 1 1107 1107 86744 May 9 2023 configure.ac
drwxrwxrwx 58 1107 1107 4096 May 9 2023 contrib/
-rw-r--r-- 1 1107 1107 1192 May 9 2023 COPYRIGHT
drwxrwxrwx 3 1107 1107 4096 May 9 2023 doc/
-rw-r--r-- 1 1107 1107 4279 May 9 2023 GNUmakefile.in
-rw-r--r-- 1 1107 1107 277 May 9 2023 HISTORY
-rw-r--r-- 1 1107 1107 63935 May 9 2023 INSTALL
-rw-r--r-- 1 1107 1107 1665 May 9 2023 Makefile
-rw-r--r-- 1 1107 1107 1213 May 9 2023 README
drwxrwxrwx 16 1107 1107 4096 May 9 2023 src/
[root@pro31 postgresql-14.8]# ./configure --prefix=/usr/local/pg14 --with-pgport=5432
监听端口是可以手动选择的
更多信息见
gmake编译
[root@pro31 postgresql-14.8]# gmake
[root@pro31 postgresql-14.8]# gmake install-world
- 创建普通用户postgres，创建数据目录/app/pg14，这2个目录授权给postgres
mkdir -pv /app/pg14
chown postgres:postgres -R /usr/local/pg14 /app/pg14
~/.bashrc
export LANG=en_US.utf8
export PGDATA=/app/pg14
export PGHOME=/usr/local/pg14
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export DATE=`date +"%Y-%m-%d %H:%M:%S"`
export PATH=$PGHOME/bin:$PATH
export MANPATH=$PGHOME/share/man:$MANPATH
export PGUSER=postgres
验证：
[postgres@pro31 ~]# psql --version
psql (PostgreSQL) 14.8
[postgres@pro31 ~]# initdb -A md5 -D $PGDATA -E utf8 --locale=C -W
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 "C".
The default text search configuration will be set to "english".
Data page checksums are disabled.
Enter new superuser password: 密码***
Enter it again: 再次输入密码
fixing permissions on existing directory /app/pg14 ... 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
Success. You can now start the database server using:
pg_ctl -D /app/pg14 -l logfile start
启动数据库
[postgres@pro31 ~]# pg_ctl -D /app/pg14 start
waiting for server to start....2025-09-13 01:01:51.034 CST [17942] LOG: starting PostgreSQL 14.8 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04.2) 11.4.0, 64-bit
2025-09-13 01:01:51.034 CST [17942] LOG: listening on IPv4 address "127.0.0.1", port 5432
2025-09-13 01:01:51.035 CST [17942] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2025-09-13 01:01:51.039 CST [17943] LOG: database system was shut down at 2025-09-13 00:59:21 CST
2025-09-13 01:01:51.041 CST [17942] LOG: database system is ready to accept connections
done
server started
关闭数据库，重启是 pg_ctl restart
[postgres@pro31 ~]# pg_ctl -D /app/pg14 stop
waiting for server to shut down....2025-09-13 00:59:21.233 CST [17807] LOG: received fast shutdown request
2025-09-13 00:59:21.234 CST [17807] LOG: aborting any active transactions
2025-09-13 00:59:21.234 CST [17807] LOG: background worker "logical replication launcher" (PID 17814) exited with exit code 1
2025-09-13 00:59:21.234 CST [17809] LOG: shutting down
2025-09-13 00:59:21.244 CST [17807] LOG: database system is shut down
done
server stopped
启动时指定日志文件保存：
pg_ctl -D /app/pg14 -l logfile start
-- 清除当前输入缓存(可选)
\abort
-- 执行创建命令
CREATE DATABASE wzy;
-- \l列出数据库
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
wzy | postgres | UTF8 | C | C |
-- 切换数据库
postgres=# \c wzy
You are now connected to database "wzy" as user "postgres".
postgres=# create table t1(id int);
CREATE TABLE
postgres=# select * from t1;
id
----
(0 rows)
-- 查看表
wzy=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | t1 | table | postgres
wzy=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | t1 | table | postgres
(1 row)
远程连接管理，一但匹配到某个连接规则，就不在往下匹配。这一点和防火墙类似；新增一条规则允许所有用户从指定IP地址(10.0.0.253)登录，tail -20 /app/pg14/pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all all 10.0.0.253/32 md5
# IPv6 local connections:
host all all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all md5
host replication all 127.0.0.1/32 md5
host replication all ::1/128 md5
修改主配置文件 /app/pg14/postgresql.conf，修改为监听所有端口。以实现远程登录。也可以指定网段
这2个配置文件修改完之后停止数据库，再重新启动
pg_ctl stop
pg_ctl start
[postgres@pro31 pg14]# psql -d postgres -U postgres -h 10.0.0.31
Password for user postgres:
psql (14.8)
Type "help" for help.
postgres=#
用户管理
1.创建用户
create user wzy;
或者指定为超级用户
create user admin with SUPERUSER PASSWORD '123@password';
2.登录新用户admin
[postgres@pro31 pg14]# psql -d postgres -U admin
Password for user admin: 输入密码
psql (14.8)
Type "help" for help.
postgres=#
3.删除用户：
drop user admin;
4.修改用户
alter user admin with password '新密码';
5.查看当前用户信息 \du
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
admin | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
普通用户的创建；创建普通用户user01，设置密码 wzy@123456，允许管理数据库wzy
-- 创建普通用户（不带SUPERUSER权限）
CREATE USER user01 WITH
PASSWORD 'wzy@123456'
NOSUPERUSER NOCREATEDB NOCREATEROLE;
-- 将目标数据库的所有权赋给该用户
ALTER DATABASE wzy OWNER TO user01;
-- 授予该用户在wzy数据库上的所有权限
GRANT ALL PRIVILEGES ON DATABASE wzy TO user01;
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
admin | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
user01 | | {}
权限管理
Cluster权限：实例权限通过pg_hba.conf配置。
Database权限：数据库权限通过GRANT和REVOKE操作数据库配置。
TBS权限：表空间权限通过GRANT和REVOKE操作表空间配置。
Schema权限：模式权限通过GRANT和REVOKE操作模式配置。
Object权限：对象权限通过GRANT和REVOKE操作模式下的对象配置。
-- 允许在数据库创建表等对象
grant create on database wzy to user01;
schema权限，给abc用户授权
-- 修改Schema所有者权限，TO 后面的avc不是库，是role
ALTER SCHEMA abc OWNER TO abc;
-- 授予Schema内表的DML操作权限
GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA abc
TO abc;
Postgresql软件架构
相关进程
- postmaster：主控制进程，监听连接请求并派生子进程
- postgres：实际的工作进程（客户端连接对应的后端进程）
- checkpointer：定期执行检查点（Checkpoint），将脏页写入磁盘
- background writer：渐进式将脏页写入磁盘，减轻检查点压力
- walwriter：预写式日志（WAL）写入器，确保事务持久性
- autovacuum launcher：自动触发 VACUUM 和 ANALYZE 操作，维护数据库健康
- stats collector：收集数据库统计信息（如表访问频率）
- archiver：归档 WAL 日志（需配置归档模式）
- logical replication launcher：管理逻辑复制槽（需配置逻辑复制）
内存结构
日志管理
修改配置文件，这几这几个开关
log_destination = 'csvlog' # Valid values are combinations of
logging_collector = on # Enable capturing of stderr and csvlog
log_file_mode = 0600 # creation mode for log files,
log_rotation_age = 1d # Automatic rotation of logfiles will
log_rotation_size = 100MB # Automatic rotation of logfiles will
log_truncate_on_rotation = on # If on, an existing log file with the
- 日志格式，csv，后期可以导入到数据库中
- 把错误输出写入到日志中
- 日志轮转参数，保存大小，天数
还有其他的设置项：
# PostgreSQL 日志配置参数详解
# 以下配置需在 postgresql.conf 文件中设置
logging_collector = on # 启用日志收集器，将 stderr 和 csvlog 输出捕获到日志文件（选择 csv 格式日志时必须设置为 on，修改此参数需重启数据库生效）
log_directory = 'log' # 日志文件输出目录（相对 $PGDATA 的路径）
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # 日志文件名格式（支持 strftime 时间格式化）
log_file_mode = 0600 # 日志文件权限模式（0600 表示仅所有者可读写）
log_truncate_on_rotation = on # 启用日志轮转时清空/覆盖旧文件
log_rotation_age = 1d # 日志轮转时间间隔（1d=1天，支持单位：min/h/d）
log_rotation_size = 10MB # 日志文件大小轮转阈值（达到该大小后轮转）
log_min_messages = warning # 记录的最小日志级别（可选值：debug5, debug4, debug3, debug2, debug1, info, notice, warning, error, log, fatal, panic）
log_min_duration_statement = 60 # 慢 SQL 记录阈值（单位：毫秒，执行时间超过此值的 SQL 将被记录）
log_checkpoints = on # 记录检查点操作信息
log_connections = on # 记录所有客户端连接信息
log_disconnections = on # 记录所有客户端断开连接信息
log_duration = on # 记录所有 SQL 语句的执行时间
log_lock_waits = on # 记录超过死锁超时时间的锁等待信息（deadlock_timeout）
log_statement = 'ddl' # 记录执行的 SQL 语句类型（none：不记录，ddl：记录 DDL 语句，mod：记录 DDL 和 DML 语句，all：记录所有语句）
重要的配置
# PostgreSQL 核心配置参数说明
## 网络与连接配置
listen_addresses = '*' # 监听所有网络接口（默认仅监听本地）
port = 5432 # 默认服务端口号
max_connections = 2000 # 最大客户端连接数（默认100）
## 内存优化配置
shared_buffers = # 共享缓冲区大小（类似Oracle的buffer cache），建议设置为系统内存的1/4
maintenance_work_mem = # 维护操作内存（用于VACUUM、CREATE INDEX等），建议值：(1/4主机内存)/autovacuum_max_workers
## 并行处理配置
max_worker_processes = # 系统最大工作进程数
max_parallel_workers_per_gather = # 单个查询节点允许的最大并行工作进程数
## WAL日志配置
wal_level = replica # WAL日志级别（11+版本默认replica）
wal_buffers = # WAL缓冲区大小（类似Oracle的log buffer）
checkpoint_timeout = # 检查点超时间隔
max_wal_size = # 最大WAL日志大小
min_wal_size = # 最小WAL日志大小
## 高可用配置
archive_command = # 归档命令（示例：'test ! -f /archive/%f && cp %p /archive/%f'）
## 维护配置
autovacuum = on # 启用自动清理（推荐开启）
## 文件路径配置
unix_socket_directories = # Unix域套接字文件位置（默认在/tmp目录下）
控制文件
[postgres@pro31 ~]# pg_controldata $PGDATA
pg_control version number: 1300
Catalog version number: 202107181
Database system identifier: 7549247923405907335
Database cluster state: in production
pg_control last modified: Sat 13 Sep 2025 10:56:45 AM CST
Latest checkpoint location: 0/16D6CE0
Latest checkpoint's REDO location: 0/16D6CA8
Latest checkpoint's REDO WAL file: 000000010000000000000001
Latest checkpoint's TimeLineID: 1
Latest checkpoint's PrevTimeLineID: 1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID: 0:749
Latest checkpoint's NextOID: 16396
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID: 727
Latest checkpoint's oldestXID's DB: 1
Latest checkpoint's oldestActiveXID: 749
Latest checkpoint's oldestMultiXid: 1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint: Sat 13 Sep 2025 10:56:45 AM CST
Fake LSN counter for unlogged rels: 0/3E8
Minimum recovery ending location: 0/0
Min recovery ending loc's timeline: 0
Backup start location: 0/0
Backup end location: 0/0
End-of-backup record required: no
wal_level setting: replica
wal_log_hints setting: off
max_connections setting: 100
max_worker_processes setting: 8
max_wal_senders setting: 10
max_prepared_xacts setting: 0
max_locks_per_xact setting: 64
track_commit_timestamp setting: off
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Size of a large-object chunk: 2048
Date/time type storage: 64-bit integers
Float8 argument passing: by value
Data page checksum version: 0
Mock authentication nonce: 7b7f99965c6b8cb52329d15cd78198bfe635384615cc26a37f0f70f0ee48f68e
WAL和检查点相关
| 字段 | 含义 |
|---|---|
| Latest checkpoint location: 0/16D6CE0 | 最近一次完整检查点的 WAL 位置（LSN）。表示“从这个位置开始，所有脏页都已刷盘”。恢复时从此处开始重做。 |
| Latest checkpoint’s REDO location: 0/16D6CA8 | 检查点记录中记录的“REDO 起始点”。这是恢复时必须重做的最小 WAL 位置。通常 ≤ checkpoint location。 |
| Latest checkpoint’s REDO WAL file: 000000010000000000000001 | 包含 REDO 位置的 WAL 文件名。格式为 XXXXXXXXXXXXXXXXXXXXXXXX（24位十六进制），前8位是时间线ID，后16位是日志序列号。 |
| Latest checkpoint’s TimeLineID: 1 | 当前时间线 ID。初始为 1，发生 PITR（基于时间点恢复）或主备切换时会递增。用于防止混淆不同分支的 WAL。 |
| Latest checkpoint’s PrevTimeLineID: 1 | 上一个时间线 ID（仅在时间线切换时变化）。此处未切换，故相同。 |
| Latest checkpoint’s full_page_writes: on | 是否开启全页写（Full Page Writes, FPW）。 开启可防止“部分写”导致的数据损坏（如断电时只写了一半页面）。 代价：增加 WAL 体积。 |
| Latest checkpoint’s NextXID: 0:749 | 下一个可用事务 ID（XID）。格式：epoch:counter。PostgreSQL 使用 32 位 XID，需循环使用（需 VACUUM 清理旧事务）。 |
| Latest checkpoint’s NextOID: 16396 | 下一个对象 OID（对象标识符）。用于内部对象（如表、索引）的唯一编号。注意：自 PG 12+ 默认禁用用户表使用 OID，此值一般无大用。 |
| Latest checkpoint’s NextMultiXactId: 1 | 下一个多事务 ID（用于行锁、多事务并发更新）。 |
| Latest checkpoint’s NextMultiOffset: 0 | 多事务偏移量，用于定位共享内存中的多事务成员列表。 |
| Latest checkpoint’s oldestXID: 727 | 最老的仍可能被引用的事务 ID。VACUUM 不能清理比这更老的事务，因为可能还有活跃查询或快照引用它。 |
| Latest checkpoint’s oldestXID’s DB: 1 | 最老事务所属的数据库 OID（通常是 template1 或 postgres）。 |
| Latest checkpoint’s oldestActiveXID: 749 | 当前活动事务中最老的 XID。若该值长期停滞，说明有长事务或未提交事务阻塞清理。 |
| Latest checkpoint’s oldestMultiXid: 1 | 最老的多事务 ID，用于多事务清理。 |
| Latest checkpoint’s oldestMulti’s DB: 1 | 最老多事务所属数据库。 |
| Latest checkpoint’s oldestCommitTsXid: 0 | 最老的提交时间戳事务 ID。只有启用 track_commit_timestamp 时才有效（本例为 off）。 |
| Latest checkpoint’s newestCommitTsXid: 0 | 最新的提交时间戳事务 ID。同上。 |
| Time of latest checkpoint: Sat 13 Sep 2025 10:56:45 AM CST | 最近一次检查点发生的时间。可用于判断是否频繁检查点（如每分钟一次则异常）。 |
WAL相关设置（来自配置文件）
| 字段 | 含义 |
|---|---|
| Fake LSN counter for unlogged rels: 0/3E8 | 用于非持久化表（UNLOGGED）的伪 LSN 计数器。这些表在重启后会被清空，但为了保持一致性，仍需模拟 LSN。 |
| Minimum recovery ending location: 0/0 | 恢复过程中允许结束的最小 WAL 位置。用于流复制或 PITR。0/0 表示无限制。 |
| Min recovery ending loc’s timeline: 0 | 恢复允许结束的最小时间线。0 表示不限制。 |
| Backup start location: 0/0 | 当前是否有备份操作？0/0 表示无。如果有，这里会记录备份开始的 LSN。 |
| Backup end location: 0/0 | 备份结束位置。0/0 表示无备份。 |
| End-of-backup record required: no | 是否需要“备份结束记录”？如果是 yes，表示这是一个在线备份（如 pg_start_backup()），必须有对应的 pg_stop_backup() 记录才能安全恢复。 |
查询 PostgreSQL 系统表 pg_class 中，名为 't1' 的表（或索引、视图等）对应的 文件节点号（relfilenode），即该对象在磁盘上的物理文件标识符
select relfilenode from pg_class where relname='t1';
wal日志
PostgreSQL 的 WAL（Write-Ahead Logging）日志在功能上等同于 Oracle 的 Redo Log，都是数据库实现 ACID 特性中“持久性”（Durability）和“崩溃恢复” 的核心。为什么叫 “Write-Ahead”？因为设计原则是：先写日志，再改数据。假设执行：UPDATE t1 SET name = 'Alice' WHERE id = 1;
会按以下顺序处理：
- 将这个更新操作（包括旧值、新值、事务ID、位置等）写入 WAL / Redo Log
- 在内存中修改数据页（Buffer Cache）
- 后台异步将脏页刷回磁盘（Checkpoint）
如果在步骤2之后、步骤3之前断电了,数据还没落盘，但 WAL 已经写入，恢复时能根据 WAL 重新应用这个更新！
查看当前sal状态：
-- 查看当前正在写入的 WAL 文件
SELECT pg_current_wal_lsn();
postgres=# SELECT pg_walfile_name(pg_current_wal_lsn());
pg_walfile_name
--------------------------
000000010000000000000001
(1 row)
使用工具pg_waldump查看
/app/pg14/pg_wal
rmgr: <资源管理器> len (rec/tot): <记录长度>/<总长度>, tx: <事务ID>, lsn: <位置>, prev <前一个LSN>, desc: <描述>
[postgres@pro31 pg_wal]# pg_waldump 000000010000000000000001
rmgr: Btree len (rec/tot): 72/ 72, tx: 257, lsn: 0/01340F38, prev 0/01340EE8, desc: INSERT_LEAF off 213, blkref #0: rel 1663/1/2673 blk 31
rmgr: Btree len (rec/tot): 72/ 72, tx: 257, lsn: 0/01340F80, prev 0/01340F38, desc: INSERT_LEAF off 277, blkref #0: rel 1663/1/2674 blk 42
rmgr: Heap len (rec/tot): 80/ 80, tx: 257, lsn: 0/01340FC8, prev 0/01340F80, desc: INSERT off 86 flags 0x00, blkref #0: rel 1663/1/2608 blk 50
rmgr: Btree len (rec/tot): 72/ 72, tx: 257, lsn: 0/01341018, prev 0/01340FC8, desc: INSERT_LEAF off 214, blkref #0: rel 1663/1/2673 blk 31
rmgr: Btree len (rec/tot): 72/ 72, tx: 257, lsn: 0/01341060, prev 0/01341018, desc: INSERT_LEAF off 278, blkref #0: rel 1663/1/2674 blk 42
rmgr: Heap len (rec/tot): 80/ 80, tx: 257, lsn: 0/013410A8, prev 0/01341060, desc: INSERT off 87 flags 0x00, blkref #0: rel 1663/1/2608 blk 50
rmgr: Btree len (rec/tot): 72/ 72, tx: 257, lsn: 0/013410F8, prev 0/013410A8, desc: INSERT_LEAF off 215, blkref #0: rel 1663/1/2673 blk 31
rmgr: Btree len (rec/tot): 72/ 72, tx: 257, lsn: 0/01341140, prev 0/013410F8, desc: INSERT_LEAF off 279, blkref #0: rel 1663/1/2674 blk 42
rmgr: Heap len (rec/tot): 80/ 80, tx: 257, lsn: 0/01341188, prev 0/01341140, desc: INSERT off 88 flags 0x00, blkref #0: rel 1663/1/2608 blk 50
rmgr: Btree len (rec/tot): 72/ 72, tx: 257, lsn: 0/013411D8, prev 0/01341188, desc: INSERT_LEAF off 216, blkref #0: rel 1663/1/2673 blk 31
rmgr: Btree len (rec/tot): 72/ 72, tx: 257, lsn: 0/01341220, prev 0/013411D8, desc: INSERT_LEAF off 280, blkref #0: rel 1663/1/2674 blk 42
rmgr: Heap len (rec/tot): 80/ 80, tx: 257, lsn: 0/01341268, prev 0/01341220, desc: INSERT off 89 flags 0x00, blkref #0: rel 1663/1/2608 blk 50
rmgr: Btree len (rec/tot): 72/ 72, tx: 257, lsn: 0/013412B8, prev 0/01341268, desc: INSERT_LEAF off 217, blkref #0: rel 1663/1/2673 blk 31
rmgr: Btree len (rec/tot): 72/ 72, tx: 257, lsn: 0/01341300, prev 0/013412B8, desc: INSERT_LEAF off 281, blkref #0: rel 1663/1/2674 blk 42
-- 查看 PostgreSQL 的 WAL 目录中所有 WAL 文件的列表，并按“修改时间升序排列
postgres=# select * from pg_ls_waldir() order by modification asc;
name | size | modification
--------------------------+----------+------------------------
000000010000000000000001 | 16777216 | 2025-09-13 11:51:53+08
arch wal日志
wal_level = replica # minimal, replica, or logical
# (change requires restart)
fsync = on # flush data to disk for crash safety
# (turning this off can cause
# unrecoverable data corruption)
synchronous_commit = on # synchronization level;
# off, local, remote_write, remote_apply, or on
wal_sync_method = fsync # the default is the first option
# supported by the operating system:
# open_datasync
# fdatasync (default on Linux and FreeBSD)
# fsync
# fsync_writethrough
# open_sync
archive_mode = off # enables archiving; off, on, or always
# (change requires restart)
archive_command = 'test ! -f /backup/pg14/arch/%f && cp %p /backup/pg14/arch/%f'
创建目录：mkdir -p /backup/pg14/arch
当前wal日志名称：
wzy=# SELECT pg_walfile_name(pg_current_wal_lsn());
pg_walfile_name
--------------------------
000000010000000000000001
-- 切换到wzy库后，t2表插入1w条数据
INSERT INTO t2 (id) SELECT generate_series(1, 10000);
-- 可以看到wal日志变成了32，（如果没有切换，就多插入几次数据）
wzy=# SELECT pg_walfile_name(pg_current_wal_lsn());
pg_walfile_name
--------------------------
000000010000000000000032
查看归档wal日志
[root@pro31 log]# ll /backup/pg14/arch/
total 65536
-rw------- 1 postgres postgres 16777216 Sep 13 15:16 000000010000000000000032
-rw------- 1 postgres postgres 16777216 Sep 13 15:16 000000010000000000000033
-rw------- 1 postgres postgres 16777216 Sep 13 15:16 000000010000000000000034
-rw------- 1 postgres postgres 16777216 Sep 13 15:16 000000010000000000000035
数据备份
逻辑备份
pg_dump、navicat等工具
# 备份 wzy 库里的数据
pg_dump -d wzy > /backup/pg14/pg_dump.sql
# 查看前10条数据和mysqldump导出类似
head /backup/pg14/pg_dump.sql
--
-- PostgreSQL database dump
--
-- Dumped from database version 14.8
-- Dumped by pg_dump version 14.8
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
删除wzy库，后导入/backup/pg14/pg_dump.sql
[postgres@pro31 pg14]# psql -d wzy < /backup/pg14/pg_dump.sql
Password for user postgres:
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
COMMENT
COMMENT
COMMENT
CREATE TABLE
ALTER TABLE
COPY 1
COPY 1000000
ALTER TABLE
物理备份
[postgres@pro31 pg14]# pg_basebackup -D "/backup/pg14/basebackup/$(date +%Y%m%d_%H%M%S)" -Ft -Pv -U postgres -R
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/39000060 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_62739"
104548/104548 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/39000138
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
[postgres@pro31 pg14]# ll -h /backup/pg14/basebackup/20250913_160000/
total 119M
-rw------- 1 postgres postgres 180K Sep 13 16:00 backup_manifest
-rw------- 1 postgres postgres 103M Sep 13 16:00 base.tar
-rw------- 1 postgres postgres 17M Sep 13 16:00 pg_wal.tar
[postgres@pro31 pg14]# pg_ctl stop
waiting for server to shut down.... done
server stopped
[postgres@pro31 pg14]# rm -rf /app/pg14/*
[postgres@pro31 pg14]# rm -rf /backup/pg14/arch/*
1.把以前的备份数据解压到数据目录
[postgres@pro31 pg14]# tar xf /backup/pg14/basebackup/20250913_160000/base.tar -C /app/pg14/
[postgres@pro31 pg14]# tar xf /backup/pg14/basebackup/20250913_160000/pg_wal.tar -C /backup/pg14/arch/
2.增加2行配置，/app/pg14/postgresql.auto.conf
restore_command = 'cp /backup/pg14/arch/%f %p'
recovery_target = 'immediate'
3.创建文件，用户记录恢复过程(必须)
touch /app/pg14/recovery.signal
4.启动数据库
[postgres@pro31 pg14]# pg_ctl start
waiting for server to start....2025-09-13 17:03:37.879 CST [62914] LOG: redirecting log output to logging collector process
2025-09-13 17:03:37.879 CST [62914] HINT: Future log output will appear in directory "log".
done
server started
5.数据恢复成功
6.此时数据库是保护模式，不能进行读写，执行以下操作后，就可以读写了
[postgres@pro31 pg14]# pg_ctl promote -D /app/pg14
waiting for server to promote.... done
server promoted
PITR恢复
恢复案例
备份是01:00进行的，14:00删除了一张表，如何恢复？如果删除的是其中几行数据如何恢复？
创建数据库pit，然后写入数据
postgres=# create database pit;
CREATE DATABASE
postgres=# \c pit
You are now connected to database "pit" as user "postgres".
pit=# create table t1 (id int);
CREATE TABLE
pit=# insert into t1 values (1);
INSERT 0 1
pit=# insert into t1 values (2);
INSERT 0 1
pit=# insert into t1 values (3);
- 实施物理备份
pg_basebackup -D "/backup/pg14/basebackup/$(date +%Y%m%d_%H%M%S)" -Ft -Pv -U postgres -R
- 进行数据增量
pit=# create table t2 (id int);
CREATE TABLE
pit=# insert into t2 values (1);
INSERT 0 1
pit=# insert into t2 values (2);
INSERT 0 1
此时，所有数据
pit=# select * from t1;
id
----
1
2
3
(3 rows)
pit=# select * from t2;
id
----
1
2
(2 rows
- 删除数据库 pit
pit=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# drop database pit;
DROP DATABASE
- 关闭数据库实例，把原实例的数据目录移动到其他目录
pg_ctl stop
mv /app/pg14/* /backup/pg_data_bak
- 从历史备份中解压并恢复数据
tar xf /backup/pg14/basebackup/20250914_000421/base.tar -C /app/pg14/
tar xf /backup/pg14/basebackup/20250914_000421/pg_wal.tar -C /app/pg14/
查看wal日志文件和之前配置的arch是一致的, …3A
# wal日志
ll /app/pg14/
total 16532
-rw------- 1 postgres postgres 16777216 Sep 14 00:04 00000002000000000000003A
# arch归档时的文件
[postgres@pro31 arch]# ll
total 81928
-rw------- 1 postgres postgres 16777216 Sep 13 16:00 000000010000000000000039
-rw------- 1 postgres postgres 16777216 Sep 14 00:04 000000020000000000000039
-rw------- 1 postgres postgres 16777216 Sep 14 00:04 00000002000000000000003A
-rw------- 1 postgres postgres 341 Sep 14 00:04 00000002000000000000003A.00000028.backup
-rw------- 1 postgres postgres 16777216 Sep 14 00:12 00000002000000000000003B
-rw------- 1 postgres postgres 16777216 Sep 14 00:14 00000002000000000000003C
-rw------- 1 postgres postgres 33 Sep 13 23:32 00000002.history
tar xf /backup/pg14/basebackup/20250914_000421/pg_wal.tar -C /backup/pg14/arch/
[postgres@pro31 arch]# ll /backup/pg14/arch/
total 81932
-rw------- 1 postgres postgres 16777216 Sep 13 16:00 000000010000000000000039
-rw------- 1 postgres postgres 16777216 Sep 14 00:04 000000020000000000000039
-rw------- 1 postgres postgres 16777216 Sep 14 00:04 00000002000000000000003A
-rw------- 1 postgres postgres 341 Sep 14 00:04 00000002000000000000003A.00000028.backup
-rw------- 1 postgres postgres 16777216 Sep 14 00:12 00000002000000000000003B
-rw------- 1 postgres postgres 16777216 Sep 14 00:14 00000002000000000000003C
-rw------- 1 postgres postgres 33 Sep 14 00:04 00000002.history
drwxrwxr-x 2 postgres postgres 4096 Sep 14 00:57 archive_status/
- 修改postgresql.auto.conf，如果有 primary_conninf 就全部注释
recovery_target需要重新指定，具体可以看arch目录下的wal日志，看DROP dir出现在哪个文件中：
[postgres@pro31 arch]# ll
total 81932
-rw------- 1 postgres postgres 16777216 Sep 13 16:00 000000010000000000000039
-rw------- 1 postgres postgres 16777216 Sep 14 00:04 000000020000000000000039
-rw------- 1 postgres postgres 16777216 Sep 14 00:04 00000002000000000000003A
-rw------- 1 postgres postgres 341 Sep 14 00:04 00000002000000000000003A.00000028.backup
-rw------- 1 postgres postgres 16777216 Sep 14 00:12 00000002000000000000003B
-rw------- 1 postgres postgres 16777216 Sep 14 00:14 00000002000000000000003C
-rw------- 1 postgres postgres 33 Sep 14 00:04 00000002.history
drwxrwxr-x 2 postgres postgres 4096 Sep 14 00:57 archive_status/
[postgres@pro31 arch]# pg_waldump 00000002000000000000003C
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/3C000028, prev 0/3B015AD0, desc: RUNNING_XACTS nextXid 823 latestCompletedXid 822 oldestRunningXid 823
rmgr: Heap len (rec/tot): 59/ 2663, tx: 823, lsn: 0/3C000060, prev 0/3C000028, desc: DELETE off 11 flags 0x00 KEYS_UPDATED , blkref #0: rel 1664/0/1262 blk 0 FPW
rmgr: Standby len (rec/tot): 54/ 54, tx: 0, lsn: 0/3C000AC8, prev 0/3C000060, desc: RUNNING_XACTS nextXid 824 latestCompletedXid 822 oldestRunningXid 823; 1 xacts: 823
rmgr: Standby len (rec/tot): 54/ 54, tx: 0, lsn: 0/3C000B00, prev 0/3C000AC8, desc: RUNNING_XACTS nextXid 824 latestCompletedXid 822 oldestRunningXid 823; 1 xacts: 823
rmgr: XLOG len (rec/tot): 114/ 114, tx: 0, lsn: 0/3C000B38, prev 0/3C000B00, desc: CHECKPOINT_ONLINE redo 0/3C000B00; tli 2; prev tli 2; fpw true; xid 0:824; oid 24622; multi 1; offset 0; oldest xid 727 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 823; online
rmgr: Database len (rec/tot): 38/ 38, tx: 823, lsn: 0/3C000BB0, prev 0/3C000B38, desc: DROP dir 1663/16430
rmgr: Transaction len (rec/tot): 66/ 66, tx: 823, lsn: 0/3C000BD8, prev 0/3C000BB0, desc: COMMIT 2025-09-14 00:13:40.232032 CST; inval msgs: catcache 21; sync
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/3C000C20, prev 0/3C000BD8, desc: RUNNING_XACTS nextXid 824 latestCompletedXid 823 oldestRunningXid 824
rmgr: XLOG len (rec/tot): 24/ 24, tx: 0, lsn: 0/3C000C58, prev 0/3C000C20, desc: SWITCH
这里出现在 00000002000000000000003C ，删除数据库 pit 发生在这里，事务号是523，所以恢复到822就可以了
完整版 primary_conninf
restore_command = 'cp /backup/pg14/arch/%f %p'
recovery_target_xid = '822'
- 启动数据库，查看pit库下的表全部还原了
pit=# selet * from t1;
ERROR: syntax error at or near "selet"
LINE 1: selet * from t1;
^
pit=# select * from t1;
id
----
1
2
3
(3 rows)
pit=# select * from t2;
id
----
1
2
(2 rows)
注意此时，还是只读模式，可以修改
pit=# create table t3(id int);
ERROR: cannot execute CREATE TABLE in a read-only transaction
[postgres@pro31 arch]# pg_ctl promote -D /app/pg14
waiting for server to promote.... done
server promoted
这时，就可以修改数据了
pit=# create table t3(id int);
CREATE TABLE
自定义标记点
自定义恢复点（Restore Point）。不是“备份”，而是在 WAL（Write-Ahead Logging）流中打一个“标记”，录对应的 WAL 文件位置和时间戳，后续做 PITR 恢复时，可以用这个标签作为 recovery_target_name 来定位恢复点
假设在 14:00 执行了：(打一个标记)
SELECT pg_create_restore_point('wzy-before-delete');
流复制
主库: 10.0.0.31 从库：10.0.0.41
主库操作：
- 创建复制用户
CREATE role replica with replication login password 'wzy@123456';
ALTER user replica WITH password 'wzy@123456'
- 主库修改配置文件后重启
max_wal_senders = 10
max_replication_slots = 10
wal_sender_timeout = 60s
hot_standby = on
wal_receiver_status_interval = 10s
hot_standby_feedback = on
wal_receiver_timeout = 60s
wal_retrieve_retry_interval = 5s
从库操作：
在从数据库关闭的状态下进行，或者安装一个新的数据库实例不启动
- 从主库那里物理备份
[postgres@node41 pg14]# pg_basebackup -D "/backup/pg14/basebackup/$(date +%Y%m%d_%H%M%S)" -Ft -Pv -U postgres -h 10.0.0.31 -R
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/3E000028 on timeline 4
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_6696"
129226/129226 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/3E000138
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
- 删除已有的数据，并从从归档数据恢复
# 我的配置文件里指定了这2个目录，一个是数据目录，一个是归档目录，所以防止干扰进行删除
rm -rf /app/pg14/* /backup/pg14/arch/*
tar xf /backup/pg14/basebackup/20250914_225315/base.tar -C /app/pg14/
tar xf /backup/pg14/basebackup/20250914_225315/pg_wal.tar -C /backup/pg14/arch/
- 设置从库只读
cat standby.signal
standby_mode = 'on'
- 配置主库连接信息，可以只修改 postgresql.auto.conf
关于复制用户，可以使用之前创建的repl；也可使用管理员用户 postgres
[postgres@node41 pg14]# yy postgresql.auto.conf
restore_command = 'cp /backup/pg14/arch/%f %p'
primary_conninfo = 'user=postgres password=***** channel_binding=disable host=10.0.0.31 port=5432 sslmode=disable sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any'
- 设置replica 配置，修改postgresql.conf
recovery_target_timeline = 'latest'
hot_standby = on
- 启动从库
pg_ctl start
验证流复制是否成功：
- 主库上查看
select pid, state, client_addr, sync_priority, sync_state from pg_stat_replication;
pid | state | client_addr | sync_priority | sync_state
------+-----------+-------------+---------------+------------
6761 | streaming | 10.0.0.41 | 0 | async
从库上查看
postgres=# select * from pg_stat_wal_receiver;
pid | status | receive_start_lsn | receive_start_tli | written_lsn | flushed_lsn | received_tli | last_msg_send_time | last_msg_receipt_time | latest_end_lsn | latest_end_time | slot_name | sender_host | sender_port | conninfo
-------+-----------+-------------------+-------------------+-------------+-------------+--------------+-------------------------------+-------------------------------+----------------+-------------------------------+-----------+-------------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
44592 | streaming | 0/3F000000 | 4 | 0/3F000148 | 0/3F000148 | 4 | 2025-09-14 23:21:44.481354+08 | 2025-09-14 23:21:44.479399+08 | 0/3F000148 | 2025-09-14 23:16:13.953419+08 | | 10.0.0.31 | 5432 | user=postgres password=******** channel_binding=disable dbname=replication host=10.0.0.31 port=5432 fallback_application_name=walreceiver sslmode=disable sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any
(1 row)
- 在主库创建数据库 test，查看是否同步
主：create database test;
更多推荐
8
0- 0
已为社区贡献5条内容
所有评论(0)