Postgresql 数据库导入导出 物理VS逻辑 集合

PostgreSQL 数据的导入导出本身并没有特别高的技术要求，属于日常操作，但熟悉导入导出以及选择数据导入导出的方式还是有点思考空间的。怎么导出数据的方式更稳妥，更适应业务的需求。下面就先总结数据导入导出中的数据导出的一部分方式和命令的实例，其中一些也是我在总结中发现的，例如COPY 的方式还可以有加速的方式，有时候觉得简单，和简单是两码事。

PostgreSQL  数据导入导出主要分为两大类

1  逻辑导入导出

2  物理导入导出

1  逻辑导入导出特点：

逻辑导出特点主要有以下几点

1  保证导出数据的一致性

2  在数据库正在被访问的情况下，逻辑数据导出不会影响正在被访问的数据正常的操作，不会阻塞读写操作  （并不指性能部分）

3  可以进行独立的某一个数据库的导出，也可以进行整体INSTANCE 下所有数据库的导出

4  导出的语句可以生成为可以阅读的SQL语句，也可以对生成的数据进行打包，成为tar 格式

5  逻辑导出的数据对PG的恢复的数据库版本没有要求，适合不同版本的PG进行数据的迁移的导入和导出

6  逻辑导出包含整体数据库中的OBJECT 以及表结构等信息，逻辑导出可以单独导出表结构或者数据。

7  逻辑导出中可以包含copy 命令或 逻辑insert 语句

2  物理导入导出特点：

物理导出特点主要有以下几点

1   物理复制是在表和标准文件系统文件之间移动数据，数据移动速度依赖与硬件本身

2   可以将单列数据进行数据迁移

3   数据的迁出目的地必须操作数据库的LINUX账号必须有目的地的写入权限

4   数据导入的过程中程序使用的是标准的数据STDOUT STDIN 模式

5   物理导出仅仅涉及数据的导出

3   物理导入导出和逻辑导入导出适应场景：

1   逻辑导入导出适合表数据量较少的表，并且适应与不同版本PG之间的数据导入导出

2   逻辑导入导出适合修改部分数据的导出，如对导出数据的修改，在讲数据导入，在导入的过程中就将修改后的数据导入

3   逻辑导入导出适合在业务工作期间，对表进行数据的导出或导入，可以最大化避免系统工作中的影响

4   物理导入导出主要应用场景为停机期间的大量数据的导出和导入，导出和导入的速度快，导出导入数据的性能上线为硬件系统的上线。

5   copy 的方式可以通过条件过滤的方式对导出的数据进行选择

6   copy 的方式可以通过binary 模式进一步提高数据导出的速度

命令操作与注意事项

命令：

1  逻辑导出

导出 postgres 数据库表结构以及其他OBJECT 和数据，到指定的 /home/postgres/backup.sql 文件中，其中数据使用copy方式呈现在导出文件中

pg_dump -d postgres -h 192.168.198.100 -p 5432 -U admin > /home/postgres/backup.sql

2  导出postgresql 中POSTGRES数据库中表的数据 到指定的 /home/postgres/backup.sql 文件中，其中数据使用copy方式呈现，其中导出文件中并不包含表结构文件

pg_dump -d postgres -h 192.168.198.100 -p 5432 -U admin -a > /home/postgres/backup.sql

3   导出数据时添加删除语句，如目的地有同名表或OBJECT 将先进行清理后，在创建新表并灌入数据，此方式与 -a 单纯导出数据模式冲突，择一

pg_dump -d postgres -h 192.168.198.100 -p 5432 -U admin  -c > /home/postgres/backup.sql

4  导出数据包含建库命令，命令中包含重新连接数据的命令

 pg_dump -d postgres -h 192.168.198.100 -p 5432 -U admin  -C > /home/postgres/backup.sql

5  导出数据通过目录的方式，并且使用多线程进行导出  使用多线程的方式是，必须使用 Fd 模式  目录模式，会生成多个压缩文件

pg_dump -d postgres -h 192.168.198.100 -p 5432 -U admin  -Fd  -j 5 -f /home/postgres/backup

6  导出数据通过tar包的方式，直接进行流方式压缩， 对比普通方式，压缩方式时间长，根据数据的不同，占用的磁盘空间低

pg_dump -d postgres -h 192.168.198.100 -p 5432 -U admin  -Ft  | gzip > backup.tar.gz

7  导出表结构，以及其他数据库中的object 

pg_dump -d postgres -h 192.168.198.100 -p 5432 -U admin  -s  -f /home/postgres/backup.sql

8 导出特定表和表结构

pg_dump -d postgres -h 192.168.198.100 -p 5432 -U admin  -s -t pgbench_accounts -f /home/postgres/backup.sql

2   物理方式数据导出

1  通过COPY 方式直接导出数据

copy pgbench_accounts to '/home/postgres/backup.data';

导出数据速度 2000万行在 11秒左右 （与整体的系统硬件有关，此硬件为CPU 2CORE  SSD 磁盘 8G memory）

2  通过copy 的方式导出特定的条件的字段记录

copy (select aid,bid from pgbench_accounts where aid < 100) to '/home/postgres/backup.data' ;

3  通过binary 模式来导出数据记录，加速导出速度

copy pgbench_accounts to '/home/postgres/backup.data' with binary;

示例中对比同样的数据量，不同模式下binary 节省时间25%

4   对导出的数据的特定的分隔符进行设定，通过 DELIMITER  设定分隔符，此选项不能使用binary 方式

copy pgbench_accounts to '/home/postgres/backup.data' DELIMITER '|';

5  导出数据使用CSV 格式并且带有页头的模式，其中数据格式分为几类   text ,  csv  , binary  ， text 为默认的导出数据的模式

copy pgbench_accounts to '/home/postgres/backup.data' DELIMITER '|' CSV  header;

6  使用copy 方式导出数据时，进行数据的压缩  （使用psql 的外部命令执行方式标准 stdout 将数据直接压缩生成文件）

psql -q -c "\copy (select * from pgbench_accounts limit 3) to stdout"\ | gzip > backup.data.gz

数据导入

数据的导入也分别根据数据导出的三种形式分别对待

主要的方式有

1  使用psql 命令直接执行导出逻辑SQL文件

2  使用pg_restore 导出 pg_dump 导出的封装格式的数据

3  使用copy from 方式 导入copy to 方式的数据

1  逻辑导入， 根据逻辑导出的数据的不同，数据的导入的方法也是不同的。其中逻辑导出的数据可以通过pg_restore的方式将数据导入进数据库内。

下面我们根据不同的数据导出方式，来去将数据导入回去

1   恢复纯SQL 方式的导出的SQL 逻辑文件 

psql -Uadmin -p 5432 -d postgres < /home/postgres/backup.sql

导出语句

（pg_dump -d postgres -h 192.168.198.100 -p 5432 -U admin > /home/postgres/backup.sql）

下图为导入数据时的CPU 内存 磁盘工作情况

2  导出数据时不包含表结构，数据以copy 模式呈现，导入数据时注意数据库表结构应以建立，数据灌入的方式与COPY无异

psql -Uadmin -p 5432 -d postgres < /home/postgres/backup.sql

3   通过pg_dump 中的 Fd 方式 加并行的方式导出的数据，也可以通过pg_restore 的方式将数据并行的倒回到数据库中

pg_restore -d postgres -e -Fd --jobs=5 /home/postgres/backup

对应的数据导出方式（pg_dump -d postgres -h 192.168.198.100 -p 5432 -U admin  -Fd  -j 5 -f /home/postgres/backup）

此方式的有点为导入数据时可以使用并行的方式将数据导入，速度比其他逻辑读取方式有优势，但需要注意本身的主机的CPU的核心数

-e  参数为导出是如果遇到错误，会停止，默认pg_restore 导出数据是遇到错误是不会停止的

4  通过copy from 来导入纯数据

copy pgbench_accounts from '/home/postgres/backup.data';

2000万数据导入数据库中的速度在1分06秒

5  copy 导入通过binary 方式导出的数据

copy pgbench_accounts from '/home/postgres/backup.data' with binary;

数据导出主要考虑以下几点 

1   导出导入数据的速度，这里以copy 的方式速度为最快，同时通过pg_dump -a 方式导出的数据也为copy方式

2   通过copy 导出数据的方式中，如果涉及的表数量较多，可以同时运行多个表的数据导出，最大化的利用当下的硬件资源

     或通过pg_dump  Fd 模式下的  -j 的模式最大化利用CPU 的多线程的工作模式

3   导入数据时，也可以通过 copy from 的模式同时导入多个数据表，加快导入的速度  或通过pg_restore 模式下的 -j 方式并行利用CPU 加快逻辑数据的导入速度