postgresql |数据库 |数据库的常用备份和恢复方法总结

admin • 2024-01-25 20:10 • 数据库

前言：

数据库的重要性就不需要在这里重复了，那么，不管是测试环境，还是开发环境，亦或者是生产环境，数据库作为系统内（项目内）的一个非常重要的组件（通常，我们也会认为数据库是一个基础性的组件），如何确保数据库不被意外情况，例如，断电或者是误操作（通常来说，百分之90以上的数据库事故都是人为的误操作因素导致的），从而引起数据的丢失或者损坏。

OK，面对以上的危险，自然是备份是最有效的一个方式，那么，有一个合适的备份策略，合理的备份方法，无疑的，会让你有一个吃后悔药的机会。这里主要想说的是备份是很重要的，没有备份的数据库不如没有数据库这个组件，这显然是一个合理的逻辑。

下面，将就比较常用的postgresql数据库备份做一个比较详尽的总结。

一，

不同数据库备份的相同和不同点

OK，我们常用的数据库有oracle，MySQL，postgresql，SQLserver等等，这里，将它们列在一起的原因仅仅是由于这些数据库是同一类型的数据库-----关系型数据库，因此，它们在使用中以及备份工作中会有很多的相似点，仅此而已。

那么，oracle数据库通常是由它的专业工具imp和emp来完成的，也就是俗称的数据泵，同样的，在MySQL和postgresql数据库中，也存在这样的专业用于备份和备份恢复的工具，在MySQL内，此工具通常为mysql_dump，在postgresql数据库内，此工具通常为pg_dump（postgresql的恢复工具是pg_restore）。

也就是说，备份工具名称稍有不同，但，实际的使用中，参数也有很多不同，但总体功能是基本一致的，例如，通过以上工具可以快速的备份整库，备份单表，备份表结构。

二，

备份目标

主要是关注点，也就是需求出发，例如，我们可能对某个MySQL数据库，关心某一张或者若干张表的数据，那么，自然的，备份重点就是这个单表或者若干表了，并且基本上数据库都是一个或者若干个表作为业务表会经常的读写，而其它的表很多是长年累月不动的，因此，定期全库备份，在更短的时间段内备份常读写的库是比较合适的策略。

或者是关注的表结构，那么，自然备份重点是表结构啦，

还有可能是整体都想要，也就是梭哈里的 all in，那么，自然是有几个数据库就备份几个数据库了。

以上备份会有很多地方不同，下面就以上需求讲解，如何在postgresql数据库备份。

注：一般情况下，数据库备份可以分为物理备份和逻辑备份这两种方式，自然的，数据泵，pg_dump这些功能更为丰富的专业工具是属于逻辑备份的，因为，它们的备份动作是有按照事先规定的一些逻辑来进行的。备份可以做到精细化，例如，精确备份某一个数据库内的某张表，或者说具体到仅仅备份某库的某表的数据而不包含表结构。而物理备份通常是粗放的全库备份，也就是整体数据库不管什么细节，都备份。

三，

物理备份

物理备份分为两种（这么分只是因为我目前就知道这两个，pg_start_backup()以后在说吧）

1，

第一种物理备份方式

冷备冷恢复

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

[root@EULEER ~]# echo $PGDATA
/usr/local/pgsql/data

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

cp -r /usr/local/pgsql/data /media/

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

物理备份的恢复：

[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

那么，此种方式比较霸道，也可以说是野蛮，因此，优点是简单，直接，高效，缺点是需要停机维护，不能实时备份，算是冷备范畴。另一个缺点是，一般生产环境的数据库由于经年累月的使用，数据库一般会是非常大的，几百G 上T也是经常的事情，而cp命令的效率是和文件大小是有关联的，受限于此，这个时候的备份和恢复是比较困难的。,

2，

第二种物理备份方式：

热备冷恢复

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

[root@EULEER bin]# mkdir /opt/backup
[root@EULEER bin]# chown -Rf pg1. /opt/backup
[root@EULEER bin]# su - pg1 -c "pg_basebackup -D /opt/backup  -Xstream -cfast -P"
32850/32850 kB (100%), 1/1 tablespace

参数说明：

-D：指定备份文件所在目录，要求为空，否则报错

-Xstream：使用流模式备份所需的wal日志，可能需适当上调max_wal_senders 参数值

-cfast：备份前会等待库执行检查点，fast表示立即执行

-P：备份过程中显示进度

恢复：

基本和上面那个最原始物理备份类似，恢复时需要删除原数据库的所有文件，然后将备份出来的文件回写到原数据库位置，然后在启动数据库即可

[root@EULEER bin]# rm -rf /usr/local/pgsql/data/*
[root@EULEER bin]# cp -r /opt/backup/* /usr/local/pgsql/data/
[root@EULEER bin]# chown -Rf pg1. /usr/local/pgsql/data/
[root@EULEER bin]# bash ~/start-pgsql.sh 
waiting for server to start....2023-03-09 10:38:11.266 CST [26246] 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 10:38:11.267 CST [26246] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2023-03-09 10:38:11.267 CST [26246] LOG:  listening on IPv6 address "::", port 5432
2023-03-09 10:38:11.271 CST [26246] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2023-03-09 10:38:11.320 CST [26247] LOG:  database system was interrupted; last known up at 2023-03-09 10:05:30 CST
2023-03-09 10:38:11.391 CST [26247] LOG:  redo starts at 0/5000028
2023-03-09 10:38:11.393 CST [26247] LOG:  consistent recovery state reached at 0/5000100
2023-03-09 10:38:11.393 CST [26247] LOG:  redo done at 0/5000100
2023-03-09 10:38:11.536 CST [26246] LOG:  database system is ready to accept connections

四，

逻辑备份

热备热恢复

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

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

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

注：

pg_dump常用参数
-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，导出的数据，有显式列名

1，

pg_dump 热备单表和该表的数据热恢复

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

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

[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

备份命令：

su - pg1 -c "pg_dump -h localhost -U postgres --port=5432 -d test -t emp -f /home/pg1/emp-bak1.sql "

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

su - pg1 -c "pg_dump -h localhost -U postgres --port=5432 -d test -t emp -f /home/pg1/emp-bak1.sql --inserts "

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

su - pg1 -c "pg_dump -h localhost -U postgres --port=5432 -d test -t emp   -C -f /home/pg1/emp-bak1.sql --inserts "

生成的备份文件内容如下：

[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）：

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 命令行，执行查询恢复即可，可查到数据表明恢复成功：

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将会报错。

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"

2，

pg_dump 热备单库热恢复（热恢复指的是不需要停止数据库服务）

su - pg1 -c "pg_dump -h localhost -U postgres --port=5432 -d test    -C -f /home/pg1/test-database.sql --inserts "

备份的文件内容：

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

[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
--

使用命令行恢复;

[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

自定义归档模式备份单库：

su - pg1 -c "pg_dump -h localhost -U postgres --port=5432 -d test -Fc -Z 9   -C -f /home/pg1/test-database.dump "

利用归档文件恢复：

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

su - pg1 -c " pg_restore -d test /home/pg1/test-database.dump "

五，

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

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

查看该文件的具体属性可以看到如下：

[pg1@EULEER ~]$ file test-database.dump 
test-database.dump: PostgreSQL custom database dump - v1.14-0

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

[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文件了：

pg_restore -l test-database.dump >TOC.txt

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

pg_restore -l -f TOC-1.txt test-database.dump

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

使用TOC文件：

[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参数即可：

[pg1@EULEER ~]$ pg_restore -v -d test1  test-database.dump

带schema的备份：

pg_dump -Upostgres -p 5432 -Fc  -d 要备份的数据库名称 -s 要备份的schema名称 -f 备份文件名称

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

pg_restore -v -d test1 -n test test.dump

六，

pg_dumpall

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

su - pg1 -c "pg_dumpall -h localhost -U postgres --port=5432 -f myglobals.sql --globals-only"

[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这个用户，看看能否通过命令行恢复：

[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来备份全局对象而非全库数据。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。

THE END

二维码

区块链背后的秘密：从交易看故事

< <上一篇

MySQL数据库——MySQL创建视图（CREATE VIEW）

下一篇>>

搜索内容