閱讀使人充實，討論使人敏捷，寫作使人精確。

?遷移工具簡介

今天介紹一個MySQL遷移PostgreSQL的自動化工具py-mysql2pgsql，該工具通過python使用不落地方式，或寫出到文件方式(可選)，直接將MySQL中的表結構轉化到pg，并自動導入數據、自動在pg端創建相關索引。

MySQL遷移PostgreSQL數據類型轉化

目前該工具可將以下38種數據類型自動轉化為PostgreSQL對應的兼容類型，如需其他類型轉化，在配置文件添加即可。

MySQLPostgreSQL

char	character
varchar	character varying
tinytext	text
mediumtext	text
text	text
longtext	text
tinyblob	bytea
mediumblob	bytea
blob	bytea
longblob	bytea
binary	bytea
varbinary	bytea
bit	bit varying
tinyint	smallint
tinyint unsigned	smallint
smallint	smallint
smallint unsigned	integer
mediumint	integer
mediumint unsigned	integer
int	integer
int unsigned	bigint
bigint	bigint
bigint unsigned	numeric
float	real
float unsigned	real
double	double precision
double unsigned	double precision
decimal	numeric
decimal unsigned	numeric
numeric	numeric
numeric unsigned	numeric
date	date
datetime	timestamp without time zone
time	time without time zone
timestamp	timestamp without time zone
year	smallint
enum	character varying (with?check?constraint)
set	ARRAY[]::text[]

?自動化遷移過程

工具運行后，整個過程可以分為三個階段：

l?在PostgreSQL端自動創建表結構；l?在PostgreSQL端自動loading加載數據;l?在PostgreSQL端自動創建索引、約束。?工具安裝及使用例子l?下載源碼安裝包下載地址https://pypi.org/project/py-mysql2pgsql/#descriptionl?解壓安裝1.需解決的依賴包：python需按照這幾個依賴包：termcolor、mysql-python、psycopg2、argparse2.安裝mysql2pgsql[root@dbhost ]# pwd/data/aken/tools/py-mysql2pgsql-0.1.5[root@dbhost ]# python setup.py install3.驗證安裝，查看help幫助[root@dbhost /data/aken/tools/py-mysql2pgsql-0.1.5]# py-mysql2pgsql -husage: py-mysql2pgsql [-h] [-v] [-f FILE] [-V]Tool for migrating/converting data from mysql to postgresql.optional arguments: -h, --help show this help message and exit -v, --verbose Show progress of data migration. -f FILE, --file FILE Location of configuration file (default:mysql2pgsql.yml). If none exists at that path, one will be created for you. -V, --version Print version and exit.https://github.com/philipsoutham/py-mysql2pgsql[root@dbhost /data/aken/tools/py-mysql2pgsql-0.1.5]#?遷移存量數據如下將MySQL中的test.tab_testtext表存量遷移到PostgreSQL中akendb的public下面。l?編輯遷移配置文件vi mysql2pgsql.yml 如下：#source # if a socket is specified we will use that# if tcp is chosen you can use compressionmysql: hostname: 100.66.66.66 port: 15140 socket: username: dbmgr password: 520DBA database: test compress: falsedestination: # if file is given, output goes to file, else postgres. file: postgres: hostname: 100.88.88.88 port: 11005 username: aken password: aken123 database: akendb# 指定遷移的表，默認遷移database下所有表。if only_tables is given, only the listed tables will be converted. leave empty to convert all tables.only_tables:#- table1#- table2 tab_testtext# 指定排除的表。if exclude_tables is given, exclude the listed tables from the conversion.#exclude_tables:#- table3#- table4# 指定是否只導出表結構，ture表示只遷移dll表結構。if supress_data is true, only the schema definition will be exported/migrated, and not the datasupress_data: false# 指定是否只遷移數據，true表示只遷移數據。if supress_ddl is true, only the data will be exported/imported, and not the schemasupress_ddl: false# 表存在是否清空導入，true表示清空再導入。if force_truncate is true, forces a table truncate before table loadingforce_truncate: false# if timezone is true, forces to append/convert to UTC tzinfo mysql datatimezone: false# if index_prefix is given, indexes will be created whith a name prefixed with index_prefixindex_prefix:l?執行數據遷移執行導入數據后，自動執行過程分3個階段：1.自動在PostgreSQL創建表結構；2.自動加載數據(約1w rows/sec)；3.自動在PostgreSQL創建索引。[root@dbhost]#?py-mysql2pgsql?-v?-f?mysql2pgsql.yml?>>>>>>>>>> STARTING <<<<<<<<<<START CREATING TABLES START - CREATING TABLE tab_testtext FINISH - CREATING TABLE tab_testtextDONE CREATING TABLESSTART WRITING TABLE DATA??START??-?WRITING?DATA?TO?tab_testtext FINISH - WRITING DATA TO tab_testtextDONE WRITING TABLE DATASTART CREATING INDEXES AND CONSTRAINTS START - ADDING INDEXES TO tab_testtext FINISH - ADDING INDEXES TO tab_testtext START - ADDING CONSTRAINTS ON tab_testtext FINISH - ADDING CONSTRAINTS ON tab_testtextDONE CREATING INDEXES AND CONSTRAINTS>>>>>>>>>> FINISHED <<<<<<<<<<數據對比驗證l表結構比對

1.MySQL表結構

MySQL [test]> show create table tab_testtext;+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| tab_testtext | CREATE TABLE `tab_testtext` ( `id` int(11) NOT NULL DEFAULT '0', `name` longtext, `owners` longtext, `parent_id` int(11) DEFAULT NULL, `busid` int(11) DEFAULT NULL, `uid` varchar(255) DEFAULT NULL, `level` int(11) DEFAULT NULL, `update_date` datetime NOT NULL, `create_date` datetime NOT NULL, `limit_load` int(11) DEFAULT '65', `children_count` int(11) DEFAULT '0', `limit_low_load` int(11) DEFAULT '30', `history_load` varchar(255) DEFAULT NULL, `status` int(11) DEFAULT '0', `group_id` int(11) DEFAULT '21576', `_alarm_types` varchar(64) DEFAULT NULL, `star_level` int(11) DEFAULT '0', `remark` text, `enable` tinyint(11) NOT NULL DEFAULT '1', UNIQUE KEY `busid` (`busid`), UNIQUE KEY `uid` (`uid`), KEY `core_business_6be37982` (`parent_id`), KEY `group_id` (`group_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1?row?in?set?(0.01?sec)MySQL [test]>2.PostgreSQL表結構akendb=# \d+ tab_testtext Table "public.tab_testtext" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ----------------+-----------------------------+-----------+----------+---------+----------+--------------+------------- id | integer | | not null | 0 | plain | | name | text | | | | extended | | owners | text | | | | extended | | parent_id | integer | | | | plain | | busid | integer | | | | plain | | uid | character varying(255) | | | | extended | | level | integer | | | | plain | | update_date | timestamp without time zone | | not null | | plain | | create_date | timestamp without time zone | | not null | | plain | | limit_load | integer | | | 65 | plain | | children_count | integer | | | 0 | plain | | limit_low_load | integer | | | 30 | plain | | history_load | character varying(255) | | | | extended | | status | integer | | | 0 | plain | | group_id | integer | | | 21576 | plain | | _alarm_types | character varying(64) | | | | extended | | star_level | integer | | | 0 | plain | | remark | text | | | | extended | | enable | smallint | | not null | 1 | plain | | Indexes: "tab_testtext_busid" UNIQUE, btree (busid) "tab_testtext_uid" UNIQUE, btree (uid) "tab_testtext_group_id" btree (group_id) "tab_testtext_parent_id" btree (parent_id)Access method: heapakendb=#l數據抽樣比對

1.MySQL數據

MySQL [test]> select count(*) from tab_testtext;+----------+| count(*) |+----------+| 4919 |+----------+1?row?in?set?(0.00?sec)MySQL [test]> select id,name from tab_testtext limit 3;+------+--------------------------------------+| id | name |+------+--------------------------------------+|?7306?|?[N][測試]????????????????????????||?7307?|?[N][數據遷移]??????????????????????|| 7308 | [客戶端接入][登陸] |+------+--------------------------------------+3 rows in set (0.00 sec)MySQL?[test]>2.PostgreSQL數據akendb=# select count(*) from tab_testtext; count ------- 4919(1?row)akendb=# select id,name from tab_testtext limit 3; id | name ------+--------------------------------------?7306?|?[N][測試]?7307?|?[N][數據遷移]?7308?|?[客戶端接入][登陸](3?rows)akendb=#>>>

參考資料

1.https://github.com/philipsoutham/py-mysql2pgsql

往期推薦

1.PostgreSQL等待事件-鎖等待分析

2.基于PG億級毫秒響應實時推薦系統-解決方案探索

? ? ? ? ? ? ? ? ? ? ? ? ?------讓學習成為一種習慣-Aken

總結

以上是生活随笔為你收集整理的yii 执行指定迁移文件_MySQL迁移PG工具pymysql2pgsql的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。