PostgreSQL：pgloader导入导出处理重复数据简单示例

注意：pgloader有两个版本，也就是两个软件都叫这个名，其中一个是使用python写的，也就是下面介绍的这个，而另一个是用lisp写的，功能类似，之前我运行着版本1的代码，读取着版本2的配置文件浪费了不少时间=。=

环境：CentOS6.4、PostgreSQL9.2

软件位置

F:快盘软件pgloaderpgloader-2.3.2.tar.gz   #这个我不知道怎么发附件，按照文件名搜一下应该会有

安装psycopg2

yum  install  python-psycopg2

将pgloader-2.3.2.tar.gz放置到/usr/local路径下

tar -xzvf  pgloader-2.3.2.tar.gz
cd pgloader-2.3.2

创建测试表

CREATE  TABLE  student (
id  integer  PRIMARY KEY,name  TEXT
);

插入测试数据

INSERT  INTO  student  VALUES(1,'Jack');
INSERT  INTO  student  VALUES(2,'Tom');
INSERT  INTO  student  VALUES(3,'Lily');

此时sg8k.student表数据如下

sg8k=#select * from student;
 id | name 
----+-------
  1 | Jack
  2 | Tom
  3 | Lily

创建数据文件/usr/local/student.data测试导入重复数据

3:Moon
4:Lucy
5:Timml

将配置文件放置到/usr/local路径下

[pgsql]
host = localhost      
port = 5432       
base =sg8k
user =postgres
pass =2012dbkey
 
log_file            = /tmp/pgloader.log
log_min_messages    = DEBUG
client_min_messages= WARNING
lc_messages         = C
 
;client_encoding= 'utf-8'
client_encoding= 'latin1'
copy_every      = 5
commit_every    = 5
#copy_delimiter  = %
 
null         = ""
empty_string= " "
 
max_parallel_sections= 4
 
[student]
table     = student
format    = csv
filename  = /usr/local/student.data
field_sep= :
 
columns   = *

运行指令

/usr/local/pgloader-2.3.2
./pgloader.py-vsc /usr/local/pgloader.conf

控制台输出如下

pgloader     INFO    Logger initialized
pgloader     WARNING path entry '/usr/share/python-support/pgloader/reformat' does notexists,ignored
pgloader     INFO    Reformat path is []
pgloader     INFO    Will consider following sections:
pgloader     INFO      student
student      INFO    columns = *,got [('id',1),('name',2)]
student      INFO    closing current database connection
student      INFO    student processing
pgloader     INFO    All threads are started,wait for them to terminate
student      WARNING COPY error,trying to find on which line
student      WARNING COPY data buffer saved in /tmp/student.czbnfS.pgloader
student      WARNING COPY error recovery done (1/2) in 0.148s
student      ERROR   1 errors found into [student] data
student      ERROR   please read /tmp/student.rej.log for errors log
student      ERROR   and /tmp/student.rej for data still to process
student      INFO     2 rows copied in 1 commits took 0.171 seconds
student      ERROR   1 database errors occured
student      INFO    Please VACUUM your database to recover space
student      INFO    closing current database connection
student      INFO    releasing student semaphore
student      INFO    Announce it's over
 
Tablename        |    duration |    size | copy rows |     errors
====================================================================
student           |      0.166s |       - |          2 |          1

重新查看sg8k.student表的数据如下

sg8k=#select * from student;
 id | name 
----+-------
  1 | Jack
  2 | Tom
  3 | Lily
  4 | Lucy
  5 | Timml
(5rows)

可以看到/usr/local/student.data中的重复数据"3:Moon"并没有插入数据库中，仅仅是报了一个errors：1表示遇到重复数据，然后继续插入余后数据，而原生的pg_dump、pg_restore、copy对于包含主键约束的数据导入导出是无能为力的，只会报出duplicate key错误，然后事物回滚

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!