Crunchy PG手動備份實驗
Crunchy PostgreSQL在Kubernets/Openshift安裝以後，可以透過yaml檔案非常容易的配置自動備份和手動備份，自動備份是由Crunchy Postgres Operator建立了CronJobs控制在什麼時間，執行什麼備份，和Linux裡面的Cronjob類似。
例如如下示例定義了自動備份的策略
每週日 1am執行一個全量備份
每週 週一到週六 1am 執行增量備份
spec: backups: pgbackrest: repos: - name: repo1 schedules: full: "0 1 * * 0" differential: "0 1 * * 1-6"
3種備份定義如下
(1) full: A backup of your entire Postgres cluster. This is the largest of all of the backup types.
(2) differential: A backup of all of the data since the last full backup.
(3) incremental: A backup of all of the data since the last full, differential, or incremental backup.
對於手工備份，這裡做3個實驗來驗證3種不同的備份 (full, differential, incremental)。
第一個實驗：手工全量備份
yaml檔案中的相關的設定部分
manual: repoName: repo1 options: - --type=full
第一次安裝完成以後自動做一個full backup
$ oc get po NAME READY STATUS RESTARTS AGE demo-backup-r7fk-27shq 0/1 Completed 0 4m52s demo-instance1-2wkh-0 5/5 Running 0 6m34s demo-instance1-6hv6-0 5/5 Running 0 6m34s demo-pgbouncer-866898f6c4-wh7zf 2/2 Running 0 6m34s demo-repo-host-0 2/2 Running 0 6m34s
第一次手動full備份
$ oc annotate postgrescluster demo postgres-operator.crunchydata.com/pgbackrest-backup="$(date)" postgrescluster.postgres-operator.crunchydata.com/demo annotated $ oc get po NAME READY STATUS RESTARTS AGE demo-backup-qpf7-nhhvh 0/1 Completed 0 27s demo-backup-r7fk-27shq 0/1 Completed 0 5m25s demo-instance1-2wkh-0 5/5 Running 0 7m7s demo-instance1-6hv6-0 5/5 Running 0 7m7s demo-pgbouncer-866898f6c4-wh7zf 2/2 Running 0 7m7s demo-repo-host-0 2/2 Running 0 7m7s
第二次手動full備份，要加 --overwrite 選項才行
$ oc annotate postgrescluster demo --overwrite postgres-operator.crunchydata.com/pgbackrest-backup="$(date)" postgrescluster.postgres-operator.crunchydata.com/demo annotated $ oc get po NAME READY STATUS RESTARTS AGE demo-backup-c7vw-l4fg4 0/1 ContainerCreating 0 1s demo-backup-r7fk-27shq 0/1 Completed 0 7m23s demo-instance1-2wkh-0 5/5 Running 0 9m5s demo-instance1-6hv6-0 5/5 Running 0 9m5s demo-pgbouncer-866898f6c4-wh7zf 2/2 Running 0 9m5s demo-repo-host-0 2/2 Running 0 9m5s $ oc get po NAME READY STATUS RESTARTS AGE demo-backup-c7vw-l4fg4 0/1 Completed 0 27s demo-backup-r7fk-27shq 0/1 Completed 0 7m49s demo-instance1-2wkh-0 5/5 Running 0 9m31s demo-instance1-6hv6-0 5/5 Running 0 9m31s demo-pgbouncer-866898f6c4-wh7zf 2/2 Running 0 9m31s demo-repo-host-0 2/2 Running 0 9m31s
檢視pgbackrest的備份資訊，應該有3個備份資訊
$ oc exec -it demo-repo-host-0 -c pgbackrest -- pgbackrest info
stanza: db status: ok cipher: none db (current) wal archive min/max (14): 000000010000000000000001/000000010000000000000008 full backup: 20230215-094253F timestamp start/stop: 2023-02-15 09:42:53 / 2023-02-15 09:44:44 wal start/stop: 000000010000000000000004 / 000000010000000000000004 database size: 37.0MB, database backup size: 37.0MB repo1: backup set size: 4.5MB, backup size: 4.5MB full backup: 20230215-094505F timestamp start/stop: 2023-02-15 09:45:05 / 2023-02-15 09:45:23 wal start/stop: 000000010000000000000006 / 000000010000000000000006 database size: 37MB, database backup size: 37MB repo1: backup set size: 4.5MB, backup size: 4.5MB full backup: 20230215-094728F timestamp start/stop: 2023-02-15 09:47:28 / 2023-02-15 09:47:44 wal start/stop: 000000010000000000000008 / 000000010000000000000008 database size: 37.2MB, database backup size: 37.2MB repo1: backup set size: 4.5MB, backup size: 4.5MB
實驗二：differential 備份
修改yaml檔案，改成
manual: repoName: repo1 options: - --type=diff
然後apply yaml檔案。
執行和full backup同樣的命令2次，可以透過 pgbackrest info檢視備份的資訊
$ oc exec -it demo-repo-host-0 -c pgbackrest -- pgbackrest info stanza: db status: ok cipher: none db (current) wal archive min/max (14): 000000010000000000000001/000000010000000000000008 full backup: 20230215-102129F timestamp start/stop: 2023-02-15 10:21:29 / 2023-02-15 10:22:42 wal start/stop: 000000010000000000000004 / 000000010000000000000004 database size: 36.9MB, database backup size: 36.9MB repo1: backup set size: 4.5MB, backup size: 4.5MB diff backup: 20230215-102129F_20230215-102455D timestamp start/stop: 2023-02-15 10:24:55 / 2023-02-15 10:25:21 wal start/stop: 000000010000000000000006 / 000000010000000000000006 database size: 37MB, database backup size: 6.5MB repo1: backup set size: 4.5MB, backup size: 1015.8KB backup reference list: 20230215-102129F diff backup: 20230215-102129F_20230215-102636D timestamp start/stop: 2023-02-15 10:26:36 / 2023-02-15 10:26:40 wal start/stop: 000000010000000000000008 / 000000010000000000000008 database size: 37.2MB, database backup size: 6.6MB repo1: backup set size: 4.5MB, backup size: 1019.6KB backup reference list: 20230215-102129F
實驗三：Incremental 備份
修改yaml檔案，改成
manual: repoName: repo1 options: - --type=incr
然後apply yaml檔案。
執行和full backup同樣的命令2次，可以透過 pgbackrest info檢視備份的資訊
$ oc exec -it demo-repo-host-0 -c pgbackrest -- pgbackrest info stanza: db status: ok cipher: none db (current) wal archive min/max (14): 000000010000000000000001/00000001000000000000000D full backup: 20230215-102129F timestamp start/stop: 2023-02-15 10:21:29 / 2023-02-15 10:22:42 wal start/stop: 000000010000000000000004 / 000000010000000000000004 database size: 36.9MB, database backup size: 36.9MB repo1: backup set size: 4.5MB, backup size: 4.5MB diff backup: 20230215-102129F_20230215-102455D timestamp start/stop: 2023-02-15 10:24:55 / 2023-02-15 10:25:21 wal start/stop: 000000010000000000000006 / 000000010000000000000006 database size: 37MB, database backup size: 6.5MB repo1: backup set size: 4.5MB, backup size: 1015.8KB backup reference list: 20230215-102129F diff backup: 20230215-102129F_20230215-102636D timestamp start/stop: 2023-02-15 10:26:36 / 2023-02-15 10:26:40 wal start/stop: 000000010000000000000008 / 000000010000000000000008 database size: 37.2MB, database backup size: 6.6MB repo1: backup set size: 4.5MB, backup size: 1019.6KB backup reference list: 20230215-102129F incr backup: 20230215-102129F_20230215-103608I timestamp start/stop: 2023-02-15 10:36:08 / 2023-02-15 10:36:10 wal start/stop: 00000001000000000000000B / 00000001000000000000000B database size: 38.0MB, database backup size: 1.7MB repo1: backup set size: 4.6MB, backup size: 96.8KB backup reference list: 20230215-102129F, 20230215-102129F_20230215-102636D incr backup: 20230215-102129F_20230215-103656I timestamp start/stop: 2023-02-15 10:36:56 / 2023-02-15 10:36:58 wal start/stop: 00000001000000000000000D / 00000001000000000000000D database size: 38MB, database backup size: 1.7MB repo1: backup set size: 4.6MB, backup size: 98.4KB backup reference list: 20230215-102129F, 20230215-102129F_20230215-102636D, 20230215-102129F_20230215-103608I
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/725820/viewspace-2935467/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- PG備份之pg_basebackup工具
- PG備份恢復工具pg_probackup
- pg_dump備份加密加密
- pg流複製備份
- 【PG備份恢復】pg_basebackup 多表空間備份恢復測試
- 【rman備份策略】實驗
- PG-pg_dump備份/恢復資料庫資料庫
- 【PG備份恢復】pg_dump命令測試
- Linux備份任務綜合實驗Linux
- pg_dump 備份，恢復資料庫資料庫
- PostgreSQL備份恢復管理器pg_probackupSQL
- OPPO手機備份
- 【Oracle設定bct備份，效率有多高】實驗Oracle
- postgresql物理備份工具pg_rman的使用詳解SQL
- Greenplum邏輯備份增強-備份期間不鎖metadata(pg_class)，不堵塞DDL
- 熱備份/冷備份/ 冷啟動/熱啟動
- linux實現mysql資料庫每天自動備份定時備份LinuxMySql資料庫
- PostgreSQL邏輯備份pg_dump使用及其原理解析SQL
- 8種手動和自動備份MySQL資料庫的方法MySql資料庫
- GitLab的自動備份、清理備份與恢復Gitlab
- oracle 增量備份恢復驗證Oracle
- 冬季實戰營動手實戰-上雲必備環境準備，動手實操快速搭建LAMP環境LAMP
- 手機通訊錄怎麼備份？手機通訊錄的幾種備份方法
- 自動備份指令碼指令碼
- Linux自動備份MySQLLinuxMySql
- PG最典型和實用的熱備指令碼實戰指令碼
- Crunchy pgo安裝Go
- 用友U8設定了自動備份，備份失效
- 備份任務實戰
- Oracle RMAN備份實戰Oracle
- oracle 12c中CDB和PDB的備份還原實驗Oracle
- DBV：冷備份的校驗和恢復
- 如何備份iPhone手機備忘錄到電腦上iPhone
- PostgreSQL邏輯備份恢復--pg_dump匯出及psql匯入案例SQL
- 動手學TCP——CS144實驗感想TCPCS144
- windows/linux自動備份mysql並刪除7天前的備份WindowsLinuxMySql
- Oracle-無備份情況下，如何手動恢復控制檔案Oracle
- 使用 restic 和 systemd 自動備份REST