Postgresql迁移中的一点小知识

迁移工作流程

前期调研包括：数据量、差异、存储过程、函数、视图
迁移方式：工具调研
逐步迁移：部分业务、odbc_fdw
正式割接：全部业务切换完成

本次主要介绍SQL server到postgresql的迁移

数据类型对比

SQL Server    PostgreSQL
VARCHAR(N)        TEXT
DATETIME          TIMESTAMP
CURRENT_TIMESTAMP(0)::TIMESTAMP
CLOCK_TIMESTAMP()::TIMESTAMP
MONEY             NUMERIC(19,4)
IMAGE             BYTEA
UNIQUEIDENTIFIER  UUID
VARBINARY         BYTEA
TIMESTAMP         INTERVAL

函数类型转换

ISNULL()          COALESCE()
DATALENGTH        OCTET_LENGTH()
LEN()             LENGTH()
CONVERT()::TYPE / (CAST())
SUBSTRING('DFADF', 1, 2)[DF]    SUBSTR()
CHARINDEX(';', 'FASD;FDS', 2)    POSITION(';' IN 'FASD;FDS')
GETDATE()         NOW();
DATEADD            NOW()+INTERVAL '3 MONTHS';
DATEDIFF(DAY, STARTTIME, ENDTIME)    DATE_PART('DAY', ENDTIME ‐ STARTTIME))
DATEPART()        DATE_PART('WEEK', NOW());

关于两者之间的外部表

SQL Server中临时表

CREATE TABLE #Temp ( id int, customer_name nvarchar(50), age int )
select * into #t12 from table01;

PG中使用方式

create temp table tmp_t12 on commit drop as select * from table01;
可选择的参数：
on commit
PRESERVE ROWS
DELETE ROWS
DROP

视图

查询方式相同，需要注意PG中基表的数据类型发生变化后，视图需要重建

begin;  -- 开始事务  
set local lock_timeout = '1s';  -- 设置锁超时  
drop view v_test;  -- 删除依赖视图  
alter table test alter column a type varchar(32);  -- 修改字段长度  
create view v_test as select id,c1 from test;  -- 创建视图  
end;  -- 结束事务

外键

SQL Server中外键可以临时禁用
PG中在创建表时设置是否可延迟约束

DEFERRABLE
INITIALLY DEFERRED
INITIALLY IMMEDIATE

索引

PostgreSQL中没有聚集索引
对于选择性底的索引可以创建条件索引

CREATE INDEX IDX_Job_CompanyId ON Job (CompanyId) WHERE IsDeleted = false;
#### 部分索引
> postgres=# create table t5(a int,name character varying);
postgres=# insert into t5  select 1,'test'||i from generate_series(1,100000) as t(i);
postgres=# insert into t5  select i,'test'||i from generate_series(1,1000) as t(i);
postgres=# explain select * from t5 where a=1;
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Seq Scan on t5  (cost=10000000000.00..10000001808.50 rows=100027 width=13)
   Filter: (a = 1)
postgres=# create index idx_a_t5 on t5(a) where a<>1;
postgres=# explain select * from t5 where a=100;
                             QUERY PLAN                             
--------------------------------------------------------------------
 Index Scan using idx_a_t5 on t5  (cost=0.28..8.33 rows=3 width=13)
   Index Cond: (a = 100)
(2 rows)

非索引列的使用

postgres=# explain  select * from t5 where a=1 and name='test1';
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Seq Scan on t5  (cost=10000000000.00..10000002061.00 rows=1 width=13)
   Filter: ((a = 1) AND ((name)::text = 'test1'::text))
postgres=# create index idx_a_name_t5 on t5(a) where name='test100';
postgres=# explain  select * from t5 where a=1 and name='test100';
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Index Scan using idx_a_name_t5 on t5  (cost=0.13..8.14 rows=1 width=13)
   Index Cond: (a = 1)

pg中存储过程

获取影响的行数

while 1=1 loop ‐‐批量删除数据
    delete from t_inofaout_zcq  where trade_id=in_trade_id and
in_trade_id>0 and rq> in_start_date and rq<=in_end_date ;
        GET DIAGNOSTICS v_count = ROW_COUNT;
        if v_count<10000 then
        exit;     ‐‐while循环中退出 
    end if;
end loop;

返回单行多列的数据

create or replace function GetDate(
  in in_month int,
  out v_date1 date,
  out v_date2 date
) returns record
as $$ BEGIN
    v_date1 :=now();
    v_date2 :=now()+make_interval(months => in_month);
    return;
END;
$$ LANGUAGE plpgsql;

PG与SQL Server同时使用
ODBC_FDW做数据同步
https://github.com/hangzhou-cstech/odbc_fdw