前往小程序，Get更优阅读体验！
立即前往
文档建议反馈控制台
首页
学习
活动
专区
工具
TVP
最新优惠活动
文章/答案/技术大牛
发布
首页
学习
活动
专区
工具
TVP最新优惠活动
返回腾讯云官网
社区首页 >专栏 >进阶数据库系列（二十）：PostgreSQL 数据库备份与恢复

进阶数据库系列（二十）：PostgreSQL 数据库备份与恢复

作者头像
民工哥
发布于 2023-08-22 14:03:19
5.4K0
发布于 2023-08-22 14:03:19
举报
文章被收录于专栏：民工哥技术之路

PostgreSQL 备份工具

物理备份

冷备冷恢复(第一种物理备份方式)

此种方式是直接备份数据库物理文件，在pg数据库里，这通常指的是PGDATA变量定义的文件夹，例如：

代码语言：javascript
复制
[root@EULEER ~]# echo $PGDATA/usr/local/pgsql/data

我的这个示例数据库是安装在了/usr/local/pgsql目录下，那么，备份如下，文件备份到了/media目录下了：

代码语言：javascript
复制
cp -r /usr/local/pgsql/data /media/

但需要注意的是，此种方式由于比较直接，不管是否数据库有无IO情况，因此，备份的时候需要先停止数据库，恢复的时候要删除原数据库文件，重新覆盖回去后，才可以在启动数据库，如果在数据库启动的时候备份，那么，可能会造成数据备份不全，也可以理解为冷备方式。

物理备份的恢复
代码语言：javascript
复制
[root@EULEER ~]# rm -rf /usr/local/pgsql/data/*
[root@EULEER ~]# cp -r /media/data/* /usr/local/pgsql/data/
[root@EULEER ~]# bash start-pgsql.sh 
waiting for server to start....2023-03-09 08:18:58.143 CST [23561] LOG:  starting PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (EulerOS 4.8.5-28), 64-bit
2023-03-09 08:18:58.144 CST [23561] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2023-03-09 08:18:58.144 CST [23561] LOG:  listening on IPv6 address "::", port 5432
2023-03-09 08:18:58.148 CST [23561] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2023-03-09 08:18:58.224 CST [23562] LOG:  database system was shut down at 2023-03-09 08:04:04 CST
2023-03-09 08:18:58.232 CST [23561] LOG:  database system is ready to accept connections
 done
server started

热备冷恢复(第二种物理备份方式)

pg_basebackup 基本原理和步骤

pg_basebackup 也算是物理方式，是可以使用流协议。并且是热备范畴，备份的时候不需要停数据库，但，恢复的时候需要停数据库（多说一句，某些项目数据库是24小时不能停的，如果停了，可能接受不了，因此，备份前最好搞清楚自己是否能够接受冷备）。

备份：使用pg_basebackup命令来进行备份，这个命令可以将postgresql的数据文件备份为两个压缩文件：base.tar和 pg_wal.tar。本别是数据文件和归档文件，恢复的时候，需要设置按照归档文件来恢复。那么，此种方式的备份可以备份自定义表空间。

恢复：需要先把备份的压缩文件替换当前的数据文件，然后修改postgresql.conf，因为这个配置文件在data文件夹中，所以只能是在把base.tar解压到数据库当前数据位置，也就是我们默认初始化指定的数据保存位置data文件夹中，才能修改配置，在配置好归档设置以后，可以启动pgsql服务，进行启动恢复。

在恢复过程中，会拷贝归档文件，进行数据恢复。

恢复成功，也就是数据库服务启动成功。这个时候我们访问数据库，它是作为归档状态存在的，所以只能读，不能写操作。

为了恢复数据库写操作，我们需要在命令行下执行切换数据库状态的指令。切换成功之后，才可以进行读写操作。

实操示例

环境介绍：数据库主节点，IP：192.168.123.60，数据库端口：5432，数据库安装路径为/usr/local/pgsql ，管理用户为pg1。

创建一个自定义表空间，创建表空间存放路径。

代码语言：javascript
复制
[root@EULER1 ~]# mkdir /opt/custome-tablespace
[root@EULER1 ~]# chown -Rf pg1. /opt/custome-tablespace/

登陆命令行，创建表空间

代码语言：javascript
复制
[root@EULER1 ~]# su - pg1 -c "psql -Upostgres -p 5432 -h 192.168.123.60"
Password for user postgres: 
psql (12.5)
Type "help" for help.
 
postgres=# create tablespace mytbs location '/opt/custome-tablespace';
CREATE TABLESPACE
postgres=# \db+
                                          List of tablespaces
    Name    |  Owner   |        Location         | Access privileges | Options |  Size   | Description 
------------+----------+-------------------------+-------------------+---------+---------+-------------
 mytbs      | postgres | /opt/custome-tablespace |                   |         | 0 bytes | 
 pg_default | pg1      |                         |                   |         | 23 MB   | 
 pg_global  | pg1      |                         |                   |         | 623 kB  | 
(3 rows)

此时的$PGDATA目录下的pg_tblspc目录下有一个软链接：

代码语言：javascript
复制
[root@EULER1 ~]# ls -al /usr/local/pgsql/data/pg_tblspc/
total 8
drwx------.  2 pg1 pg1 4096 Mar 19 20:06 .
drwx------. 20 pg1 pg1 4096 Mar 19 18:39 ..
lrwxrwxrwx   1 pg1 pg1   23 Mar 19 20:06 32771 -> /opt/custome-tablespace

创建一个新库和新表在此表空间内：

代码语言：javascript
复制
postgres=# create database test;
CREATE DATABASE
postgres=# alter database test set tablespace mytbs;
ALTER DATABASE
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# create table tb_mytps(i int,name varchar(32)) tablespace mytbs;
CREATE TABLE

插入实验数据

代码语言：javascript
复制
insert into tb_mytps(i,name) values(2,'name2');
insert into tb_mytps(i,name) values(3,'name3');
insert into tb_mytps(i,name) values(4,'name4');
insert into tb_mytps(i,name) values(5,'name5');
insert into tb_mytps(i,name) values(6,'name6');
insert into tb_mytps(i,name) values(7,'name7');
insert into tb_mytps(i,name) values(8,'name8');
insert into tb_mytps(i,name) values(9,'name9');
insert into tb_mytps(i,name) values(10,'name10');

查看是否正确插入数据:

代码语言：javascript
复制
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# select * from tb_mytps ;
 i  |  name  
----+--------
  1 | name1
  2 | name2
  3 | name3
  4 | name4
  5 | name5
  6 | name6
  7 | name7
  8 | name8
  9 | name9
 10 | name10
(10 rows)
开始备份

建立备份文件存放路径

代码语言：javascript
复制
[root@EULER1 ~]# mkdir /opt/backup
[root@EULER1 ~]# chown -Rf pg1. /opt/backup

可以看到有两个tablespace被备份了，-z -Z 5是压缩等级，范围是0-9， -Ft是tar包格式备份。

代码语言：javascript
复制
[root@EULER1 ~]# su - pg1 -c "pg_basebackup  -D /opt/backup -Ft -z -Z 5 -Pv  -Upostgres   -h 192.168.123.60  -p5432"
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/8000028 on timeline 2
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_119924"
32567/32567 kB (100%), 2/2 tablespaces                                         
pg_basebackup: write-ahead log end point: 0/8000100
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: base backup completed

查看备份的文件：

代码语言：javascript
复制
[root@EULER1 backup]# ls -al /opt/backup/
total 48968
drwx------  2 pg1  pg1      4096 Mar 19 20:27 .
drwxr-xr-x. 5 root root     4096 Mar 19 20:24 ..
-rw-------  1 pg1  pg1   8162304 Mar 19 20:27 32771.tar
-rw-------  1 pg1  pg1  25188352 Mar 19 20:27 base.tar
-rw-------  1 pg1  pg1  16780288 Mar 19 20:27 pg_wal.tar

恢复

停止数据库：

代码语言：javascript
复制
[root@EULER1 ~]# bash ~/stop-pgsql.sh 
waiting for server to shut down.... done
server stopped

删除原库的数据文件：

代码语言：javascript
复制
[root@EULER1 ~]# rm -rf /usr/local/pgsql/data/*
[root@EULER1 ~]# rm -rf /opt/custome-tablespace/*

解压备份文件到对应路径：要注意是解压到哪里

代码语言：javascript
复制
[root@EULER1 ~]# tar xf /opt/backup/32771.tar -C /opt/custome-tablespace/
[root@EULER1 ~]# tar xf /opt/backup/base.tar -C /usr/local/pgsql/data/

创建wal文件存放路径：赋予数据库管理用户权限，最后一个命令不能忘

代码语言：javascript
复制
[root@EULER1 ~]# mkdir /usr/local/pgsql/wal-back
[root@EULER1 ~]# tar xf /opt/backup/pg_wal.tar -C /usr/local/pgsql/wal-back/
[root@EULER1 data]# chown -Rf pg1. /usr/local/pgsql/

修改postgresql.conf文件：

任选一种恢复方式
  • 立刻恢复
代码语言：javascript
复制
restore_command = 'cp /usr/local/pgsql/wal-back/%f %p'
recovery_target = 'immediate'
  • 可以按时间线恢复到最新
代码语言：javascript
复制
恢复到最新： 
restore_command = 'cp /usr/local/pgsql/wal-back/%f %p'
recovery_target_timeline = 'latest'
  • 按时间点恢复
代码语言：javascript
复制
restore_command = 'cp /usr/local/pgsql/wal-back/%f %p' 
recovery_target_time = '2023-03-19 16:16:16.007657+08'

如果不想进入备份模式，直接数据库启动就可以用，那么就使用promote。

代码语言：javascript
复制
recovery_target_action  #指定在达到恢复目标时服务器采取的动作。 
pause  #默认值，表示恢复将被暂停
promote  #表示恢复结束且服务器将开始接受连接
shutdown #表示在达到恢复目标之后停止服务器。
启动数据库
代码语言：javascript
复制
[pg1@EULER1 ~]$ pg_ctl -D /usr/local/pgsql/data/ start
waiting for server to start....2023-03-19 21:17:49.399 CST [28172] LOG:  starting PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (EulerOS 4.8.5-28), 64-bit
2023-03-19 21:17:49.400 CST [28172] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2023-03-19 21:17:49.400 CST [28172] LOG:  listening on IPv6 address "::", port 5432
2023-03-19 21:17:49.405 CST [28172] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2023-03-19 21:17:49.444 CST [28172] LOG:  redirecting log output to logging collector process
2023-03-19 21:17:49.444 CST [28172] HINT:  Future log output will appear in directory "log".
 done
server started

查看数据库状态：此时的数据库是备份状态，因此pg_ctl promote即可。

代码语言：javascript
复制
[root@EULER1 data]# pg_controldata 
pg_control version number:            1201
Catalog version number:               201909212
Database system identifier:           7211655189372047015
Database cluster state:               in archive recovery
pg_control last modified:             Sun 19 Mar 2023 09:17:49 PM CST
Latest checkpoint location:           0/A000060
Latest checkpoint's REDO location:    0/A000028

或者是进入命令行，执行以下函数即可：

代码语言：javascript
复制
select pg_wal_replay_resume()；

最终确认是否恢复正常：

代码语言：javascript
复制
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# select *from tb_mytps;
 i  |  name  
----+--------
  1 | name1
  2 | name2
  3 | name3
  4 | name4
  5 | name5
  8 | name8
  9 | name9
 10 | name10
  6 | name61
  7 | name71
(10 rows)
test=# update  tb_mytps set name = 'name999' where i = 7;
UPDATE 1
test=# select * from tb_mytps;
 i  |  name   
----+---------
  1 | name1
  2 | name2
  3 | name3
  4 | name4
  5 | name5
  8 | name8
  9 | name9
 10 | name10
  6 | name61
  7 | name999
(10 rows)

逻辑备份

热备热恢复

首选当然是pg_dump啦，这个备份工具是和pg_restore配套的，也可以看成是一个组合。

该备份工具的特点是稳定，高效，冷热备份恢复都可以，可以选择数据库部分表备份，只备份表结构，因此，该工具的使用比较复杂，这点是相对物理备份来说的。

怎么说呢，物理备份有一种暴力的美学感觉，简单的方法有时候更为高效。逻辑备份比较枯燥，复杂。

pg_dump常用参数
代码语言：javascript
复制
-h host，指定数据库主机名，或者IP
-p port，指定端口号
-U user，指定连接使用的用户名
-W，按提示输入密码
-F, --format=c|d|t|p   output file format (备份文件的格式是自定义，目录，tar包，纯文本，不使用该参数，将会是纯文本默认)
-d  指定连接的数据库名称，实际上也是要备份的数据库名称。
-a，–data-only，只导出数据，不导出表结构,该选项只对纯文本格式有意义。
-c，–clean，是否生成清理该数据库对象的语句，比如drop table,该选项只对纯文本格式有意义。
-C，–create，是否输出一条创建数据库语句,该选项只对纯文本格式有意义。
-f file，–file=file，输出到指定文件中
-n schema，–schema=schema，只转存匹配schema的模式内容
-N schema，–exclude-schema=schema，不转存匹配schema的模式内容
-O，–no-owner，不设置导出对象的所有权
-s，–schema-only，只导致对象定义模式，不导出数据
-t table，–table=table，只转存匹配到的表，视图，序列，可以使用多个-t匹配多个表
-T table，–exclude-table=table，不转存匹配到的表。
–inserts，使用insert命令形式导出数据，这种方式比默认的copy方式慢很多，但是可用于将数据导入到非PostgreSQL数据库。
–column-inserts，导出的数据，有显式列名
pg_dump 热备单表和该表的数据热恢复

备份前数据库test的表内容：

可以看到有三个表，分别是emp，bonus，salgrade，库名是test。

代码语言：javascript
复制
[root@EULEER ~]# su - pg1 -c "psql -U postgres"
psql (12.5)
Type "help" for help.
 
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# \l
                                List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | Access privileges 
-----------+----------+----------+-------------+-------------+-------------------
 postgres  | pg1      | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | pg1      | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/pg1           +
           |          |          |             |             | pg1=CTc/pg1
 template1 | pg1      | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/pg1           +
           |          |          |             |             | pg1=CTc/pg1
 test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)
 
test=# \d+
                        List of relations
 Schema |   Name   | Type  |  Owner   |    Size    | Description 
--------+----------+-------+----------+------------+-------------
 public | bonus    | table | postgres | 8192 bytes | 
 public | emp      | table | postgres | 16 kB      | 
 public | salgrade | table | postgres | 16 kB      | 
(3 rows)

现在计划备份emp表，该表备份到pg1用户的家目录下（如果是其它目录，可能会没有权限），使用工具为pg_dump。

备份命令
代码语言：javascript
复制
su - pg1 -c "pg_dump -h localhost -U postgres --port=5432 -d test -t emp -f /home/pg1/emp-bak1.sql "

如果有迁移到其它数据库，比如oracle的计划，那么，最好还是添加参数--inserts，上面的命令修改为如下：

代码语言：javascript
复制
su - pg1 -c "pg_dump -h localhost -U postgres --port=5432 -d test -t emp -f /home/pg1/emp-bak1.sql --inserts "

如果希望恢复的时候不需要切换数据库，那么，应该使用参数大C，命令如下：

代码语言：javascript
复制
su - pg1 -c "pg_dump -h localhost -U postgres --port=5432 -d test -t emp   -C -f /home/pg1/emp-bak1.sql --inserts "
生成的备份文件内容如下
代码语言：javascript
复制
[root@EULEER ~]# cat  /home/pg1/emp-bak1.sql \
> 
--
-- PostgreSQL database dump
--
 
-- Dumped from database version 12.5
-- Dumped by pg_dump version 12.5
 
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
 
--
-- Name: test; Type: DATABASE; Schema: -; Owner: postgres
--
 
CREATE DATABASE test WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';
 
ALTER DATABASE test OWNER TO postgres;
 
\connect test
#大C参数的作用
 
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
 
SET default_tablespace = '';
 
SET default_table_access_method = heap;
 
--
-- Name: emp; Type: TABLE; Schema: public; Owner: postgres
--
 
CREATE TABLE public.emp (
    empno numeric NOT NULL,
    ename character varying(10),
    job character varying(9),
    mgr numeric,
    hiredate date,
    sal numeric(7,2),
    comm numeric(7,2),
    deptno numeric(2,0)
);
 
ALTER TABLE public.emp OWNER TO postgres;
 
--
-- Data for Name: emp; Type: TABLE DATA; Schema: public; Owner: postgres
--
 
INSERT INTO public.emp VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800.00, NULL, 20);
INSERT INTO public.emp VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600.00, 300.00, 30);
INSERT INTO public.emp VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250.00, 500.00, 30);
INSERT INTO public.emp VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975.00, NULL, 20);
INSERT INTO public.emp VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250.00, 1400.00, 30);
INSERT INTO public.emp VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850.00, NULL, 30);
INSERT INTO public.emp VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450.00, NULL, 10);
INSERT INTO public.emp VALUES (7788, 'SCOTT', 'ANALYST', 7566, '0087-04-19', 3000.00, NULL, 20);
INSERT INTO public.emp VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000.00, NULL, 10);
INSERT INTO public.emp VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500.00, 0.00, 30);
INSERT INTO public.emp VALUES (7876, 'ADAMS', 'CLERK', 7788, '0087-05-23', 1100.00, NULL, 20);
INSERT INTO public.emp VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950.00, NULL, 30);
INSERT INTO public.emp VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000.00, NULL, 20);
INSERT INTO public.emp VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300.00, NULL, 10);
 
--
-- Name: emp pk_emp; Type: CONSTRAINT; Schema: public; Owner: postgres
--
 
ALTER TABLE ONLY public.emp
    ADD CONSTRAINT pk_emp PRIMARY KEY (empno);
 
--
-- PostgreSQL database dump complete
--
pg_dump的恢复命令

进入pg命令行直接执行备份的SQL文件即可（执行SQL语句前需要切换数据库到test）：

代码语言：javascript
复制
postgres=# \c test
You are now connected to database "test" as user "postgres".
 
test=# \i /home/pg1/emp-bak1.sql 
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)
 
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
COPY 14
ALTER TABLE

重进一次pg 命令行，执行查询恢复即可，可查到数据表明恢复成功：

代码语言：javascript
复制
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# select * from emp;
 empno | ename  |    job    | mgr  |  hiredate  |   sal   |  comm   | deptno 
-------+--------+-----------+------+------------+---------+---------+--------
  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |         |     20
  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30
  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30
  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |         |     20
  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30
  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |         |     30
  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |         |     10
  7788 | SCOTT  | ANALYST   | 7566 | 0087-04-19 | 3000.00 |         |     20
  7839 | KING   | PRESIDENT |      | 1981-11-17 | 5000.00 |         |     10
  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30
  7876 | ADAMS  | CLERK     | 7788 | 0087-05-23 | 1100.00 |         |     20
  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |         |     30
  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |         |     20
  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |         |     10
(14 rows)

附1

归档文件分类

归档格式的备份文件又分为两种，最灵活的输出文件格式是“custom”自定义格式（使用命令项参数“-Fc”来指定），它允许对归档元素进行选取和重新排列，并且默认是压缩的；另一种是tar格式（使用命令项参数“-Ft”来指定），这种格式的文件不是压缩的，并且加载时不能重新排序，但是它也很灵活，可以用标准UNIX下的tar工具进行处理。custom自定义格式比较常用。

不带-F参数的时候，默认是纯文本模式（纯文本模式备份的文件可以使用记事本打开，里面都是SQL语句）

归档格式的备份文件必须与pg_restore一起使用来重建数据库，这种格式允许pg_restore选择恢复哪些数据，甚至可以在恢复之前对需要恢复的条目重新排序。

pg_dump可以将整个数据库备份到一个归档格式的备份文件中，而pg_restore则可以从这个归档格式的备份文件中选择性地恢复部分表或数据库对象，而不必恢复所有的数据。

归档模式的时候，必须使用pg_restore工具来进行恢复

以下仍然是单表的备份和恢复，备份文件名做了一个时间格式化，恢复的时候是使用pg_restore命令，使用psql将会报错。

代码语言：javascript
复制
su - pg1 -c "pg_dump -h localhost -U postgres --port=5432 -d test -t emp  -Fc    -f /home/pg1/$(date +'%Y-%m-%dT%H:%M:%S.%2N%z')-emp-bak1.sql"
 
su - pg1 -c "pg_restore -d test  /home/pg1/2023-03-10T10:25:39.18+0800-emp-bak1.sql"
pg_dump 热备单库热恢复（热恢复指的是不需要停止数据库服务）
代码语言：javascript
复制
su - pg1 -c "pg_dump -h localhost -U postgres --port=5432 -d test  -C -f /home/pg1/test-database.sql --inserts "
备份的文件内容

可以看到有建库的SQL语句，因为使用了-C参数，一般情况下是不使用--insecres参数，如果数据库比较大的话，会非常的费时间，同样的，恢复的时候也非常费时间。

代码语言：javascript
复制
[root@EULEER pg1]# cat test-database.sql 
--
-- PostgreSQL database dump
--
 
-- Dumped from database version 12.5
-- Dumped by pg_dump version 12.5
 
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
 
--
-- Name: test; Type: DATABASE; Schema: -; Owner: postgres
--
 
CREATE DATABASE test WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';
 
ALTER DATABASE test OWNER TO postgres;
 
\connect test
 
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
 
SET default_tablespace = '';
 
SET default_table_access_method = heap;
 
--
-- Name: bonus; Type: TABLE; Schema: public; Owner: postgres
--
 
CREATE TABLE public.bonus (
    ename character varying(10),
    job character varying(9),
    sal numeric,
    comm numeric
);
 
ALTER TABLE public.bonus OWNER TO postgres;
 
--
-- Name: emp; Type: TABLE; Schema: public; Owner: postgres
--
 
CREATE TABLE public.emp (
    empno numeric NOT NULL,
    ename character varying(10),
    job character varying(9),
    mgr numeric,
    hiredate date,
    sal numeric(7,2),
    comm numeric(7,2),
    deptno numeric(2,0)
);
 
ALTER TABLE public.emp OWNER TO postgres;
 
--
-- Name: salgrade; Type: TABLE; Schema: public; Owner: postgres
--
 
CREATE TABLE public.salgrade (
    grade numeric NOT NULL,
    losal numeric,
    hisal numeric
);
  
ALTER TABLE public.salgrade OWNER TO postgres;
 
--
-- Data for Name: bonus; Type: TABLE DATA; Schema: public; Owner: postgres
--
 
--
-- Data for Name: emp; Type: TABLE DATA; Schema: public; Owner: postgres
--
 
INSERT INTO public.emp VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800.00, NULL, 20);
INSERT INTO public.emp VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600.00, 300.00, 30);
INSERT INTO public.emp VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250.00, 500.00, 30);
INSERT INTO public.emp VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975.00, NULL, 20);
INSERT INTO public.emp VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250.00, 1400.00, 30);
INSERT INTO public.emp VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850.00, NULL, 30);
INSERT INTO public.emp VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450.00, NULL, 10);
INSERT INTO public.emp VALUES (7788, 'SCOTT', 'ANALYST', 7566, '0087-04-19', 3000.00, NULL, 20);
INSERT INTO public.emp VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000.00, NULL, 10);
INSERT INTO public.emp VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500.00, 0.00, 30);
INSERT INTO public.emp VALUES (7876, 'ADAMS', 'CLERK', 7788, '0087-05-23', 1100.00, NULL, 20);
INSERT INTO public.emp VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950.00, NULL, 30);
INSERT INTO public.emp VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000.00, NULL, 20);
INSERT INTO public.emp VALUES (7934, 'MILLER', 'CLERK', 7782, '2023-03-24', 1300.00, NULL, 10);
 
--
-- Data for Name: salgrade; Type: TABLE DATA; Schema: public; Owner: postgres
--
 
INSERT INTO public.salgrade VALUES (1, 700, 1200);
INSERT INTO public.salgrade VALUES (2, 1201, 1400);
INSERT INTO public.salgrade VALUES (3, 1401, 2000);
INSERT INTO public.salgrade VALUES (4, 2001, 3000);
INSERT INTO public.salgrade VALUES (5, 3001, 9999);
 
--
-- Name: emp pk_emp; Type: CONSTRAINT; Schema: public; Owner: postgres
--
 
ALTER TABLE ONLY public.emp
    ADD CONSTRAINT pk_emp PRIMARY KEY (empno);
 
--
-- Name: salgrade salgrade_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--
 
ALTER TABLE ONLY public.salgrade
    ADD CONSTRAINT salgrade_pkey PRIMARY KEY (grade);
 
--
-- PostgreSQL database dump complete
--
使用命令行恢复
代码语言：javascript
复制
[root@EULEER pg1]# su - pg1 -c 'psql -U postgres -h localhost'
psql (12.5)
Type "help" for help.
 
postgres=# drop database test;
DROP DATABASE
postgres=# \i /home/pg1/test-database.sql 
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)
 
SET
SET
SET
SET
CREATE DATABASE
ALTER DATABASE
You are now connected to database "test" as user "postgres".
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)
 
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
ALTER TABLE
ALTER TABLE
自定义归档模式备份单库
代码语言：javascript
复制
su - pg1 -c "pg_dump -h localhost -U postgres --port=5432 -d test -Fc -Z 9   -C -f /home/pg1/test-database.dump "
利用归档文件恢复

需要提前建立test这个数据库

代码语言：javascript
复制
su - pg1 -c " pg_restore -d test /home/pg1/test-database.dump "

pg_dump命令备份出来的自定义格式的dump文件的恢复

这里说的备份文件格式是由pg_dump -Fc参数生成的文件，此类文件是二进制格式的，不可当做文本文件查看，恢复的时候必须使用pg_restore命令，例如上面的test-database.dump文件就是此类文件。

查看该文件的具体属性可以看到如下
代码语言：javascript
复制
[pg1@EULEER ~]$ file test-database.dump 
test-database.dump: PostgreSQL custom database dump - v1.14-0

此文件是可以利用pg_restore命令查看的，参数为-l ：

代码语言：javascript
复制
[pg1@EULEER ~]$ pg_restore -l test-database.dump 
;
; Archive created at 2023-04-04 00:25:10 CST
;     dbname: test
;     TOC Entries: 29
;     Compression: 9
;     Dump Version: 1.14-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 12.5
;     Dumped by pg_dump version: 12.5
;
;
; Selected TOC Entries:
;
211; 1255 16420 FUNCTION public __tmp_create_user() postgres
225; 1255 16428 FUNCTION public f_inittables1(text[]) postgres
212; 1255 16427 FUNCTION public ftest(character varying, character varying) postgres
210; 1255 16426 FUNCTION public getsum(character varying) postgres
205; 1259 16490 TABLE public bonus postgres
203; 1259 16469 TABLE public dept postgres
204; 1259 16477 TABLE public emp postgres
207; 1259 16545 TABLE public emp1 postgres
208; 1259 16551 TABLE public emp3 postgres
209; 1259 16559 TABLE public emp7 postgres
206; 1259 16496 TABLE public salgrade postgres
202; 1259 16423 TABLE public tmp postgres
3135; 0 16490 TABLE DATA public bonus postgres
3133; 0 16469 TABLE DATA public dept postgres
3134; 0 16477 TABLE DATA public emp postgres
3137; 0 16545 TABLE DATA public emp1 postgres
3138; 0 16551 TABLE DATA public emp3 postgres
3139; 0 16559 TABLE DATA public emp7 postgres
3136; 0 16496 TABLE DATA public salgrade postgres
3132; 0 16423 TABLE DATA public tmp postgres
3002; 2606 16558 CONSTRAINT public emp3 emp3_pkey postgres
3004; 2606 16566 CONSTRAINT public emp7 emp7_pkey postgres
2998; 2606 16476 CONSTRAINT public dept pk_dept postgres
3000; 2606 16484 CONSTRAINT public emp pk_emp postgres
3005; 2606 16485 FK CONSTRAINT public emp fk_deptno postgres
[pg1@EULEER ~]$ file test-database.dump 
test-database.dump: PostgreSQL custom database dump - v1.14-0
[pg1@EULEER ~]$ pg_restore -l test-database.dump 
;
; Archive created at 2023-04-04 00:25:10 CST
;     dbname: test
;     TOC Entries: 29
;     Compression: 9
;     Dump Version: 1.14-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 12.5
;     Dumped by pg_dump version: 12.5
;
;
; Selected TOC Entries:
;
211; 1255 16420 FUNCTION public __tmp_create_user() postgres
225; 1255 16428 FUNCTION public f_inittables1(text[]) postgres
212; 1255 16427 FUNCTION public ftest(character varying, character varying) postgres
210; 1255 16426 FUNCTION public getsum(character varying) postgres
205; 1259 16490 TABLE public bonus postgres
203; 1259 16469 TABLE public dept postgres
204; 1259 16477 TABLE public emp postgres
207; 1259 16545 TABLE public emp1 postgres
208; 1259 16551 TABLE public emp3 postgres
209; 1259 16559 TABLE public emp7 postgres
206; 1259 16496 TABLE public salgrade postgres
202; 1259 16423 TABLE public tmp postgres
3135; 0 16490 TABLE DATA public bonus postgres
3133; 0 16469 TABLE DATA public dept postgres
3134; 0 16477 TABLE DATA public emp postgres
3137; 0 16545 TABLE DATA public emp1 postgres
3138; 0 16551 TABLE DATA public emp3 postgres
3139; 0 16559 TABLE DATA public emp7 postgres
3136; 0 16496 TABLE DATA public salgrade postgres
3132; 0 16423 TABLE DATA public tmp postgres
3002; 2606 16558 CONSTRAINT public emp3 emp3_pkey postgres
3004; 2606 16566 CONSTRAINT public emp7 emp7_pkey postgres
2998; 2606 16476 CONSTRAINT public dept pk_dept postgres
3000; 2606 16484 CONSTRAINT public emp pk_emp postgres
3005; 2606 16485 FK CONSTRAINT public emp fk_deptno postgres

OK，将查看到的内容重定向到一个文件，那么，此重定向文件就是TOC文件了：

代码语言：javascript
复制
pg_restore -l test-database.dump >TOC.txt

当然了，还可以这样生成TOC文件，和上面的命令是等价的（TOC.txt和TOC-1.txt两个文件是一模一样的）：

代码语言：javascript
复制
pg_restore -l -f TOC-1.txt test-database.dump

这个时候的TOC文件是可编辑的文本文件，不需要执行的部分删除或者;;注释掉就可以了。

使用TOC文件：
代码语言：javascript
复制
[pg1@EULEER ~]$ pg_restore -v -d test1 -L  TOC.txt test-database.dump 
pg_restore: connecting to database for restore
pg_restore: creating FUNCTION "public.__tmp_create_user()"
pg_restore: creating FUNCTION "public.f_inittables1(text[])"
pg_restore: creating FUNCTION "public.ftest(character varying, character varying)"
pg_restore: creating FUNCTION "public.getsum(character varying)"
pg_restore: creating TABLE "public.bonus"
pg_restore: creating TABLE "public.dept"
pg_restore: creating TABLE "public.emp"
pg_restore: creating TABLE "public.emp1"
pg_restore: creating TABLE "public.emp3"
pg_restore: creating TABLE "public.emp7"
pg_restore: creating TABLE "public.salgrade"
pg_restore: creating TABLE "public.tmp"
pg_restore: processing data for table "public.bonus"
pg_restore: processing data for table "public.dept"
pg_restore: processing data for table "public.emp"
pg_restore: processing data for table "public.emp1"
pg_restore: processing data for table "public.emp3"
pg_restore: processing data for table "public.emp7"
pg_restore: processing data for table "public.salgrade"
pg_restore: processing data for table "public.tmp"
pg_restore: creating CONSTRAINT "public.emp3 emp3_pkey"
pg_restore: creating CONSTRAINT "public.emp7 emp7_pkey"
pg_restore: creating CONSTRAINT "public.dept pk_dept"
pg_restore: creating CONSTRAINT "public.emp pk_emp"
pg_restore: creating FK CONSTRAINT "public.emp fk_deptno"
[pg1@EULEER ~]$ echo $?
0

当然，不使用TOC文件也是可以的，这也就是意味着全部还原，去掉-L参数即可：

代码语言：javascript
复制
[pg1@EULEER ~]$ pg_restore -v -d test1  test-database.dump

带schema的备份：

代码语言：javascript
复制
pg_dump -Upostgres -p 5432 -Fc  -d 要备份的数据库名称 -s 要备份的schema名称 -f 备份文件名称

带schema的恢复（-n后面接的是schema的名称，此schema需要提前创建）：

代码语言：javascript
复制
pg_restore -v -d test1 -n test test.dump

pg_dumpall

此工具是全库备份，但，一般是不使用这个的，因为，数据库有可能会很大，进而备份的时候出现问题，此工具可以备份用户信息，例如下面这个命令（全局对象里包括用户，因此，如果是仅备份用户信息，也可以使用参数r即可）：

代码语言：javascript
复制
su - pg1 -c "pg_dumpall -h localhost -U postgres --port=5432 -f myglobals.sql --globals-only"
代码语言：javascript
复制
[root@EULEER pg1]# cat myglobals.sql 
--
-- PostgreSQL database cluster dump
--
 
SET default_transaction_read_only = off;
 
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
 
--
-- Roles
--
 
CREATE ROLE pg1;
ALTER ROLE pg1 WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS;
CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md59f56b2b83a029175c74aafe56b0764da';
CREATE ROLE zsk;
ALTER ROLE zsk WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
 
--
-- PostgreSQL database cluster dump complete
--

OK，删除zsk这个用户，看看能否通过命令行恢复：

代码语言：javascript
复制
[root@EULEER pg1]# su - pg1 -c 'psql -U postgres -h localhost'
psql (12.5)
Type "help" for help.
 
postgres=# \du+
                                          List of roles
 Role name |                         Attributes                         | Member of | Description 
-----------+------------------------------------------------------------+-----------+-------------
 pg1       | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        | 
 postgres  | Superuser, Create role, Create DB                          | {}        | 
 zsk       | Cannot login                                               | {}        | 
 
postgres=# drop role zsk;
DROP ROLE
postgres=# \du+
                                          List of roles
 Role name |                         Attributes                         | Member of | Description 
-----------+------------------------------------------------------------+-----------+-------------
 pg1       | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        | 
 postgres  | Superuser, Create role, Create DB                          | {}        | 
 
postgres=# \i /home/pg1/myglobals.sql 
SET
SET
SET
2023-03-13 23:23:54.556 CST [5060] ERROR:  role "pg1" already exists
2023-03-13 23:23:54.556 CST [5060] STATEMENT:  CREATE ROLE pg1;
psql:/home/pg1/myglobals.sql:14: ERROR:  role "pg1" already exists
ALTER ROLE
2023-03-13 23:23:54.559 CST [5060] ERROR:  role "postgres" already exists
2023-03-13 23:23:54.559 CST [5060] STATEMENT:  CREATE ROLE postgres;
psql:/home/pg1/myglobals.sql:16: ERROR:  role "postgres" already exists
ALTER ROLE
CREATE ROLE
ALTER ROLE
postgres=# \du+
                                          List of roles
 Role name |                         Attributes                         | Member of | Description 
-----------+------------------------------------------------------------+-----------+-------------
 pg1       | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        | 
 postgres  | Superuser, Create role, Create DB                          | {}        | 
 zsk       | Cannot login                                               | {}        |

非常的方便，简单就可以把数据库内的所有用户信息备份了。

附注

建议每天对角色和表空间定义等全局对象进行备份，但不建议每天使用pg_dumpall来备份全库数据，因为pg_dumpall仅支持导出为SQL文本格式，而使用这种庞大的SQL文本备份来进行全库级别的数据库恢复时极其耗时的，所以一般只建议使用pg_dumpall来备份全局对象而非全库数据。

Barman 安装及备份PostgreSQL

Barman (Backup and Recovery Manager-备份恢复管理器) 。

是一个用Python语言实现的PostgreSQL灾难恢复管理工具，它由第二象限公司（2ndQuadrant）开源并维护。它允许我们在关键业务环境中执行远程备份，为数据库管理员在恢复阶段提供有效的数据保证。Barman最优秀的功能包括备份元数据、增量备份、保留策略、远程回复、WAL文件归档压缩和备份。

barman 建议以一个 dedicated server 存在。尽量不要部署到 postgresql server 上，但不是不能部署到 postgresql server 主机上。

barman优点
  • 可以与postgresql的master和slaver分离部署，但是barman服务器需要安装postgresql的一些工具，如pg_basebackup、pg_receivewal
  • 通过流复制可以同步拉取wal，如果postgresql当前的wal还没close，barman会产生一个.partial文件实时同步，若在恢复时使用该文件需要将扩展后缀去掉
  • barman可以一台服务器管理多个postgresql server，提供本地及远程恢复（远程恢复需要配置ssh免密）
  • 提供备份相关文件的检测及详细查询
barman特点
  • 零数据丢失备份。保证用户在只有一台备份服务器的情况下达到零数据丢失。
  • 与备份服务器合作。允许备份服务器在与主服务器的流式复制不可用时，从barman获取wal文件。
  • 可靠的监控集成。用于监控和创建有关Barman配置状态的报告。
  • 可并行的备份和恢复。以更快的速度执行拷贝，加快了整个备份和恢复过程。
  • 备份目录。允许用户在同一界面中列出、保留、删除、存档和恢复多个完整备份。
  • 支持多个服务器。允许您从同一位置管理多个服务器的备份和恢复。
  • 存储备份数据。存储来自多个PostgreSQL实例的备份数据，支持不同的版本。
  • 完全热备份。从其他服务器执行PostgreSQL服务器的完全热备份，专门为备份和灾难恢复而设计。
  • 增量备份和恢复。针对新的和修改过的数据生成不同的备份，从而加快处理速度。
  • 保留策略。配置自定义备份保留策略以满足组织的需要。
  • 远程恢复。执行PostgreSQL服务器备份的远程恢复。
  • Wal文件压缩。自定义归档和压缩WAL文件和备份。
  • 备份信息。生成所有备份的常规和磁盘使用信息。
  • 地理冗余。给DBA和系统管理员为PostgreSQL数据库的灾难恢复解决方案增加另一层保障。
barman安装
  • 192.168.2.2 pg （postgresql server服务器）
  • 192.168.2.3 barman （barman备份服务器）
  • postgresql版本11.2
  • barman版本2.9.1
yum安装postgresql
代码语言：javascript
复制
yum install -y https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7.6-x86_64/pgdg-centos11-11-2.noarch.rpm
yum install -y postgresql11-server-11.2 postgresql11-contrib-11.2

/usr/pgsql-11/bin/postgresql-11-setup initdb  #执行数据库初始化

修改postgresql.conf配置

代码语言：javascript
复制
listen_addresses = '*'
port = 5432
wal_level = replica  #11版本后改为replica
 
archive_mode = on
archive_command = 'cp %p /var/lib/pgsql/11/data/pg_archive/%f'
max_wal_senders = 10  #建议大于等于3
wal_keep_segments = 64
synchronous_standby_names = '*'
hot_standby = on
log_filename = 'postgresql-%Y%m%d_%H%M.log'

修改pg_hba.conf配置

代码语言：javascript
复制
local   all             all                                       trust
host    all             all               0.0.0.0/0               md5
host    all             all               ::1/128                 trust
host    replication     barman            0.0.0.0/0               md5
host    replication     streaming_barman  0.0.0.0/0               md5

创建pg_archive，并设置用户组。

代码语言：javascript
复制
mkdir /var/lib/pgsql/11/data/pg_archive
chown postgres:postgres /var/lib/pgsql/11/data/pg_archive -R

启动postgresql

代码语言：javascript
复制
systemctl enable postgresql-11
systemctl start postgresql-11

创建barman和barman_streaming用户

代码语言：javascript
复制
su postgres
createuser -s -P barman
#设置密码123456
createuser -P --replication streaming_barman
#设置密码123456

#创建.pgpass
touch ~/.pgpass
#添加
pg:5432:*:streaming_barman:123456
pg:5432:*:barman:123456
#更改权限
chmod 600 ~/.pgpass
#验证
psql -c 'SELECT version()' -U barman -h pg postgres
yum安装barman

由于barman是python写的，且有些功能需要使用postgresql的工具，安装时会有些依赖

代码语言：javascript
复制
yum install -y https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7.6-x86_64/postgresql11-libs-11.2-1PGDG.rhel7.x86_64.rpm
yum install -y https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7.6-x86_64/python2-psycopg2-2.8.2-1.rhel7.x86_64.rpm
yum install -y https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7.6-x86_64/barman-2.9-1.rhel7.noarch.rpm
yum install -y https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7.6-x86_64/barman-cli-2.9-1.rhel7.noarch.rpm

修改barman的公共配置

代码语言：javascript
复制
vi /etc/barman.conf
#修改以下配置
parallel_jobs = 2   #并发数
immediate_checkpoint = true   #在执行back时是否会自动执行checkpoint
network_compression = true    #网络传输压缩

修改barman对postgresql server的私有配置

代码语言：javascript
复制
cd /etc/barman.d/
cp streaming-server.conf-template pg.conf
#将pg.conf中的[streaming]修改为[pg]

将192.168.2.2 pg添加到/etc/hosts中

barman使用

创建复制槽

代码语言：javascript
复制
barman receive-wal --create-slot pg

barman check

代码语言：javascript
复制
[root@25ff7df1b128 pg]# barman check pg
Server pg:
 PostgreSQL: OK
 is_superuser: OK
 PostgreSQL streaming: OK
 wal_level: OK
 replication slot: OK
 directories: OK
 retention policy settings: OK
 backup maximum age: OK (no last_backup_maximum_age provided)
 compression settings: OK
 failed backups: OK (there are 0 failed backups)
 minimum redundancy requirements: FAILED (have 0 backups, expected at least 1)
 pg_basebackup: OK
 pg_basebackup compatible: OK
 pg_basebackup supports tablespaces mapping: OK
 pg_receivexlog: OK
 pg_receivexlog compatible: OK
 receive-wal running: OK
 archiver errors: OK

其中minimum redundancy requirements: FAILED可忽略，首次配置后还没有全备，执行全备会变为OK。

代码语言：javascript
复制
[root@25ff7df1b128 pg]# barman backup pg
Starting backup using postgres method for server pg in /var/lib/barman/pg/base/20190821T171724
Backup start at LSN: 0/90000D0 (000000010000000000000009, 000000D0)
Starting backup copy via pg_basebackup for 20190821T171724
Copy done (time: 2 seconds)
Finalising the backup.
This is the first backup for server pg
WAL segments preceding the current backup have been found:
 000000010000000000000008 from server pg has been removed
Backup size: 166.6 MiB
Backup end at LSN: 0/B000060 (00000001000000000000000B, 00000060)
Backup completed (start time: 2019-08-21 17:17:24.956763, elapsed time: 2 seconds)
Processing xlog segments from streaming for pg
 000000010000000000000009
 00000001000000000000000A

首次配置完会产生minimum redundancy requirements: FAILED、receive-wal running:FAILED，执行barman cron即可变为OK，barman会执行如下过程。

代码语言：javascript
复制
[root@25ff7df1b128 pg]# barman cron
Starting WAL archiving for server pg
Starting streaming archiver for server pg
Starting check-backup for backup 20190821T172217 of server pg

#正常流复制时的进程
[root@25ff7df1b128 pg]# ps -ef|grep barman
root      1278     1  0 17:30 ?        00:00:00 /usr/bin/python2 /usr/bin/barman -c /etc/barman.conf -q receive-wal pg
root      1281  1278  0 17:30 ?        00:00:00 /usr/pgsql-11/bin/pg_receivewal --dbname=dbname=replication host=pg options=-cdatestyle=iso replication=true user=streaming_barman application_name=barman_receive_wal --verbose --no-loop --no-password --directory=/var/lib/barman/pg/streaming --slot=barman --synchronous

#执行全备时的进程
[root@25ff7df1b128 ~]# ps -ef|grep barman
root      1325     1  1 17:33 ?        00:00:00 /usr/bin/python2 /usr/bin/barman -c /etc/barman.conf -q receive-wal pg
root      1327  1325  0 17:33 ?        00:00:00 /usr/pgsql-11/bin/pg_receivewal --dbname=dbname=replication host=pg options=-cdatestyle=iso replication=true user=streaming_barman application_name=barman_receive_wal --verbose --no-loop --no-password --directory=/var/lib/barman/pg/streaming --slot=barman --synchronous
root      1331  1035 12 17:33 pts/0    00:00:00 /usr/bin/python2 /usr/bin/barman backup pg
root      1334  1331  0 17:33 pts/0    00:00:00 /usr/pgsql-11/bin/pg_basebackup --dbname=dbname=replication host=pg options=-cdatestyle=iso replication=true user=streaming_barman application_name=barman_streaming_backup -v --no-password --pgdata=/var/lib/barman/pg/base/20190821T173331/data --no-slot --wal-method=none --checkpoint=fast

所以barman的全备是利用pg_basebackup实现，增量是利用pg_receivewal实现。

barman优点

1.可以与postgresql的master和slaver分离部署，但是barman服务器需要安装postgresql的一些工具，如pg_basebackup、pg_receivewal

2.通过流复制可以同步拉取wal，如果postgresql当前的wal还没close，barman会产生一个.partial文件实时同步，若在恢复时使用该文件需要将扩展后缀去掉

3.barman可以一台服务器管理多个postgresql server，提供本地及远程恢复（远程恢复需要配置ssh免密）

4.提供备份相关文件的检测及详细查询

barman部分功能
查看当前复制状态
代码语言：javascript
复制
[root@25ff7df1b128 ~]# barman replication-status pg
Status of streaming clients for server 'pg':
  Current LSN on master: 0/130001A8
  Number of streaming clients: 1

  1. #1 Sync WAL streamer
     Application name: barman_receive_wal
     Sync stage      : 3/3 Remote write
     Communication   : TCP/IP
     IP Address      : 192.168.2.3 / Port: 33256 / Host: -
     User name       : streaming_barman
     Current state   : streaming (sync)
     Replication slot: barman
     WAL sender PID  : 1731
     Started at      : 2019-08-21 17:33:16.495522+08:00
     Sent LSN   : 0/130001A8 (diff: 0 B)
     Write LSN  : 0/130001A8 (diff: 0 B)
     Flush LSN  : 0/130001A8 (diff: 0 B)
查看备份列表
代码语言：javascript
复制
[root@25ff7df1b128 ~]# barman list-backup pg
pg 20190821T173331 - Wed Aug 21 17:33:35 2019 - Size: 294.6 MiB - WAL Size: 0 B - WAITING_FOR_WALS
pg 20190821T172217 - Wed Aug 21 17:22:20 2019 - Size: 230.6 MiB - WAL Size: 64.0 MiB
pg 20190821T171724 - Wed Aug 21 17:17:27 2019 - Size: 198.6 MiB - WAL Size: 32.0 MiB
查看postgresql server
代码语言：javascript
复制
[root@25ff7df1b128 ~]# barman show-server pg
Server pg:
 active: True
 archive_timeout: 3600
 archiver: False
 archiver_batch_size: 0
 backup_directory: /var/lib/barman/pg
 backup_method: postgres
 backup_options: BackupOptions(['concurrent_backup'])
 bandwidth_limit: None
 barman_home: /var/lib/barman
 barman_lock_directory: /var/lib/barman
 basebackup_retry_sleep: 30
 basebackup_retry_times: 3
 basebackups_directory: /var/lib/barman/pg/base
 check_timeout: 30
 checkpoint_timeout: 300
 compression: None
 config_file: /var/lib/pgsql/11/data/postgresql.conf
 connection_error: None
 conninfo: host=pg user=barman dbname=postgres
 current_size: 23808077
 current_xlog: 000000010000000000000015
 custom_compression_filter: None
 custom_decompression_filter: None
 data_checksums: off
 data_directory: /var/lib/pgsql/11/data
 description: Example of PostgreSQL Database (Streaming-Only)
 disabled: False
 errors_directory: /var/lib/barman/pg/errors
 hba_file: /var/lib/pgsql/11/data/pg_hba.conf
 hot_standby: on
 ident_file: /var/lib/pgsql/11/data/pg_ident.conf
 immediate_checkpoint: True
 incoming_wals_directory: /var/lib/barman/pg/incoming
 is_in_recovery: False
 is_superuser: True
 last_backup_maximum_age: None
 max_incoming_wals_queue: None
 max_replication_slots: 10
 max_wal_senders: 10
 minimum_redundancy: 1
 msg_list: []
 name: pg
 network_compression: False
 parallel_jobs: 1
 passive_node: False
 path_prefix: /usr/pgsql-11/bin
 pg_basebackup_bwlimit: True
 pg_basebackup_compatible: True
 pg_basebackup_installed: True
 pg_basebackup_path: /usr/pgsql-11/bin/pg_basebackup
 pg_basebackup_tbls_mapping: True
 pg_basebackup_version: 11.2
 pg_receivexlog_compatible: True
 pg_receivexlog_installed: True
 pg_receivexlog_path: /usr/pgsql-11/bin/pg_receivewal
 pg_receivexlog_supports_slots: True
 pg_receivexlog_synchronous: True
 pg_receivexlog_version: 11.2
 pgespresso_installed: False
 post_archive_retry_script: None
 post_archive_script: None
 post_backup_retry_script: None
 post_backup_script: None
 post_delete_retry_script: None
 post_delete_script: None
 post_recovery_retry_script: None
 post_recovery_script: None
 post_wal_delete_retry_script: None
 post_wal_delete_script: None
 pre_archive_retry_script: None
 pre_archive_script: None
 pre_backup_retry_script: None
 pre_backup_script: None
 pre_delete_retry_script: None
 pre_delete_script: None
 pre_recovery_retry_script: None
 pre_recovery_script: None
 pre_wal_delete_retry_script: None
 pre_wal_delete_script: None
 primary_ssh_command: None
 recovery_options: RecoveryOptions([])
 replication_slot: Record(slot_name='barman', active=True, restart_lsn='0/150000C8')
 replication_slot_support: True
 retention_policy: None
 retention_policy_mode: auto
 reuse_backup: None
 server_txt_version: 11.2
 slot_name: barman
 ssh_command: None
 streaming: True
 streaming_archiver: True
 streaming_archiver_batch_size: 0
 streaming_archiver_name: barman_receive_wal
 streaming_backup_name: barman_streaming_backup
 streaming_conninfo: host=pg user=streaming_barman
 streaming_supported: True
 streaming_wals_directory: /var/lib/barman/pg/streaming
 synchronous_standby_names: ['*']
 systemid: 6727448561719488729
 tablespace_bandwidth_limit: None
 timeline: 1
 wal_compression: off
 wal_level: replica
 wal_retention_policy: main
 wals_directory: /var/lib/barman/pg/wals
 xlog_segment_size: 16777216
 xlogpos: 0/150000C8
远程执行switch-wal
代码语言：javascript
复制
[root@25ff7df1b128 ~]# barman switch-wal pg
The WAL file 000000010000000000000015 has been closed on server 'pg'
检查backupid
代码语言：javascript
复制
[root@25ff7df1b128 ~]# barman list-backup pg
pg 20190821T174304 - Wed Aug 21 17:43:08 2019 - Size: 326.6 MiB - WAL Size: 0 B - WAITING_FOR_WALS
pg 20190821T173331 - Wed Aug 21 17:33:35 2019 - Size: 310.6 MiB - WAL Size: 16.0 MiB - WAITING_FOR_WALS
pg 20190821T172217 - Wed Aug 21 17:22:20 2019 - Size: 230.6 MiB - WAL Size: 80.0 MiB
pg 20190821T171724 - Wed Aug 21 17:17:27 2019 - Size: 198.6 MiB - WAL Size: 32.0 MiB
[root@25ff7df1b128 ~]# barman check-backup pg 20190821T171724
[root@25ff7df1b128 ~]# barman check-backup pg 20190821T171724s
ERROR: Unknown backup '20190821T171724s' for server 'pg'
查看文件

–target参数包括：

代码语言：javascript
复制
data：#列出data目录的所有文件
standalone：#列出全备和需要的wal文件
wal：#列出全备开始到当前时间的wal文件
full：#等于`data` + `wal`
代码语言：javascript
复制
[root@25ff7df1b128 ~]# barman list-files --target wal pg 20190821T173331
/var/lib/barman/pg/wals/0000000100000000/000000010000000000000012
/var/lib/barman/pg/wals/0000000100000000/000000010000000000000013
备份恢复
代码语言：javascript
复制
#备份恢复
barman recover <server_name> <backup_id> <postgres server data path>
#时间点恢复
barman recover <server_name> <backup_id> <postgres server data path> --target-time TARGET_TIME

还提供：--target-xid TARGET_XID、--target-lsn TARGET_LSN、--target-name TARGET_NAME、--target-immediate

查看备份详情
代码语言：javascript
复制
[root@25ff7df1b128 ~]# barman show-backup pg 20190821T173331
Backup 20190821T173331:
  Server Name            : pg
  Status                 : WAITING_FOR_WALS
  PostgreSQL Version     : 110002
  PGDATA directory       : /var/lib/pgsql/11/data

  Base backup information:
    Disk usage           : 294.6 MiB (310.6 MiB with WALs)
    Incremental size     : 294.6 MiB (-0.00%)
    Timeline             : 1
    Begin WAL            : 000000010000000000000012
    End WAL              : 000000010000000000000012
    WAL number           : 1
    Begin time           : 2019-08-21 17:33:31+08:00
    End time             : 2019-08-21 17:33:35.321271+08:00
    Copy time            : 3 seconds
    Estimated throughput : 75.6 MiB/s
    Begin Offset         : 96
    End Offset           : 0
    Begin LSN           : 0/12000060
    End LSN             : 0/13000000

  WAL information:
    No of files          : 1
    Disk usage           : 16.0 MiB
    WAL rate             : 12.58/hour
    Last available       : 000000010000000000000013

  Catalog information:
    Retention Policy     : not enforced
    Previous Backup      : 20190821T172217
    Next Backup          : 20190821T174304

参考文章：https://blog.csdn.net/alwaysbefine/article/details /129422083 https://blog.csdn.net/weixin_39992480/article /details/100022373 https://zskjohn.blog.csdn.net/article/ details/129648750

本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2023-07-18，如有侵权请联系 cloudcommunity@tencent.com 删除
数据库
postgresql
备份
服务器
数据

本文分享自 民工哥技术之路 微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ，欢迎热爱写作的你一起参与！

数据库
postgresql
备份
服务器
数据
评论
登录后参与评论
0 条评论
热度
最新
登录 后参与评论
推荐阅读
LV.
文章
0
获赞
0
目录
  • 物理备份
    • 冷备冷恢复(第一种物理备份方式)
      • 热备热恢复
        • 归档文件分类
          • pg_dump 热备单库热恢复（热恢复指的是不需要停止数据库服务）
          • pg_dump命令备份出来的自定义格式的dump文件的恢复
          • 附注
          • Barman 安装及备份PostgreSQL
            • barman优点
              • barman特点
                • barman安装
                  • barman使用
                    • barman优点
                      • barman部分功能
                      相关产品与服务
                      数据库
                      云数据库为企业提供了完善的关系型数据库、非关系型数据库、分析型数据库和数据库生态工具。您可以通过产品选择和组合搭建，轻松实现高可靠、高可用性、高性能等数据库需求。云数据库服务也可大幅减少您的运维工作量，更专注于业务发展，让企业一站式享受数据上云及分布式架构的技术红利！
                      产品介绍
                      精选特惠 用云无忧
                      领券

                      腾讯云开发者

                      扫码关注腾讯云开发者

                      扫码关注腾讯云开发者

                      领取腾讯云代金券

                      热门产品

                      热门推荐

                      更多推荐

                      Copyright © 2013 - 2024 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

                      深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

                      腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

                      问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档

                      Copyright © 2013 - 2024 Tencent Cloud.

                      All Rights Reserved. 腾讯云 版权所有

                      登录 后参与评论