Postgressql using the Copy command can greatly improve data import speed

Recently in the membership system, where the membership system has a copy of the enterprise information initialized data, need to import from SQL Server database to PostgreSQL, single-table data of nearly 300,000.
The first scenario is to generate SQL for INSERT into on SQL Server, and then import the generated SQL on PostgreSQL. On the first import, the index was not deleted and it took nearly 2 hours to complete a single table.
After finding PostgreSQL on the Internet, there is a copy command for importing and exporting large amounts of data (http://www.2cto.com/database/201309/241346.html), so copy is used to guide the data.
Copy syntax: http://blog.sina.com.cn/s/blog_73094447010158zr.html, the Copy command to import enterprise information is as follows:
COPY tb_uni_enterprise (AppID, Eid, ename, status, creator, Createtime,modifier, Modifytime, Memo) from '/tmp/init/ Enterprise.sql ' DELIMITER ' | ';

Import Data procedure:
/*1. Delete Index */
Drop index uq_idx_uni_enterprise_001;

/*2. Clear the table data, note that this step is only performed when the test is in a recurring deployment environment, the production environment is initialized only once, and no need to execute */
Truncate tb_uni_enterprise;

/*3. Import Data */
COPY tb_uni_enterprise (AppID, Eid, ename, status, creator, Createtime,modifier, Modifytime, Memo) from '/tmp/init/ Enterprise.sql ' DELIMITER ' | ';

/*4. Create an index */
Create unique index uq_idx_uni_enterprise_001 on tb_uni_enterprise (
AppID
Eid
);

With copy import data, 300,000 data is imported for about 7 seconds.

The first time I used copy import failed, prompting AppID data is too long, because the whole row of data as AppID data, copy imported text content to be \ n as the line terminator (http://bbs.chinaunix.net/thread-1830462-1-1.html).
In addition PostgreSQL is installed in the Linux environment, there are several issues when running copy:
1. The Psql command was not found after using the shell to connect to the server because the PostgreSQL command was not added to $path
Linux in each user's $home directory has a. bash_profile file, this file is executed every time the user logs in, you can add the PostgreSQL command to $path in. Bash_profile. In the PostgreSQL installation directory there is a set_env.sh file, the file is set environment variable shell footsteps, you can add the content copy of the footstep to the. Bash_profile, you can also directly added to the. Bash_profile Run set_ env.sh footstep statement:./postgresql installation directory/set_env.sh. A re-login is required after modifying the. Bash_profile to take effect.
2. There is no directory permission when you execute the copy command
PostgreSQL installed in Linux has a user named Postgres, you can log in and then create a directory with Postgres, so that the directory created for Postgres have read and write permissions.
Another way is to create a directory with the root user and then use the "chmod 777 directory Name" command to add read and write permissions to all users.

Note:
The PostgreSQL command used during the test is
1.psql-u Uni_auth Use specified to enter the command line
2. After entering the command line, the default database is Postgres, you need to use the \c uni_auth_db command to switch the database
3. Use \ To view all commands and command descriptions in command-line mode.

Postgressql using the Copy command can greatly improve data import speed