pg数据库的备份和恢复

pg数据库的备份和恢复
1 备份恢复⽅法
sql转储
⽂件系统级备份
连续归档
2 sql转储
sql转储⽅法的思想就是创建⼀个由SQL命令组成的⽂件，当把这个⽂件返回数据库时候，数据库利⽤其中的sql命令重建与转储状态⼀样的数据库实例。

postgresql提供的⼯具是pg_dump，这个⼯具的基本⽤法如下：
备份：pg_dump dbname > outfile
恢复：psql dbname < infile
需要注意的是，上述两个命令都是在postgres⽤户下运⾏的，outfile和infile都是sql⽂件。

2.1 pg_dump
pg_dump是⼀个普通的客户端⼯具，如果不指定主机IP和端⼝，那么默认备份的是本地服务器上的数据库。

⼀般来说，这个命令由超级管理员来运⾏，这样可以备份到整个数据库的所有对象。

由数据库创建的对象是⼀致的，即在运⾏pg_dump那⼀刻存储了该时刻的数据库快照，这个命令在运⾏过程中数据库的更新不会被转储。

同时，pg_dump不会阻塞其他对数据库的操作。

以下是⼀个数据库转储⽂件的例⼦，从转储⽂件中的内容来看，是由⼀系列的sql语句组成，包含了重建这个数据库所有的sql操作。

--
-- PostgreSQL database dump
--
-- Dumped from database version 9.6.3
-- Dumped by pg_dump version 9.6.3
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;
--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
--
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
--
COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: module_permission; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE module_permission (
profile_key integer NOT NULL,
campaign_key integer NOT NULL,
status integer NOT NULL,
create_time date,
modify_time date
);
ALTER TABLE module_permission OWNER TO postgres;
--
-- Name: userprofile_data; Type: TABLE; Schema: public; Owner: postgres
--
2.2 恢复
pg_dump⽣成的sql⽂本可以有psql程序读取，但是注意的是恢复数据库的时候dbname是需要存在的，也就是说，转储⽂件中并不包含创建数据库的语句。

可以在恢复之前创建⼀个数据库，⽤如下命令来创建⼀个数据库。

createdb -T template0 dbname
表明这个数据库dbname是基于模板template0来创建的，然后再执⾏如下恢复语句：
psql dbname < infile
恢复结果
SET
SET
SET
SET
SET
SET
SET
SET
CREATE EXTENSION
COMMENT
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE VIEW
ALTER TABLE
ALTER TABLE
ALTER TABLE
COPY 10
COPY 2514830
COPY 2514830
COPY 511969
setval
--------
388095
(1 row)
COPY 37
setval
--------
37
(1 row)
COPY 10
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
默认情况下，psql在遇到⼀个sql执⾏错误后，会继续执⾏，并不会停⽌。

这样可能恢复出来的就是⼀个不完整的数据库，需要⼿动修复那⼀⼩部分的错误。

但是实际过程中，这种情况出现的较少，在这⾥不讨论，可以参考相关的数据⼿册中“备份和恢复”章节中的内容。

pg_dump和psql读写管道的能⼒使得直接从⼀个服务器转储⼀个数据库到另外⼀个服务器中成为可能：
pg_dump -h host1 dbname | psql -h host2 dbname
在恢复完成后，基本可以使⽤，但是同时需要注意的是，需要使⽤新数据库的账号密码，原来数据库的账号密码并没有同步到新数据库中，同时为了在新数据库中能有更好的性能，在恢复完毕后，对全库进⾏⼀次analyze操作，在数据字典中建⽴统计信息。

2.3 pg_dumpall
pg_dump⼯具转储的是⼀个数据库dbname中所有的信息，不会转储⾓⾊和表空间等信息，如果需要完整转储整个数据库中所有的数据库实例，PostgreSQL提供了⼀个⼯具pg_dumpall,该⼯具能够转储⼀个数据库集簇中所有的内容，包含了前⾯提到的⾓⾊和表空间。

使⽤的⽅法是：
备份：pg_dumpall > outfile
恢复：psql -f infile postgres
从pg_dump和pg_dumpall两个⼯具的作⽤来看，前者偏向于重建⼀个数据库实例，这个实例中包含了基本的对象和数据信息，适⽤于⾓⾊等信息⽐较简单等，常⽤于定期备份使⽤；后者偏向于重建⼀个数据库集簇，适⽤于在⼀个新的环境中部署⼀套与原来⼀致的数据库环境，包括表空间和⾓⾊，尤其是在⾓⾊较多的复杂环境下，减少了⼿动⼲预的⼯作量。

3 ⽂件系统级备份
这种备份⽅式⽐较简单粗暴，直接将存储数据库的⽂件进⾏备份，也就是通常下的pgdata⽬录，例如：
tar -czvf backup.tar.gz /data1/pgdata
这种⽅法有两个限制：
1、为了得到可⽤的备份，需要停⽌数据库。

创建完备份后，就相当于将该数据库的⼀个快照进⾏了备份，在需要时候，解压缩，启动数据库指定数据⽬录即可。

2、只适⽤于完整的备份，不适⽤于部分数据库的备份。

4 连续归档和时间点恢复
待更新。