DataX-在Windows上实现postgresql同步数据到mysql

短命女 2023-10-09 20:18 67阅读 0赞

## 场景 ##

DataX-阿里开源离线同步工具在Windows上实现Sqlserver到Mysql全量同步和增量同步:

[DataX-阿里开源离线同步工具在Windows上实现Sqlserver到Mysql全量同步和增量同步\_霸道流氓气质的博客-CSDN博客][DataX-_Windows_Sqlserver_Mysql_-CSDN]

在上面实现sqlserver到mysql的数据同步之后，如果要实现postgresql到mysql数据同步流程一样。

以PostGis中的OGC元数据表中的保存坐标系统信息的spatial\_ref\_sys表为例。

![666731b4178745d58ea6190c347e02b6.png][]

在mysql中新建同样结构的表，将postgresql中这个表的数据同步到mysql的表中。

注：

博客：  
[霸道流氓气质的博客\_CSDN博客-C\#,架构之路,SpringBoot领域博主][CSDN_-C_SpringBoot]

## 实现 ##

1、获取postgresqlreader到mysqlwriter的模板

python datax.py -r postgresqlreader -w mysqlwriter

![b89a83cc956841ea90aad77a31db22b1.png][]

2、复制json模板到postgresql2mysql.json文件

参考配置项说明文档

[https://github.com/alibaba/DataX/blob/master/postgresqlreader/doc/postgresqlreader.md][https_github.com_alibaba_DataX_blob_master_postgresqlreader_doc_postgresqlreader.md]

[DataX/mysqlwriter.md at master · alibaba/DataX · GitHub][DataX_mysqlwriter.md at master _ alibaba_DataX _ GitHub]

修改json文件为自己对应的url和列等信息。

{
        "job": {
            "content": [
                {
                    "reader": {
                        "name": "postgresqlreader",
                        "parameter": {
                            "connection": [
                                {
                                    "jdbcUrl": [
            "jdbc:postgresql://127.0.0.1:5432/数据库名"
            ],
                                    "table": [
            "spatial_ref_sys"
            ]
                                }
                            ],
                            "password": "密码",
                            "username": "postgres",
          "column": [
          "srid",
          "auth_name",
          "auth_srid",
          "srtext",
          "proj4text",
          ]
                        }
                    },
                    "writer": {
                        "name": "mysqlwriter",
                        "parameter": {
                            "column": [
          "srid",
          "auth_name",
          "auth_srid",
          "srtext",
          "proj4text",
          ],
                            "connection": [
                                {
                                    "jdbcUrl": "jdbc:mysql://127.0.0.1:3306/数据库名?useUnicode=true&characterEncoding=gbk",
                                    "table": [
            "spatial_ref_sys"
            ]
                                }
                            ],
                            "password": "密码",
                            "preSql": [
          "delete from spatial_ref_sys"
          ],
                            "session": [],
                            "username": "root",
                            "writeMode": "insert"
                        }
                    }
                }
            ],
            "setting": {
                "speed": {
                    "channel": "5"
                }
            }
        }
    }

执行同步命令

python datax.py ./postgresql2mysql.json

同步成功

![a856eebe231344ff9cedd5a4bf94fc47.png][]

[DataX-_Windows_Sqlserver_Mysql_-CSDN]: https://blog.csdn.net/BADAO_LIUMANG_QIZHI/article/details/130330353
[666731b4178745d58ea6190c347e02b6.png]: https://img-blog.csdnimg.cn/666731b4178745d58ea6190c347e02b6.png
[CSDN_-C_SpringBoot]: https://blog.csdn.net/badao_liumang_qizhi
[b89a83cc956841ea90aad77a31db22b1.png]: https://img-blog.csdnimg.cn/b89a83cc956841ea90aad77a31db22b1.png
[https_github.com_alibaba_DataX_blob_master_postgresqlreader_doc_postgresqlreader.md]: https://github.com/alibaba/DataX/blob/master/postgresqlreader/doc/postgresqlreader.md
[DataX_mysqlwriter.md at master _ alibaba_DataX _ GitHub]: https://github.com/alibaba/DataX/blob/master/mysqlwriter/doc/mysqlwriter.md
[a856eebe231344ff9cedd5a4bf94fc47.png]: https://img-blog.csdnimg.cn/a856eebe231344ff9cedd5a4bf94fc47.png