PostgreSQL备份与恢复工具介绍

PG备份与恢复介绍: pg_dump、pg_dumpall、pg_restore、pg_basebackup、pg_rman

PG备份逻辑备份和物理备份:

逻辑备份工具

pg_dump、pg_dumpall
物理备份工具

pg_basebackup、pg_rman(第三方备份工具)

一、pg逻辑备份与恢复

postgres=# show port;
 port 
------
 5432
(1 row)

postgres=# \c mydb admin
You are now connected to database "mydb" as user "admin".

mydb=> \dt
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 public | t    | table | admin
 public | t1   | table | admin
(2 rows)

1.pg_dump

pg_dump备份单库单表数据，pg_dump生成的备份文件是一个SQL文件或归档文件，SQL文件使用psql命令行恢复，归档文件使用pg_restore恢复

(1)备份单个库mydb下面的所有表
[postgres@server ~]$ pg_dump -U admin -h 127.0.0.1 -p 5432 -d mydb > /tmp/pg_data.sql
[postgres@server ~]$ pg_dump -v -d mydb -Fc > /tmp/pg_backup.dump
 
(2)备份单张表
---导出为sql文件, -d参数可以省略
[postgres@server ~]$ pg_dump -U admin -h 127.0.0.1 -p 5432 mydb -t t > /tmp/pg_data_t.sql
---导出为dump文件
[postgres@server ~]$ pg_dump -U admin -h 127.0.0.1 -p 5432 -d mydb -t t -Fc > /tmp/pg_data_t.dump

(3)备份多张表
[postgres@server ~]$ pg_dump -U admin -h 127.0.0.1 -p 5432 mydb -t t -t t1 -f /tmp/pg_data_t_t1.sql

(4)备份t开头的表
[postgres@server ~]$ pg_dump -U admin -h 127.0.0.1 -p 5432 mydb -t "t*" -f /tmp/pg_data_t.sql
[postgres@server ~]$ pg_dump -U admin -h 127.0.0.1 -p 5432 mydb -t "public.t*" -f /tmp/pg_data_t.sql

(5)-T参数排除其中某些表, 不备份t1表
[postgres@server ~]$ pg_dump -U admin -h 127.0.0.1 -p 5432 mydb -t t -T t1 -f /tmp/pg_data_t.sql

(6)-s参数只导出对象定义，不包含数据
[postgres@server ~]$ pg_dump -U admin -h 127.0.0.1 -p 5432 mydb -t t -s -f /tmp/pg_data_t.sql  

(7)-Fd使用目录备份 
[postgres@server ~]$ pg_dump -U admin -h 127.0.0.1 -p 5432 -t t mydb -Fd -f /tmp/pg_backup_dir

(8)-j使用并行导出
[postgres@server ~]$ pg_dump -U admin -j 3 mydb -Fd -f /tmp/pg_backup_dir

(9)远程备份
[postgres@server ~]$ pg_dump -U admin -h 192.168.1.136 mydb -t t1 -f /tmp/backup.sql
[postgres@server ~]$ pg_dump -U admin -h 192.168.1.136 mydb -t t1 -Fd -f /tmp/remote_backup_dir


参数详解:
-F或--format=c|d|t|p: 指定输出的文件格式
	c: 用于pg_restore工具恢复使用的文档文件格式
	d: 将备份文件输出到指定的目录中，以目录形式备份，可以使用-j并行
	t: 输出为tar包
	p: 输出为纯文本SQL脚本文件的格式，默认格式p

2.pg_dumpall

pg_dump支持对整个数据库进行备份，将一个数据库中的所有数据备份到一个SQL文件中，该备份比较耗时，一般建议备份全局对象而非全库数据，恢复时使用 psql工具进行恢复
pg_dumpall需要和PostgreSQL 服务器连接多次(每个数据库一次)。如果你使用口令认证，可能每次都会询问口令。这种情况下写一个~/.pgpass可能会比较方便。

(1)全库备份
[postgres@server ~]$ pg_dumpall > /tmp/backup.sql

(2)远程全库备份
[postgres@server ~]$ pg_dumpall -h 192.168.1.136 -p 5432 -U postgres > /tmp/backup.sql

(3)备份角色和表空间定义，只转储全局对象(角色和表空间)，而不转储数据库
pg_dumpall -h 192.168.1.136 -p 5432 -U postgres --globals-only -f /tmp/backup.sql
pg_dumpall -h 192.168.1.136 -p 5432 -U postgres -g -f /tmp/backup.sql

(4)仅备份角色定义，只转储角色，不转储数据库或表空间
pg_dumpall -h 192.168.1.136 -p 5432 -U postgres --roles-only -f /tmp/backup.sql
pg_dumpall -h 192.168.1.136 -p 5432 -U postgres -r -f /tmp/backup.sql

3.pg_restore

pg_restore用于恢复由pg_dump 转储的任何非纯文本格式中的PostgreSQL数据库

---备份
[postgres@server ~]$ pg_dump -d mydb -Fc -f /tmp/mydb.dump

---恢复单库
postgres=# drop database mydb;
postgres=# create database mydb;
[postgres@server ~]$ pg_restore -v -d mydb /tmp/mydb.dump

---恢复单张表
mydb=> drop table t;
DROP TABLE

mydb=> \dt
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 public | t1   | table | admin
(1 row)
[postgres@server ~]$ pg_restore -d mydb -t t /tmp/mydb.dump 

mydb=> \dt
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 public | t    | table | admin
 public | t1   | table | admin
(2 rows)

---使用-c，恢复前先删除已经存在的对象 
[postgres@server ~]$ pg_restore -v -c -d mydb -t t /tmp/mydb.dump 

-d: -d参数是指将数据导入到指定的数据库下

---恢复多个表
[postgres@server ~]$ pg_restore -v -d mydb -c -t t -t t1   /tmp/mydb.dump 

温馨提示:
如果t1表已经存在，则使用pg_restore导入时，会将数据导入进去，导致重复数据产生，因此在导入数据之前一定要drop table或pg_restore加入-c参数
[postgres@server ~]$ pg_restore -v -d db1 -t  t1   /tmp/mydb.dump 
pg_restore: connecting to database for restore
pg_restore: creating TABLE "public.t1"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 197; 1259 114780 TABLE t1 admin
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "t1" already exists
    Command was: CREATE TABLE public.t1 (
    id integer
);
pg_restore: processing data for table "public.t1"
WARNING: errors ignored on restore: 1




---pg_restore将数据恢复到不同的owner
postgres=# \c db2
You are now connected to database "db2" as user "postgres".
db2=# \dt
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 public | t2   | table | admin
(1 row)

db2=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 public | postgres
 s2     | postgres
(2 rows)

[postgres@server ~]$ pg_restore -c --no-owner --role=app -v -d db2 /tmp/backup.dump 
pg_restore: connecting to database for restore
pg_restore: dropping TABLE DATA t2
pg_restore: dropping TABLE DATA t2
pg_restore: dropping TABLE t2
pg_restore: dropping TABLE t2
pg_restore: dropping COMMENT EXTENSION plpgsql
pg_restore: dropping EXTENSION plpgsql
pg_restore: dropping SCHEMA s2
pg_restore: dropping COMMENT SCHEMA public
pg_restore: dropping SCHEMA public
pg_restore: creating SCHEMA "public"
pg_restore: creating COMMENT "SCHEMA public"
pg_restore: creating SCHEMA "s2"
pg_restore: creating EXTENSION "plpgsql"
pg_restore: creating COMMENT "EXTENSION plpgsql"
pg_restore: creating TABLE "public.t2"
pg_restore: creating TABLE "s2.t2"
pg_restore: processing data for table "public.t2"
pg_restore: processing data for table "s2.t2"

postgres=# \c db2
You are now connected to database "db2" as user "postgres".
db2=# \dt
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 public | t2   | table | app
(1 row)

db2=# \dn
List of schemas
  Name  | Owner 
--------+-------
 public | app
 s2     | app
(2 rows)


---恢复某个schema下的数据
db2=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 public | postgres
 s2     | postgres
(2 rows)

[postgres@server ~]$ pg_restore -c  -v -d db2 -n s2 /tmp/backup.dump 
pg_restore: connecting to database for restore
pg_restore: dropping TABLE DATA t2
pg_restore: dropping TABLE t2
pg_restore: creating TABLE "s2.t2"
pg_restore: processing data for table "s2.t2"

---压缩备份
pg_dump -U username -d dbname | gzip > dbname.sql.gz
pg_dump -U username -d dbname | gzip -c > dbname.sql.gz

---解压备份
gunzip -c dump_file.sql.gz | pg_restore -U username -d database_name
cat filename.gz | gunzip | psql dbname

---备份文件分割
pg_dump dbname | split -b 2G - filename
pg_dump dbname | split -b 2G --filter='gzip > $FILE.gz'

---分割文件数据导入
cat filename* | psql dbname
zcat filename* | psql dbname

4.pg_dump与pg_dumpall区别

pg_dump和pg_dumpall的主要区别在于备份的范围和格式支持

备份范围：‌
- pg_dump用于备份单个数据库，‌它允许用户选择备份的格式（‌如SQL、‌自定义、‌目录、‌tar）‌和对象（‌如特定的表或schema）‌，‌提供了高度的灵活性。‌它适用于需要备份或迁移单个数据库的场景
- pg_dumpall用于备份PostgreSQL实例中的所有数据库，‌包括全局对象，‌如表空间和角色。‌它只支持纯文本SQL脚本格式，‌这意味着pg_dumpall的输出是一个大型SQL文件，‌可以通过PostgreSQL的psql工具执行来恢复
格式支持：‌
- pg_dump支持多种输出格式，‌包括自定义格式，‌这些格式可能更适合大型数据库的快速备份和恢复。‌这种多样性使得pg_dump在备份和恢复大型数据库时具有更大的灵活性
- pg_dumpall仅支持纯文本SQL脚本格式，‌这意味着它的用途相对单一，‌主要用于导出所有数据库的全局对象和数据

总的来说，‌选择使用pg_dump还是pg_dumpall取决于你的具体需求：‌如果你需要备份单个数据库并希望有更多的格式选择，‌pg_dump是更好的选择；‌如果你需要备份整个PostgreSQL实例，‌包括所有数据库和全局对象，‌并且不介意输出格式为纯文本SQL脚本，‌那么pg_dumpall可能更适合你的需求

二、pg物理备份与恢复

1.pg_basebackup

pg_basebackup从postgresql9.1开始引入，该工具通过replication协议连接到数据库，因此需要在pg_hba.conf文件中允许replication连接

备份

(1)设置允许本地连接
[postgres@server ~]$ vim $PGDATA/pg_hba.conf
local   all             postgres                        trust

(2)备份本地数据
[postgres@server ~]$ pg_basebackup -D pg_backup -Ft -v -z -P -l pg_backup_lable
[postgres@server ~]$ pg_basebackup -D pg_backup/`date +%F` -Ft -v -z -P -l pg_backup_lable

(3)远程备份数据
pg_basebackup -h 192.168.1.136 -p 5432 -D pg_remote_backup -Fp -X fetch -P -R -l pg_backup_lable

[postgres@server ~]$ pg_basebackup -h 192.168.1.136 -p 5432 -D pg_remote_backup -Fp -X fetch -P -R -l pg_backup_lable
pg_basebackup: error: could not create directory "/opt/pgdata": Permission denied
pg_basebackup: removing data directory "pg_remote_backup"

原因：
源库设置了自定义表空间数据文件路径导致远程备份创建相同路径时出现权限错误
postgres=# show data_directory;
    data_directory     
-----------------------
 /usr/local/pgsql/data
(1 row)

postgres=# \db
         List of tablespaces
    Name    |  Owner   |  Location   
------------+----------+-------------
 hr         | admin    | /opt/hr_tbs
 pg_default | postgres | 
 pg_global  | postgres | 
 tbs_test   | admin    | /opt/pgdata
(4 rows)

解决方法:
使用-T参数将源库自定义表空间数据文件路径指向本地备份文件路径，如果源库有多个自定义表空间，则使用多个-T参数
[postgres@server ~]$ pg_basebackup -h 192.168.1.136 -p 5432 -D pg_remote_backup -Fp -X fetch -P -R -l pg_backup_lable -T /opt/pgdata=$(pwd)/pg_remote_backup/pgdata -T /opt/hr_tbs=$(pwd)/pg_remote_backup/hr_tbs

-D path 或 --pgdata=direction: 指定备份数据的存储路径，如果该路径不存在，则pg_basebackup会自动创建，如果路径存在且不为空，则会导致执行失败
-F p|t 或 --format=p|t: 指定输出的格式p(plain，默认格式) 或 t(tar)
-z 或 --gzip: 与tar输出模式配合使得，表明输出的tar备份包是经过gzip压缩的，相当于生成了一个*.tar.gz包
-Z level 或 --compress=level: 指定gzip压缩级别，压缩级别为1~9, 1表示最低压缩率，9表示最高压缩率
-l lable 或 --lable=lable: 指定备份标识，便于维护人员识别备份文件
-P 或 --progress: 允许在备份过程中实时显示备份进度
-X f|fetch|s|stream 或 --xlog-method=f|fetch|s|stream: f或fetch表示把备份过程中产生的xlog文件也进行备份，s与stream表示备份开始后，启动另一个连接从源数据库接收WAL日志文件，因此，源数据库的max_wal_senders参数至少要设置为大于或等于2的值

恢复

(1)停止数据库
[postgres@server ~]$ pg_ctl stop

(2)移除源库数据文件
postgres=# show data_directory;
    data_directory     
-----------------------
 /usr/local/pgsql/data
(1 row)

postgres=# \db
         List of tablespaces
    Name    |  Owner   |  Location   
------------+----------+-------------
 hr         | admin    | /opt/hr_tbs
 pg_default | postgres | 
 pg_global  | postgres | 
 tbs_test   | admin    | /opt/pgdata
(4 rows)

[postgres@server ~]$ rm -rf /usr/local/pgsql/data/*
[postgres@server ~]$ rm -rf /opt/hr_tbs/*
[postgres@server ~]$ rm -rf /opt/pgdata/*

(3)将备份解压至源库数据文件所在位置
[postgres@server ~]$ cd /home/postgres/pg_backup/2024-07-31
[postgres@server 2024-07-31]$ ll
total 9632
-rw-rw-r-- 1 postgres postgres     113 Jul 31 08:24 16390.tar.gz
-rw-rw-r-- 1 postgres postgres     111 Jul 31 08:24 90114.tar.gz
-rw-rw-r-- 1 postgres postgres 9829860 Jul 31 08:24 base.tar.gz
-rw------- 1 postgres postgres   21547 Jul 31 08:24 pg_wal.tar.gz

[postgres@server 2024-07-31]$ tar xvf base.tar.gz -C /usr/local/pgsql/data/

(3)解压归档日志
[postgres@server 2024-07-31]$ mkdir pg_wal
[postgres@server 2024-07-31]$ tar xvf pg_wal.tar.gz -C pg_wal/

[postgres@server 2024-07-31]$ ll pg_wal
total 16384
-rw------- 1 postgres postgres 16777216 Jul 31 08:24 000000010000000000000032

(4)创建recovery.conf文件
在PG家目录下的share目录下有一个recovery.conf.sample文件，将其复制到数据目录/usr/local/pgsql/data，并重命名为recovery.conf，授予600权限，编辑recovery.conf文件，添加以下内容:
restore_command='cp /home/postgres/pg_backup/2024-07-31/pg_wal/%f %p'


[postgres@server ~]$ cp /usr/local/pgsql/share/recovery.conf.sample /usr/local/pgsql/data/recovery.conf

[postgres@server ~]$ chmod 0600 /usr/local/pgsql/data/recovery.conf

[postgres@server ~]$ ll /usr/local/pgsql/data/recovery.conf
-rw------- 1 postgres postgres 5762 Jul 31 09:53 /usr/local/pgsql/data/recovery.conf

[postgres@server ~]$ vim /usr/local/pgsql/data/recovery.conf
restore_command='cp /home/postgres/pg_backup/2024-07-31/pg_wal/%f %p'

[postgres@server ~]$ tail -1  /usr/local/pgsql/data/recovery.conf
restore_command='cp /home/postgres/pg_backup/2024-07-31/pg_wal/%f %p'

(5)启动数据，开始恢复
[postgres@server ~]$ pg_ctl start -l postgres.log 
waiting for server to start.... done
server started

(6)登录数据库，检查数据库状态
[postgres@server ~]$ pg_ctl status
pg_ctl: server is running (PID: 5130)
/usr/local/pgsql/bin/postgres

[postgres@server ~]$ psql
psql (10.12)
Type "help" for help.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 db1       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 db2       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 db3       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 mydb      | admin    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 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
(7 rows)

postgres=# \db
         List of tablespaces
    Name    |  Owner   |  Location   
------------+----------+-------------
 hr         | admin    | /opt/hr_tbs
 pg_default | postgres | 
 pg_global  | postgres | 
 tbs_test   | admin    | /opt/pgdata
(4 rows)

数据恢复完成后，recover.conf文件会自动重命名为recover.done
[postgres@server ~]$ ll /usr/local/pgsql/data/recovery*
-rw------- 1 postgres postgres 5834 Jul 31 09:55 /usr/local/pgsql/data/recovery.done

增量备份恢复

(1)创建全库基础备份
[postgres@server ~]$ pg_basebackup -Ft -Pv -Xf -z -Z5 -D pg_backup/`date +%F` -l pg_backup_lable
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/35000028 on timeline 2
824457/824457 kB (100%), 4/4 tablespaces                                         
pg_basebackup: write-ahead log end point: 0/350000F8
pg_basebackup: base backup completed

(2)模拟数据丢失
test=# create table t1(id int);
CREATE TABLE

test=# insert into t1 select * from generate_series(1,5000,2);
INSERT 0 2500

test=# insert into t1 values(8000);
INSERT 0 1

---将数据库恢复到以下这个时间点
test=# select now();
             now              
------------------------------
 2024-07-31 13:54:42.99852+08
(1 row)

test=# insert into t1 values(9000);
INSERT 0 1

test=# select pg_ls_waldir();
                              pg_ls_waldir                               
-------------------------------------------------------------------------
 (00000002.history,42,"2024-07-31 09:57:08+08")
 (000000020000000000000036,16777216,"2024-07-31 12:59:54+08")
 (000000020000000000000035.00000028.backup,295,"2024-07-31 12:43:11+08")
 (000000020000000000000037,16777216,"2024-07-31 12:43:06+08")
 (000000020000000000000038,16777216,"2024-07-31 12:43:11+08")
(5 rows)

[postgres@server ~]$ ls -lrt /usr/local/pgsql/data/pg_archive/2024-07-31
total 344104
......
-rw------- 1 postgres postgres       42 Jul 31 09:57 00000002.history
-rw------- 1 postgres postgres 16777216 Jul 31 12:09 000000020000000000000033
-rw------- 1 postgres postgres 16777216 Jul 31 12:43 000000020000000000000034
-rw------- 1 postgres postgres 16777216 Jul 31 13:53 000000020000000000000036
-rw------- 1 postgres postgres 16777216 Jul 31 13:53 000000020000000000000037
-rw------- 1 postgres postgres      295 Jul 31 13:53 000000020000000000000037.00000028.backup


test=# select pg_switch_wal();
 pg_switch_wal 
---------------
 0/38040910
(1 row)

test=# select * from pg_walfile_name('0/38040910');
     pg_walfile_name      
--------------------------
 000000020000000000000038
(1 row)

test=# \! ls -lrt /usr/local/pgsql/data/pg_archive/2024-07-31
total 360488
......
-rw------- 1 postgres postgres 16777216 Jul 31 12:09 000000020000000000000033
-rw------- 1 postgres postgres 16777216 Jul 31 12:43 000000020000000000000034
-rw------- 1 postgres postgres 16777216 Jul 31 13:53 000000020000000000000036
-rw------- 1 postgres postgres 16777216 Jul 31 13:53 000000020000000000000037
-rw------- 1 postgres postgres      295 Jul 31 13:53 000000020000000000000037.00000028.backup
-rw------- 1 postgres postgres 16777216 Jul 31 13:59 000000020000000000000038

模拟数据丢失，删除数据文件:
[postgres@server ~]$ pg_ctl stop -l postgres.log 
[postgres@server ~]$ rm -rf /usr/local/pgsql/data/*
[postgres@server data]$ mv pg_archive /tmp
[postgres@server data]$ rm -rf *

(3)将备份解压至数据目录位置
[postgres@server ~]$ tar -xvf pg_backup/2024-0(4)7-31/base.tar.gz -C /usr/local/pgsql/data/

(4)创建recover.conf文件
[postgres@server ~]$ cp /usr/local/pgsql/share/recovery.conf.sample /usr/local/pgsql/data/recovery.conf

[postgres@server ~]$ chmod 0600 /usr/local/pgsql/data/recovery.conf

[postgres@server ~]$ ll /usr/local/pgsql/data/recovery.conf
-rw------- 1 postgres postgres 5834 Jul 31 09:55 /usr/local/pgsql/data/recovery.conf

[postgres@server ~]$ vim /usr/local/pgsql/data/recovery.conf
restore_command='cp /tmp/pg_archive/2024-07-31/%f %p'
recovery_target_time='2024-07-31 13:54:42.99852+08'

温馨提示: 如果需要将数据库恢复到最新时间点，则recovery_target_timeline='lastest'，该参数默认值为lastest，不需要填写该参数也可以

[postgres@server ~]$ tail -2 /usr/local/pgsql/data/recovery.conf
restore_command='cp /tmp/pg_archive/2024-07-31/%f %p'
recovery_target_time='2024-07-31 13:54:42.99852+08'

(5)启动数据库，数据库会自动恢复
[postgres@server ~]$ pg_ctl start 
waiting for server to start....2024-07-31 15:15:01.500 CST [12644] LOG:  listening on IPv4 address "192.168.1.136", port 5432
2024-07-31 15:15:01.500 CST [12644] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2024-07-31 15:15:01.501 CST [12644] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2024-07-31 15:15:01.506 CST [12645] LOG:  database system was interrupted; last known up at 2024-07-31 13:53:24 CST
2024-07-31 15:15:01.520 CST [12645] LOG:  starting point-in-time recovery to 2024-07-31 13:54:42.99852+08
2024-07-31 15:15:01.522 CST [12645] LOG:  restored log file "00000002.history" from archive
2024-07-31 15:15:01.558 CST [12645] LOG:  restored log file "000000020000000000000037" from archive
2024-07-31 15:15:01.579 CST [12645] LOG:  redo starts at 0/37000028
2024-07-31 15:15:01.580 CST [12645] LOG:  consistent recovery state reached at 0/370000F8
2024-07-31 15:15:01.581 CST [12644] LOG:  database system is ready to accept read only connections
 done
server started
[postgres@server ~]$ 2024-07-31 15:15:01.626 CST [12645] LOG:  restored log file "000000020000000000000038" from archive
2024-07-31 15:15:01.640 CST [12645] LOG:  recovery stopping before commit of transaction 1795, time 2024-07-31 13:55:24.997222+08
2024-07-31 15:15:01.640 CST [12645] LOG:  recovery has paused
2024-07-31 15:15:01.640 CST [12645] HINT:  Execute pg_wal_replay_resume() to continue.

(6)当前数据库为只读状态，将数据库切换为读写模式
[postgres@server ~]$ psql
psql (10.12)
Type "help" for help.

postgres=# select pg_wal_replay_resume();
 pg_wal_replay_resume 
----------------------
 
(1 row)

---日志显示如下:
waiting for server to start....2024-07-31 15:15:01.500 CST [12644] LOG:  listening on IPv4 address "192.168.1.136", port 5432
2024-07-31 15:15:01.500 CST [12644] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2024-07-31 15:15:01.501 CST [12644] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2024-07-31 15:15:01.506 CST [12645] LOG:  database system was interrupted; last known up at 2024-07-31 13:53:24 CST
2024-07-31 15:15:01.520 CST [12645] LOG:  starting point-in-time recovery to 2024-07-31 13:54:42.99852+08
2024-07-31 15:15:01.522 CST [12645] LOG:  restored log file "00000002.history" from archive
2024-07-31 15:15:01.558 CST [12645] LOG:  restored log file "000000020000000000000037" from archive
2024-07-31 15:15:01.579 CST [12645] LOG:  redo starts at 0/37000028
2024-07-31 15:15:01.580 CST [12645] LOG:  consistent recovery state reached at 0/370000F8
2024-07-31 15:15:01.581 CST [12644] LOG:  database system is ready to accept read only connections
 done
server started
[postgres@server ~]$ 2024-07-31 15:15:01.626 CST [12645] LOG:  restored log file "000000020000000000000038" from archive
2024-07-31 15:15:01.640 CST [12645] LOG:  recovery stopping before commit of transaction 1795, time 2024-07-31 13:55:24.997222+08
2024-07-31 15:15:01.640 CST [12645] LOG:  recovery has paused
2024-07-31 15:15:01.640 CST [12645] HINT:  Execute pg_wal_replay_resume() to continue.
2024-07-31 15:28:30.773 CST [12645] LOG:  redo done at 0/380407B8
2024-07-31 15:28:30.773 CST [12645] LOG:  last completed transaction was at log time 2024-07-31 13:54:35.657468+08
cp: cannot stat ‘/tmp/pg_archive/2024-07-31/00000003.history’: No such file or directory
2024-07-31 15:28:30.776 CST [12645] LOG:  selected new timeline ID: 3
2024-07-31 15:28:30.838 CST [12645] LOG:  archive recovery complete
2024-07-31 15:28:30.840 CST [12645] LOG:  restored log file "00000002.history" from archive
2024-07-31 15:28:30.942 CST [12644] LOG:  database system is ready to accept connections

(7)验证数据库是否恢复完成
[postgres@server ~]$ ll $PGDATA/recovery*
-rw------- 1 postgres postgres 5873 Jul 31 15:14 /usr/local/pgsql/data/recovery.done

[postgres@server ~]$ psql -d test  -c 'select * from t1 order by 1 desc limit 1'
  id  
------
 8000
(1 row)

2.pg_rman

pg_rman类似于oracle数据库的rman备份恢复工具，支持在线和基于时间点的恢复，pg_rman使用前提需要开启归档和配置csvlog
https://ossc-db.github.io/pg_rman/index.html

全库备份

(1)下载pg_rman
[root@server ~]# psql -V
psql (PostgreSQL) 10.12
[root@server ~]# wget https://github.com/ossc-db/pg_rman/releases/download/V1.3.14/pg_rman-1.3.14-pg10.tar.gz
温馨提示: pg_rman安装包下载选择与数据库版本号对应的版本

(2)安装pg_rman
---安装操作系统依赖包
[root@server ~]# yum install zlib-devel
---安装pg_rman
[root@server ~]# tar xvf pg_rman-1.3.14-pg10.tar.gz
[root@server ~]# cd pg_rman-1.3.14-pg10
[root@server pg_rman-1.3.14-pg10]# make 
[root@server pg_rman-1.3.14-pg10]# make install

make install完成后，pg_rman会默认安装在PG_HOME/bin目录下 
[root@server pg_rman-1.3.14-pg10]# which pg_rman
/usr/local/pgsql/bin/pg_rman

(3)初始化备份目录
初始化过程中，pg_rman工具会自动读取postgresql配置文件，获取归档文件路径和数据库系统日志路径，并写入到pg_rman.ini文件中，需要注意的是pg_rman无法识别archive_command中带有$、`command`等环境变量的命令，否则pg_rman初始化会报错
WARNING: ARCLOG_PATH is not set yet
DETAIL: Pg_rman failed to parse archive_command
 
postgres=# show archive_command;
              archive_command              
-------------------------------------------
 cp %p /usr/local/pgsql/data/pg_archive/%f
(1 row)

[postgres@server ~]$ pg_rman init -B /home/postgres/rmanbak
INFO: ARCLOG_PATH is set to '/usr/local/pgsql/data/pg_archive'
INFO: SRVLOG_PATH is set to '/usr/local/pgsql/data/log'

也可以手工指定初始化归档目录:
[postgres@server rmanbak]$ pg_rman init -B /home/postgres/rmanbak -A /usr/local/pgsql/data/pg_archive
INFO: ARCLOG_PATH is set to '/usr/local/pgsql/data/pg_archive'
INFO: SRVLOG_PATH is set to '/usr/local/pgsql/data/log'

[postgres@server ~]$ ll rmanbak
total 8
drwx------ 4 postgres postgres 34 Aug  1 12:01 backup
-rw-rw-r-- 1 postgres postgres 88 Aug  1 12:01 pg_rman.ini
-rw-rw-r-- 1 postgres postgres 40 Aug  1 12:01 system_identifier
drwx------ 2 postgres postgres  6 Aug  1 12:01 timeline_history

(4)pg_rman工具全量备份数据库
postgres=# \c test
You are now connected to database "test" as user "postgres".

test=# \dt
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | t    | table | postgres
 public | t1   | table | postgres
(2 rows)

test=# select count(*) from t;
 count  
--------
 105000
(1 row)

[postgres@server ~]$ pg_rman backup --backup-mode=full --with-serverlog -B /home/postgres/rmanbak
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.

(5)查看备份集状态
[postgres@server ~]$ pg_rman show -B /home/postgres/rmanbak
=====================================================================
 StartTime           EndTime              Mode    Size   TLI  Status 
=====================================================================
2024-08-01 12:49:34  2024-08-01 12:49:36  FULL   134MB     3  DONE
2024-08-01 12:44:14  2024-08-01 12:44:17  FULL    50MB     3  ERROR

[postgres@server ~]$ pg_rman show detail -B /home/postgres/rmanbak
======================================================================================================================
 StartTime           EndTime              Mode    Data  ArcLog  SrvLog   Total  Compressed  CurTLI  ParentTLI  Status 
======================================================================================================================
2024-08-01 12:49:34  2024-08-01 12:49:36  FULL    71MB    83MB    ----   134MB       false       3          0  OK
2024-08-01 12:44:14  2024-08-01 12:44:17  FULL    71MB      0B    ----    50MB       false       3          0  ERROR

[postgres@server ~]$ pg_rman show '2024-08-01 12:49:34' -B /home/postgres/rmanbak
# configuration
BACKUP_MODE=FULL
FULL_BACKUP_ON_ERROR=false
WITH_SERVERLOG=false
COMPRESS_DATA=false
# result
TIMELINEID=3
START_LSN=0/3d000028
STOP_LSN=0/3d0000f8
START_TIME='2024-08-01 12:49:34'
END_TIME='2024-08-01 12:49:36'
RECOVERY_XID=1797
RECOVERY_TIME='2024-08-01 12:49:36'
TOTAL_DATA_BYTES=71178398
READ_DATA_BYTES=71178126
READ_ARCLOG_BYTES=83886704
WRITE_BYTES=134621562
BLOCK_SIZE=8192
XLOG_BLOCK_SIZE=8192
STATUS=OK

(6)校验备份
[postgres@server ~]$ pg_rman validate -B /home/postgres/rmanbak
INFO: validate: "2024-08-01 12:49:34" backup and archive log files by CRC
INFO: backup "2024-08-01 12:49:34" is valid

pg_rman增量备份

(1)新增数据
test=# insert into t select * from generate_series(1,5000);
INSERT 0 5000
test=# select count(*) from t;
 count  
--------
 110000
(1 row)

(2)增量备份
[postgres@server ~]$ pg_rman backup --backup-mode=incremental --with-serverlog -B /home/postgres/rmanbak
INFO: copying database files
INFO: copying archived WAL files
INFO: copying server log files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.

--with-serverlog参数是指将数据库日志一起备份

(3)查看备份集
[postgres@server ~]$ pg_rman show -B /home/postgres/rmanbak
=====================================================================
 StartTime           EndTime              Mode    Size   TLI  Status 
=====================================================================
2024-08-01 13:14:29  2024-08-01 13:14:31  INCR    33MB     3  DONE
2024-08-01 12:49:34  2024-08-01 12:49:36  FULL   134MB     3  OK
2024-08-01 12:44:14  2024-08-01 12:44:17  FULL    50MB     3  ERROR

(4)校验备份
[postgres@server ~]$ pg_rman validate -B /home/postgres/rmanbak
INFO: validate: "2024-08-01 13:14:29" backup, archive log files and server log files by CRC
INFO: backup "2024-08-01 13:14:29" is valid

pg_rman归档备份

(1)继续新增数据
test=# insert into t select * from generate_series(1,10000);
INSERT 0 10000
test=# select count(*) from t;
 count  
--------
 120000
(1 row)

(2)备份归档
[postgres@server ~]$ pg_rman backup --backup-mode=archive --with-serverlog --progress -B /home/postgres/rmanbak
INFO: copying archived WAL files
INFO: copying server log files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied

使用--progress参数显示执行过程详细信息

(3)查看备份集
[postgres@server ~]$ pg_rman show -B /home/postgres/rmanbak
=====================================================================
 StartTime           EndTime              Mode    Size   TLI  Status 
=====================================================================
2024-08-01 13:22:39  2024-08-01 13:22:40  ARCH    16MB     3  DONE
2024-08-01 13:14:29  2024-08-01 13:14:31  INCR    33MB     3  OK
2024-08-01 12:49:34  2024-08-01 12:49:36  FULL   134MB     3  OK
2024-08-01 12:44:14  2024-08-01 12:44:17  FULL    50MB     3  ERROR

[postgres@server ~]$ pg_rman show detail -B /home/postgres/rmanbak
======================================================================================================================
 StartTime           EndTime              Mode    Data  ArcLog  SrvLog   Total  Compressed  CurTLI  ParentTLI  Status 
======================================================================================================================
2024-08-01 13:22:39  2024-08-01 13:22:40  ARCH    ----    16MB      0B    16MB       false       3          0  OK
2024-08-01 13:14:29  2024-08-01 13:14:31  INCR  4005kB    33MB      0B    33MB       false       3          0  OK
2024-08-01 12:49:34  2024-08-01 12:49:36  FULL    71MB    83MB    ----   134MB       false       3          0  OK
2024-08-01 12:44:14  2024-08-01 12:44:17  FULL    71MB      0B    ----    50MB       false       3          0  ERROR

(4)校验备份
[postgres@server ~]$ pg_rman validate -B /home/postgres/rmanbak
INFO: validate: "2024-08-01 13:22:39" archive log files and server log files by CRC
INFO: backup "2024-08-01 13:22:39" is valid

删除备份

[postgres@server ~]$ pg_rman show detail -B /home/postgres/rmanbak
======================================================================================================================
 StartTime           EndTime              Mode    Data  ArcLog  SrvLog   Total  Compressed  CurTLI  ParentTLI  Status 
======================================================================================================================
2024-08-01 13:22:39  2024-08-01 13:22:40  ARCH    ----    16MB      0B    16MB       false       3          0  OK
2024-08-01 13:14:29  2024-08-01 13:14:31  INCR  4005kB    33MB      0B    33MB       false       3          0  OK
2024-08-01 12:49:34  2024-08-01 12:49:36  FULL    71MB    83MB    ----   134MB       false       3          0  OK
2024-08-01 12:44:14  2024-08-01 12:44:17  FULL    71MB      0B    ----    50MB       false       3          0  ERROR

[postgres@server ~]$ pg_rman delete 2024-08-01 12:44:14 -B /home/postgres/rmanbak
INFO: delete the backup with start time: "2024-08-01 12:44:14"

[postgres@server ~]$ pg_rman show detail -B /home/postgres/rmanbak
======================================================================================================================
 StartTime           EndTime              Mode    Data  ArcLog  SrvLog   Total  Compressed  CurTLI  ParentTLI  Status 
======================================================================================================================
2024-08-01 13:22:39  2024-08-01 13:22:40  ARCH    ----    16MB      0B    16MB       false       3          0  OK
2024-08-01 13:14:29  2024-08-01 13:14:31  INCR  4005kB    33MB      0B    33MB       false       3          0  OK
2024-08-01 12:49:34  2024-08-01 12:49:36  FULL    71MB    83MB    ----   134MB       false       3          0  OK

Though delete command removes actual data from file system, there remains some catalog information of deleted backups. In order to remove this, execute purge command.

[postgres@server ~]$ pg_rman show -a -B /home/postgres/rmanbak
=====================================================================
 StartTime           EndTime              Mode    Size   TLI  Status 
=====================================================================
2024-08-01 13:22:39  2024-08-01 13:22:40  ARCH    16MB     3  OK
2024-08-01 13:14:29  2024-08-01 13:14:31  INCR    33MB     3  OK
2024-08-01 12:49:34  2024-08-01 12:49:36  FULL   134MB     3  OK
2024-08-01 12:44:14  2024-08-01 12:44:17  FULL    50MB     3  DELETED

[postgres@server ~]$ pg_rman purge -B /home/postgres/rmanbak
INFO: DELETED backup "2024-08-01 12:44:14" is purged

[postgres@server ~]$ pg_rman show -a -B /home/postgres/rmanbak
=====================================================================
 StartTime           EndTime              Mode    Size   TLI  Status 
=====================================================================
2024-08-01 13:22:39  2024-08-01 13:22:40  ARCH    16MB     3  OK
2024-08-01 13:14:29  2024-08-01 13:14:31  INCR    33MB     3  OK
2024-08-01 12:49:34  2024-08-01 12:49:36  FULL   134MB     3  OK

pg_rman恢复数据

(1)停止数据库
[postgres@server ~]$ pg_ctl stop 
waiting for server to shut down.... done
server stopped

(2)查看备份
[postgres@server ~]$ pg_rman show -B /home/postgres/rmanbak
=====================================================================
 StartTime           EndTime              Mode    Size   TLI  Status 
=====================================================================
2024-08-01 13:22:39  2024-08-01 13:22:40  ARCH    16MB     3  OK
2024-08-01 13:14:29  2024-08-01 13:14:31  INCR    33MB     3  OK
2024-08-01 12:49:34  2024-08-01 12:49:36  FULL   134MB     3  OK

(3)恢复
将数据恢复到增量时间点: 2024-08-01 13:14:31，备份过程中pg_rman会自动在$PGDATA目录创建recovery.conf文件
[postgres@server ~]$ pg_rman restore --recovery-target-time "2024-08-01 13:14:31" -B /home/postgres/rmanbak
WARNING: pg_controldata file "/usr/local/pgsql/data/global/pg_control" does not exist
INFO: the recovery target timeline ID is not given
INFO: use timeline ID of latest full backup as recovery target: 3
INFO: calculating timeline branches to be used to recovery target point
INFO: searching latest full backup which can be used as restore start point
INFO: found the full backup can be used as base in recovery: "2024-08-01 12:49:34"
INFO: copying online WAL files and server log files
INFO: clearing restore destination
INFO: validate: "2024-08-01 12:49:34" backup and archive log files by SIZE
INFO: backup "2024-08-01 12:49:34" is valid
INFO: restoring database files from the full mode backup "2024-08-01 12:49:34"
INFO: searching incremental backup to be restored
INFO: validate: "2024-08-01 13:14:29" backup, archive log files and server log files by SIZE
INFO: backup "2024-08-01 13:14:29" is valid
INFO: restoring database files from the incremental mode backup "2024-08-01 13:14:29"
INFO: searching backup which contained archived WAL files to be restored
INFO: backup "2024-08-01 13:14:29" is valid
INFO: restoring WAL files from backup "2024-08-01 13:14:29"
INFO: validate: "2024-08-01 13:22:39" archive log files and server log files by SIZE
INFO: backup "2024-08-01 13:22:39" is valid
INFO: restoring WAL files from backup "2024-08-01 13:22:39"
INFO: restoring online WAL files and server log files
INFO: generating recovery.conf
INFO: restore complete
HINT: Recovery will start automatically when the PostgreSQL server is started.

[postgres@server ~]$ ll $PGDATA/recovery*
-rw-rw-r-- 1 postgres postgres  182 Aug  1 14:09 /usr/local/pgsql/data/recovery.conf
-rw------- 1 postgres postgres 5873 Aug  1 14:09 /usr/local/pgsql/data/recovery.done

[postgres@server ~]$ more  /usr/local/pgsql/data/recovery.conf
# recovery.conf generated by pg_rman 1.3.14
restore_command = 'cp /usr/local/pgsql/data/pg_archive/%f %p'
recovery_target_time = '2024-08-01 13:14:31'
recovery_target_timeline = '3'

(4)启动数据库，pg会按时间点自动恢复数据
[postgres@server ~]$ pg_ctl start 
waiting for server to start....2024-08-01 14:14:12.415 CST [4805] LOG:  listening on IPv4 address "192.168.1.136", port 5432
2024-08-01 14:14:12.415 CST [4805] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2024-08-01 14:14:12.415 CST [4805] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2024-08-01 14:14:12.423 CST [4806] LOG:  database system was interrupted; last known up at 2024-08-01 13:14:29 CST
cp: cannot stat ‘/usr/local/pgsql/data/pg_archive/00000003.history’: No such file or directory
2024-08-01 14:14:12.443 CST [4806] LOG:  starting point-in-time recovery to 2024-08-01 13:14:31+08
cp: cannot stat ‘/usr/local/pgsql/data/pg_archive/00000003.history’: No such file or directory
2024-08-01 14:14:12.464 CST [4806] LOG:  restored log file "00000003000000000000003F" from archive
2024-08-01 14:14:12.480 CST [4806] LOG:  redo starts at 0/3F000028
2024-08-01 14:14:12.481 CST [4806] LOG:  consistent recovery state reached at 0/3F0000F8
2024-08-01 14:14:12.481 CST [4805] LOG:  database system is ready to accept read only connections
2024-08-01 14:14:12.496 CST [4806] LOG:  restored log file "000000030000000000000040" from archive
2024-08-01 14:14:12.511 CST [4806] LOG:  recovery stopping before commit of transaction 1799, time 2024-08-01 13:14:31.022559+08
2024-08-01 14:14:12.511 CST [4806] LOG:  recovery has paused
2024-08-01 14:14:12.511 CST [4806] HINT:  Execute pg_wal_replay_resume() to continue.
 done(5)
server started

(5)当前数据库为只读状态，将数据库切换为读写模式
[postgres@server ~]$ psql
psql (10.12)
Type "help" for help.

postgres=# select pg_wal_replay_resume();
 pg_wal_replay_resume 
----------------------
 
(1 row)

postgres=# 2024-08-01 14:15:45.595 CST [4806] LOG:  redo done at 0/40000028
2024-08-01 14:15:45.617 CST [4806] LOG:  restored log file "00000003000000000000003F" from archive
cp: cannot stat ‘/usr/local/pgsql/data/pg_archive/00000004.history’: No such file or directory
2024-08-01 14:15:45.637 CST [4806] LOG:  selected new timeline ID: 4
2024-08-01 14:15:45.655 CST [4806] LOG:  archive recovery complete
cp: cannot stat ‘/usr/local/pgsql/data/pg_archive/00000003.history’: No such file or directory
2024-08-01 14:15:45.762 CST [4805] LOG:  database system is ready to accept connections

(6)验证数据
[postgres@server rmanbak]$ psql
psql (10.12)
Type "help" for help.

postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# select count(*) from t;
 count  
--------
 110000
(1 row)

[postgres@server ~]$ ll $PGDATA/recovery*
-rw-rw-r-- 1 postgres postgres 182 Aug  1 14:09 /usr/local/pgsql/data/recovery.done

pg_rman语法

pg_rman manage backup/recovery of PostgreSQL database.

Usage:
  pg_rman OPTION init
  pg_rman OPTION backup
  pg_rman OPTION restore
  pg_rman OPTION show [DATE]
  pg_rman OPTION show detail [DATE]
  pg_rman OPTION validate [DATE]
  pg_rman OPTION delete DATE
  pg_rman OPTION purge

Common Options:
  -D, --pgdata=PATH         location of the database storage area
  -A, --arclog-path=PATH    location of archive WAL storage area
  -S, --srvlog-path=PATH    location of server log storage area
  -B, --backup-path=PATH    location of the backup storage area
  -c, --check               show what would have been done
  -v, --verbose             show what detail messages
  -P, --progress            show progress of processed files

Backup options:
  -b, --backup-mode=MODE    full, incremental, or archive
  -s, --with-serverlog      also backup server log files
  -Z, --compress-data       compress data backup with zlib
  -C, --smooth-checkpoint   do smooth checkpoint before backup
  -F, --full-backup-on-error   switch to full backup mode
                               if pg_rman cannot find validate full backup
                               on current timeline
      NOTE: this option is only used in --backup-mode=incremental or archive.
  --keep-data-generations=NUM keep NUM generations of full data backup
  --keep-data-days=NUM        keep enough data backup to recover to N days ago
  --keep-arclog-files=NUM   keep NUM of archived WAL
  --keep-arclog-days=DAY    keep archived WAL modified in DAY days
  --keep-srvlog-files=NUM   keep NUM of serverlogs
  --keep-srvlog-days=DAY    keep serverlog modified in DAY days
  --standby-host=HOSTNAME   standby host when taking backup from standby
  --standby-port=PORT       standby port when taking backup from standby

Restore options:
  --recovery-target-time    time stamp up to which recovery will proceed
  --recovery-target-xid     transaction ID up to which recovery will proceed
  --recovery-target-inclusive whether we stop just after the recovery target
  --recovery-target-timeline  recovering into a particular timeline
  --hard-copy                 copying archivelog not symbolic link

Catalog options:
  -a, --show-all            show deleted backup too

Delete options:
  -f, --force               forcibly delete backup older than given DATE

Connection options:
  -d, --dbname=DBNAME       database to connect
  -h, --host=HOSTNAME       database server host or socket directory
  -p, --port=PORT           database server port
  -U, --username=USERNAME   user name to connect as
  -w, --no-password         never prompt for password
  -W, --password            force password prompt

Generic options:
  -q, --quiet               don't show any INFO or DEBUG messages
  --debug                   show DEBUG messages
  --help                    show this help, then exit
  --version                 output version information, then exit

Don's Blog

PostgreSQL备份与恢复工具介绍

一、pg逻辑备份与恢复

1.pg_dump

2.pg_dumpall

3.pg_restore

4.pg_dump与pg_dumpall区别

二、pg物理备份与恢复

1.pg_basebackup

2.pg_rman