FWD » Core Development » Database

In a multitenant environment, there is a requirement to "move" a tenant database instance from one database cluster to another, bringing down neither cluster nor the FWD server managing either cluster. All records and indices must be preserved (including word tables), and the requirement that we do not have duplicate primary keys across all physical databases in the target multitenant cluster must be enforced. This includes the shared database instance and all tenant database instances. This means that the primary keys from the old tenant instance will very likely be different in the new tenant instance.

With PostgreSQL (at least), there is no built-in feature that moves a database across clusters. The typical way to do this is via a dump (from the old cluster/instance) and a load (into the new cluster/instance). However, a dump and load at the PostgreSQL level does not take the primary key requirement into account; it could easily result in duplicate primary keys across instances in the target cluster.

The simple but time-consuming way to achieve the move is via a FWD-level dump and load, which combines the dump feature implemented in #7504 (which dumps data to *.d files, sans primary keys) with the load feature implemented in #10094 (which enforces the unique primary key requirement). However, this can be too slow for some customers with large tenant databases.

The purpose of this task is to combine the core dump feature of #7504 with the "primary-key-safe" load feature of #10094 in such a way that this can be done directly from the source (old) tenant database in one cluster to the target (new) tenant database in another cluster:

• without intermediate *.d files; and
• without bringing down either FWD server.

Performing the move this way should be much faster than a separate dump and load with intermediate *.d files. Other than the fact that the tenant database instance being "moved" will be temporarily unavailable until the transition is complete, there should be no runtime impact to users of either running system. Depending on available CPU and I/O resources, the additional load of the transfer may be a temporary issue as well.

Just to be sure I understand the goal of this new FWD tool: the idea is to balance the load of FWD servers by migrating one tenant from one FWD server instance to another; most likely, using a different database cluster, and even more, possible database with different dialect. At the end of this operation the tenant metadata must also be migrated from one landlord database to another.

• moving only data to another cluster/database, but keeping under the same logical database (therefore same FWD server). In this case the PKs should be preserved, only the records in landlord database would require update. This can be performed for balancing of database or a smoother migration to a new database engine (dialect is not mandatory to remain the same);
• keeping the data on same cluster location but 'reallocate' the FWD server instance. Although this seems simple (only delete tenant records from source landlord database and adding those record to target logical database), due to PK constraints you mentioned earlier, the recid will have to be reassigned. This is a nightmare and possible the easy solution is to reimport the full database.

The recid uniqueness in FWD maps to the FWD server, not to the PostgreSQL cluster. This means that this migration process needs to modify the recid in that case, and will need to have a connection to the target FWD server. This migration might occur between PostgreSQL clusters or even within the same PostgreSQL cluster if more than 1 FWD server is authoritative for database instances in the same cluster.

I think we also have the case where a database instance must be moved between PostgreSQL clusters BUT DOES NOT NEED recid changes because the database instance is not changing its FWD server.