laravel-mysql2pgsql

发表于2020-09-16更新于2020-09-16

Laravel mysql to pgsql

laravel 项目直接修改配置文件即可切换 driver

  • php 开启 pdo_pgsql.so extension php.ini
    • 下载源码-解压-CD…
    • phpize
    • 执行 ./configuration with-php-config=php-config
    • make && make install
    • vim php.ini extensions pdo_pgsql
  • 时区问题
    • 使用 工具从 mysql 导入到 pgsql 的数据存在时区 例如: 2020-08-19 19:00:12+08
    • 需要修改所有 Model 的 dateFormat
    • protected $dateFormat = 'Y-m-d H:i:sP';
    • model->save(): 自动更新时间逻辑 freshTimestamp()
    • 数据查询渲染 serializeDate()
    • Carbon 类的使用 && Datetime
    • 扩展php中的其他时区格式: e/O/P/T/Z 等含义
    • 相关代码 
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      32
      33
      34
      35
      36
      37
      38
      39
      40
      41
      42
      43
      44
      45
      46
      47
      48
      49
      50
      51
      52
      53
      54
      55
      class BaseModel extends Model
      {

          public $timestamps = true;
          protected $dateFormat = "Y-m-d H:i:sP";
          protected $dates = ['deleted_at',"updated_at","created_at"];
          /**
           * 获取当前时间
           */
          public function freshTimestamp()
          {
              return date($this->dateFormat);
          }

          /**
           * 为数组 / JSON 序列化准备日期。
           *
           * @param DateTimeInterface $date
           * @return string
           */
          protected function serializeDate(DateTimeInterface $date)
          {
              // 默认时区
              // $timezone = new DateTimeZone(date_default_timezone_get());
              // $val->setTimezone($timezone);
              // return $val->format('Y-m-d H:i:s');
              return $date->format('Y-m-d H:i:s');
          }



      // - **走过的弯路**
      // - 自定义 `Model` 的 `asDatetime()` / `get-DATEFIELD-Attribute()`

      //    protected function getCreatedAtAttribute($value){
      //        return  "2020-04-15 08:30:04+08:00";
      //    }
      //    protected function getUpdatedAtAttribute($value){
      //        return "2020-04-15 08:30:04+08:00";
      //    }
      //    protected function asDateTime($value)
      //    {
      //        $ts = explode("+", $value);
      //        var_dump($ts);
      //        if (count($ts) > 1) {
      //            return \Carbon\Carbon::rawCreateFromFormat('Y-m-d H:i:sP', "2020-04-15 08:30:04+08:00");
      //        }
      //        return \Carbon\Carbon::rawCreateFromFormat('Y-m-d H:i:sP', "2020-04-15 08:30:04+08:00");
      //    }
      //    public function toArray()
      //    {
      //        return [];
      //    }

      }

操作方案: 综合考虑后使用两步走的方案

  1. 先转换 dbschame.sql :使用 py-mysql2pgsql 工具包

    1
    2
    3
    oomcc:py-mysql2pgsql oom$ py-mysql2pgsql -h
    https://github.com/philipsoutham/py-mysql2pgsql
    oomcc:py-mysql2pgsql oom$ py-mysql2pgsql -f mysql2pgsql.yml

    mysql2pgsql.yml 文件:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    # a socket connection will be selected if a 'socket' is specified
    # also 'localhost' is a special 'hostname' for MySQL that overrides the 'port' option
    # and forces it to use a local socket connection
    # if tcp is chosen, you can use compression

    mysql:
    hostname: 127.0.0.1
    port: 33036
    username: root
    password: 123456
    database: eerrrr
    compress: false
    destination:
    # if file is given, output goes to file, else postgres
    file: ./td.sql
    postgres:
    hostname: localhost
    port: 5432
    username: mysql2psql
    password: 
    database: mysql2psql_test

    # if tables is given, only the listed tables will be converted.  leave empty to convert all tables.
    #only_tables:
    #- table1
    #- table2
    # if exclude_tables is given, exclude the listed tables from the conversion.
    #exclude_tables:
    #- table3
    #- table4

    # if supress_data is true, only the schema definition will be exported/migrated, and not the data
    supress_data: true

    # if supress_ddl is true, only the data will be exported/imported, and not the schema
    supress_ddl: false

    # if force_truncate is true, forces a table truncate before table loading
    force_truncate: false

    # if timezone is true, forces to append/convert to UTC tzinfo mysql data
    timezone: false

    # if index_prefix is given, indexes will be created whith a name prefixed with index_prefix
    index_pre**fix:** idx_

  1. 再导入数据: 使用 pgloader/navicate:

    a): navicate -> 工具-> 数据传输: 关闭设置中的 DDL相关选项

    b): pgloader: 自定义传输模版 pg.load

    pg.load 文件:

    1
    2
    3
    4
    5
    6
    7
    8
    LOAD DATABASE
        FROM mysql://www:wwww@12wwww1:33036/aaa
        INTO pgsql://www:wwww@10wwww87:31139/ffff

        WITH include drop, create tables, create indexes, workers = 8, concurrency = 1

    ALTER SCHEMA 'wwwww' RENAME TO 'public'
    ;

    执行日志:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    oomcc:py-mysql2pgsql oom$ vim pg.load 
    oomcc:py-mysql2pgsql oom$ pgloader pg.load
    2020-09-15T11:31:53.011646+01:00 LOG pgloader version "3.6.2"
    2020-09-15T11:31:53.013707+01:00 LOG Data errors in '/private/tmp/pgloader/'
    2020-09-15T11:31:53.013761+01:00 LOG Parsing commands from file #P"/Users/oom/Desktop/py-mysql2pgsql/pg.load"
    2020-09-15T11:31:53.258908+01:00 LOG Migrating from #<MYSQL-CONNECTION mysql://[email protected]:33036/dns_cache_new {1005ABE6E3}>
    2020-09-15T11:31:53.259212+01:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://[email protected]:31139/cache_ms_s {1005ABF393}>
    2020-09-15T11:32:37.324643+01:00 LOG report summary reset
                    table name     errors       rows      bytes      total time
    -----------------------------  ---------  ---------  ---------  --------------
                 fetch meta data          0         60                     0.406s
                  Create Schemas          0          0                     0.029s
                Create SQL Types          0          0                     0.034s
                   Create tables          0         48                     2.472s
                  Set Table OIDs          0         24                     0.024s
    -----------------------------  ---------  ---------  ---------  --------------
        public.admin_permissions          0         10     0.8 kB          0.362s
      public.admin_operation_log          0      27832     3.8 MB          2.596s
         public.admin_role_users          0          3     0.0 kB          0.569s
               ... ...
    -----------------------------  ---------  ---------  ---------  --------------
         COPY Threads Completion          0          8                    31.225s
                  Create Indexes          0         36                    35.220s
          Index Build Completion          0         36                     2.508s
                 Reset Sequences          0         22                     0.568s
                    Primary Keys          0         24                     1.041s
             Create Foreign Keys          0          0                     0.000s
                 Create Triggers          0         20                     0.997s
                Install Comments          0         90                     4.185s
    -----------------------------  ---------  ---------  ---------  --------------
               Total import time          ✓     762609   130.7 MB       1m15.744s
    oomcc:py-mysql2pgsql oom$

Error and fix:

  1. message: “SQLSTATE[23502]: Not null violation: 7 ERROR: null value in column "id" violates not-null constraint\nDETAIL: Failing row contains (null, 12, 57, null, null, null, null). (SQL: insert into "dc_view_recursion" ("device_id", "view_id") values (57, 12), (90, 12), (91, 12), (92, 12))”

    fix: 转换建表语句 py-mysql2pgsql

  2. 解决mac _mysql.c:44:10: fatal error: ‘my_config.h‘ file not found

    fix: /usr/local/include/mysql 下有个 mysql.h 文件，这个就应该是我们要找的my_config.h 文件了吧，cp mysql.h my_config.h ，再次 pip install MySQL-python

  3. ld: library not found for -lssl

    fix: env LDFLAGS="-I/usr/local/opt/openssl/include -L/usr/local/opt/openssl/lib" pip install mysql-python | psycopg2

  1. f"Unable to find 'pg_config' file in '{self.pg_config_exe}'")

  1. ERROR: Command errored out with exit status 1: python setup.py egg_info Check the logs for full command output.

    fix: pip install -U setuptools

  2. UnicodeEncodeError: ‘latin-1’ codec can’t encode characters in position 0-6: ordinal not in range(256)

    fix: ⬇️

  3. UnicodeDecodeError: ‘ascii’ codec can’t decode byte 0xe7 in position 1: ordinal not in range(128)

    fix: 注销第三方库的表注释生成代码:/Library/Python/2.7/site-packages/py_mysql2pgsql-0.1.6-py2.7.egg/mysql2pgsql/lib/postgres_writer.py:Line 144-148

  4. yaml.load(input) Deprecation Warning!

    fix:. 手动修改 py-mysql2pgsql 源码,添加Loader:

    self.options = load(open(config_file_path),Loader=FullLoader)

    /Library/Python/2.7/site-packages/py_mysql2pgsql-0.1.6-py2.7.egg/mysql2pgsql/lib/config.py:Line 18

其他

  • pgloader : 存在自增索引初始值不对的问题