postgresql 9.1 下的 pg_dump 的初步研究

20170629 本次操作是完成pgsql的逻辑备份 pg_dump

安装debian 8.2

省略

安装postgresql

省略

备份前准备工作

创建用户

postgres=# create user usr_pyb with password 'rootroot';
CREATE ROLE

查看用户
postgres=# \du+
                             角色列表
  角色名称  |               属性                | 成员属于 | 描述 
------------+-----------------------------------+----------+------
 postgres   | 超级用户, 建立角色, 建立 DB, 复制    | {}       | 
 replicator | 复制                              | {}       | 
 usr_pyb    |                                  | {}       | 


创建数据库
postgres=# create database db_pyb with owner=usr_pyb;
CREATE DATABASE

查看数据库
postgres=# \l
                                     资料库列表
   名称    |  拥有者  | 字元编码 |  校对规则   |    Ctype    |       存取权限        
-----------+----------+----------+-------------+-------------+-----------------------
 db_pyb    | usr_pyb  | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 行记录)

以 usr_pyb 用户登录 db_pyb 数据库
$ psql -h 127.0.0.1 -U usr_pyb -d db_pyb

先查看下连接情况

db_pyb=> \x
扩展显示已打开。
db_pyb=> select * from pg_stat_activity;
-[ RECORD 1 ]----+--------------------------------
datid            | 24649
datname          | db_pyb
procpid          | 5513
usesysid         | 24645
usename          | usr_pyb
application_name | psql
client_addr      | 127.0.0.1
client_hostname  | 
client_port      | 53029
backend_start    | 2017-06-28 22:29:11.271483+08
xact_start       | 2017-06-28 22:29:47.887419+08
query_start      | 2017-06-28 22:29:47.887419+08
waiting          | f
current_query    | select * from pg_stat_activity;

创建表
db_pyb=> create table test_1 (c1 bigint,c2 text);
CREATE TABLE


插入100W条数据
db_pyb=> insert into test_1(c1,c2) select generate_series(1,1000000), md5(random()::text);
INSERT 0 1000000

创建索引
create index idx_test_1_x1 on test_1(c1); 
create index idx_test_1_x2 on test_1(c2);

或者用下面的语句创建索引
CREATE INDEX idx_test_1_x1 ON test_1 USING btree (c1);
CREATE INDEX idx_test_1_x2 ON test_1 USING btree (c2);

查看对象大小
select pg_size_pretty(pg_relation_size('test_1')) ,
       pg_size_pretty(pg_indexes_size('test_1')),
       pg_size_pretty(pg_total_relation_size('test_1')); 

 pg_size_pretty | pg_size_pretty | pg_size_pretty 
----------------+----------------+----------------
 73 MB          | 78 MB          | 151 MB
(1 行记录)

开始备份

---

纯文本 备份某个数据库
pg_dump -h 127.0.0.1 -U postgres db_pyb > /mnt/pg_dump_test/20170629_1341_db_pyb.sql
pg_dump -h 127.0.0.1 -U postgres db_pyb -f /mnt/pg_dump_test/20170629_1341_db_pyb.sql

纯文本 恢复某个数据库
psql -h 127.0.0.1 -U postgres -d db_pyb < /mnt/pg_dump_test/20170629_1341_db_pyb.sql
psql -h 127.0.0.1 -U postgres -d db_pyb -f /mnt/pg_dump_test/20170629_1341_db_pyb.sql

---

纯文本 压缩 备份
pg_dump -h 127.0.0.1 -U postgres db_pyb | gzip > /mnt/pg_dump_test/20170629_1341_db_pyb.gz

纯文本 压缩 恢复
gunzip -c /mnt/pg_dump_test/20170629_1341_db_pyb.gz | psql -h 127.0.0.1 -U postgres -d db_pyb

---

使用 pg_dump -F 备份
-F format
–format=format
选择输出的格式。format可以是下列之一：
p 输出纯文本SQL脚本文件（缺省）
t 输出适合输入到 pg_restore 里的tar归档文件。 使用这个归档允许在恢复数据库时重新排序和/或把表结构排除在外。
同时也可能可以在恢复的时候限制对哪些数据进行恢复。
c 输出适于给 pg_restore 用的客户化归档。 这是最灵活的格式，它允许对装载的数据和纲要元素进行重新排列。
这个格式缺省的时候是压缩的。

-F, –format=c|d|t|p output file format (custom, directory, tar, plain text)

------------
custom 备份
pg_dump -h 127.0.0.1 -U postgres db_pyb -Fc -f /mnt/pg_dump_test/20170629_1341_db_pyb.cus

custom 恢复
pg_restore -h 127.0.0.1 -U postgres -d db_pyb -v /mnt/pg_dump_test/20170629_1341_db_pyb.cus 

------------
directory 备份
pg_dump -h 127.0.0.1 -U postgres db_pyb -Fd -f /mnt/pg_dump_test/20170629_1341_db_pyb

directory 恢复
pg_restore -h 127.0.0.1 -U postgres -d db_pyb -v /mnt/pg_dump_test/20170629_1341_db_pyb 

------------
tar 备份
pg_dump -h 127.0.0.1 -U postgres db_pyb -Ft -f /mnt/pg_dump_test/20170629_1341_db_pyb.tar

tar 恢复
pg_restore -h 127.0.0.1 -U postgres -d db_pyb -v /mnt/pg_dump_test/20170629_1341_db_pyb.tar 

---

备份指定表结构和内容 -t test_1

pg_dump -h 127.0.0.1 -U postgres -t test_1 db_pyb -f /mnt/pg_dump_test/20170629_1341_db_pyb

备份指定表结构 -s -t test_1

pg_dump -h 127.0.0.1 -U postgres -s -t test_1 db_pyb -f /mnt/pg_dump_test/20170629_1341_db_pyb

备份指定表数据 -a -t test_1

pg_dump -h 127.0.0.1 -U postgres -a -t test_1 db_pyb -f /mnt/pg_dump_test/20170629_1341_db_pyb

psql，pg_restore 都可以用来恢复，只是有些导出格式必须用pg_restore

以下是pg_dump 导出的 纯文本文件的一些信息，记录一下
–备份指定数据库的指定对象

SET statement_timeout = 0;
SET client_encoding = ‘UTF8’;
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

SET search_path = public, pg_catalog;
SET default_tablespace = ”;
SET default_with_oids = false;