从AWS RDS Oracle迁移至本地PostgreSQL：日期时间类型适配方案咨询

一、修复当前INSERT语句导出的格式兼容问题 #

1. 调整Oracle导出时的会话格式 #

在Oracle SQL Developer中，先执行以下SQL修改会话的日期/时间戳输出格式，再执行导出操作：

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF9';

这样导出的INSERT语句里，日期和时间戳会直接生成PostgreSQL兼容的格式，无需后续修改。

2. 批量修正已导出的SQL文件 #

如果已经导出了不符合格式的SQL，可以用文本编辑器的正则替换或脚本处理：

• DATE格式修正：匹配(\d{2})-([A-Z]{3})-(\d{2})（如01-SEP-22），替换为'20\3-'+对应月份数字+'-\1'（比如SEP替换为09，得到'2022-09-01'）。
• TIMESTAMP格式修正：先把时间部分的.替换为:，再处理AM/PM转24小时制（比如09.36.33 AM转为09:36:33，09.36.33 PM转为21:36:33），最后调整日期部分为YYYY-MM-DD格式，最终得到'2023-03-17 09:36:33.000000'这类格式。

二、最佳数据迁移方案 #

手动导出INSERT语句仅适合小批量数据，大规模迁移推荐以下方案：

1. CSV导出+PostgreSQL COPY命令（高效轻量） #

步骤：

• 在Oracle端用SQL*Plus导出CSV，指定兼容格式：

  SET HEADING OFF
  SET COLSEP ','
  SET NLS_DATE_FORMAT 'YYYY-MM-DD'
  SET NLS_TIMESTAMP_FORMAT 'YYYY-MM-DD HH24:MI:SS.FF6'
  SPOOL /local/path/table_data.csv
  SELECT * FROM your_schema.your_table;
  SPOOL OFF
  

• 将CSV文件传到PostgreSQL服务器，用COPY命令导入（比INSERT快10倍以上）：

  COPY your_postgres_table FROM '/local/path/table_data.csv' WITH (FORMAT csv, HEADER false, DELIMITER ',');
  

2. 使用ETL工具（适合复杂/增量迁移） #

用Apache NiFi、Talend这类ETL工具，直接建立Oracle和PostgreSQL的数据源连接，自动处理数据类型映射（包括日期格式转换），支持增量同步、数据校验。只需配置表字段映射，即可一键执行迁移，适合大规模、多表的迁移场景。

3. PostgreSQL oracle_fdw外部表（直接跨库迁移） #

通过oracle_fdw扩展，让PostgreSQL直接读取Oracle数据，无需中间文件：

步骤：

1. 在PostgreSQL服务器安装oracle_fdw扩展：

   CREATE EXTENSION oracle_fdw;
   

2. 创建到Oracle RDS的连接：

   CREATE SERVER oracle_rds FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//your_rds_endpoint:1521/your_service_name');
   CREATE USER MAPPING FOR postgres SERVER oracle_rds OPTIONS (user 'oracle_user', password 'oracle_pwd');
   

3. 创建映射Oracle表的外部表：

   CREATE FOREIGN TABLE oracle_source_table (
       id INT,
       create_date DATE,
       update_ts TIMESTAMP
   ) SERVER oracle_rds OPTIONS (schema 'ORACLE_SCHEMA', table 'SOURCE_TABLE');
   

4. 直接将数据插入本地表：

   INSERT INTO local_postgres_table SELECT * FROM oracle_source_table;
   

oracle_fdw会自动处理日期、时间戳的类型转换，无需手动调整格式。

内容的提问来源于stack exchange，提问作者Abhishek K M