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_pybpsql，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;
