Postgresql pg_dump&pg_restore用法

PostgreSQL提供的一个工具pg_dump,逻辑导出数据,生成sql文件或其他格式文件,pg_dump是一个客户端工具,可以远程或本地导出逻辑数据,恢复数据至导出时间点。
pg_dump 一次只转储一个数据库, 并且不会转储有关角色或表空间的信息 (因为那些是群集范围而不是每个数据库)。为支持方便地转储数据库群集的全部内容, 提供了 pg_dumpall 程序。pg_dumpall 在给定的群集中备份每个数据库, 并保留群集范围内的数据, 如角色和表空间定义。
PostgreSQL提供的一个工具pg_restore用来导入数据

1 具体用法参考help

$ pg_dump --help
pg_dump dumps a database as a text file or to other formats.
Usage:
  pg_dump [OPTION]... [DBNAME]
General options:
  -f, --file=FILENAME          output file or directory name
  -F, --format=c|d|t|p         output file format (custom, directory, tar,
                               plain text (default))
......
$ pg_restore --help
pg_restore restores a PostgreSQL database from an archive created by pg_dump.
Usage:
  pg_restore [OPTION]... [FILE]
General options:
  -d, --dbname=NAME        connect to database name
  -f, --file=FILENAME      output file name
  -F, --format=c|d|t       backup file format (should be automatic)
  -l, --list               print summarized TOC of the archive
  -v, --verbose            verbose mode
  -V, --version            output version information, then exit
  -?, --help               show this help, then exit
......

2 导出

操作系统平面文件格式
$ pg_dump -h 192.168.6.16 -U highgo -p 5433 -Fp -t test_dump > /tmp/test_dump1.dmp highgo
自定义格式
$ pg_dump -h 192.168.6.16 -U highgo -p 5433 -Fc -t test_dump > /tmp/test_dump2.dmp highgo
tar包格式
$ pg_dump -h 192.168.6.16 -U highgo -p 5433 -Ft -t test_dump > /tmp/test_dump3.dmp highgo
自定义、0级压缩格式
$ pg_dump -h 192.168.6.16 -U highgo -p 5433 -Fc -Z 0 -t test_dump > /tmp/test_dump4.dmp highgo
自定义、9级压缩格式
$ pg_dump -h 192.168.6.16 -U highgo -p 5433 -Fc -Z 9 -t test_dump > /tmp/test_dump5.dmp highgo
显示信息内容
$ pg_dump -h 192.168.6.16 -U highgo -p 5433 -Fc -v -t test_dump > /tmp/test_dump6.dmp highgo

验证不同格式的文件

# ll
-rw-rw-r-- 1 pg   pg   379738 Nov  1 15:05 test_dump1.dmp
-rw-rw-r-- 1 pg   pg   217142 Nov  1 15:05 test_dump2.dmp
-rw-rw-r-- 1 pg   pg   384512 Nov  1 15:05 test_dump3.dmp
-rw-rw-r-- 1 pg   pg   429852 Nov  1 15:08 test_dump4.dmp
-rw-rw-r-- 1 pg   pg   217142 Nov  1 15:07 test_dump5.dmp
压缩格式的文件明显较小

不同格式的文件
# file test_dump1.dmp
test_dump1.dmp: ASCII text
# file test_dump2.dmp
test_dump2.dmp: PostgreSQL custom database dump - v1.13-0
# file test_dump3.dmp
test_dump3.dmp: POSIX tar archive
# file test_dump4.dmp
test_dump4.dmp: PostgreSQL custom database dump - v1.13-0
注意1:默认是-Fp格式(操作系统平面文件)
注意2:并行导出只支持-Fd 目录格式

$ pg_dump -h 192.168.6.16 -U highgo -p 5433 -Fc -v -Z 1 -t test_dump > /tmp/test_dump3.dmp highgo
pg_dump: parallel backup only supported by the directory format

3 导入

导入前创建table所需的role
CREATE ROLE highgo;
ALTER ROLE highgo WITH NOSUPERUSER INHERIT LOGIN NOCREATEROLE NOCREATEDB NOREPLICATION PASSWORD 'highgo@123';

pg_restore -h 127.0.0.1 -d postgres -p 5432 -v /tmp/highgo_table.dmp

case:

create user highgo with password 'Highgo@123';
create schema highgo AUTHORIZATION highgo;
create table test_dump (id int,name text,tim timestamp);
insert into test_dump select generate_series(1,100),md5(random()::text),clock_timestamp();

postgres=>  select schemaname,tablename,tableowner from pg_tables where tablename='test_dump';
 schemaname | tablename | tableowner 
------------+-----------+------------
 test       | test_dump | test
 highgo     | test_dump | highgo
(2 rows)


1
pg_dump -h 127.0.0.1 -U highgo -p 5432 -Fc -v -t test_dump > /tmp/highgo_table.dmp postgres
drop table highgo_table;


2 
CREATE ROLE highgo;
ALTER ROLE highgo WITH NOSUPERUSER INHERIT LOGIN NOCREATEROLE NOCREATEDB NOREPLICATION PASSWORD 'highgo@123';
\c highgo highgo

[pg@pg tmp]$ pg_restore -h 127.0.0.1  -d postgres -p 5432  -v /tmp/highgo_table.dmp
pg_restore: connecting to database for restore
pg_restore: creating TABLE "highgo.test_dump"
pg_restore: processing data for table "highgo.test_dump"

导入失败情况
$ pg_restore -h 127.0.0.1 -d postgres -p 5432 -U pg  -v /tmp/test_dump1.dmp
pg_restore: [archiver] input file appears to be a text format dump. Please use psql.
注:pg_resotre仅支持Fc/Ft格式的导出文件,Fp格式的文件是sql脚本,需要使用psql工具导入脚本数据
如:
postgres=# i /tmp/test_dump1.dmp

上一篇:Psqlgresql Time function时间函数


下一篇:Postgresql服务器配置-设置参数