PostgreSQL学习实践:PG15使用pg_rman1.3进行备份恢复

pg_rman 是一款用于备份和恢复 PostgreSQL 数据库的插件。其功能类似于 Oracle 的 RMAN 工具

支持以下三种备份类型:

完整备份:备份整个数据库集群。
增量备份:仅备份在同一时间线上次验证备份后修改的文件或页面。
归档 WAL备份:仅备份归档WAL文件。

部署安装:

下载地址:

  
  
  
  1. https://github.com/ossc-db/pg_rman/releases

安装:

  
  
  
  1. #上传文件至/opt

  2. cd /opt

  3. #解压文件:

  4. tar zxvf pg_rman-1.3.16-pg15.tar.gz

  5. #解压完成后:

  6. chown -R postgres.postgres /opt/pg_rman-1.3.16-pg15

  7. su - postges

  8. cd /opt/pg_rman-1.3.16-pg15

  9. make

  10. make install

  11. #安装完成后校验版本

  12. [postgres@wypg15 pg_rman-1.3.16-pg15]$ pg_rman --version

  13. pg_rman 1.3.16

  14. #安装位置

  15. [postgres@wypg15 pg_rman-1.3.16-pg15]$ which pg_rman

  16. /postgresql/pg15/bin/pg_rman

  17. #使用帮助

  18. [postgres@wypg15 pg_rman-1.3.16-pg15]$ pg_rman --help

  19. pg_rman manage backup/recovery of PostgreSQL database.

  20. Usage:

  21. pg_rman OPTION init

  22. pg_rman OPTION backup

  23. pg_rman OPTION restore

  24. pg_rman OPTION show [DATE]

  25. pg_rman OPTION show detail [DATE]

  26. pg_rman OPTION validate [DATE]

  27. pg_rman OPTION delete DATE

  28. pg_rman OPTION purge

  29. Common Options:

  30. -D, --pgdata=PATH location of the database storage area

  31. -A, --arclog-path=PATH location of archive WAL storage area

  32. -S, --srvlog-path=PATH location of server log storage area

  33. -B, --backup-path=PATH location of the backup storage area

  34. -G, --pgconf-path=PATH location of the configuration storage area

  35. -c, --check show what would have been done

  36. -v, --verbose show what detail messages

  37. -P, --progress show progress of processed files

  38. Backup options:

  39. -b, --backup-mode=MODE full, incremental, or archive

  40. -s, --with-serverlog also backup server log files

  41. -Z, --compress-data compress data backup with zlib

  42. -C, --smooth-checkpoint do smooth checkpoint before backup

  43. -F, --full-backup-on-error switch to full backup mode

  44. if pg_rman cannot find validate full backup

  45. on current timeline

  46. NOTE: this option is only used in --backup-mode=incremental or archive.

  47. --keep-data-generations=NUM keep NUM generations of full data backup

  48. --keep-data-days=NUM keep enough data backup to recover to N days ago

  49. --keep-arclog-files=NUM keep NUM of archived WAL

  50. --keep-arclog-days=DAY keep archived WAL modified in DAY days

  51. --keep-srvlog-files=NUM keep NUM of serverlogs

  52. --keep-srvlog-days=DAY keep serverlog modified in DAY days

  53. --standby-host=HOSTNAME standby host when taking backup from standby

  54. --standby-port=PORT standby port when taking backup from standby

  55. Restore options:

  56. --recovery-target-time time stamp up to which recovery will proceed

  57. --recovery-target-xid transaction ID up to which recovery will proceed

  58. --recovery-target-inclusive whether we stop just after the recovery target

  59. --recovery-target-timeline recovering into a particular timeline

  60. --recovery-target-action action the server should take once the recovery target is reached

  61. --hard-copy copying archivelog not symbolic link

  62. Catalog options:

  63. -a, --show-all show deleted backup too

  64. Delete options:

  65. -f, --force forcibly delete backup older than given DATE

  66. Connection options:

  67. -d, --dbname=DBNAME database to connect

  68. -h, --host=HOSTNAME database server host or socket directory

  69. -p, --port=PORT database server port

  70. -U, --username=USERNAME user name to connect as

  71. -w, --no-password never prompt for password

  72. -W, --password force password prompt

  73. Generic options:

  74. -q, --quiet don't show any INFO or DEBUG messages

  75. --debug show DEBUG messages

  76. --help show this help, then exit

  77. --version output version information, then exit

  78. Read the website for details. <http://github.com/ossc-db/pg_rman>

  79. Report bugs to <http://github.com/ossc-db/pg_rman/issues>.

配置环境变量:

  
  
  
  1. #使用root用户创建文件夹与授权:

  2. mkdir -p /bak/{backup,arch_bak,srvlog}

  3. chown -R postgres.postgres /bak/{backup,arch_bak,srvlog}

  4. [root@wypg15 ~]# ll /bak

  5. total 0

  6. drwxr-xr-x. 2 postgres postgres 6 Jan 9 21:48 arch_bak

  7. drwxr-xr-x. 2 postgres postgres 6 Jan 9 21:48 backup

  8. drwxr-xr-x. 2 postgres postgres 6 Jan 9 21:48 srvlog

  9. #切换到postgres用户并输入cd命令到根目录

  10. su - postgres

  11. vi .bashrc

  12. export PG_RMAN=/postgresql/pg15/bin

  13. export SRVLOG_PATH=/bak/srvlog

  14. export ARCLOG_PATH=/bak/arch_bak

  15. export BACKUP_PATH=/bak/backup

  16. source .bashrc

语法及说明:

  
  
  
  1. pg_rman [ OPTIONS ] { init |

  2. backup |

  3. restore |

  4. show [ DATE | detail ] |

  5. validate [ DATE ] |

  6. delete DATE |

  7. purge }

  8. init 初始化备份目录。

  9. backup 在线备份。

  10. restore 恢复。

  11. show 显示备份历史记录。详细信息选项显示每个备份的附加信息。

  12. validate 验证备份文件。未经验证的备份不能用于恢复和增量备份。

  13. delete 删除备份文件。

  14. purge 从备份目录中删除已删除的备份。

初始化备份目录:

  
  
  
  1. [postgres@wypg15 ~]$ pg_rman init -B /bak/backup

  2. INFO: ARCLOG_PATH is set to '/bak/arch_bak'

  3. INFO: SRVLOG_PATH is set to '/bak/srvlog'

  4. [postgres@wypg15 bak]$ cd /bak/backup

  5. [postgres@wypg15 backup]$ ll

  6. total 8

  7. drwx------. 4 postgres postgres 34 Jan 9 21:52 backup

  8. -rw-rw-r--. 1 postgres postgres 55 Jan 9 21:52 pg_rman.ini

  9. -rw-rw-r--. 1 postgres postgres 40 Jan 9 21:52 system_identifier

  10. drwx------. 2 postgres postgres 6 Jan 9 21:52 timeline_history

查看配置文件:

  
  
  
  1. [postgres@wypg15 backup]$ cat pg_rman.ini

  2. ARCLOG_PATH='/bak/arch_bak'

  3. SRVLOG_PATH='/bak/srvlog'

  4. #配置文件中可增加备份策略

完整备份:

  
  
  
  1. [postgres@wypg15 backup]$ pg_rman backup --backup-mode=full --with-serverlog --progress

  2. INFO: copying database files

  3. Processed 1608 of 1608 files, skipped 0

  4. INFO: copying archived WAL files

  5. INFO: copying server log files

  6. INFO: backup complete

  7. INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.

验证及查看备份文件:

  
  
  
  1. [postgres@wypg15 backup]$ pg_rman validate

  2. INFO: validate: "2024-01-09 21:57:06" backup, archive log files and server log files by CRC

  3. INFO: backup "2024-01-09 21:57:06" is valid

  4. [postgres@wypg15 backup]$ pg_rman show

  5. =====================================================================

  6. StartTime EndTime Mode Size TLI Status

  7. =====================================================================

  8. 2024-01-09 21:57:06 2024-01-09 21:57:09 FULL 28MB 1 OK

增量备份:

  
  
  
  1. [postgres@wypg15 backup]$ pg_rman backup --backup-mode=incremental --progress --compress-data

  2. INFO: copying database files

  3. Processed 1608 of 1608 files, skipped 1575

  4. INFO: copying archived WAL files

  5. INFO: backup complete

  6. INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.

验证及查看备份文件:

  
  
  
  1. [postgres@wypg15 backup]$ pg_rman validate

  2. INFO: validate: "2024-01-09 22:00:19" backup and archive log files by CRC

  3. INFO: backup "2024-01-09 22:00:19" is valid

  4. [postgres@wypg15 backup]$ pg_rman show

  5. =====================================================================

  6. StartTime EndTime Mode Size TLI Status

  7. =====================================================================

  8. 2024-01-09 22:00:19 2024-01-09 22:00:22 INCR 1516B 1 OK

  9. 2024-01-09 21:57:06 2024-01-09 21:57:09 FULL 28MB 1 OK

归档备份:

  
  
  
  1. [postgres@wypg15 backup]$ pg_rman show

  2. =====================================================================

  3. StartTime EndTime Mode Size TLI Status

  4. =====================================================================

  5. 2024-01-09 22:00:19 2024-01-09 22:00:22 INCR 1516B 1 OK

  6. 2024-01-09 21:57:06 2024-01-09 21:57:09 FULL 28MB 1 OK

验证及查看备份文件:

  
  
  
  1. [postgres@wypg15 backup]$ pg_rman backup --backup-mode=archive --progress --compress-data

  2. INFO: copying archived WAL files

  3. INFO: backup complete

  4. INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.

  5. [postgres@wypg15 backup]$ pg_rman validate

  6. INFO: validate: "2024-01-09 22:03:42" archive log files by CRC

  7. INFO: backup "2024-01-09 22:03:42" is valid

如何删除备份集:

  
  
  
  1. pg_rman删除备份集

  2. pg_rman delete -f "starttime"

  3. 清除备份集

  4. pg_rman purge

恢复:

pg_rman 将备份的数据恢复到目标数据库集群路径中。

恢复前应停止 PostgreSQL 服务器。另外,不要删除原始数据库集群,因为pg_rman必须从中检查时间线ID或数据校验和状态。恢复命令将保存未归档的事务日志并删除所有数据库文件。您可以重试恢复,直到创建新的备份。恢复文件后,pg_rman 在 中创建recovery.conf $PGDATA.conf 文件包含恢复参数,如果需要,您也可以修改该文件。

pg_rman 恢复时配置恢复相关的guc参数。配置文件取决于PostgreSQL的版本和pg_rman的版本。如果需要,请手动修改文件后启动服务器并执行 PITR。

  • PostgreSQL的版本低于12:pg_rman创建并配置$PGDATA/recovery.conf

  • PostgreSQL 的版本为 12 或更高,pg_rman 的版本为 1.3.12 或更低:pg_rman 将与恢复相关的配置附加到$PGDATA/postgresql.conf并创建 $PGDATA/recovery.signal.

  • PostgreSQL 的版本为 12 或更高,并且 pg_rman 的版本高于 1.3.12:pg_rman 创建并配置$PGDATA/pg_rman_recovery.conf,并将include 指令附加到$PGDATA/postgresql.conf. 如果include过去恢复 pg_rman 时添加了指令,请将其删除。它创建了$PGDATA/recovery.signal.

恢复选项:

  
  
  
  1. --recovery-target-timeline TIMELINE

  2. #如果不指定时间线,则使用$PGDATA/global/pg_control,如果没有$PGDATA/global/pg_control,则使用最新的全量备份集的时间线

  3. --recovery-target-time TIMESTAMP

  4. #此参数指定恢复将继续进行的时间戳。如果未指定,则继续恢复到最新时间。

  5. --recovery-target-xid XID

  6. 此参数指定恢复将继续进行的事务 ID。如果未指定,则继续恢复到最新的xid

  7. --recovery-target-inclusive

  8. #是否在指定的恢复目标(true)之后停止,默认为true,如果指定false意识是在恢复目标之前停止

  9. --hard-copy

  10. #是否使用硬链接复制archive log,如果不指定使用符号连接(软连接)的方式。

恢复实验:

全备数据:

  
  
  
  1. [postgres@wypg15 ~]$ pg_rman backup --backup-mode=full --with-serverlog --progress

  2. INFO: copying database files

  3. Processed 1608 of 1608 files, skipped 0

  4. INFO: copying archived WAL files

  5. INFO: copying server log files

  6. INFO: backup complete

  7. INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.

查看数据库:

  
  
  
  1. [postgres@wypg15 backup]$ psql

  2. psql (15.3)

  3. Type "help" for help.

  4. postgres=# \l

  5. List of databases

  6. Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges

  7. -----------+----------+----------+------------+------------+------------+-----------------+-----------------------

  8. postgres | postgres | UTF8 | zh_CN.utf8 | zh_CN.utf8 | | libc |

  9. template0 | postgres | UTF8 | zh_CN.utf8 | zh_CN.utf8 | | libc | =c/postgres +

  10. | | | | | | | postgres=CTc/postgres

  11. template1 | postgres | UTF8 | zh_CN.utf8 | zh_CN.utf8 | | libc | =c/postgres +

  12. | | | | | | | postgres=CTc/postgres

  13. wydb01 | wy01 | UTF8 | zh_CN.utf8 | zh_CN.utf8 | | libc |

  14. wydb03 | wy03 | UTF8 | zh_CN.utf8 | zh_CN.utf8 | | libc |

  15. (5 rows)

模拟故障:

  
  
  
  1. pg_ctl stop

  2. rm -rf /pgdata/data

  3. [postgres@wypg15 data]$ psql

  4. could not identify current directory: No such file or directory

  5. psql: error: connection to server on socket "/tmp/.s.PGSQL.6543" failed: FATAL: could not open file "global/pg_filenode.map": No such file or directory

恢复:

  
  
  
  1. [postgres@wypg15 arch]$ pg_rman restore -B /bak/backup -A /arch -hard_copy -D /pgdata/data

  2. INFO: the recovery target timeline ID is not given

  3. INFO: use timeline ID of current database cluster as recovery target: 1

  4. INFO: calculating timeline branches to be used to recovery target point

  5. INFO: searching latest full backup which can be used as restore start point

  6. INFO: found the full backup can be used as base in recovery: "2024-01-09 22:33:57"

  7. INFO: copying online WAL files and server log files

  8. INFO: clearing restore destination

  9. INFO: validate: "2024-01-09 22:33:57" backup, archive log files and server log files by SIZE

  10. INFO: backup "2024-01-09 22:33:57" is valid

  11. INFO: restoring database files from the full mode backup "2024-01-09 22:33:57"

  12. INFO: searching incremental backup to be restored

  13. INFO: searching backup which contained archived WAL files to be restored

  14. INFO: backup "2024-01-09 22:33:57" is valid

  15. INFO: restoring WAL files from backup "2024-01-09 22:33:57"

  16. INFO: restoring online WAL files and server log files

  17. INFO: create pg_rman_recovery.conf for recovery-related parameters.

  18. INFO: remove an 'include' directive added by pg_rman in postgresql.conf if exists

  19. INFO: append an 'include' directive in postgresql.conf for pg_rman_recovery.conf

  20. INFO: generating recovery.signal

  21. INFO: removing standby.signal if exists to restore as primary

  22. INFO: restore complete

  23. HINT: Recovery will start automatically when the PostgreSQL server is started. After the recovery is done, we recommend to remove recovery-related parameters configured by pg_rman.

  24. [postgres@wypg15 arch]$ pg_ctl start

  25. waiting for server to start....2024-01-09 14:43:08.637 GMT [33403] LOG: 00000: redirecting log output to logging collector process

  26. 2024-01-09 14:43:08.637 GMT [33403] HINT: Future log output will appear in directory "log".

  27. 2024-01-09 14:43:08.637 GMT [33403] LOCATION: SysLogger_Start, syslogger.c:715

  28. done

  29. server started

  30. [postgres@wypg15 arch]$ psql

  31. psql (15.3)

  32. Type "help" for help.

  33. postgres=# \l

  34. List of databases

  35. Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges

  36. -----------+----------+----------+------------+------------+------------+-----------------+-----------------------

  37. postgres | postgres | UTF8 | zh_CN.utf8 | zh_CN.utf8 | | libc |

  38. template0 | postgres | UTF8 | zh_CN.utf8 | zh_CN.utf8 | | libc | =c/postgres +

  39. | | | | | | | postgres=CTc/postgres

  40. template1 | postgres | UTF8 | zh_CN.utf8 | zh_CN.utf8 | | libc | =c/postgres +

  41. | | | | | | | postgres=CTc/postgres

  42. wydb01 | wy01 | UTF8 | zh_CN.utf8 | zh_CN.utf8 | | libc |

  43. wydb03 | wy03 | UTF8 | zh_CN.utf8 | zh_CN.utf8 | | libc |

  44. (5 rows)



本文分享自微信公众号 - 开源软件联盟PostgreSQL分会(kaiyuanlianmeng)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部