前言：
MySQL數(shù)據(jù)庫(kù)和postgresql數(shù)據(jù)庫(kù)之間的差異并不多，這里的差異指的是對(duì)SQL語(yǔ)言的支持兩者并不大，但底層的東西差異是非常多的，例如，MySQL的innodb引擎概念，數(shù)據(jù)庫(kù)用戶管理，這些和postgresql相比是完全不同的（MySQL用戶就是用戶，沒有角色，postgresql有用戶，有角色，但差異不大），但，索引例如btree這些兩者又是相同的或者說相通的。
那么，這些差異化的地方導(dǎo)致MySQL在向postgresql數(shù)據(jù)庫(kù)遷移數(shù)據(jù)的時(shí)候會(huì)造成一些困擾，雖然兩者數(shù)據(jù)庫(kù)使用的SQL語(yǔ)言百分之九十是相同的，但就是這剩下的百分之十也是一個(gè)巨大的鴻溝。
普通的方案，MySQLdump或者navicat直接備份MySQL，然后將備份文件導(dǎo)入postgresql需要修改很多地方，最基本的是需要提取insert語(yǔ)句，光這些工作量就非常的大了，如果要遷移的數(shù)據(jù)很多的話，因此，該方案基本上是不能考慮的。
OK，那么，有沒有MySQL數(shù)據(jù)庫(kù)平滑的向postgresql數(shù)據(jù)庫(kù)遷移的方案呢？
答案是有的，版本答案就是pgloader工具，但丑話先說前面，此工具只支持全量遷移并不支持增量遷移，這應(yīng)該是一個(gè)比較遺憾的地方，其它的方面，比如遷移效率（遷移用時(shí)），遷移質(zhì)量（也就是遷移到postgresql的數(shù)據(jù)是否完整，準(zhǔn)確），遷移方式（例如，庫(kù)對(duì)庫(kù)遷移，讀取csv文件遷移，編寫配置文件遷移）這些方面是讓人挑不出理的。
一，
pgloader的部署安裝
下載
pgloader通常的部署方式是三種，第一種是編譯部署，第二種是docker鏡像部署，第三種是二進(jìn)制部署，本文選擇的是二進(jìn)制部署，其它部署方式本文不予討論。
pgloader的官方下載地址：
https://github.com/dimitri/pgloader/releases
離線安裝的地址：
鏈接：https://pan.baidu.com/s/18aN0CvyoXy_o8iP-aqVwGg?pwd=kkey?
提取碼：kkey?
?
建議選擇最新版本3.6.9
?二，
環(huán)境依賴安裝
上述的離線安裝包 ，解壓后，進(jìn)入文件夾，rpm -ivh *?即可。
如果是centos的，確認(rèn)是否有安裝libtool-ltdl，有輸出表示已經(jīng)安裝，將那個(gè)libtool-ltdl-2.4.2-22.h1.eulerosv2r7.x86_64.rpm移除即可。
確認(rèn)是否有安裝libtool-ltdl，有輸出表示已經(jīng)安裝，
[root@centos5 ~]# rpm -qa |grep libtool-ltdl
libtool-ltdl-2.4.2-22.el7_3.x86_64
這里說明一下，兩個(gè)關(guān)鍵依賴，分別是freetds-devel和sbcl-1.4.0，其它rpm包是這兩個(gè)關(guān)鍵依賴的依賴。
三，
部署
已經(jīng)部署過了
[root@centos5 pgloader-bundle-3.6.9]# pwd
/root/pgloader-bundle-3.6.9
[root@centos5 pgloader-bundle-3.6.9]# make pgloader
make: `pgloader' is up to date.
第一次部署的，make?pgloader的輸出是這樣的：
;; loading system "cffi"
;; loading system "cl+ssl"
;; loading system "mssql"
;; loading file #P"/root/pgloader_soft/pgloader-bundle-3.6.2/local-projects/pgloader-3.6.2/src/hooks.lisp"
;; loading system "pgloader"
compressed 0 bytes into 8 at level -1
compressed 32768 bytes into 617 at level -1
compressed 3047424 bytes into 820965 at level -1
compressed 23691264 bytes into 6785671 at level -1
compressed 91127808 bytes into 16513612 at level -1
# that's ugly, but necessary when building on Windows :(
mv bin/pgloader.tmp bin/pgloader環(huán)境變量設(shè)置：
vi /etc/profile
alias pgloader=/root/pgloader-bundle-3.6.9/bin/pgloader
:wq
source /etc/profile驗(yàn)證：
[root@centos5 ~]# pgloader --version
pgloader version "3.6.9"
compiled with SBCL 1.4.0-1.el7
四，
pgloader的初步使用
既然是MySQL遷移數(shù)據(jù)到postgresql，那么，自然需要兩個(gè)服務(wù)器，服務(wù)器1? IP是192.168.123.15，該服務(wù)器部署postgresql-12.5，服務(wù)器2?IP是192.168.123.16，該服務(wù)器部署mariadb
如何部署這兩個(gè)數(shù)據(jù)庫(kù)就不在這里廢話了。
MySQL數(shù)據(jù)庫(kù)有test數(shù)據(jù)庫(kù)，該庫(kù)里有erp表：
MariaDB [(none)]> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| DEPT |
| EMP |
+----------------+
2 rows in set (0.00 sec)
MariaDB [test]> select * from EMP;
+-------+--------+-----------+------+------------+------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL | 10 |
+-------+--------+-----------+------+------------+------+------+--------+
14 rows in set (0.00 sec)
編輯遷移配置文件，在postgresql所在的服務(wù)器上，文件內(nèi)容如下：
vi pg.load.test1
load database
from mysql://root:密碼@192.168.123.16:3306/test
into pgsql://postgres:密碼@192.168.123.15:5432/postgres;開始遷移：
遷移命令和日志文件的內(nèi)容：
[postgres@centos5 ~]$ pgloader --verbose pg.load.test1>> pg.load.test2 2>&1
[postgres@centos5 ~]$ cat pg.load.test2
-bash: pgloader: command not found
[postgres@centos5 ~]$ logout
[root@centos5 ~]# mv /home/postgres/pg.load.test1 ./
[root@centos5 ~]# pgloader --verbose pg.load.test1>> pg.load.test2 2>&1
[root@centos5 ~]# cat pg.load.test
pg.load.test1 pg.load.test2
[root@centos5 ~]# cat pg.load.test2
2023-08-04T08:03:56.023000+08:00 NOTICE Starting pgloader, log system is ready.
2023-08-04T08:03:56.049000+08:00 LOG pgloader version "3.6.9"
2023-08-04T08:03:56.115000+08:00 LOG Migrating from #<MYSQL-CONNECTION mysql://root@192.168.123.16:3306/test {1008D4C553}>
2023-08-04T08:03:56.115000+08:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://postgres@192.168.123.15:5432/postgres {1008D4D933}>
2023-08-04T08:03:56.404000+08:00 ERROR mysql: Failed to connect to mysql at "192.168.123.16" (port 3306) as user "root": MySQL Error [1045]: "Access denied for user 'root'@'192.168.123.15' (using password: YES)"
2023-08-04T08:03:56.404000+08:00 LOG report summary reset
table name errors read imported bytes total time read write
----------------- --------- --------- --------- --------- -------------- --------- ---------
fetch meta data 0 0 0 0.000s
----------------- --------- --------- --------- --------- -------------- --------- ---------
----------------- --------- --------- --------- --------- -------------- --------- ---------
[root@centos5 ~]# vim pg.load.test1
[root@centos5 ~]# pgloader --verbose pg.load.test1>> pg.load.test2 2>&1
[root@centos5 ~]# cat pg.load.test2
2023-08-04T08:03:56.023000+08:00 NOTICE Starting pgloader, log system is ready.
2023-08-04T08:03:56.049000+08:00 LOG pgloader version "3.6.9"
2023-08-04T08:03:56.115000+08:00 LOG Migrating from #<MYSQL-CONNECTION mysql://root@192.168.123.16:3306/test {1008D4C553}>
2023-08-04T08:03:56.115000+08:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://postgres@192.168.123.15:5432/postgres {1008D4D933}>
2023-08-04T08:03:56.404000+08:00 ERROR mysql: Failed to connect to mysql at "192.168.123.16" (port 3306) as user "root": MySQL Error [1045]: "Access denied for user 'root'@'192.168.123.15' (using password: YES)"
2023-08-04T08:03:56.404000+08:00 LOG report summary reset
table name errors read imported bytes total time read write
----------------- --------- --------- --------- --------- -------------- --------- ---------
fetch meta data 0 0 0 0.000s
----------------- --------- --------- --------- --------- -------------- --------- ---------
----------------- --------- --------- --------- --------- -------------- --------- ---------
2023-08-04T08:05:15.005000+08:00 NOTICE Starting pgloader, log system is ready.
2023-08-04T08:05:15.015000+08:00 LOG pgloader version "3.6.9"
2023-08-04T08:05:15.048000+08:00 LOG Migrating from #<MYSQL-CONNECTION mysql://root@192.168.123.16:3306/test {1008DBCD63}>
2023-08-04T08:05:15.048000+08:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://postgres@192.168.123.15:5432/postgres {1008DBE1E3}>
2023-08-04T08:05:15.138000+08:00 NOTICE Prepare PostgreSQL database.
2023-08-04T08:05:15.194000+08:00 NOTICE Processing tables in this order: test.emp: 14 rows, test.dept: 4 rows
2023-08-04T08:05:15.208000+08:00 NOTICE COPY test.emp with 14 rows estimated [2/4]
2023-08-04T08:05:15.227000+08:00 NOTICE COPY test.dept with 4 rows estimated [1/4]
2023-08-04T08:05:15.241000+08:00 NOTICE DONE copying test.dept in 0.000s
2023-08-04T08:05:15.241000+08:00 NOTICE DONE copying test.emp in 0.000s
2023-08-04T08:05:15.253000+08:00 NOTICE CREATE UNIQUE INDEX idx_16388_primary ON test.emp (empno);
2023-08-04T08:05:15.268000+08:00 NOTICE Completing PostgreSQL database.
2023-08-04T08:05:15.268000+08:00 NOTICE Reset sequences
2023-08-04T08:05:15.282000+08:00 NOTICE ALTER TABLE test.emp ADD PRIMARY KEY USING INDEX idx_16388_primary;
2023-08-04T08:05:15.284000+08:00 NOTICE ALTER DATABASE "postgres" SET search_path TO public, test;
2023-08-04T08:05:15.286000+08:00 LOG report summary reset
table name errors read imported bytes total time read write
----------------------- --------- --------- --------- --------- -------------- --------- ---------
fetch meta data 0 3 3 0.045s
Create Schemas 0 0 0 0.001s
Create SQL Types 0 0 0 0.002s
Create tables 0 4 4 0.017s
Set Table OIDs 0 2 2 0.006s
----------------------- --------- --------- --------- --------- -------------- --------- ---------
test.emp 0 14 14 0.6 kB 0.033s 0.023s
test.dept 0 4 4 0.1 kB 0.009s 0.005s
----------------------- --------- --------- --------- --------- -------------- --------- ---------
COPY Threads Completion 0 4 4 0.022s
Index Build Completion 0 1 1 0.006s
Create Indexes 0 1 1 0.002s
Reset Sequences 0 0 0 0.010s
Primary Keys 0 1 1 0.001s
Create Foreign Keys 0 0 0 0.000s
Create Triggers 0 0 0 0.000s
Set Search Path 0 1 1 0.000s
Install Comments 0 0 0 0.000s
----------------------- --------- --------- --------- --------- -------------- --------- ---------
Total import time ? 18 18 0.7 kB 0.041s
在postgresql數(shù)據(jù)庫(kù)上查看是否遷移成功：
可以看到，完美遷移了
文章來源：http://www.zghlxwxcb.cn/news/detail-629989.html
?文章來源地址http://www.zghlxwxcb.cn/news/detail-629989.html
到了這里，關(guān)于postgresql|數(shù)據(jù)庫(kù)|MySQL數(shù)據(jù)庫(kù)向postgresql數(shù)據(jù)庫(kù)遷移的工具pgloader的部署和初步使用的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
