[草稿]pg的备份与还原

[TOC]

准备工作

• 准备3个测试环境

• 实例1：准备测试数据

/opt/pgsql/bin/psql -h 127.0.0.1 -p 1921 -U postgres -d postgres

CREATE USER user_backup WITH PASSWORD 'Aa_123456';

CREATE DATABASE db1 WITH OWNER=user_backup ENCODING='UTF-8';
\c db1
CREATE TABLE tb1 (id int NOT NULL, name character(32));
INSERT INTO tb1 (id, name) VALUES (1, 'jason');
GRANT ALL PRIVILEGES ON all tables in schema public TO user_backup;

CREATE DATABASE db2 WITH OWNER=user_backup ENCODING='UTF-8';
\c db2
CREATE TABLE tb2 (id int NOT NULL, name character(32));
INSERT INTO tb2 (id, name) VALUES (2, 'sam');
GRANT ALL PRIVILEGES ON all tables in schema public TO user_backup;

逻辑备份

场景1：利用 pg_dump 进行单库的备份与还原

• pg_dump：只能备份单个数据库的数据（不支持同时备份多个库），包括建表、数据等，但不会导出角色和表空间相关的信息（比如建库、建账号），常用参数如下：

#.参数 -F 指定导出文件的格式，其中默认 -Fp 备份为文本，大库不推荐
pg_dump -h <host> -p <port> -U <username> -Fp -d <database_name> --no-owner -f /path/xxx.sql

#.参数 -Fc 备份为二进制格式，可压缩存储，可被 pg_restore 精细还原
pg_dump -h <host> -p <port> -U <username> -Fc -d <database_name> --no-owner -f /path/xxx.bin

#.参数 -Fd 备份为目录格式，可被 pg_restore 精细还原
pg_dump -h <host> -p <port> -U <username> -Fd -j <number> -d <database_name> -f /path/xxx.dir

#.参数 -t 用来备份特定的表
pg_dump -h <host> -p <port> -U <username> -Fp -t <table_name> <database_name> -f /path/xxx.sql

• 实例1：利用 pg_dump 备份单个库

#.实例1：确认一下数据库和用户清单
/opt/pgsql/bin/psql -h 127.0.0.1 -p 1921 -U postgres -d postgres -c "select * from pg_database;"
/opt/pgsql/bin/psql -h 127.0.0.1 -p 1921 -U postgres -d postgres -c "select * from pg_shadow;"

#.实例1：使用pg_dump备份单个库(-d指定单个库)，不支持同时备份多个库(只能遍历备份)
/opt/pgsql/bin/pg_dump -h 127.0.0.1 -p 1921 -U postgres -Fp -d db1 -f /opt/dump/db1.sql
/opt/pgsql/bin/pg_dump -h 127.0.0.1 -p 1921 -U postgres -Fp -d db2 -f /opt/dump/db2.sql

#.示例1：使用 -t 备份以 tb2 开头的表
/opt/pgsql/bin/pg_dump -h 127.0.0.1 -p 1921 -U postgres -Fp -t tb2* db2 -f /opt/dump/tb2.sql

• 实例2：恢复 pg_dump 所备份的dump文件，因为dump文件不包含建库及账号的脚本（包含建表），所以导入之前需要先建库及账号

注：设置 --no-owner 只是不导出对象的所有权，比如丢弃 ALTER TABLE public.tb1 OWNER TO postgres; 但仍会保留 GRANT ALL ON TABLE public.tb1 TO user_backup; 所以即便备份过程指定--no-owner，导入之前仍要先建库和账号

#.实例2：先创建实例1的库和账号，否则导入后的所有表都会创建到postgres库下面且账号导入失败
/opt/pgsql/bin/psql -h 127.0.0.1 -p 1922 -U postgres -d postgres

CREATE USER user_backup WITH PASSWORD 'Aa_123456';
CREATE DATABASE db1 WITH OWNER=user_backup ENCODING='UTF-8';
\c db1
GRANT ALL PRIVILEGES ON all tables in schema public TO user_backup;

#.实例2：导入pg_dump所备份的dump文件到新实例(务必先建库及账号再导入)
/opt/pgsql/bin/psql -h 127.0.0.1 -p 1922 -U postgres -d db1 < /opt/dump/db1.sql

#.实例2：确认一下
/opt/pgsql/bin/psql -h 127.0.0.1 -p 1922 -U postgres -d postgres -c "select * from pg_database;"

• 实例2：对于 pg_dump 为 custom二进制或tar类型的备份，需要使用 pg_restore 进行恢复

#.实例1：备份为二进制文件
/opt/pgsql/bin/pg_dump -h 127.0.0.1 -p 1921 -U postgres -Fc -d db1 -f /opt/dump/db1.bin

#.实例2：上面已经在db1库下面创建tb1表，为了测试，这里将tb1表删除，避免恢复报错
/opt/pgsql/bin/psql -h 127.0.0.1 -p 1922 -U postgres -d db1 -c "drop table tb1;"

#.实例2：利用pg_restore恢复二进制备份文件
/opt/pgsql/bin/pg_restore -h 127.0.0.1 -p 1922 -U postgres -d db1 -Fc /opt/dump/db1.bin

场景2：利用 pg_dumpall 进行实例的备份与还原

• pg_dumpall：用于备份整个实例的所有数据，包括用户、角色、数据库、表空间的定义信息等，常用参数如下：

#.默认以COPY命令的形式备份
pg_dumpall -h <host> -p <port> -U <username> > /path/xxx.sql

#.参数 --inserts 以INSERT命令（而非默认的COPY命令）的形式转储数据
pg_dumpall -h <host> -p <port> -U <username> --inserts | gzip > /path/xxx.sql.gz

• 实例1：利用 pg_dumpall 备份整个实例

#.实例1：确认一下数据库和用户清单
/opt/pgsql/bin/psql -h 127.0.0.1 -p 1921 -U postgres -d postgres -c "select * from pg_database;"
/opt/pgsql/bin/psql -h 127.0.0.1 -p 1921 -U postgres -d postgres -c "select * from pg_shadow;"

#.实例1：使用pg_dumpall备份整个实例(去掉-d参数)
/opt/pgsql/bin/pg_dumpall -h 127.0.0.1 -p 1921 -U postgres -f /opt/dump/alldb.sql

#.实例1：以INSERT命令的形式进行压缩备份
/opt/pgsql/bin/pg_dumpall -h 127.0.0.1 -p 1921 -U postgres --inserts | gzip > /opt/dump/alldb.sql.gz

• 实例3：恢复 pg_dumpall 所备份的dump文件，由于dump文件包含了建库及账号的脚本，所以导入之前不需要先建库及账号

#.还原整个实例(去掉-d参数)
/opt/pgsql/bin/psql -h 127.0.0.1 -p 1923 -U postgres < /opt/dump/alldb.sql

场景3：大型实例的备份方案

• 若遇到 pg 实例过大而导致磁盘空间报警甚至不足的情况，可以考虑如下备份方案：

#.1.使用压缩转储：使用pg_dump导出数据到标准输出，然后通过管道将其传递给gzip进行压缩
/opt/pgsql/bin/pg_dump -h 127.0.0.1 -p 1921 -U postgres -Fp -d db1 | gzip > /opt/dump/db1.sql.gz
gunzip -c /opt/dump/db1.sql.gz | /opt/pgsql/bin/psql -h 127.0.0.1 -p 1922 -U postgres -d db1

#.2.使用自定义转储格式：pg_dump支持自定义格式，它会在写入输出文件时进行压缩
/opt/pgsql/bin/pg_dump -h 127.0.0.1 -p 1921 -U postgres -Fc -d db2 -f /opt/dump/db2.bin
/opt/pgsql/bin/pg_restore -h 127.0.0.1 -p 1922 -U postgres -d db2 -Fc /opt/dump/db2.bin

#.3.使用并行转储和恢复：pg_dump支持以目录的格式进行并行转储，可以显著加速转储过程
/opt/pgsql/bin/pg_dump -h 127.0.0.1 -p 1921 -U postgres -Fd -j 3 -d db1 -f /opt/dump/db1.dir
/opt/pgsql/bin/pg_restore -h 127.0.0.1 -p 1922 -U postgres -d db2 -j 3 /opt/dump/db1.dir