一、介紹
pgloader是一款PostgreSQL資料遷移工具，最初只用于支持格式化檔案的資料快速匯入到PostgreSQL，pgloader支持跳過無法匯入的出錯資料并進行記錄，因此在源資料檔案有部份錯誤資料的情況下依然可以繼續完成遷移任務，節省遷移排錯后重復匯入的時間損耗，最新的版本中，還支持直接將SQLite、MySQL、MS SQL Server資料庫作為資料源，進行資料的直接匯入，并針對不同資料型別進行自動轉換，甚至還會針對不同資料庫特性完成自動替換，遺憾的是，pgloader無法支持從源端到目標端的增量復制，也就是說無法用于生產環境的割接中，但pgloader的出現，使得PostgreSQL開發人員可以十分方便的進行資料庫遷移轉換，對于在新專案中由于功能及開放性需求要使用PostgreSQL的業務來說，pgloader依然是資料遷移的絕佳利器，
二、作業原理
pgloader是PostgreSQL的一個資料加載工具，使用COPY命令，pgloader v1 是 Tcl 寫的，pgloader v2 是 Python 寫的，pgloader v3 是 Common Lisp 寫的，
三、安裝
下載地址
3.1、安裝依賴包
yum install unzip libsqlite3-dev make curl gawk freetds-dev freetds libzip-dev
# 需要安裝sbcl；且版本>=1.2.5 http://www.sbcl.org/getting.html
wget https://sourceforge.net/projects/sbcl/files/sbcl/1.5.2/sbcl-1.5.2-x86-64-linux-binary.tar.bz2
bzip2 -cd sbcl-1.5.2-x86-64-linux-binary.tar.bz2 | tar xvf -
cd sbcl-1.5.2-x86-64-linux
./install.sh
ln -s /usr/local/bin/sbcl /usr/bin/sbcl
3.2、安裝pgloader
cd /path/to/pgloader
make pgloader
./build/bin/pgloader --help
四、命令語法
LOAD <source-type>
FROM <source-url>
[ HAVING FIELDS <source-level-options> ]
INTO <postgresql-url>
[ TARGET TABLE [ "<schema>" ]."<table name>" ]
[ TARGET COLUMNS <columns-and-options> ] [ WITH <load-options> ] [ SET <postgresql-settings> ][ BEFORE LOAD [ DO <sql statements> | EXECUTE <sql file> ] ... ]
[ AFTER LOAD [ DO <sql statements> | EXECUTE <sql file> ] ... ]
;
五、示例
--https://pgloader.readthedocs.io/en/latest/tutorial/tutorial.html
5.1、pgloader加載csv檔案
csv檔案：
Header, with a ? sign
"2.6.190.56","2.6.190.63","33996344","33996351","GB","United Kingdom"
"3.0.0.0","4.17.135.31","50331648","68257567","US","United States"
"4.17.135.32","4.17.135.63","68257568","68257599","CA","Canada"
"4.17.135.64","4.17.142.255","68257600","68259583","US","United States"
"4.17.143.0","4.17.143.15","68259584","68259599","CA","Canada"
"4.17.143.16","4.18.32.71","68259600","68296775","US","United States"
控制檔案示例
LOAD CSV
FROM '/home/postgres/file.csv' (x, y, a, b, c, d)
INTO postgresql://lottu@ip:5432/lottu?csv (a, b, d, c)
WITH truncate,
skip header = 1,
fields optionally enclosed by '"',
fields escaped by double-quote,
fields terminated by ','
SET client_encoding to 'utf8',
work_mem to '12MB',
standard_conforming_strings to 'on'
BEFORE LOAD DO
$$ drop table if exists csv; $$,
$$ create table csv (
a bigint,
b bigint,
c char(2),
d text
);
$$;
單獨使用copy命令加載csv檔案
lottu=> \copy csv2 from '/home/postgres/file2.csv' with (format csv);
優勢：
- 可以過濾行數
- 可以定制插入列數
- 可以在加載前執行創建表、洗掉索引操作；也可以在加載后執行創建索引，即可擴展
5.2、加載mysql資料庫
連接mysql語法db://user:pass****@host:port/dbname
mysql://[user[:password]@][netloc][:port][/dbname]
控制檔案示例
load database
from mysql://system:li0924@ip:3306/lottu
into postgresql://lottu@ip:5432/lottuWITH include drop, create tables, no truncate,
create indexes, reset sequences, foreign keysSET maintenance_work_mem to '128MB', work_mem to '12MB', search_path to 'lottu'
BEFORE LOAD DO
$$ create schema if not exists lottu; $$;
5.3、加載固定寬度字串
加載檔案內容
01234567892008052011431250firstline
01234562008052115182300left blank-padded
12345678902008052208231560another line
2345609872014092914371500
2345678902014092914371520
pgloader語法
LOAD FIXED
FROM '/home/postgres/fixed.file'
(
a from 0 for 10,
b from 10 for 8,
c from 18 for 8,
d from 26 for 17 [null if blanks, trim right whitespace]
)
INTO postgresql://[email protected]:5432/lottu
TARGET TABLE fixed
(
a, b,
c time using (time-with-no-separator c),
d
) WITH truncate SET work_mem to '14MB',
standard_conforming_strings to 'on'BEFORE LOAD DO
$$ drop table if exists fixed; $$,
$$ create table fixed (
a integer,
b date,
c time,
d text
);
$$;
5.4、加載壓縮檔案
LOAD ARCHIVE
FROM /Users/dim/Downloads/GeoLiteCity-latest.zip
INTO postgresql:///ip4r BEFORE LOAD
DO $$ create extension if not exists ip4r; $$,
$$ create schema if not exists geolite; $$, EXECUTE 'geolite.sql' LOAD CSV
FROM FILENAME MATCHING ~/GeoLiteCity-Location.csv/
WITH ENCODING iso-8859-1
(
locId,
country,
region null if blanks,
city null if blanks,
postalCode null if blanks,
latitude,
longitude,
metroCode null if blanks,
areaCode null if blanks
)
INTO postgresql:///ip4r?geolite.location
(
locid,country,region,city,postalCode,
location point using (format nil "(~a,~a)" longitude latitude),
metroCode,areaCode
)
WITH skip header = 2,
fields optionally enclosed by '"',
fields escaped by double-quote,
fields terminated by ',' AND LOAD CSV
FROM FILENAME MATCHING ~/GeoLiteCity-Blocks.csv/
WITH ENCODING iso-8859-1
(
startIpNum, endIpNum, locId
)
INTO postgresql:///ip4r?geolite.blocks
(
iprange ip4r using (ip-range startIpNum endIpNum),
locId
)
WITH skip header = 2,
fields optionally enclosed by '"',
fields escaped by double-quote,
fields terminated by ',' FINALLY DO
$$ create index blocks_ip4r_idx on geolite.blocks using gist(iprange); $$;
六、遷移
https://pgloader.readthedocs.io/en/latest/ref/pgsql.html#postgresql-database-source-specification-from
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/232415.html
標籤：PostgreSQL