Database copy tool

Consider two potentially differing database systems e.g. Postgresql™ and Mysql™.

Source database:

Hosting an active instance i.e. a set of tables containing data records and optionally views.

Destination database

Yet empty or containing non-conflicting table and view names.

We assume full JDBC™ read access to our source database and full read/write access to the destination database. The copy tool shall allow for the following sequence of operations:

  1. Copying all tables among with related records from source to destination database.

    Vendor specific SQL syntax rules e.g. when dealing with generated ID values (IDENTITY vs. AUTONUM) must be addressed. It is advisable to keep them in configuration files rather than hard-code them.

  2. Transfer as many integrity constraints as possible:

    • Data types potentially differing on both systems.

    • null / not null constraints.

    • Column default values.

    • Primary / candidate key constraints.

    • Foreign key constraints.

    • Check constraints.

Considerations:

  • null / not null defaults may differ on both database systems.

  • The destination database may be a non-SQL database like Mongodb supporting a limited subset of schema constraints. A copy tool thereby supports database migration.

Tip

Useful technologies:

Prev  Up  Next
Extending UNIX grep to databases  Home  Database schema evolution tool