pg 备份恢复（四）—— 逻辑备份（pg_dump与copy导入导出）

常用方法

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

一、 pg_dumpall

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

注意工具版本，低版本pg_dumpall不能用于高版本db

#导出整个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

详细参考   PostgreSQL: Documentation: 15: pg_dumpall

二、 pg_dump

pg_dump 只会导出表及数据、函数等。数据库和用户的创建需要提前在目标库建立。

注意工具版本，低版本pg_dump不能用于高版本db

1. 导出

#导出指定库（不含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连接串 --schema=待导出schema名 --schema-only 库名 > fin_rw.sql

#导出特定schema（包含数据） 
pg_dump -h连接串 --schema=待导出schema名 库名 > fin_rw.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

详细参考   PostgreSQL: Documentation: 15: pg_dump

2、 导入

#postgres用户执行
psql -dmydb -f db.sql 

#或登录数据库后运行脚本
\i db.sql

三、 COPY 与 \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 [, ...] ) ]

• WITH csv：导入csv格式数据
• HEADER：不导入第一行
• encoding：指定编码

1. txt格式

导出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' 

备注：COPY是sql命令，需要superuser权限，性能更好；\copy是元命令，不需要superuser权限

2. csv格式

导出csv文件

把products表导出成csv文件

psql -ddb_name -Uuser_name -W

COPY products TO '/path/to/output.csv' WITH csv;

可以导出指定的列

psql -ddb_name -Uuser_name -W

COPY products (name, price) TO '/path/to/output.csv' WITH csv;

也可以配合查询语句

psql -ddb_name -Uuser_name -W -c "COPY (select * from temp_0524 where id='xxx') TO STDOUT with csv header" > home/postgres/temp_0524.csv
#或者
psql -dplatform -Ubom_rw -W 
COPY (select * from temp_0524 where id='xxx') TO 'home/postgres/temp_0524.csv' with csv header

导入csv文件

跟上面的导出差不多，只是把TO换成FROM

注意导入前最好用ultra edit等工具将文件编码设置为UTF-8，传到linux服务器后再用vi -b检查下有没有乱码，否则非常容易出现奇奇怪怪的报错。

psql -ddb_name -Uuser_name -W

COPY products FROM '/path/to/input.csv' WITH csv;

不导入第一行，指定编码为UTF-8

\copy tmp_3 FROM '/data/tmp_3.csv' WITH csv HEADER encoding 'UTF-8';

关于pg_dump的源码实现分析，可以查看  PgSQL · 源码分析· pg_dump分析

参考

pg_dump与pg_dumpall的用法_lk_db的博客-CSDN博客_pg_dump pg_dumpall

用 PostgreSQL 的 COPY 导入导出 CSV_weixin_34123613的博客-CSDN博客