Database copy tool

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

Database 1:

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

Database 2

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

We assume full JDBC administrative read access to database 1 and full read/write access to database 2. The copy tool shall allow for the following sequence of operations:

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

    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. Transver as much 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.

  • Database 2 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: