PG迁移

从MySQL到PG

OAD DATABASE
     FROM mysql://root@127.0.0.1:3306/v2
     INTO postgresql://postgres@10.10.xxx.xxx:5432/data

 WITH 
     include drop,
     create tables,
     create indexes,
     reset sequences,
     foreign keys,
     batch rows = 1000,
     workers = 4,
     concurrency = 1,                    -- 稍微提升效率，Postgres足以应对
     prefetch rows = 1000,
     on error stop


CAST 
    -- 布尔类型：去掉长度修饰符
    type tinyint when (= 1 precision) to boolean drop typemod,
    type tinyint to smallint,
    
    -- 整型
    type smallint to smallint,
    type int to int,
    type bigint to bigint,
    
    -- 浮点
    type float to float8,
    type double to float8,
    
    -- 日期时间：去掉精度修饰符
    type datetime to timestamptz drop typemod using zero-dates-to-null,
    type timestamp to timestamptz drop typemod using zero-dates-to-null,
    type date to date,
    type year to integer,
    
    -- 字符串
    type enum to text,
    type set to text,
    type json to jsonb,
    type varchar to varchar keep typemod,
    type char to char keep typemod,
    type longtext to text,
    type mediumtext to text,
    type text to text,
    
    -- 二进制
    type longblob to bytea,
    type mediumblob to bytea,
    type tinyblob to bytea,
    type blob to bytea

    ALTER schema 'v2' rename to 'public';

从SQLITE到PG

#!/bin/bash

# 1. 定义你的数据库信息（在这里直接写原始密码，不需要转义）
DB_USER=""
DB_PASS=""
DB_HOST=""
DB_PORT=""
DB_NAME=""

# 2. 构造连接字符串
PG_URL="postgresql://${DB_USER}:${DB_PASS}@${DB_HOST}:${DB_PORT}/${DB_NAME}"

# 3. 动态生成配置并传给 pgloader
pgloader - <<EOF
LOAD DATABASE
     FROM sqlite:///migrate/sqlite/numbers.db
     INTO $PG_URL

 WITH include drop, create tables, create indexes, reset sequences

 CAST type integer to bigint,
      type real to double precision,
      type numeric to numeric

 ALTER SCHEMA 'main' RENAME TO 'public';
EOF