备份恢复

# 备份
pg_dump --host hostname --port port -U user1 -d db1 -t t1 > table.sql

# 恢复
psql < dumpfile
psql -d sentry -U sentry -f dump1.sql
psql -h x.x.x.x -p 5432 -d db1 -U user1 -W pw -f xx.sql


# 恢复二进制备份
pg_restore -F non-plain-text-file

# 远程备份 + 恢复
pg_dump -h host1 dbname | psql -h host2 dbname

# 备份和恢复表结构和模式
pg_dump -U user1 -s db1 > backup.sql
psql -U user1 -d db1 -a -f backup.sql

-h  host
-p  port
-d  定义默认数据库

--inserts               导出表数据为 INSERT 语句, 默认为 COPY
--column-inserts        每个 INSERT 语句包含列名
-f --file file.sql      将输出保存到指定的文件

-j --jobs=              并行备份,并行粒度是表级别的

-n --schema=            指定模式, 可以多次使用
-N                      指定排除的模式, 可以多次使用

-t, --table=TABLE       指定表名, 可以指定多次
-T                      排除表名, 可以指定多次
--exclude-table         排除表

-x --no-privileges      不转储权限信息

-F --format=c|d|t|p     定义备份格式格式; custom | directory | tar| plain text
    plain 默认, 一个包含 SQL 命令的文本文件;
        不能并行恢复，恢复速度较慢

    custom 自定义格式是一种二进制格式，支持压缩
        不支持并行备份, 但并行恢复, 需要使用 pg_restore 工具来恢复
        支持数据压缩, 支持并行, 包含所有必要的元数据, 可以完整恢复数据库

    directory 新的备份格式, 支持更高级的并行备份和恢复
        支持并行, 二进制格式

    tar 单一文件, 支持压缩, 不支持并行
        生成的 .tar 文件可以使用标准的 tar 工具进行操作


-Z --compress=0-9       压缩级别

-b                      包含大对象

--lock-wait-timeout=    在等待表锁超时后失败

-O --no-owner           不指定对象的所有者
-a --data-only          仅导出表数据，不包括表结构和其他对象
-s --schema-only        只导出表结构(序列和索引等), 不包含数据

-c --clean              在重新创建前, 先删除对象
-C --create             在转储中包含命令, 以便创建数据库

-1 --single-transaction 作为单个事务进行

-w                      不提示输入密码
-W                      必须输入密码
-v                      输出详细信息

# 一般备份库
pg_dump dbname > dumpfile

# 导出表结构
pg_dump -U user1 -s db1 > backup.sql
pg_dump -U user1 db1 -W -s -t t_name > t.sql

# 流式压缩和恢复, 避免文件过大磁盘空间不足的场景
pg_dump dbname | gzip > filename.gz
gunzip -c filename.gz | psql dbname

# 导出多个表, 多次指定 -t 选项
# 正则匹配
pg_dump -d db1 -n sam1 -U user1 -Fp -t"*_id_seq" -f"f:/mchshd1.sql" --inserts

pg_dump -d db1 -n sam1 -U user1 -t "sam1.dict_area" -Fp -f"f:/cdump" --inserts -x -v


# 定期执行全量备份
pg_dump -U user1 -d db1 -F c -b -f /backup/full_backup.backup

# 执行增量备份
pg_dump -U user1 -d db1 -F c -b -f /backup/incremental_backup.backup --data-only

pg_dump -h host -U user1 -F d -f backup_directory db1

pg_dumpall > dumpfile

# 导出表为 csv
\COPY t_name TO 'content-0429.csv' WITH CSV HEADER;

# 备份 - 连接到数据库并执行 COPY 命令 - 导出数据没有 header 头
time psql -h database-1.xxxxx.ap-southeast-1.rds.amazonaws.com -p 5432 -U user1 -d db1 \
    -c "\COPY (SELECT * FROM public.tx WHERE user_send_time > '2025-10-21') TO 'tx10.csv' WITH CSV;"

# 删除新库多余数据
delete FROM public.tx WHERE user_send_time > '2025-10-21';
select count(1) FROM public.tx WHERE user_send_time > '2025-10-21';

# 恢复数据 - 指定第一行为头部
time psql -h me-master-1.xxxxx.ap-southeast-1.rds.amazonaws.com \
          -p 5432 -U user1 -d db1 \
          -c "\COPY public.tx FROM 'tx10.csv' WITH CSV HEADER;"

# 恢复数据 - 指定第一行为数据
time psql -h me-master-1.xxxxx.ap-southeast-1.rds.amazonaws.com \
          -p 5432 -U user1 -d db1 \
          -c "\COPY public.tx FROM 'tx10.csv' WITH CSV;"

# 需要创建 replication 权限的角色, 或者超级用户的角色;
create role repl nosuperuser replication login connection limit 32 encrypted password '111111';

# hba.conf
host replication repl 0.0.0.0/0 md5

# 并行备份
pg_basebackup -j 4 -D /path/to/backup

# 远程备份
pg_basebackup -F t -x -D /home/postgres/bak/`date +%F` -h 192.168.1.2 -p 5432 -U repl


# 增量备份

先执行一个全量备份
pg_basebackup -D /data1/fullbackup/ -Ft -R -Xs -Pv

再执行一个增量备份
pg_basebackup -D /data1/incrbackup/ -Ft -R -Xs -Pv -c fast -i /data1/fullbackup/backup_manifest

-- 合并全量和增量备份(只能对平文本备份进行合并,因此需要先解包或者直接进行Fp备份)
pg_combinebackup /data1/fullbackup /data1/incrbackup -o /data1/combinebackup/

pg_basebackup -D /postgresql/backup/full_$(date +%Y%m%d_%H%M) -h localhost -p 5432 -U postgres \
    -F tar -X stream -P -v -z --checkpoint=fast

-F tar             # tar格式便于传输和存储
-X stream          # 并行流式传输WAL日志
-P                 # 显示实时进度
-v                 # 详细输出
-z                 # gzip压缩
--checkpoint=fast  # 快速检查点减少业务影响
-X 表示备份开始后, 启动另一个流复制连接从主库接收 WAL 日志

--pgdata=/data/pg_data
--waldir=/data/pg_wal
--write-recovery-conf
--progress
--verbose

pg_restore [选项] [备份文件] [--dbname=<数据库名>]

-F  指定文件
-Ft 指定 tar 格式的文件
-Fp 指定 plain 文本格式的文件
-c  还原前清空库
-O  不还原所有者
-j  并行度
-n  指定还原的 模式
-t  指定还原的 表
-d  指定还原的 库

--data-only     只恢复数据

select pg_switch_wal();         # 手动触发 wal 文件切换

# 查看归档文件
SELECT * FROM pg_stat_archiver;

# 创建基础备份
pg_basebackup -D /backup/pg_basebackup -Fp -P -R
pg_basebackup -D /data/base_backup/$(date +%Y%m%d) -Fp -Xs

# 常用参数:
# -D: 备份目录
# -Fp: 纯格式(文件系统格式)
# -Ft: tar 格式
# -P: 显示进度
# -R: 创建恢复配置
# -X: WAL 日志包含方式

# 切换到 postgres 用户或实际用户进行操作, 避免权限问题
su - postgres

# 在数据目录创建 恢复触发文件 (版本 v12 之前使用 recovery.conf)
touch $PGDATA/recovery.signal

vi /var/lib/pgsql/14/data/postgresql.conf
更推荐这个文件 $PGDATA/postgresql.auto.conf 文件中设置恢复目标
如果知道确切时间, 使用 recovery_target_time:

# 设定恢复 wal 文件的命令, 从归档目录读取 WAL 日志用于重放事务
restore_command = 'cp /path/to/archive/%f %p'

# 可选: 指定恢复到特定时间点(按需配置, 不配置则恢复到最新), 支持 ='latest'
recovery_target_time = '2025-11-10 10:00:00'

# 可选: 指定恢复到特定 WAL 位置
recovery_target_lsn = '0/1234ABCD'

# 可选: 包含目标时间点
recovery_target_inclusive = true


# 启动数据库服务, 并实时查看日志, 观察恢复过程
$ pg_ctl -D $PGDATA start -l recovery.log
waiting for server to start.... done
server started

tail -f recovery.log

# 检查恢复后的数据
postgres=# select * from tx;


# 结束恢复模式并允许写入
SELECT pg_wal_replay_resume();