一.从SQL Server数据库导入到PostgreSQL，单表的数据近30万，如何实现？

1. 最开始的方案是在SQL Server上生成insert into的sql，再把生成的sql在Postgresql上导入。第一次导入时，没有删除索引，用了近2个小时才导完一张单表。
2. PostgreSQL有一个Copy命令用于大量数据的导入导出，于是就用了Copy来导数据。
 

导入数据过程：
/*1.删除索引*/
drop index UQ_IDX_UNI_ENTERPRISE_001;

/*2.清空表数据，注意该步骤，只有测试在重复部署环境时才执行，生产环境只初始化一次，不需要执行 */
truncate tb_uni_enterprise;

/*3.导入数据*/
COPY tb_uni_enterprise(appid, eid, ename, status, creator, createtime,modifier, modifytime, memo) FROM '/tmp/init/enterprise.sql' DELIMITER '|';

/*4.创建索引*/
create unique index UQ_IDX_UNI_ENTERPRISE_001 on TB_UNI_ENTERPRISE (
appid,
eid
);

使用Copy导入数据，30万数据导入约7秒。

 

第一次使用Copy导入时失败了，提示appid数据太长，原因是把整行数据都当做appid的数据了，Copy导入的文本内容要以\n作为行结束符。

另外PostgreSQL是安装在Linux环境的，在运行Copy时也有几个问题：

1.使用shell连接服务器后，找不到psql命令，原因是PostgreSQL的命令没有加到$PATH中

Linux中在每个用户的$HOME目录下有一个.bash_profile文件，这个文件是每次用户登录的时候都会执行，可以在.bash_profile中把PostgreSQL的命令加到$PATH中，在PostgreSQL的安装目录有一个set_env.sh的文件，文件中有设置环境变量的shell脚步，可以把该脚步的内容拷贝追加到.bash_profile中，也可以直接在.bash_profile中添加运行set_env.sh脚步语句：./PostgreSQL安装目录/set_env.sh。修改.bash_profile后需要重新登录才会生效。

2.执行Copy命令时没有目录权限
在Linux中安装的PostgreSQL都有一个叫postgres的用户，可以用postgres登录再创建目录，这样创建的目录对postgres来说就有读写权限了。
另外一种方法是用root用户创建目录，然后用"chmod 777 目录名"命令为所有用户加上读写权限。

 

备注：
测试过程中用到的PostgreSQL命令有
1.psql -U uni_auth 使用指定用于进入命令行
2.进入命令行后，默认的数据库是postgres，需要使用\c uni_auth_db命令来切换数据库
3.使用\?可以查看在命令行模式下所有的命令以及命令说明。

 

二. copy知识点整理

COPY tablename [ ( column [, ...] ) ] 
FROM { 'filename' | STDIN } [ [ WITH ] [ BINARY ] [ OIDS ] [ DELIMITER [ AS ] 'delimiter' ] [ NULL [ AS ] 'null string' ] [ CSV [ QUOTE [ AS ] 'quote' ] [ ESCAPE [ AS ] 'escape' ] [ FORCE NOT NULL column [, ...] ] 
COPY tablename [ ( column [, ...] ) ]
TO { 'filename' | STDOUT } [ [ WITH ] [ BINARY ] [ OIDS ] [ DELIMITER [ AS ] 'delimiter' ] [ NULL [ AS ] 'null string' ] [ CSV [ QUOTE [ AS ] 'quote' ] [ ESCAPE [ AS ] 'escape' ] [ FORCE QUOTE column [, ...] ]


1.描述

 

COPY 在 PostgreSQL表和标准文件系统文件之间交换数据。 COPY TO 把一个表的所有内容都拷贝到一个文件， 而 COPY FROM 从一个文件里拷贝数据到一个表里（把数据附加到表中已经存在的内容里）。

如果声明了一个字段列表，COPY 将只在文件和表之间拷贝声明的字段的数据。 如果表中有任何不在字段列表里的字段，那么 COPY FROM 将为那些字段插入缺省值。

带文件名的 COPY 指示 PostgreSQL 服务器直接从文件中读写数据。 如果声明了文件名，那么该文件必须为服务器可见，而且文件名必须从服务器的角度声明。如果声明的是STDIN 或 STDOUT，数据通过连接在客户前端和服务器之间流动。


2.参数

tablename

现存表的名字（可以有模式修饰）。

column

可选的待拷贝字段列表。如果没有声明字段列表，那么将使用所有字段。

filename

输入或输出文件的绝对路径名。

STDIN

声明输入是来自客户端应用。

STDOUT

声明输入前往客户端应用。

BINARY

使用二进制格式存储和读取，而不是以文本的方式。 在二进制模式下，不能声明 DELIMITERS，NULL 或者 CSV选项。

OIDS

声明为每行拷贝内部对象标识（OID）。 （如果给那些没有 OID 的表声明了 OIDS 选项，则抛出一个错误。）

delimiter

用于在文件中每行中分隔各个字段的单个字符。 在文本模式下，缺省是水平制表符（tab），在 CSV 模式下是一个逗号。

null string

个代表 NULL 值的字串。在文本模式下缺省是 \N （反斜杠-N）， 在 CSV 模式下是一个没有引号的空值。 如果你不想区分空值和空字串，那么即使在文本模式下可能你也会用一个空字串。

注意: 在使用 COPY FROM 的时候，任何匹配这个字串的字串将被存储为 NULL 值， 所以你应该确保你用的字串和COPY TO相同。

CSV

打开逗号分隔变量（CSV）模式。

quote

声明 CSV 模式里的引号字符。缺省是双引号。

escape

声明在 CSV 模式下应该出现在数据里 QUOTE 字符值前面的字符。 缺省是 QUOTE 值（通常是双引号）。

FORCE QUOTE

在 CSV COPY TO 模式下，强制在每个声明的字段周围对所有非 NULL 值都使用引号包围。 NULL 从不会被引号包围。

FORCE NOT NULL

在 CSV COPY FROM 模式下，把声明的每个字段都当作它们有引号包围来处理， 因此就没有 NULL 值。对于在 CSV 模式下的缺省空字串（''）， 这样导致一个缺失的数值当作一个零长字串输入。


3.注意

COPY 只能用于表，不能用于视图。

BINARY 关键字将强制使用二进制对象而不是文本存储/读取所有数据。 这样做在一定程度上比传统的拷贝命令快，但二进制拷贝文件在不同机器体系间的植性不是很好。

你对任何要COPY TO 出来的数据必须有选取数据的权限，对任何要 COPY FROM 入数据的表必须有插入权限。

COPY 命令里面的文件必须是由服务器直接读或写的文件，而不是由客户端应用读写。 因此，它们必须位于数据库服务器上或者可以为数据库服务器所访问，而不是由客户端做这些事情。 它们必须是PostgreSQL用户（服务器运行的用户 ID）可以访问到并且可读或者可写，而不是客户端。 COPY 到一个命名文件是只允许数据库超级用户进行的，因为它允许读写任意服务器有权限访问的文件。

不要混淆 COPY 和 psql 指令 \copy。 \copy 调用 COPY FROM STDIN 或者 COPY TO STDOUT， 然后把数据抓取/存储到一个 psql 客户端可以访问的文件中。 因此，使用 \copy 的时候，文件访问权限是由客户端而不是服务器端决定的。

我们建议在 COPY 里的文件名字总是使用绝对路径。 在 COPY TO 的时候是由服务器强制进行的， 但是对于 COPY FROM，你的确有从一个声明为相对路径的文件里读取的选择。 该路径将解释为相对于服务器的工作目录（在数据目录里的什么地方），而不是客户端的工作目录。

COPY FROM 会激活所有触发器和检查约束。不过，不会激活规则。

COPY 输入和输出是被 DateStyle 影响的。 为了和其它 PostgreSQL 安装移植，（它们可能用的不是缺省 DateStyle 设置）， 我们应该在使用 COPY 前把 DateStyle 设置为ISO。

COPY 在第一个错误处停下来。这些在 COPY TO中不应该导致问题， 但在 COPY FROM 时目的表会已经接收到早先的行， 这些行将不可见或不可访问，但是仍然会占据磁盘空间。 如果你碰巧是拷贝很大一块数据文件的话， 积累起来，这些东西可能会占据相当大的一部分磁盘空间。你可以调用 VACUUM 来恢复那些磁盘空间。