PG物理备份本地恢复实践
更新时间:2025-06-30
概览
本篇说明如何将公有云上已产生的备份文件下载并恢复到自建数据库中。
环境准备
操作系统
版本:Linux系统,具体Linux发行版不限,最好为市面上当前主流的Linux版本。
PostgreSQL
- 确认您要恢复的PostgreSQL版本,并安装对应的社区版PostgreSQL实例,且主要版本号一致(10,11,12,13,14,15,16,17)。
- 规划PostgreSQL数据文件路径:data。
- data所在磁盘剩余空间必须大于原始数据文件大小(注意:不是备份文件大小,因为RDS上产生的备份文件开启了压缩,若您不知道原始数据文件大小,也可以用备份文件大小粗略预估,通常备份文件和原始文件的压缩比为1:2 ~ 1:4之间)。
pg_ctl(PostgreSQL物理命令行工具)
- pg_ctl 是随 PostgreSQL 安装包一起提供的命令行工具。
- 主要用于启动、停止、重启 PostgreSQL 数据库服务器,以及查看服务器状态等管理操作。
- 你可以在 PostgreSQL 的 bin 目录下(如 /usr/pgsql-x.y/bin/ 或 /usr/local/pgsql/bin/ 或 Windows 下的安装目录)找到它。
常用命令示例:
Plain Text
1pg_ctl start # 启动数据库
2pg_ctl stop # 停止数据库
3pg_ctl restart # 重启数据库
4pg_ctl status # 查看数据库状态
官方文档链接: https://www.postgresql.org/docs/current/app-pg-ctl.html
恢复步骤
一、获取备份下载链接
- 登录RDS管理控制台。
- 在控制台页面左上角,选择实例所在地域,进入“云数据库 RDS列表”页面。
- 找到目标实例,点击实例名称,进入实例详情页面。
- 点击左侧导航的备份恢复按钮,进入备份管理页面。
- 在备份恢复页面,点击相应备份操作列中的下载。
二、流式恢复数据到本地:下载 -> 解包解压 -> 写入本地
Plain Text
1# 预设变量:
2tmp_datadir="/path-to-extract-and-decompress-dir" # 解压解包备份文件的临时路径
3download_url="https://download_url" # 在上一步中获取到的下载链接
4path_to_pg_ctl="/path-to-pg_ctl" # 包含pg_ctl命令行工具的路径,就在postgresql安装目录的bin目录下
5
6## 根据您donwload_url中文件的后缀选择如下两种恢复方式中的一种(确保您本地操作系统已经安装了wget和gzip这两种常用工具):
7## 1. 若您的备份文件后缀是tar,用下面的命令进行下载和解包解压
8mkdir -p ${tmp_datadir} && wget "${download_url}" --no-check-certificate -O - | tar -xf - -C ${tmp_datadir}
9## 2. 若您的备份文件后缀是tar.gz,用下面的命令进行下载和解包解压
10mkdir -p ${tmp_datadir} && wget "${download_url}" --no-check-certificate -O - | tar -xzf - -C ${tmp_datadir}
三、查看数据库状态并停止数据库
Plain Text
1# 预设变量:
2postgresql_datadir="/path-to-postgresql-datadir" # PostgreSQL数据文件目录
3
4## 1. 查看数据库状态
5${path_to_pg_ctl} —D ${postgresql_datadir} status
6## 2. 停止数据库(可选,如果数据库未运行则无需操作此步骤)
7${path_to_pg_ctl} stop —D ${postgresql_datadir} -m fast
四、替换数据文件目录
Plain Text
1# 备份或删除原始datadir:若postgresql_datadir已经存在则备份该路径,或者确定不需要了也可以直接删除
2[[ -d "${postgresql_datadir}" ]] && mv "${postgresql_datadir}" "${postgresql_datadir}_$(date '+%Y%m%d_%H%M%S')"
3
4# 替换数据文件路径:将步骤【三】准备完成的数据文件目录替换MySQL的datadir路径
5mv -v "${tmp_datadir}" "${postgresql_datadir}"
6
7# 将数据目录文件夹的操作权限改为700
8chmod 700 "${postgresql_datadir}"
五、修改postgresql.conf文件的配置
Plain Text
1# 预设变量:
2path_to_postgresql_conf="/path-to-postgresql-conf" # postgresql.conf文件路径,就在PostgreSQL数据文件目录下
3
4# 修改postgresql.conf文件配置
5vim ${path_to_postgresql_conf}
6
7# 1. 归档与WAL相关(必改)
8archive_command = '/home/mysql/xagent/script/bin/boscli cp %p http://bos.bj.baidubce.com/binlog/实例id/%f'
9# 本地建议:这个命令是上传 WAL 日志到百度云 BOS 的命令,本地没这个需求,也没有这个脚本和云地址
10 # (1) 建议:要么注释掉,要么改成适合本地的归档方式,比如备份到本地目录:archive_command = 'cp %p /some/local/dir/%f'
11 # (2)或者如果不做归档,可以设置为 archive_mode = off
12archive_mode = on
13# 如果不需要WAL归档(流复制/归档备份),可以关掉。设置为 archive_mode = off
14archive_timeout
15# 只在开启归档模式时有意义。
16
17# 2. 流复制与热备
18wal_level = replica
19max_wal_senders = 10
20hot_standby = on
21# 流复制参数(如 wal_level = replica, max_wal_senders = 10, hot_standby = on 等)如果本地没做主备,可以适当关闭或简化。
22
23# 3. 共享库/插件相关
24shared_preload_libraries = 'timescaledb,pg_stat_statements'
25# 本地建议:本地 PostgreSQL 必须也装有 timescaledb 和 pg_stat_statements 插件,否则数据库无法启动。
26# 若本地没这些插件,建议暂时注释掉,安装插件后再开启。
27
28# 4. 动态共享内存类型
29dynamic_shared_memory_type = posix
30# 只要本地 Linux 支持一般不用改。如果提示不支持可以换为 sysv
31
32# 5. 网络相关参数
33listen_addresses = '*'
34#本地建议:如果只在本地访问,建议改为 localhost 或指定局域网 IP,避免数据库暴露在公网。
35#云上通常:云服务商会有安全组等防护,本地环境要自己注意安全。
36port = 5100
37#本地建议:可以保留,也可以改为常用的 5432(默认端口),或根据本地需求设置。
38
39# 6. 内存与连接数
40max_connections = 200
41superuser_reserved_connections = 20
42shared_buffers = 768MB
43effective_cache_size = 1536MB
44# 本地建议:这些参数要根据你本地服务器的内存大小和并发需求调整。
45# 例如,如果本地内存较小,建议适当减小这几个值。
46
47# 7.本地化与时区
48timezone = 'PRC'
49lc_messages = 'en_US'
50lc_monetary = 'en_US'
51lc_numeric = 'en_US'
52lc_time = 'en_US'
53# 本地建议:可以保留,但要确保本地系统支持这些 locale,否则可能会报错。
54# 检查方法:locale -a | grep en_US
55# 如果没有,需先在系统上安装相应的 locale。
56
57# 8. 日志相关
58log_filename
59# 路径和文件名可以保留,按需调整。
60log_timezone = 'PRC'
61# 可以保留,表示日志时间为中国时区
62
63# 9. 路径相关
64# 检查任何路径型参数(如 archive_command、log_directory)是否符合本地实际目录结构
六、修改主从复制环境、流复制 standby设置
Plain Text
1# 如果你只想让这个PostgreSQL备份文件是来自于云上的主从多节点数据库,又想恢复的节点作为单机数据库启动(即:不是主从复制环境、不是流复制 standby),你需要:
2# (1)删除或注释掉所有与主从、恢复、复制相关的参数和配置。
3# (2)保留常规参数,根据硬件资源调整内存、连接数等。
4
5# 1. 请注释掉或删除如下参数:
6recovery_target_timeline='latest'
7primary_conninfo = 'host=xxx.xxx.xx.xxx port=5100 user=pgsync application_name=node2'
8restore_command = ''
9# 并确保数据目录下没有如下文件(如果有,请删除):
10${postgresql_datadir}/recovery.conf
11${postgresql_datadir}/standby.signal
12${postgresql_datadir}/recovery.signal
13
14# 2. 热备相关参数
15# 单机库可以设为:
16hot_standby = off
17# postgresql.conf中可能有多个hot_standby的配置,请注意全部要改掉
18
19# 3. 可参考的postgresql.conf的配置
20port = 5100
21max_connections = 200
22superuser_reserved_connections = 20
23shared_buffers = 768MB
24dynamic_shared_memory_type = posix
25listen_addresses = '*'
26datestyle = 'iso, mdy'
27timezone = 'PRC'
28lc_messages = 'en_US'
29lc_monetary = 'en_US'
30lc_numeric = 'en_US'
31lc_time = 'en_US'
32default_text_search_config = 'pg_catalog.english'
33wal_level = replica # 只要不做逻辑复制,保持 replica 就行
34fsync = on
35synchronous_commit = on
36wal_sync_method = fdatasync
37max_wal_senders = 0 # 不做复制,建议设为0,节省资源
38wal_keep_size = 0 # 不做复制,可以设为0
39max_wal_size = 16GB
40archive_mode = off
41# archive_command = ...
42archive_timeout = 600
43# shared_preload_libraries = 'timescaledb,pg_stat_statements'
44pg_stat_statements.max = 500
45pg_stat_statements.track = all
46log_lock_waits = on
47log_rotation_age = 6h
48log_timezone = 'PRC'
49log_line_prefix = '%t|%p|%l|%h|%d|%u|'
50log_rotation_size = 100MB
51log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
52logging_collector = on
53log_min_messages = 'notice'
54log_min_duration_statement = 1s
55log_destination = 'stderr'
56log_directory = 'pg_log'
57checkpoint_completion_target = 0.9
58autovacuum_analyze_scale_factor = 0.01
59autovacuum_analyze_threshold = 1000
60autovacuum_freeze_max_age = 200000000
61autovacuum_max_workers = 4
62autovacuum_vacuum_scale_factor = 0.02
63autovacuum_vacuum_threshold = 1000
64effective_cache_size = 1536MB
65wal_log_hints = on
七、启用数据库实例
Plain Text
1# 预设变量
2path_to_psql="/path-to-psql" # 包含psql命令行工具的路径,就在postgresql安装目录的bin目录下
3
4# 启动数据库
5${path_to_pg_ctl} -D start ${postgresql_datadir}
6
7# 查看数据库状态,是否启动成功
8${path_to_pg_ctl} -D status ${postgresql_datadir}
9
10# 登录默认的postgres
11${/home/mysql/postgresql/bin/psql} -d postgres -U pgsync