Top 10 Mistakes When Migrating From Oracle to PostgreSQL

下载 0

苏寒

发布于

1582

人观看

#信息技术

随着越来越多的人从Oracle转向PostgreSQL，一种错误模式正在出现。它们可能是由于所使用的工具或者仅仅是因为不理解PostgreSQL与Oracle的区别。在本文中，我们将讨论人们从Oracle迁移到PostgreSQL时通常会犯的错误，以及正确的操作过程。

展开查看详情

2 .Why? • Project deadline • Looming Oracle renewal • Lack of education • Attitude • Only see the world through an Oracle lens • Using migration tools or other short cuts © 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

3 .System Tuning When moving to PostgreSQL, many admins start with configuring values similar to the Oracle settings “My SGA was set to 16GB so shared_buffers is 16GB” “My redo logs are 2GB so max_wal_size is 2GB” © 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

4 .Table Spaces • In Oracle, table spaces are critical for storing data • Generally many table spaces are used for indexes and tables CREATE TABLESPACE ts_data1 LOGGING DATAFILE '/data/ts_data1.dbf' SIZE 32m AUTOEXTEND ON NEXT 32m MAXSIZE 2048m EXTENT MANAGEMENT local; © 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

5 .Table Spaces • In PostgreSQL, table spaces are just directory locations • Provides no real benefit unless the database spans multiple mount points CREATE TABLESPACE ts_data1 LOCATION '/data/ts_data1'; © 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

6 .Case Folding In Oracle, all meta-data folds to uppercase SQL> DESC USERS Name Null? Type ---------- ------- ------------------------ FNAME VARCHAR2(100) MNAME VARCHAR2(100) LNAME VARCHAR2(100) © 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

7 .Case Folding In PostgreSQL, all meta-data folds to lowercase test=# \d users Table ”public.users” Column | Type | Nullable --------+------------------------+--------- fname | character varying(100) | mname | character varying(100) | lname | character varying(100) | © 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

8 .Case Folding Some migration tools carry the uppercase from Oracle over to PostgreSQL test=# \d ”USERS” Table ”public.USERS” Column | Type | Nullable --------+------------------------+---------- FNAME | character varying(100) | MNAME | character varying(100) | LNAME | character varying(100) | © 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

10 .DUAL Table In Oracle, the DUAL table is used to run functions SQL> SELECT SYSDATE FROM DUAL; SYSDATE --------- 09-MAY-17 © 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

11 .DUAL Table • In PostgreSQL, the FROM clause is optional and is unnecessary • Do not mock a DUAL table test=# SELECT CURRENT_DATE; current_date -------------- 2017-05-09 (1 row) © 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

12 .Synonyms “PostgreSQL doesn’t have synonyms so I can’t migrate my application” CREATE PUBLIC SYNONYM emp FOR SCOTT.emp; • Synonyms are used to not fully qualify cross schema objects • Mostly a convenience feature © 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

13 .Synonyms In PostgreSQL, search_path can accomplish many of the same things and is less tedious to setup test=# show search_path; search_path ----------------- ”$user”, public (1 row) © 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

14 .Synonyms CREATE FUNCTION user1.get_int() RETURNS int AS $$ SELECT 1; $$ LANGUAGE sql; CREATE FUNCTION user2.get_int() RETURNS int AS $$ SELECT 2; $$ LANGUAGE sql; CREATE FUNCTION public.get_number() RETURNS float8 AS $$ SELECT 3.14::float8; $$ LANGUAGE sql; © 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

15 .Synonyms test=# SELECT get_int(); 2017-05-08 17:38 EDT [28855] ERROR: function get_int() does not ... 2017-05-08 17:38 EDT [28855] HINT: No function matches the given... 2017-05-08 17:38 EDT [28855] STATEMENT: SELECT get_int(); ERROR: function get_int() does not exist LINE 1: SELECT get_int(); ^ HINT: No function matches the given name and argument types. You... © 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

16 .Synonyms test=# SET search_path = user1, user2, public; SET test=# SELECT get_int(); get_int --------- 1 (1 row) © 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

17 .Synonyms test=# SET search_path = user2, user1, public; SET test=# SELECT get_int(); get_int --------- 2 (1 row) © 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

18 .Synonyms test=# select get_number(); get_number ------------ 3.14 (1 row) © 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

19 .Nulls PostgreSQL and Oracle handle nulls a bit differently • Need to account for them appropriately • Most often seen with string concatenation In Oracle NULL = ‘’ In PostgreSQL NULL != ‘’ And NULL != NULL © 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

20 .Nulls CREATE TABLE users ( fname VARCHAR2(100), mname VARCHAR2(100), lname VARCHAR2(100) ); SELECT fname || ' ' || mname || ' ' || lname FROM users; © 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

21 .Nulls SQL> SELECT fname || ' ' || mname || ' ' || lname FROM users; FNAME||''||MNAME||''||LNAME --------------------------------------------------------------- George Washington John Adams Thomas Jefferson James Madison James Monroe Andrew Jackson Martin Van Buren John Tyler John Quincy Adams William Henry Harrison 10 rows selected. © 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

22 .Nulls test=# SELECT fname || ' ' || mname || ' ' || lname FROM users; ?column? ------------------------ John Quincy Adams William Henry Harrison (10 rows) © 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

23 .Nulls test=# SELECT COALESCE(fname, '') || ' ' || COALESCE(mname, '') || ' ' || COALESCE(lname, '') FROM users; ?column? ------------------------ George Washington John Adams Thomas Jefferson James Madison James Monroe Andrew Jackson Martin Van Buren John Tyler John Quincy Adams William Henry Harrison (10 rows) © 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

24 .Nulls SQL> CREATE UNIQUE INDEX null_test_idx ON null_test (c1, c2); Index created. SQL> INSERT INTO null_test (c1,c2) VALUES (1, 'a'); 1 row created. SQL> INSERT INTO null_test (c1) VALUES (1); 1 row created. SQL> INSERT INTO null_test (c1) VALUES (1); INSERT INTO null_test (c1) VALUES (1) * ERROR at line 1: ORA-00001: unique constraint (MASTER.NULL_TEST_IDX) violated © 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

25 .Nulls => CREATE UNIQUE INDEX null_test_idx ON null_test (c1, c2); CREATE INDEX => INSERT INTO null_test (c1,c2) VALUES (1, 'a'); INSERT 0 1 => INSERT INTO null_test (c1) VALUES (1); INSERT 0 1 => INSERT INTO null_test (c1) VALUES (1); INSERT 0 1 © 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

26 .Fine Tuning Queries “I added a hint to use an index but PostgreSQL does not use it” • PostgreSQL does not have hints as part of the core database • It is available as an extension (pg_hint_plan) • It treats Oracle hints as comments • PostgreSQL’s optimizer is different than Oracle so queries are tuned differently © 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

27 .Fine Tuning Queries “I didn’t index my column in Oracle, why would I in PostgreSQL?” PostgreSQL has more and different types of indexes than Oracle • B-tree • GiST • Hash • SP-GiST • GIN • BRIN © 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

28 .Fine Tuning Queries PostgreSQL can use indexes on LIKE queries CREATE INDEX idx_users_lname ON users USING gin (lname gin_trgm_ops); EXPLAIN SELECT * FROM users WHERE lname LIKE '%ing%'; QUERY PLAN ----------------------------------------------------- Bitmap Heap Scan on users (cost=8.00..12.02 rows=1 width=654) Recheck Cond: ((lname)::text ~~ '%ing%'::text) -> Bitmap Index Scan on idx_users_lname (cost=0.00..8.00 rows=1 width=0) Index Cond: ((lname)::text ~~ '%ing%'::text) © 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

29 .Not Using Native Features PostgreSQL is more feature rich for developers than Oracle • Stored Procedure Languages • Foreign Data Wrappers • Data Types • Spatial © 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

0点赞

1收藏

0下载