1

There is a mariaDB instance that is already running and has data in many tables. Let me call this old database. Say, the structure (tables and their fields) were not well thought out and hence lead to duplication of data in multiple records and most of the columns are empty in many records. Further, the column names were not informative - like col1, col2, and col3 instead of name, place, and address. Since, the mariaDB instance is in active usage, any restructuring will involve changing other apps that use it. So, we would like to replicate it to a different structure in a different instance located in a different location, let me call this new database. This way, all apps that were deployed before are still functional but there is a better structured copy available. This new copy can be used for development of new apps and slow migration from the old badly structured database.

As data is added to the old database, it is replicated in the new structure live. For example, say the old database has a table employee like

create table employee (
  g1 int,
  g2 varchar(255),
  g3 varchar(255),
  g4 varchar(255),
  g5 date
);

In the new database, say the same data is structured by splitting into two tables:

create table employee_table (
  id int primary key,
  first_name varchar(255),
  last_name varchar(255),
  date_of_birth date
);

create table address_table (
  id int,
  address varchar(255),
  foreign key (id) references employee_table(id)
);

In this scenario, say we want the following mapping of fields in employee in old database to fields in new database:

  • employee.g1 -> employee_table.id, address_table.id
  • employee.g2 -> employee_table.first_name
  • employee.g3 -> employee_table.last_name
  • employee.g4 -> address_table.address
  • employee.g5 -> employee_table.date_of_birth

Is it possible to accomplish this using any available methods of replication supported by mariadb or postgresql?

I understand that it is possible to replicate to the same structure as the old database but I was unable to find anything on replicating to a different structure. The only method I was able to come up with involves writing a json config that maps each field in new database to old database along with necessary info such as types so that type casting be performed if necessary. A python script for example can use this as a look up to populate the new database from the old database and this script can be job run at regular intervals. The config should also mention ways to flag or deal with conflicts as they are expected due to the bad structure of the old database. For example, the same employee can have two different data_of_birth in the old database and this should be dealt with or flagged while replicating.

There is also the possibility of having triggers for each table on insert, so data inserted into old database is reflected in the different structure in new database. Data is always inserted only in old database through already deployed apps. Then, the new database can be replicated using standard approach to the other location. But I feel this not be great as editing a json config is easier than going to each table and modifying the trigger in case changes are needed.

Would appreciate suggestions on better approaches to do this type of replication and pointers on standard methods to accomplish this.

1 Answer 1

2

For simple name changes, defining VIEWs may be the 'best' solution.

Each VIEW could be a thin layer on top of an existing one. This would still require changing from the table names to the view names. (Or vice versa -- you decide which to change.)

See J.D.'s comment for some serious "planning ahead" by using VIEWs always.

Replication, even "row based", may be unable to ignore column names.

Part of the problem is that must change the application at the "same" time as the schema. But this may be a workaround for it:

  1. Develop and deploy code that will determine whether the schema is old or new, reaching for different tables/views as needed.
  2. Change the schema (Lots of ALTERs).
  3. Simplify the code so that it looks only at the 'new' schema.

Note that this 3-step process can handle many types of change, not just name changes.

pt-online-schema-change may be the best way to do ALTERs; it runs ALTER with essentially zero downtime.

2
  • VIEW is a nice suggestion. This can be short-term fix since schema changes (not limiting to only column names but also constraint changes and datatype changes among others) is required at some point down the line. If I understand right, there is no direct way to directly replicate to a different schema. The 3-step process suggested is a brief outline of the way to go about this and I agree. It will need a careful rewrite of the schema and slow migration of the codebase at later point which will slowly phase out the old schema. Thanks for confirming that there is no direct way to achieve this!
    – hwhap
    Aug 13, 2023 at 10:26
  • 2
    @hwhap I find Views are a good layer of abstraction even under normal cases too. I prefer to surround my base Tables with them, such that if the structure changes in the Table (columns added, re-ordered, renamed, table is split into multiple tables, etc) I don't have as much concern with my consuming applications breaking since the View's structure doesn't change, or can be easily updated to reflect Table changes that do require the View to change. I also don't have to worry about updating all the consuming applications to reflect the Table, since it's all centralized to the View, less work.
    – J.D.
    Aug 13, 2023 at 13:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.