Top 10 Mistakes When Migrating From Oracle to PostgreSQL

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

展开查看详情

1. May, 2019 © 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved. © 2018, Amazon Web Services, Inc. or its Affiliates. All rights reserved.

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.

9.Case Folding Becomes very tedious needing to double quote everything test=# 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) © 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.