4

Are there any best practices, experiences or guidelines when it comes to do per-tenant backups/restores in a microservice multi-tenant application?

In my case I have multiple Postgres servers which are regularly backed up as a whole. Each of them contains a single database which is migrated using Flyway. The single database contains one schema with all the tables with the data of all tenants. Tenants are identified by UUID and all the data has foreign key relations so that e.g. one tenant can easily be deleted.

Now there's the requirement to be able to do restores for an individual tenant. I have found very little about the entire topic so far, but the only process I have come up with so far is this:

  • Restore all the backups to a different set of databases or different database servers
  • Spin up a new instance of each microservice so that it can start in isolation and is able to perform the necessary database migrations, if required
  • Delete the tenant from each of the production databases
  • Execute script or code in each service that identifies the data in the respective restored database and copy exactly these rows over to the production database

However doing or automating this seems like a disproportionate effort to me. I'm specifically looking for alternative (simpler) approaches, tools or databases that can simplify this process or actual experience how other people approach this problem.

Improve this question
9
  • How is your multi-tenant DB structured. There are at least three options: separate DBs for each tenant; a single DB, but separate tables for each tenant; a single DB, with tables used by all tenants (you would normal have a tenant table, and each table would point to the tenant_id as a foreign key). Presumably, you don't have option 1, or you wouldn't be asking the question.
    – RDFozz
    Sep 21, 2018 at 20:30
  • Exactly, by "multi-tenant database" what I meant was each server has a single database with all tables used by all tenants. The main issues I see are doing consistent backups, ensuring migrated schemas are compatible and doing some sort of partial restore.
    – xcq1
    Sep 22, 2018 at 5:57
  • How do you/ will you identify individual tenants?
    – Vérace
    Sep 22, 2018 at 13:21
  • Every tenant has a UUID which serves as its primary key. The data related to one tenant should have a foreign key on it which is already used when we delete tenants.
    – xcq1
    Sep 22, 2018 at 15:57
  • 1
    @Glasnhost Sadly no. We still haven't even started developing a solution but the "each service must expose a standardized interface for it" idea is increasingly looking appropriate.
    – xcq1
    Nov 23, 2019 at 11:15

1 Answer 1

Reset to default
3

Postgres has no feature to ferret out semantically a subset of the rows in your tables. Nor does any other database I’ve heard of.

You will need to write your own code to retrieve just the one tenant’s rows from each of the relevant tables. This code might be in your app, or might be done on the server within Postgres.

Also, you’ll need to decide if any other tables not split by tenant should also be copied into each tenant’s backup.

I have heard about some database vendors considering features to support multi-tenancy explicitly. The database would understand that the data for each tenant should be considered separate and kept in logical and/or physical silos. But I don’t know of any such products ready for production yet.

Improve this answer
6
  • Thanks for the suggestion. I would of course expect that I have to define some sort of relationship, e.g. via foreign keys. I'm also not totally settled on the technology, if there was a some promising alternative. Do you speak from experience of working on such a system? I'm just a little bit confused what I'm doing different so that apparently nobody else is facing this problem.
    – xcq1
    Sep 24, 2018 at 16:44
  • @xcq1 ?? Relationships are not part of a roll-your-own backup, just the dump of data goes into your own backup. The data of the foreign key columns is included in that data dump of course, but not their semantics, not the fact of the relationship between tables. That relationship is defined in SQL scripts that you should be collecting and managing with a database migration tool such as Flyway, Liquibase, etc.
    – Basil Bourque
    Sep 24, 2018 at 16:50
  • @xcq1 You’ve not yet actually explained what you are doing different. Edit your Question to explain the issues or concerns or processes that are complicating matters. Tables are simply shells into which we store data rows. You can export out a copy of a subset of rows. You can delete the originals. You can import those rows into any other database carrying the same table definitions.
    – Basil Bourque
    Sep 24, 2018 at 16:56
  • As for experience, most anybody with a database eventually exports subsets of data for use in other apps or systems, quite often for use in spreadsheets. Your situation is the same, exporting all the rows of all the tables where the tenant_id column value matches your particular tenant of interest. Each table’s exported rows would commonly be exported to a new file with data in a plain text format like CSV or tab-limited, or whatever format floats your boat.
    – Basil Bourque
    Sep 24, 2018 at 17:04
  • I have probably expressed myself not clearly enough and have tried to explain my thought process more in the question. As you mentioned, it seems it is a typical use case to export one tenant only. I just wonder why it is then that I can't find any mechanisms, frameworks, tools to support this.
    – xcq1
    Sep 24, 2018 at 17:23

Your Answer

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

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