Refresh staging schema from PostgreSQL database

[fljdin]

Hi

Currently, the db_migrate_prepare method relies on plugin callbacks to populate the staging schema tables. It is therefore necessary to defer all adjustments by hand, by modifying the contents of the tables.

In some migrations, the target data model is already supplied by the development teams. It is guaranteed to be compliant with the application version of the remote model and contains all necessary adjustments (column types, constraints or indexes, stored procedures).

I can imagine two solutions to meet this need:

• write a new plugin to connect to a PostgreSQL database (a plugin called pg_migrator) and enable the staging schema to be refreshed from a desired data model, and then carry out the migration with another plugin and the db_migrate_mkforeign method.
• create a new method similar to db_migrate_prepare, to be called "db_migrate_prepare_local", where the tables in the staging schema would be simple views compiling information from the local catalog.

The first solution would leave db_migrator untouched. This plugin could be provided by another project, maintained by anyone. It could also be proposed as an example from the db_migrator project, replacing the noop_migrator plugin, which is provided for testing purposes. This is elegant, but also ridiculous, since it's not db_migrator's purpose to migrate data from PostgreSQL to PostgreSQL.

The second solution might be simple to implement, but the extension won't be able to consult the catalog of another database in the instance. Schemas will have to be made available for refreshment, then deleted before starting the migration of relations and data.

What do you think about it? Regards, Florent