本文主要介绍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配合使用 |