????????????
哈嘍！大家好，我是【IT邦德】，江湖人稱jeames007，10余年DBA及大數(shù)據(jù)工作經(jīng)驗
一位上進心十足的【大數(shù)據(jù)領(lǐng)域博主】！??????
中國DBA聯(lián)盟(ACDU)成員，目前服務(wù)于工業(yè)互聯(lián)網(wǎng)
擅長主流Oracle、MySQL、PG、高斯及Greenplum運維開發(fā)，備份恢復，安裝遷移，性能優(yōu)化、故障應(yīng)急處理等。
? 如果有對【數(shù)據(jù)庫】感興趣的【小可愛】，歡迎關(guān)注【IT邦德】??????
??????感謝各位大可愛小可愛！??????
前言
本文詳細介紹了我的旅程、從Oracle遷移到PostgreSQL遇到的挑戰(zhàn),我希望分享這些經(jīng)驗將使你的PostgreSQL之旅更加順利.?? 1.Ora2Pg介紹
Ora2Pg是我的第一個盟友。
它是一個開源工具，可將Oracle數(shù)據(jù)庫模式轉(zhuǎn)換為PostgreSQL格式。
可以處理大量的甲骨文對象
可通過配置文件進行配置
https://ora2pg.darold.net/
特點：
支持導出數(shù)據(jù)庫絕大多數(shù)對象類型，包括表、視圖、序列、索引、外鍵、約束、函數(shù)、存儲過程等。
提供PL/SQL到PL/PGSQL語法的自動轉(zhuǎn)換，一定程度避免了人工修正。
可生成遷移報告，包括遷移難度評估、人天估算。
可選對導出數(shù)據(jù)進行壓縮，節(jié)約磁盤開銷。
配置選項豐富，可自定義遷移行為。
?? 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.相關(guān)配置
? 3.1 表結(jié)構(gòu)配置
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 表數(shù)據(jù)的配置文件
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遷移數(shù)據(jù)
? 4.1 遷移全部表結(jié)構(gòu)
mkdir -p /ora2pg
ora2pg -c /etc/ora2pg/ora2pg_table_ddl.conf -t table -b /ora2pg
? 4.2 PG中創(chuàng)建數(shù)據(jù)
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
文章來源：http://www.zghlxwxcb.cn/news/detail-751628.html
? 4.3 遷移數(shù)據(jù)
ora2pg -d -t copy -c /etc/ora2pg/ora2pg_table_data.conf -P 12 -L 100000 -j 12
此過程執(zhí)行完成后，數(shù)據(jù)就已經(jīng)插入到PG數(shù)據(jù)庫中了：文章來源地址http://www.zghlxwxcb.cn/news/detail-751628.html
?? 5.數(shù)據(jù)驗證
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 | {}
到了這里，關(guān)于基于ora2pg遷移Oracle19C到postgreSQL14的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
