Postgresql杂谈 17—Postgresql中的备份和恢复（一）

       本文主要介绍Postgresql的数据库备份和还原。数据库备份是防止数据丢失的一种最简单有效的方式。通过数据库的备份和还原，我们可以将数据库回退到历史上任意一个时间点，进而排除错误的数据。也可以快速克隆线上的数据库环境，协助我们在线下进行缺陷的复现和回归。Postgresql中数据库的备份的方式大体分为两种——逻辑备份和物理备份,下面我们分别进行介绍。

       在介绍备份的方法之前，笔者先介绍下用到的需要进行备份的数据库test，在test数据库中有两个模式如下：

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

       public模式下包含数据库t1，视图v1。

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

       数据库t1包含的数据如下：

test=# select * from t1;
 id | name  
----+-------
  1 | tom
  2 | jerry
(2 rows)

       模式myshema下，具有如下表t2：

test=# \d myschema.*
                       Table "myschema.t2"
 Column |         Type          | Collation | Nullable | Default 
--------+-----------------------+-----------+----------+---------
 id     | integer               |           |          | 
 name   | character varying(32) |           |          | 

一、逻辑备份

        所谓的逻辑备份就是针对整个数据库的逻辑对象进行备份，这些逻辑对象包括数据库表、索引、函数、视图等等。使用逻辑备份，我们可以进行整个数据库的全量备份，也可以进行部分逻辑对象的备份。而且逻辑备份时不会阻塞其他用户对数据库的访问，可以做到热备份。

       Postgresql中使用pg_dump进行数据库的逻辑备份，pg_dump可以将数据库备份成一个普通的文本文件，里面是备份出来的sql语句，使用psql程序执行这些sql命令就可以恢复数据，甚至可以在该文件中进行一定的修改，还原到其它类型的数据库中。也可以将数据库备份成归档格式的备份文件，然后使用pg_restore进行恢复。恢复时Postgresql提供了灵活的恢复方式，可以选择全量恢复，也可以选择部分恢复。

1.1 pg_dump备份成文本文件

       这是默认的备份方式，笔者以自己的测试数据库为例进行备份，查看生成的备份文件：

[postgres@VM-115-39-centos ~]/usr/pgsql-11/bin/pg_dump -h 127.0.0.1 -p 55432 -U postgres test > /data/test.sql
Password: 

-h：目标数据库的IP地址

-p：目标数据库的端口号

-U：备份数据的用户

       test是要备份的数据库名称，执行上述命令后，终端会提示要输入密码，输入密码之后，会在/data/目录下生成名称为test.sql的文本文件。

--
-- PostgreSQL database dump
--
-- Dumped from database version 11.8
-- Dumped by pg_dump version 11.8
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: myschema; Type: SCHEMA; Schema: -; Owner: postgres
--
CREATE SCHEMA myschema;
ALTER SCHEMA myschema OWNER TO postgres;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: mytable; Type: TABLE; Schema: myschema; Owner: postgres
--
CREATE TABLE myschema.mytable (
    id integer,
    name character varying
);
ALTER TABLE myschema.mytable OWNER TO postgres;
--
-- Name: t2; Type: TABLE; Schema: myschema; Owner: postgres
--
CREATE TABLE myschema.t2 (
    id integer,
    name character varying(32)
);
ALTER TABLE myschema.t2 OWNER TO postgres;
--
-- Name: t1; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE public.t1 (
    id integer,
    name character varying(32)
);
ALTER TABLE public.t1 OWNER TO postgres;
--
-- Name: v1; Type: VIEW; Schema: public; Owner: postgres
--
CREATE VIEW public.v1 AS
 SELECT t1.id,
    t1.name
   FROM public.t1;
ALTER TABLE public.v1 OWNER TO postgres;
--
-- Data for Name: mytable; Type: TABLE DATA; Schema: myschema; Owner: postgres
--
COPY myschema.mytable (id, name) FROM stdin;
\.
--
-- Data for Name: t2; Type: TABLE DATA; Schema: myschema; Owner: postgres
--
COPY myschema.t2 (id, name) FROM stdin;
\.
--
-- Data for Name: t1; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.t1 (id, name) FROM stdin;
1       tom
2       jerry
\.
--
-- PostgreSQL database dump complete
--
ALTER TABLE myschema.t2 OWNER TO postgres;
--
-- Name: t1; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE public.t1 (
    id integer,
    name character varying(32)
);
ALTER TABLE public.t1 OWNER TO postgres;
--
-- Name: v1; Type: VIEW; Schema: public; Owner: postgres
--
CREATE VIEW public.v1 AS
 SELECT t1.id,
    t1.name
   FROM public.t1;
ALTER TABLE public.v1 OWNER TO postgres;
--
-- Data for Name: mytable; Type: TABLE DATA; Schema: myschema; Owner: postgres
--
COPY myschema.mytable (id, name) FROM stdin;
\.
--
-- Data for Name: t2; Type: TABLE DATA; Schema: myschema; Owner: postgres
--
COPY myschema.t2 (id, name) FROM stdin;
\.
--
-- Data for Name: t1; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.t1 (id, name) FROM stdin;
1       tom
2       jerry
\.
--
-- PostgreSQL database dump complete
--

       可以看到，里面的内容实际上就是一些导出来的SQL语句和注释，把所有的模式、表、视图内容全部备份成了相应的SQL语句。我们可以对该文本文件进行任意修改，也可以改成符合其他类型数据库的语法，从而在其他数据库中使用。通常情况下，使用psql对该文件进行数据库的还原，这里，我们将数据还原到一个名叫test2的数据库中：

[postgres@VM-115-39-centos ~]/usr/pgsql-11/bin/psql -h 127.0.0.1 -p 55432 -U postgres test2 < /data/test.sql 
Password for user postgres: 

       这样，我们就能把test数据库里面的全部内容还原到了test2中。其实，用默认的方式进行数据库的备份和还原还有些美中不足的地方，那就是：

（1）我们在还原数据库时必须先新建数据库，然后才能执行还原命令，比如说我们必须先有test2数据库才能进行还原。

（2）如果还原时的目标数据库存在和原数据库同名的逻辑对象，比如说数据表，则还原过程会发生错误，将会导致还原失败。

       针对上述两个问题，pg_dump备份时有相应的解决方案。针对问题1，pg_dump备份时提供了-C 选项，可以在备份文件中先执行Create database语句：

[postgres@VM-115-39-centos ~]/usr/pgsql-11/bin/pg_dump -h 127.0.0.1 -p 55432 -U postgres -C test > /data/test.sql
Password: 

       备份成功之后，我们发现在目标文件里面多了创建数据库的语句：

CREATE DATABASE test WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.utf8' LC_CTYPE = 'en_US.utf8';

      此时，使用psql还原时，我们可以执行以下命令：

[postgres@VM-115-39-centos ~]/usr/pgsql-11/bin/psql -h 127.0.0.1 -p 55432 -U postgres  < /data/test.sql 
Password for user postgres: 

       可以看到，和之前还原命令的区别在于不必再明确指出要还原到的目标数据库。

       针对问题2，pg_dump备份时可以使用-c参数，这个参数的意义是在创建新的逻辑对象之前，先drop掉旧的对象，这样即使目标数据库存在同名的对象也不会出现错误。

[postgres@VM-115-39-centos ~]/usr/pgsql-11/bin/pg_dump -h 127.0.0.1 -p 55432 -U postgres -c test > /data/test.sql
Password

       可以看到生成的文本文件中，先进行了drop操作：

DROP VIEW public.v1;
DROP TABLE public.t1;
DROP TABLE myschema.t2;
DROP TABLE myschema.mytable;
DROP SCHEMA myschema;

       如果我们同时使用-c -C参数，还可以先drop数据库，然后再新建。

[postgres@VM-115-39-centos ~]/usr/pgsql-11/bin/pg_dump -h 127.0.0.1 -p 55432 -U postgres -cC test > /data/test.sql
Password:

test.sql:

DROP DATABASE test;
--
-- Name: test; Type: DATABASE; Schema: -; Owner: postgres
--

CREATE DATABASE test WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.utf8' LC_CTYPE = 'en_US.utf8';

1.2 pg_dump部分备份

       使用pg_dump备份文本文件时，除了做全量备份，还可以只备份部分表，只需要在备份时使用-t 参数指明需要备份的表，下面的例子，就是笔者选择备份public.t1和myschema.t2两个数据表：

[postgres@VM-115-39-centos ~]/usr/pgsql-11/bin/pg_dump -h 127.0.0.1 -p 55432 -U postgres -t public.t1 -t myschema.t2 test > /data/test.sql
Password:

       我们在备份表时，也可以选择使用-a参数只备份数据，而不创建表结构：

[postgres@VM-115-39-centos ~]/usr/pgsql-11/bin/pg_dump -h 127.0.0.1 -p 55432 -U postgres -t public.t1 -t myschema.t2 -a test > /data/test.sql
Password:

       或者使用-s参数，只备份表结构，不备份数据：

[postgres@VM-115-39-centos ~]/usr/pgsql-11/bin/pg_dump -h 127.0.0.1 -p 55432 -U postgres -t public.t1 -t myschema.t2 -s test > /data/test.sql
Password:

       备份部分表时，也支持通过“*”通配符匹配多个表：

[postgres@VM-115-39-centos ~]/usr/pgsql-11/bin/pg_dump -h 127.0.0.1 -p 55432 -U postgres -t 'public.t*' test > /data/test.sql
Password: 

1.3 pg_dump进行数据库快照

       使用pg_dump可以将数据库备份成文本文件之外，还可以备份成快照格式，快照格式的文件使用pg_restore进行恢复。

       使用pg_dump生成数据库快照的方式，只需要添加-F c的参数，但是需要注意的是使用这种方式备份，是无法进行部分表备份的，只能进行全量备份：

[postgres@VM-115-39-centos ~]/usr/pgsql-11/bin/pg_dump -h 127.0.0.1 -p 55432 -U postgres -Fc test> /data/test.dump
Password:

       使用pg_restore进行数据库的还原：

[postgres@VM-115-39-centos ~]/usr/pgsql-11/bin/pg_restore -h 127.0.0.1 -p 55432 -U postgres -d test < /data/test.dump 
Password: 
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 5; 2615 18633 SCHEMA myschema postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  schema "myschema" already exists
    Command was: CREATE SCHEMA myschema;

-h：目标数据库的ip地址

-p：目标数据库的端口号

-U：目标数据库执行还原的用户名

-d：目标数据库名称

       当笔者直接在我本机上执行时上述命令时，出现了上述错误，原因是我本机上的test数据库存在同名的模式、表等等，解决的方法很简单，就是执行pg_restore时加上-c的参数：

[postgres@VM-115-39-centos ~]/usr/pgsql-11/bin/pg_restore -h 127.0.0.1 -p 55432 -U postgres -c -d test < /data/test.dump 
Password:

       -c的参数表明在恢复前会清理掉当前数据库的逻辑对象，可能细心的童鞋已经发现，这和使用pg_dump生成带有drop命令的文本文件格式备份文件的参数一致。的确是这样，使用pg_dump进行数据库快照的生成时，虽然不能进行部分备份、也不能使用-C或者-c参数，但是在使用pg_restore恢复时，却可以使用这些参数。

• pg_restore常用参数：

参数	作用
-h IP地址	指定目标数据库的IP地址
-p 端口号	指定目标数据库的端口号
-d 数据库名称	指定目标数据库名称
-U	指定用户名
-w	不需要密码
-W	以明文的形式把密码写在pg_dump命令行
-C	恢复时，若没有数据库则先创建数据库
-c	恢复时，若存在同名的表、模式等逻辑对象，先drop
-a	只恢复数据，不恢复表结构
-s	只恢复表结构，不恢复数据
-t 表名称或者通配符	恢复指定的表
-n 模式名称	只恢复指定的模式下的逻辑数据，可以和-t配合使用

• pg_dump常用参数：

参数	作用
-h IP地址	指定目标数据库的IP地址
-p 端口号	指定目标数据库的端口号
-U	指定用户名
-w	不需要密码
-W	以明文的形式把密码写在pg_dump命令行
-F format	生成指定格式的备份文件。 -Fp：默认，生成文本格式的备份文件；-Fc：生成数据库快照
-C	备份时，先创建数据库（这个参数在官方可以看到，但是笔者经过实验，貌似没有效果）
-c	备份时，先drop同名的逻辑对象
-a	只备份数据，不备份表结构
-s	只备份表结构，不备份数据
-t 表名称或者通配符	备份指定的表
-n 模式名称	只备份指定的模式下的逻辑数据，可以和-t配合使用