# pgBackRest異機備份操作手冊 簡介 --- 最近有朋友詢問pgBackRest的使用及操作，就順手把流程記錄下來 pgBackRest的安裝及使用主要分為以下四大步驟 **1.環境安裝及Config設定(Linux、Postgresql、pgBackRest) 2.建立stanza 3.建立備份及排程 4.災難還原(祈禱一輩子走不到這一步)** 一、環境安裝及Config設定 --- **1.1 準備兩台伺服器當做主資料庫和備份伺服器** ``` 伺服器名稱及IP：: pgproduct 192.168.16.131 pgbackup 192.168.16.132 系統環境： Linux：Ubuntu 22.04 DB：Postgresql-14.11 Backup Tool：pgBackRest 2.51 Other Tools：net-tools、vim、openssh-server ``` **1.2 安裝所需套件** >安裝常用工具 ```shell= sudo apt-get install net-tools -y sudo apt-get install vim -y sudo apt-get install openssh-server -y ``` >安裝Postgresql ```shell= sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - sudo apt-get update sudo apt-get -y install postgresql-14 ``` >安裝pgBackRest ```shell= sudo apt-get install pgbackrest -y ``` **1.3 配置環境參數** **1.3.1 將主資料庫與備份伺服器互相加入hosts** >pgproduct端 ```shell= sudo vi /etc/hosts 127.0.0.1 localhost 127.0.1.1 pgproduct 192.168.16.132 pgbackup ``` >pgbackup端 ```shell= sudo vi /etc/hosts 127.0.0.1 localhost 127.0.1.1 pgbackup 192.168.16.131 pgproduct ``` **1.3.2 配置pgbackrest config** >pgproduct端 ```shell= sudo vi /etc/pgbackrest.conf ### prod_backup為stanza的名稱，可自行定義 [prod_backup] pg1-path=/var/lib/postgresql/14/main [global] repo1-host=pgbackup repo1-host-user=postgres ``` >pgbackup端 ```shell= sudo vi /etc/pgbackrest.conf [prod_backup] pg1-host=pgproduct pg1-host-user=postgres pg1-path=/var/lib/postgresql/14/main [global] process-max=2 repo1-path=/var/lib/pgbackrest repo1-retention-full=4 repo1-host-user=postgres start-fast=y ``` **1.3.3 配置Postgresql.conf (pgproduct端)** ```shell= sudo vi /etc/postgresql/14/main/postgresql.conf listen_addresses = '*' wal_level = replica archive_mode = on ### 注意！--stanza的參數與pgbackrest.conf中的[prod_backup]同名 archive_command = 'pgbackrest --stanza=prod_backup archive-push %p' ###修改完重啟postgresql讓設定生效 sudo systemctl restart postgresql ``` **1.3.4 配置ssh無密碼連接** >pgproduct端 ```shell= ### 先修改postgres密碼 sudo passwd postgres ###切換至postgres user su - postgres ###產生ssh key ssh-keygen ###將key複製到pgbackup ssh-copy-id postgres@pgbackup ``` >pgbackup端 ```shell= ### 先修改postgres密碼 sudo passwd postgres ###切換至postgres user su - postgres ###產生ssh key ssh-keygen ###將key複製到pgproduct ssh-copy-id postgres@pgproduct ``` 二、建立stanza --- **2.1在pgbackup端創建stanza** ```shell= su - postgres pgbackrest --stanza=prod_backup --log-level-console=info stanza-create ###出現以下訊息代表創建成功 INFO: stanza-create command end: completed successfully (1177ms) ``` **2.2在pgproduct端檢查stanza** ```shell= su - postgres pgbackrest --stanza=prod_backup --log-level-console=info check ###出現以下訊息代表檢查成功 INFO: check command end: completed successfully (1502ms) ``` 三、建立備份及排程 --- **3.1在pgbackup端執行備份，pgbackrest會自動選擇Full Backup或Increment Backup** ```shell= su - postgres pgbackrest --stanza=prod_backup --log-level-console=info backup ###出現以下訊息代表備份成功 INFO: expire command end: completed successfully (3ms) ``` **3.2可查看目前的備份清單，剛剛是第一次備份，所以會是Full Backup** ```shell= su - postgres pgbackrest --stanza=prod_backup info ``` ![pgbackrest backup info](https://hackmd.io/_uploads/SyZAl59JC.jpg) **3.3在pgbackup端建立備份排程** ```shell= su - postgres crontab -e ###新增以下排程，此排程會在周日0點執行完整備份，並在其餘時間每小時進行增量備份 0 0 * * 0 pgbackrest --type=full --stanza=prod_backup backup 0 01-23 * * 0 pgbackrest --type=diff --stanza=prod_backup backup 0 * * * 1-6 pgbackrest --type=diff --stanza=prod_backup backup ``` 四、災難還原 --- **4.1在pgproduct創建一個測試資料庫** ```shell= su - postgres psql postgres=# CREATE DATABASE test; CREATE DATABASE postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | (4 rows) ``` **4.2在pgbackup端再進行一次備份** ```shell= su - postgres pgbackrest --stanza=prod_backup --log-level-console=info backup ``` **4.3查看備份，會發現多一個incr backup** ```shell= su - postgres pgbackrest --stanza=prod_backup info ``` ![pgbackrest incr backup info](https://hackmd.io/_uploads/HyAF95qyA.jpg) **4.4刪除DB，測試備份還原** ```shell= postgres=# DROP DATABASE test; DROP DATABASE postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (3 rows) ``` **4.5還原前將postgresql停止，並執行還原工作** >pgproduct端，停止postgresql ```shell= su - postgres systemctl stop postgresql ``` >還原備份(在pgproduct執行) ```shell= ###還原到最近一次的備份 pgbackrest --stanza=prod_backup --recovery-option=recovery_target=immediate --delta --log-level-console=info restore ###出現以下訊息代表檢查成功 INFO: restore command end: completed successfully (2562ms) ``` >啟動postgresql，查看是否還原成功 ```shell= su - postgres systemctl start postgresql psql postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | (4 rows) ``` 五、注意事項 --- * Q1：若還原之後啟動資料庫，卻無法寫入該怎麼辦? A1：當 PostgreSQL 啟動時,它會檢查資料目錄中是否存在 recovery.signal 文件。如果該文件存在,資料庫就會進入復原模式(recovery mode) ```shell= su - postgres psql ###返回t代表PG還在恢復模式 postgres=# SELECT pg_is_in_recovery(); pg_is_in_recovery ------------------- t (1 row) ##執行pg_wal_replay_resume()切換回正常模式 postgres=# SELECT pg_wal_replay_resume(); pg_wal_replay_resume ---------------------- (1 row) ``` * Q2：若我想還原到指定的時間點呢? A2：還原指令可以改為以下格式，target的時間可以參考pgbackrest info裡的備份時間 ```shell= pgbackrest --stanza=prod_backup --delta --type=time "--target=2024-04-04 11:35:17+08" --target-action=promote --log-level-console=info restore ```