There are many reasons to consider migrating from one database to another. Migration can allow you to take advantage of new or better technology. If your current database does not offer the right set of capabilities to allow you to scale the system, moving to a database that offers the functionality you need is the best move for your company.
Migration can also be very cost effective. Migrating systems with significant maintenance costs can save money spent on system upkeep. By consolidating the number of databases in use, you can also reduce in-house administrative costs. By using fewer database platforms (or possibly taking advantage of database compatibility), you can do more with your IT budget.
Using more than one database platform can offer you a graceful migration path should a vendor raise their pricing or change their company directive. EnterpriseDB has helped companies migrate their existing database systems to Postgres for years.
We recommend following the methodology detailed in The Migration Process.
The Migration Process¶
The migration path to Postgres includes the following main steps:
Start the migration process by determining which database objects and data will be included in the migration. Form a migration team that includes someone with solid knowledge of the architecture and implementation of the source system.
Identify potential migration problems. If it is an Oracle-to-Advanced Server migration, consult the EnterpriseDB documentation for complete details about the compatibility features supported in Advanced Server. Consider using EnterpriseDB’s migration assessment service to assist in this review.
Prepare the migration environment. Obtain and install the necessary software, and establish connectivity between the servers.
If the migration involves a large body of data, consider migrating the schema definition before moving the data. Verify the results of the DDL migration and resolve any problems reported in the migration summary. The Migration Errors section of this document includes information about resolving migration problems.
Migrate the data. For small data sets, use Migration Toolkit. If it is an Oracle migration (into Advanced Server), and the data set is large or if you notice slow data transfer, take advantage of one of the other data movement methods available:
Use the Advanced Server database link feature compatible with Oracle databases.
If your data has BLOB or CLOB data, use the dblink_ora style database links instead of the Oracle style database links.
Both of these methods use the Oracle Call Interface (OCI) to connect to Oracle. After connecting, use an SQL statement to select the data from the ‘linked’ Oracle database and insert the data into the Advanced Server database.
Confirm the results of the data migration and resolve any problems reported in the migration summary.
Convert applications to work with the newly migrated Postgres database. Applications that use open standard connectivity such as JDBC or ODBC normally only require changes to the database connection strings and selection of the EnterpriseDB driver. See Connecting an Application to Postgres for more information.
Test the system performance, and tune the new server. If you are migrating into an Advanced Server database, take advantage of Advanced Server’s performance tuning utilities:
Dynatuneto dynamically adjust database configuration resources.
Optimizer Hintsto direct the query path.
ANALYZEcommand to retrieve database statistics.
The EDB Postgres Advanced Server Guide and Database Compatibility for Oracle Developer’s Guide (both available through the EnterpriseDB website) offer information about the performance tuning tools available with Advanced Server.
Connecting an Application to Postgres¶
To convert a client application to use a Postgres database, you must
modify the connection properties to specify the new target database. In
the case of a Java application, change the JDBC driver name
Class.forName) and JDBC URL.
A Java application running on Oracle might have the following connection properties:
Class.forName("oracle.jdbc.driver.OracleDriver"); Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "user", "password")
Modify the connection string to connect to a Postgres server:
Class.forName("com.edb.Driver") Connection con = DriverManager.getConnection("jdbc:edb://localhost:5444/edb", "user", "password");
Converting an ODBC application to connect to an instance of Postgres is a two-step process.
To connect an ODBC application, use an ODBC data source administrator to create a data source that defines the connection properties for the new target database.
Most Linux and Windows systems include graphical tools that allow you to create and edit ODBC data sources. After installing ODBC, check the
Administrative Toolsmenu for a link to the
ODBC Data Source Administrator. Click the
Addbutton to start the
Create New Data Sourcewizard; complete the dialogs to define the new target data source.
Change the application to use the new data source.
The application will contain a call to
SQLConnect (or possibly
SQLDriverConnect); edit the invocation to change the data source name.
In the following example, the data source is named
result = SQLConnect(conHandle, // Connection handle (returned) "OracleDSN", SQL_NTS, // Data source name username, SQL_NTS, // User name password, SQL_NTS); // Password
To connect to an instance of Postgres defined in a data source named
PostgresDSN, change the data source name:
result = SQLConnect(conHandle, // Connection handle (returned) "PostgresDSN", SQL_NTS, // Data source name username, SQL_NTS, // User name password, SQL_NTS); // Password
After establishing a connection between the application and the server, test the application to find any compatibility problems between the application and the migrated schema. In most cases, a simple change will resolve any incompatibility that the application encounters. In cases where a feature is not supported, use a workaround or third party tool to provide the functionality required by the application. See Migration Errors, for information about some common problems and their workarounds.