Migration methodology

You might consider migrating from one database to another for many reasons. Migration can allow you to take advantage of new or better technology. If your current database doesn’t 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 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 if a vendor raises their pricing or changes their company directive. EnterpriseDB has, for years, helped companies migrate their existing database systems to Postgres.

The migration process

The migration path to Postgres that we recommend includes the following main steps:

  1. Start the migration process by determining the database objects and data to include in the migration. Form a migration team that includes someone with solid knowledge of the architecture and implementation of the source system.

  2. Identify potential migration problems. If it is an Oracle-to-EDB Postgres Advanced Server migration, consult the EnterpriseDB Postgres Advanced Server documentation for complete details about the compatibility features supported in EDB Postgres Advanced Server. Consider using the EnterpriseDB migration assessment service to help with this review.

  3. Prepare the migration environment. Obtain and install the needed software, and establish connectivity between the servers.

  4. 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. Migration errors includes information about resolving migration problems.

  5. Migrate the data. For small data sets, use Migration Toolkit. If it’s an Oracle migration into EDB Postgres 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 EDB Postgres 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 EDB Postgres Advanced Server database.

  1. Confirm the results of the data migration, and resolve any problems reported in the migration summary.

  2. Convert applications to work with the newly migrated Postgres database. Applications that use open standard connectivity such as JDBC or ODBC normally require changes only to the database connection strings and selection of the EnterpriseDB driver. See Connecting an application to Postgres for more information.

  3. Test the system performance, and tune the new server. If you’re migrating into an EDB Postgres Advanced Server database, take advantage of EDB Postgres Advanced Server’s performance tuning utilities:

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.

  1. 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 Tools menu for a link to the ODBC Data Source Administrator. Select Add to start the Create New Data Source wizard. Then, complete the dialogs to define the new target data source.

  1. Change the application to use the new data source.

  • The application contains a call to SQLConnect or SQLDriverConnect. Edit the invocation to change the data source name. In the following example, the data source is named OracleDSN:

*   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 resolves any incompatibility that the application encounters. When 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.