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

[laurenz]

I would rather not build any special functions for this purpose into db_migrator.

Like you, I feel that this is somewhat ridiculous. If someone prepares the PostgreSQL table definitions by hand or with some other tool, why would they use db_migrator? The only value would be the data transfer through the foreign tables. But I feel that anybody who prepares CREATE TABLE statements can also prepare CREATE FOREIGN TABLE statements with little more effort, and then the added value of using db_migrator is pretty much gone.