迁移工作流程

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

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

数据类型对比

  1. SQL Server PostgreSQL
  2. VARCHAR(N) TEXT
  3. DATETIME TIMESTAMP
  4. CURRENT_TIMESTAMP(0)::TIMESTAMP
  5. CLOCK_TIMESTAMP()::TIMESTAMP
  6. MONEY NUMERIC(19,4)
  7. IMAGE BYTEA
  8. UNIQUEIDENTIFIER UUID
  9. VARBINARY BYTEA
  10. TIMESTAMP INTERVAL

函数类型转换

  1. ISNULL() COALESCE()
  2. DATALENGTH OCTET_LENGTH()
  3. LEN() LENGTH()
  4. CONVERT()::TYPE / (CAST())
  5. SUBSTRING('DFADF', 1, 2)[DF] SUBSTR()
  6. CHARINDEX(';', 'FASD;FDS', 2) POSITION(';' IN 'FASD;FDS')
  7. GETDATE() NOW();
  8. DATEADD NOW()+INTERVAL '3 MONTHS';
  9. DATEDIFF(DAY, STARTTIME, ENDTIME) DATE_PART('DAY', ENDTIME STARTTIME))
  10. 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中基表的数据类型发生变化后,视图需要重建

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

外键

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

  1. DEFERRABLE
  2. INITIALLY DEFERRED
  3. INITIALLY IMMEDIATE

索引

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

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

非索引列的使用

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

pg中存储过程

获取影响的行数

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

返回单行多列的数据

  1. create or replace function GetDate(
  2. in in_month int,
  3. out v_date1 date,
  4. out v_date2 date
  5. ) returns record
  6. as $$ BEGIN
  7. v_date1 :=now();
  8. v_date2 :=now()+make_interval(months => in_month);
  9. return;
  10. END;
  11. $$ LANGUAGE plpgsql;

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

0 评论  
添加一条新评论