我要评论1.ora2pg介绍
ora2pg是我的第一个盟友。
它是一个开源工具，可将oracle数据库模式转换为postgresql格式。
可以处理大量的甲骨文对象
可通过配置文件进行配置
https://ora2pg.darold.net/
特点：
支持导出数据库绝大多数对象类型，包括表、视图、序列、索引、外键、约束、函数、存储过程等。
提供pl/sql到pl/pgsql语法的自动转换，一定程度避免了人工修正。
可生成迁移报告，包括迁移难度评估、人天估算。
可选对导出数据进行压缩，节约磁盘开销。
配置选项丰富，可自定义迁移行为。
2.ora2pg安装
2.1 安装依赖包
yum install -y gcc perl-dbd-pg perl perl-devel perl-dbi perl-cpan bzip2 perl-extutils-ebuilder perl-extutils-makemaker perl-time-hires perl-tests perf cpan
2.2 正式安装
perl -mcpan -e ‘install dbi' perl -mcpan -e ‘install dbd::oracle' perl -mcpan -e ‘install dbd::pg'
3.相关配置
3.1 表结构配置
cat > /etc/ora2pg/ora2pg_table_ddl.conf <<“eof” oracle_home /usr/lib/oracle/21/client64 oracle_dsn dbi:oracle:host=172.18.12.90;sid=oradb;port=1521 #oracle_dsn dbi:oracle:host=192.168.1.29;service_name=pdb1;port=1521 #oracle_dsn dbi:oracle:tns_ora19c oracle_user system oracle_pwd oracle schema stest export_schema 1 create_schema 1 type table pg_numeric_type 0 pg_integer_type 1 default_numeric float skip fkeys checks #skip keys pkeys ukeys indexes checks nls_lang american_america.utf8 output_dir /tmp output ora2pg_table_ddl.sql pg_version 14 eof
3.2 表数据的配置文件
cat > /etc/ora2pg/ora2pg_table_data.conf <<“eof” oracle_home /usr/lib/oracle/21/client64 oracle_dsn dbi:oracle:host=172.18.12.90;sid=oradb;port=1521 #oracle_dsn dbi:oracle:tns_ora19c oracle_user system oracle_pwd oracle schema stest type copy pg_numeric_type 0 pg_integer_type 1 default_numeric float skip fkeys checks #skip fkeys pkeys ukeys indexes checks nls_lang american_america.utf8 output_dir /tmp output ora2pg_table_data.sql pg_dsn dbi:pg:dbname=jemdb;host=172.18.12.50;port=5432 pg_user postgres pg_pwd jeames pg_schema stest pg_version 14 eof
4.ora2pg迁移数据
4.1 迁移全部表结构
mkdir -p /ora2pg ora2pg -c /etc/ora2pg/ora2pg_table_ddl.conf -t table -b /ora2pg
4.2 pg中创建数据
su - postgres psql create user stest with password ‘post' createdb superuser replication createrole login; create database jemdb; alter database jemdb owner to stest; \c jemdb
– 跑脚本 \i /ora2pg/ora2pg_table_ddl.sql jemdb=# \d
4.3 迁移数据
ora2pg -d -t copy -c /etc/ora2pg/ora2pg_table_data.conf -p 12 -l 100000 -j 12
此过程执行完成后，数据就已经插入到pg数据库中了：
5.数据验证
su - postgres
psql
\c jemdb
emdb=# show search_path;
search_path
-----------------
"$user", public
(1 row)
jemdb=# set search_path=stest,public;
set
jemdb=# \dt
list of relations
schema | name | type | owner
--------+----------------------+-------+----------
stest | addresses | table | postgres
stest | card_details | table | postgres
stest | customers | table | postgres
stest | inventories | table | postgres
stest | logon | table | postgres
stest | order_items | table | postgres
stest | orderentry_metadata | table | postgres
stest | orders | table | postgres
stest | product_descriptions | table | postgres
stest | product_information | table | postgres
stest | warehouses | table | postgres
(11 rows)
jemdb=# select nspname as schemaname,
jemdb-# relname,
jemdb-# reltuples::numeric as rowcount,
jemdb-# pg_size_pretty (
jemdb(# pg_total_relation_size ( '"' || nspname || '"."' || relname || '"' )) as size
jemdb-# from pg_class c left join pg_namespace n on ( n.oid = c.relnamespace )
jemdb-# where nspname not in ( 'pg_catalog', 'information_schema' )
jemdb-# and relkind = 'r'
jemdb-# order by reltuples desc
jemdb-# limit 20;
schemaname | relname | rowcount | size
------------+----------------------+----------+------------
stest | inventories | 899441 | 433 mb
stest | order_items | 7341 | 1072 kb
stest | logon | 2383 | 160 kb
stest | card_details | 1500 | 264 kb
stest | addresses | 1500 | 264 kb
stest | orders | 1430 | 376 kb
stest | warehouses | 1000 | 192 kb
stest | customers | 1000 | 440 kb
stest | product_descriptions | 1000 | 288 kb
stest | product_information | 1000 | 400 kb
stest | orderentry_metadata | -1 | 8192 bytes
(11 rows)
jemdb=# \l
list of databases
name | owner | encoding | collate | ctype | access privileges
-----------+----------+----------+-------------+-------------+-----------------------
jemdb | stest | utf8 | en_us.utf-8 | en_us.utf-8 |
postgres | postgres | utf8 | en_us.utf-8 | en_us.utf-8 |
template0 | postgres | utf8 | en_us.utf-8 | en_us.utf-8 | =c/postgres +
| | | | | postgres=ctc/postgres
template1 | postgres | utf8 | en_us.utf-8 | en_us.utf-8 | =c/postgres +
| | | | | postgres=ctc/postgres
(4 rows)
jemdb=# \dn
list of schemas
name | owner
--------+----------
public | postgres
stest | stest
(2 rows)
jemdb=# \du
list of roles
role name | attributes | member of
-----------+------------------------------------------------------------+-----------
postgres | superuser, create role, create db, replication, bypass rls | {}
stest | superuser, create role, create db, replication | {}
以上就是基于ora2pg迁移oracle19c到postgresql14的全过程的详细内容，更多关于迁移oracle19c到postgresql14的资料请关注代码网其它相关文章！
发表评论