pg_dump实例详解（备份postgresql和greenplum数据库）
⼀、pg_dump的⽤法：
数据库的导⼊导出是最常⽤的功能之⼀，每种数据库都提供有这⽅⾯的⼯具，例如Oracle的exp/imp，Informix的dbexp/dbimp，MySQL的mysqldump，⽽PostgreSQL提供的对应⼯具为pg_dump和pg_restore。
pg_dump是⽤于备份PostgreSQL数据库的⼯具。它可以在数据库正在使⽤的时候进⾏完整⼀致的备份，并不阻塞其它⽤户对数据库的访问。
转储格式可以是⼀个脚本或者归档⽂件。转储脚本的格式是纯⽂本，包含许多SQL命令，这些SQL命令可以⽤于重建该数据库并将之恢复到保存脚本时的状态。可以使⽤ psql从这样的脚本中恢复。它们甚⾄可以⽤于在其它机器甚⾄是其它硬件体系的机器上重建数据库，通过对脚本进⾏⼀些修改，甚⾄可以在其它SQL数据库产品上重建数据库。
归档⽂件格式必须和pg_restore⼀起使⽤重建数据库。它们允许pg_restore对恢复什么东西进⾏选择，甚⾄是在恢复之前对需要恢复的条⽬进⾏重新排序。归档⽂件也是可以跨平台移植的。
D:\Program Files\PowerCmd>pg_dump --help
pg_dump 把⼀个数据库转储为纯⽂本⽂件或者是其它格式.
⽤法:  pg_dump [选项]... [数据库名字]
⼀般选项:
-f, --file=FILENAME        output file or directory name
-F, --format=c|d|t|p        output file format (custom, directory, tar, plain text)
-v, --verbose            详细模式
-Z, --compress=0-9      被压缩格式的压缩级别
--lock-wait-timeout=TIMEOUT 在等待表锁超时后操作失败
--help                      显⽰此帮助信息, 然后退出
--versoin                    输出版本信息, 然后退出
控制输出内容选项:
-
a, --data-only          只转储数据，不包括模式
-b, --blobs              在转储中包括⼤对象
-c, --clean              在重新创建之前，先清除（删除）数据库对象
-C, --create            在转储中包括命令，以便创建数据库
-E, --encoding=ENCODING    转储以ENCODING形式编码的数据
-n, --schema=SCHEMA      只转储指定名称的模式
-N, --exclude-schema=SCHEMA    不转储已命名的模式
-o, --oids              在转储中包括 OID
-O, --no-owner          在明⽂格式中, 忽略恢复对象所属者
-s, --schema-only        只转储模式, 不包括数据
-S, --superuser=NAME    在转储中, 指定的超级⽤户名
-
t, --table=TABLE        只转储指定名称的表
-T, --exclude-table=TABLE      只转储指定名称的表
-x, --no-privileges      不要转储权限 (grant/revoke)
--binary-upgrade        只能由升级⼯具使⽤
--column-inserts          以带有列名的INSERT命令形式转储数据
--disable-dollar-quoting    取消美元 (符号) 引号, 使⽤ SQL 标准引号
--disable-triggers        在只恢复数据的过程中禁⽤触发器
--inserts                以INSERT命令，⽽不是COPY命令的形式转储数据
--no-security-labels        do not dump security label assignments
--no-tablespaces          不转储表空间分配信息
--no-unlogged-table-data    do not dump unlogged table data
-
greenplum数据库-quote-all-identifiers    quote all identifiers, even if not key words
--serializable-deferrable  wait until the dump can run without anomalies
--use-set-session-authorization
使⽤ SESSION AUTHORIZATION 命令代替ALTER OWNER 命令来设置所有权
联接选项:
-h, --host=主机名数据库服务器的主机名或套接字⽬录
-p, --port=端⼝号数据库服务器的端⼝号
-U, --username=名字以指定的数据库⽤户联接
-w, --no-password        永远不提⽰输⼊⼝令
-W, --password          强制⼝令提⽰ (⾃动)
--role=ROLENAME          do SET ROLE before dump
如果没有提供数据库名字, 那么使⽤ PGDATABASE 环境变量的数值.
⼆、pg_dump的使⽤实例
1、创建两个数据库
CREATE DATABASE "TestDb1"
WITH OWNER = "TestRole1"
ENCODING = 'UTF8'
TABLESPACE = "TestTbs1";
CREATE DATABASE "TestDb2"
WITH OWNER = "TestRole1"
ENCODING = 'UTF8'
TABLESPACE = "TestTbs1";
在TestDb1中创建表csm_bill、cfg_public_int_transport插⼊⼏条记录，并创建索引，索引使⽤索引表空间TestTbsIndex。
2、仅迁移数据库结构：
E:\>pg_dump -U TestRole1 -s -f TestDb1.sql TestDb1
⼝令:
-U TestRole1和超级⽤户-U postgres结果完全相同：
E:\>pg_dump -U postgres -s -f TestDb11.sql TestDb1
E:\>psql -U TestRole2 -f TestDb1.sql TestDb2 & 2>&1
⽤户 TestRole2 的⼝令：
导⼊时，使⽤-U TestRole2往往有很多权限不够，要想成功导⼊需要修改相关数据库对象的属主，所以最好使⽤超级⽤户-U postgres：E:\>psql -U postgres -f TestDb1.sql TestDb2 & 2>&1
不转储权限选项：-x
E:\>pg_dump -U postgres -x -s -f TestDb12.sql TestDb1
TestDb12.sql⽐TestDb1.sql少了⼀下⼏⾏：
为了可以多次运⾏TestDb1.sql，可以在⽂件开始加以下两⾏：
drop schema public cascade;
create schema public;
或者使⽤-c选项：
E:\>pg_dump -U postgres -c -x -s -f TestDb13.sql TestDb1
TestDb13.sql⽐TestDb1.sql多以下⼏⾏：
此时，可以多次运⾏：
E:\>psql -U postgres -f TestDb13.sql TestDb2 & 2>&1
但是，如果两个库有不同的表或索引，应该使⽤第⼀种⽅法，因为第⼆种⽅法在不到某些数据库对象时会报错。
3、迁移数据库结构和数据（可以实现数据库的备份与恢复）
数据的复制使⽤copy命令：
E:\>pg_dump -U postgres TestDb1>TestDb14.sql
数据的复制使⽤insert语句：
E:\>pg_dump -U postgres --column-inserts TestDb1>TestDb15.sql
4、把远程linux上PostgreSQL上的cpost数据库结构迁移⾄本地PostgreSQL
（1）在本地建⼀个完全相同的环境
create user "cpost" inherit createdb;
create tablespace "pis_data" owner cpost location 'E:\PostgreSQL/data/pis_data';
create tablespace "pis_index" owner cpost location 'E:\PostgreSQL/data/pis_index';
远程数据库cpost仍使⽤了默认表空间：
CREATE DATABASE cpost
WITH OWNER = cpost
--ENCODING = 'LATIN9'
TABLESPACE = pg_default
--LC_COLLATE = 'C'
--LC_CTYPE = 'C'
CONNECTION LIMIT = -1;
使⽤以上三个参数报错，建成后的数据库如下：
CREATE DATABASE cpost
WITH OWNER = cpost
ENCODING = 'UTF8'
TABLESPACE = pg_default
LC_COLLATE = 'Chinese (Simplified)_People''s Republic of China.936'
LC_CTYPE = 'Chinese (Simplified)_People''s Republic of China.936'
CONNECTION LIMIT = -1;
（2）使⽤pg_dump迁移表结构
使⽤-h选项，使导出的sql⽂件直接存放在本地：
E:\>pg_dump -h 132.10.10.11 -p 1234 -U cpost -x -s -f cpost.sql cpost
E:\>psql -U postgres -f cpost.sql
导⼊成功，但报了⼀个错误：
psql:cpost.sql:22: ERROR:  character 0xe99499 of encoding "UTF8" has no equivalent in "LATIN9"字符集错误，字符集问题详见我的另⼀篇blog：由PostgreSQL的区域与字符集说起
三、使⽤pg_dump和pg_restore实现数据库的备份与恢复
E:\>pg_restore --help
pg_restore 从⼀个归档中恢复⼀个由 pg_dump 创建的 PostgreSQL 数据库.
⽤法:
pg_restore [选项]... [⽂件名]
⼀般选项:
-d, --dbname=名字连接数据库名字
-f, --file=⽂件名输出⽂件名
-F, --format=c|d|t      backup file format (should be automatic)
-l, --list              打印归档⽂件的 TOC 概述
-v, --verbose            详细模式
--help                  显⽰此帮助信息, 然后退出
-
-version                输出版本信息, 然后退出恢复控制选项:
-a, --data-only          只恢复数据, 不包括模式
-c, --clean              在重新创建数据库对象之前需要清除（删除）数据库对象
-C, --create            创建⽬标数据库
-e, --exit-on-error      发⽣错误退出, 默认为继续
-I, --index=名称恢复指定名称的索引
-j, --jobs=NUM          可以执⾏多个任务并⾏进⾏恢复⼯作
-L, --use-list=⽂件名从这个⽂件中使⽤指定的内容表排序输出
-n, --schema=NAME      在这个模式中只恢复对象
-O, --no-owner          忽略恢复对象所属者
-P, --function=名字(参数)  恢复指定名字的函数
-
s, --schema-only        只恢复模式, 不包括数据
-S, --superuser=NAME    使⽤指定的超级⽤户来禁⽤触发器
-t, --table=NAME        恢复指定命字的表
-T, --trigger=NAME      恢复指定命字的触发器
-x, --no-privileges      跳过处理权限的恢复 (grant/revoke)
-1, --single-transaction  作为单个事务恢复
--disable-triggers        在只恢复数据的过程中禁⽤触发器
--no-data-for-failed-tables 没有恢复⽆法创建表的数据
--no-security-labels    do not restore security labels
--no-tablespaces          不恢复表空间的分配信息
--use-set-session-authorization 使⽤ SESSION AUTHORIZATION 命令代替ALTER OWNER命令来设置对象所有权联接选项:
-h, --host=主机名数据库服务器的主机名或套接字⽬录
-p, --port=端⼝号数据库服务器的端⼝号
-U, --username=名字以指定的数据库⽤户联接
-w, --no-password        永远不提⽰输⼊⼝令
-W, --password          强制⼝令提⽰ (⾃动)
--role=ROLENAME          在恢复前执⾏SET ROLE操作
如果没有提供输⼊⽂件名, 则使⽤标准输⼊.
1、使⽤dump格式备份和恢复：
E:\>pg_dump -U postgres -Fc TestDb1 >TestDb1.dump
postgres=# drop database "TestDb2";
DROP DATABASE
postgres=# create database "TestDb2"
postgres-# with owner="TestRole2"
postgres-# tablespace="TestTbs2";
CREATE DATABASE
E:\>pg_restore -U postgres -d TestDb2 TestDb1.dump & 2>&1
2、使⽤tar格式备份和恢复：
E:\>pg_dump -U postgres -Ft TestDb1>TestDb1.tar
postgres=# drop database "TestDb2";
DROP DATABASE
postgres=# create database "TestDb2"
postgres-# with owner="TestRole2"
postgres-# tablespace="TestTbs2";
CREATE DATABASE
E:\>pg_restore -U postgres -d TestDb2 TestDb1.tar & 2>&1

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。