Tips] Key Points of migrating an Oracle database to PostgreSQL

[Tips] key points for migrating an Oracle database to PostgreSQL

Recently, I tried to port an Oracle database, along with Java applications built on this database, to the PostgreSQL environment. During the transplantation process, I summarized some key points. On the one hand, I forgot my notes, and on the other hand, I also gave a reference to my friends who needed to handle similar tasks but could not help.

1-first, prepare the PostgreSQL environment. If there are conditions, it is best to find an idle PC as a test server, install Linux or BSD, and then compile the latest PostgreSQL 8.3.0 from the source code. During compilation, use configure to specify -- With-perl and -- With-python to support PL/perl and PL/python. Because most Linux distributions have their own Perl and Python versions, you do not need to install them.

2-if it is a Windows environment and Perl and Python are required, additional installation is required. For python, you can easily find the 2.5 for Windows installation package. For Perl, ActivePerl is recommended, it is a little more troublesome. To facilitate the loading of some later functions, select 5.8.8 for Perl as much as possible.

3-create a database and a user. Use initdb to initialize the data directory and configure PostgreSQL. conf specifies the Host IP address, port, and other information, configure pg_cmd.conf to specify the access permission, start Postmaster through pg_ctl-D-l start, and then createdb and createuser to create databases and users. After the database is created, you can create createlang-d [plperl | plperlu | plpython | plpythonu] to enable PL/perl and PL/python. You can view the specific command line parameters by adding -- help to each command.

4-install PostgreSQL client pgadminiii. The latest version is 1.8.2. If necessary, you can download the source code and compile it yourself.

5-to install the Oracle client, you must install Perl on the same machine in PostgreSQL so that the DBI and Oracle driver DBD: The Oracle module can be used to connect to the database. For ActivePerl on Windows, you can install DBD-Oracle through ppm. For Linux/BSD, you can install it through CPAN, such as Perl-mcpan-e shell to enter CPAN shell, install DBI and DBD: Oracle using install or force install.

6-for database migration, you can select ora2pg for help. The current version is 4.7. Ora2pg is a Perl tool used to read Oracle database schema and data, generate PostgreSQL scripts, or directly import the PostgreSQL database. It is easy to use. conf file to specify the database connection information including nls_lang, schema, table, view, and data to be exported and imported, and then execute a pl script. This is a relatively mature solution at present, but there are still a lot of places where manual processing is required when a database with complicated schema and strong constraints is encountered. We recommend that you do not directly write data to PostgreSQL. Instead, you can generate an SQL script and run the script after verification. By default, ora2pg converts the names in Oracle to lower-case letters, because PostgreSQL converts the names to lower-case letters by default, unless "" is included in SQL parsing. Schema, table, view, sequence, data, and so on. You can use ora2pg and add some manual adjustments. As for functions and stored procedure, you should move them manually. In addition to ora2pg, you can also configure DBI-link to attach the Oracle database to the PostgreSQL database as an independent "schema", and then use Create Table XXX as select... from... this syntax is used to reverse tables and data. The contrib package of postgresql also comes with a dblink that connects to other PostgreSQL databases. If you need to connect to a non-PostgreSQL database, consider DBI-link and any database that can be accessed through the Perl DBI interface, can be linked to PostgreSQL.

7-the next step is the Java application itself. The application I moved this time is based on the spring + ibatis architecture, and many SQL statements are in plain text. Fortunately, the basic part of the DaO layer (crud) the sqlmap of is automatically generated by the tool and complies with the ANSI sql92 standard. It can be used without modification. There are many other advanced query SQL statements that need to be adjusted. Some common modifications are listed as follows:

I. Select columns (including subqueries). If there is an alias, you must add as. For example, select null as some_column from some_table;
Ii. PostgreSQL does not have dual tables. Similar to the select 0 from dual statement, you can write it as select 0;
III. the decode function needs to be re-constructed (case when some_column = 'some _ value' then 'some _ other_value 'when... then... else 'some _ default_value 'end) as some_column;
Iv. nvl () function, which corresponds to coalesce () in PostgreSQL. In fact, almost all mainstream DBMS support coalesce, including Oracle, which is the standard writing;
V. comparison date. In PostgreSQL, we recommend that you use the format date_trunc ('day', some_date) = date_trunc ('day', # entereddate, the optional fields of the 'day' position include year, month, week, hour, minute, and second;
Vi. sysdate, corresponding to PostgreSQL is current_timestamp, you can use current_date as needed;
VII. rownum. We usually use rownum to limit the number of records to be queried. PostgreSQL does not have this keyword and needs to add a limit statement at the end of the SELECT statement, such as limit 100;
VIII. (+) the outer join method needs to be adjusted to the SQL standard Table1 [left | right | Full] Outer Join Table2 on (...);
IX. Connect by... start with... for Recursive queries, refer to the connectby () function of http: // www.postgresql.org/docs/8.3/static/tablefunc.html.

Finally, I would like to mention that PostgreSQL's built-in process language is PL/pgsql. In addition to plpqsql, it also supports SQL, plperl (u), plpython (u), and so on to Write Functions on PostgreSQL. If you are naturally allergic to SQL, it is very difficult to read PL/pgsql-like code, not to mention writing. You can use your preferred language to express the logic of functions and stored procedures. What are you afraid of with pL/python? You can do almost anything.

[Update 20080313] part of the JDBC driver missed, porting Java applications, in addition to changing SQL, also need to take PostgreSQL JDBC driver put under classpath, such as WEB-INF/lib, modify the database connection URL to JDBC: PostgreSQL :// : / You can.

[Update 20080323] One of the more convenient ways to port schema and data than ora2pg is to use the migration tool of enterprisedb, copy the oracle JDBC driver ojdbc14.jar to JRE/lib/ext in the enterprisedb installation path, and start developer studio to establish an oracle connection. After selecting schema, you can right-click online migration to import the schema, Data, function package, and so on to enterprisedb. If you want to move to "pure" PostgreSQL, backup from EDB, and restore under PostgreSQL, the function package will be lost, after all, EDB has made a considerable transformation based on PostgreSQL to be compatible with Oracle, but it is safer to manually move the function package and the like.
Comparison of common database functions (1)
Today, we are processing PostgreSQL database functions. Encountered a problem

In the SELECT statement, I want to determine whether the attribute of a field is null or a specific value.
In Oracle, we will use functions such as nvl decode. MySQL also has ifnull and if functions. Sometimes it will confuse and forget that

Which database supports this function.

So I will summarize some common functions in different databases, so that they can be used in the future.

I. Date operations

1. operate on the current date and time

Microsoft SQL Server
Select getdate ()
Go
MySQL return date does not include time
Select curdate ();
MySQL return date and time
Select now ();
Oracle
Select sysdate from dual;
PostgreSQL
Select current_date;
Select now (); return date time also includes the time zone

2. Obtain the subdomain of the operation time

Microsoft SQL Server
Select datepart (DW, getdate ())
Go

MySQL
Select dayname (curdate ());

Oracle
Select to_char (sysdate, 'day ')
From dual;

PostgreSQL
Select date_part ('dow', date 'right'); // Dow = day of week
Select date_part ('hour', timestamp 'right ')

In Microsoft SQL, the syntax for calling the function datepart is: datepart (datetype, date_expression). Function Parameters

Datetype is month, day, week, day of week, etc. The second parameter is a field containing the date type or a real

In MySQL, The dayname function directly specifies the current date of the week. in Oracle, to_char can be

Obtain the required subdomains, date, hour, and minute in the mid-term.

3. Time Interval. In some applications, you need to know how far the two time intervals are.

Microsoft SQL Server
Select datediff (DD, '2014/1/01', getdate ())
Go

MySQL
Select from_days (to_days (curdate ())-
To_days ('2017-11-25 '));

Oracle
Select to_date ('25-Nov-2000 ', 'dd-mon-yyyy ')-
To_date ('25-Aug-1969 ', 'dd-mon-yyyy ')
From dual;

PostgreSQL

Select age (current_date, '25-Aug-1969 ');

The function syntax varies greatly between different databases during different measurement intervals.

4. format the date and time

Microsoft SQL Server
Select convert (varchar (11), getdate (), 102)
Go
MySQL
Select date_format (/"2001-11-25/",/"% m % E, % Y /");
Oracle
Select to_char (sysdate, 'dd-mon-yyyy hh: MI: SS Ps ')
From dual;
PostgreSQL
Select to_char (timestamp (current_date), 'dd-mon-yyyy hh: MI: SS Ps ');

2. String operations

1. The string contains characters.

Microsoft SQL Server
Select charindex ('eat', 'great ')
Go
MySQL
Select position ('eid' in 'great ');
Oracle
Select instr ('great', 'eat') from dual;
PostgreSQL
Select position ('eid' in 'great ');

The above functions can be used to determine the position of a string in another string (and the position where another string contains this string ).

2. Remove spaces from the string

Microsoft SQL Server
Select ltrim ('SQL _in_a_nutshell '),
Select rtrim ('SQL _ in_a_nutshell '),
Select ltrim (rtrim ('SQL _in_a_nutshell ')
Go
MySQL
Select ltrim ('SQL _in_a_nutshell '),
Select rtrim ('SQL _ in_a_nutshell '),
Select trim ('SQL _in_a_nutshell '),
Select trim (both from 'SQL _in_a_nutshell ');
Oracle
Select ltrim ('SQL _in_a_nutshell '),
Select rtrim ('SQL _ in_a_nutshell '),
Trim ('SQL _in_a_nutshell ')
From dual;
PostgreSQL
Select trim (leading from 'SQL _in_a_nutshell '),
Trim (trailing from 'SQL _ in_a_nutshell '),
Trim (both from 'SQL _in_a_nutshell ');

3. Clear the opposite space and add a space.

Microsoft SQL Server
Not Supported
MySQL
Select lpad ('SQL _ in_a_nutshell', 20 ,''),
Rpad ('SQL _ in_a_nutshell', 20 ,'');
Oracle
Select lpad ('SQL _ in_a_nutshell', 20 ,''),
Rpad ('SQL _ in_a_nutshell', 20 ,'')
From dual;
PostgreSQL
Select lpad ('SQL _ in_a_nutshell', 20 ,''),
Rpad ('SQL _ in_a_nutshell', 20 ,'');
The functions of the databases that support this operation are the same and both include adding spaces from left to right.

4. String replacement

Microsoft SQL Server [returns 'wabbit _ hunting_season ']
Select stuff ('wabbit _ season ', 7, 1,' _ hunting _')
Go
MySQL [returns 'wabbit _ hunting_season ']
Select
Replace ('wabbit _ season ', 'it _', 'it _ hunting _');
Oracle [returns 'wabbit _ hunting_season ']
Select
Replace ('wabbit _ season ', 'it _', 'it _ hunting _')
From dual;
PostgreSQL
Select translate ('wabbit _ season ', 'it _', 'it _ hunting _');
Select Replace ('wabbit _ season ', 'it _', 'it _ hunting _');

5. String Truncation

Microsoft SQL Server
Select substring ('wabbit _ duck_season ', 7, 11)
Go
MySQL
Select
Substring ('wabbit _ duck_season ', 7, 11 );
Oracle
Select substr ('wabbit _ duck_season ', 7, 11)
From dual;
PostgreSQL
Select substr ('wabbit _ duck_season ', 7, 11 );

3. Condition judgment

1. Condition judgment
Microsoft SQL Server
Select case
When Foo = 'Hi' then 'there'
When Foo = 'good' then 'bye'
Else 'default'
End
From T2
Go

MySQL
Select if ('11' = '11'), '1', '2 ')
Select if (2> 1, '1', '2 ')

Oracle
Select decode
(Payments_info, 'cr ', 'credentials', 'db', 'debit', null)
From dual;

PostgreSQL
Select case
When Foo = 'Hi' then 'there'
When Foo = 'good' then 'bye'
Else 'default'
End
From T2;

The above functions are not explained much. It is easy to understand. Let's talk about the mysql if () function. If the first parameter is

True: The second parameter is returned. Otherwise, the third parameter is returned.

2. Judge empty Functions

Microsoft SQL Server
Select isnull (Foo, 'value is null ')
Go

MySQL
Select ifnull (122, 'aaa ')

Oracle
Select nvl (Foo, 'value is null ')
From dual;
PostgreSQL
Select coalesce (Foo, 'value is null ')

3. The following is different from the above function.

Microsoft SQL Server [returns NULL when Foo equates to 'wabbits! ']
Select nullif (Foo, 'wabbits! ')
Go
MySQL
N/
Oracle
Select decode (Foo, 'wabbits! ', Null)
From dual;
PostgreSQL
Select nullif (Foo, 'wabbits! ');
Function Syntax: nullif (expression1, expression2)
If expression1 is equal to expression2, null is returned. If expression1 is null, null is also returned.