---
# System prepended metadata

title: pgBackRest異機備份操作手冊
tags: [Book, Templates]

---

# 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
```