PG逻辑备份

常用命令工具

pg_dumpall：适合导出cluster中所有业务库
pg_dump：适合单个库、schema级、表级导出
copy：适合单表或带条件sql结果导出（可导出为csv或txt格式）

pg_dumpall

低版本pg_dumpall不兼容高版本数据库，pg_dumpall可以转储cluster里的所有数据库（postgres、template0、template1除外）到一个sql文件，通过对cluster里的每个数据库调用pg_dump实现。pg_dumpall会dump出所有数据库公用的全局对象（即角色和表空间）， 而pg_dump不会
pg_dumpall官网

导出整个cluster
pg_dumpall -h localhost -U postgres -f /data/bak/entire_cluster.sql

仅备份角色和表空间定义：
pg_dumpall -h localhost -U postgres --port=5432 -f myglobals.sql --globals-only

仅备份角色定义
pg_dumpall -h localhost -U postgres --port=5432 -f myroles.sql --****roles-only

pg_dump

低版本pg_dumpa不兼容高版本数据库，数据库和用户的创建需要提前在目标库建立。
pg_dump官网

导出指定库（不含create database语句）
pg_dump -h连接串 -U读写用户 库名 > dbname.sql

导出指定库（包含create database语句）
pg_dump -h连接串 -U读写用户 -C 库名 > dbname.sql

仅导出指定库结构
pg_dump -h连接串 -U读写用户 -s 库名 > dbname.sql

导出指定库，结果以自定义压缩格式输出
pg_dump -h连接串 -U读写用户 -F c -b -v 库名 > dbname.backup

导出特定schema 
pg_dump -h连接串 -U读写用户 -n schemaname 库名 > dbname.sql

导出指定库中hr和payroll两个schema中的所有数据，结果以自定义压缩格式输出
pg_dump -h连接串 -U读写用户 -F c -b -v -n hr -n payroll 库名 > hr_payroll.backup 

导出指定库中除public schema以外的所有数据，结果以自定义压缩格式输出
pg_dump -h连接串 -U读写用户 -F c -b -v -N public 库名 > all_sch_except_pub.backup

导出特定的表 
pg_dump -h连接串 -U读写用户 -t mytab 库名 > dbname.sql

导出某个db中所有以“pay”开头的表
pg_dump -h连接串 -U读写用户 -t *.pay* 库名 > dbname.sql

copy

#导出
COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
    TO { 'filename' | PROGRAM 'command' | STDOUT }
    [ [ WITH ] ( option [, ...] ) ]

#导入
COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]

导出txt文件

psql -ddb_name -Uuser_name -W
#执行
COPY 表名 copy TO '/home/postgres/test.txt'
#或者
\copy test_copy to '/home/postgres/test.txt'

导入txt文件

psql -ddb_name -Uuser_name -W

COPY 表名 copy FROM '/home/postgres/test.txt'
#或
\copy test_copy from '/home/postgres/test.txt'

导出csv

\copy (SELECT * FROM tab_name ) to './test.csv' WITH csv header delimiter ',' encoding 'UTF8';

导入csv

ksql -U system  -d 库名 -c "COPY  模式.表名 from '/xxx/xxx2.csv' csv HEADER;"

摘抄地址：

https://blog.51cto.com/u_13631369/6203198