0

I have multiple servers running in data centers around the world. Each server has its own configuraiton database, and cli command needs to be executed to update in-memory cache.

I want to automate this system. I have following requirements:

  1. Single source of truth (i.e. git repository)
  2. No drift in the system (inserts/updates/deletes are applied to databases as required)
  3. Feedback that everything was executed correctly (i.e. if some db replication of cli commands fails, I want to know that)

Please note I would like to store configuration as "Desired state", not as migrations to database which insert additional records.

Right now process if following:

1. Create commmit to source-of-truth repository
2. Connect to database
3. Update configuration in database (this is usually lots of records)
4. Ensure configuration is applied correctly (select and verify)
5. Connect to service via ssh
6. Execute command to reload configuration
7. Verify if configuration is reloaded (i.e. run command to dump config and grep new configuraiton entries)
8. Repeat steps 2-7 for multiple datacenters.

I've evaluated some existing tooling, and I cannot find anything good:

  • Infrastructure management tools like terraform are not good for managing contents of databases, and they cannot really call scripts
  • Configuration management tools like ansible are more focused on executing scripts, rather than synchronizing state - i.e. I can apply DB migraiton, but this is very cumbersome (btw, I don't want to configure server instances, but software running on these server instances).

Can you suggest how to automate this system?

2 Answers 2

0

You can use Flyway for database migrations and you could wrap actual migrations as releases.

So something like you have a Flyway container running as a Cron job and if there is new release - it would be applying migrations from the new releases, otherwise it would just use existing migrations - meaning not change the state.

Then you can use Reliza Hub - the tool I am developing - to manage rollout of these migrations to different instances - see a sample here on how promotion logic may work - https://worklifenotes.com/2023/05/12/how-to-promote-software-to-production-with-reliza-hub/

Reliza Hub can also give you Slack / MS Teams notifications of the releases in progress and if there were errors.

You could also add your additional verification steps in the end of each cronjob, although Flyway is already capable to provide feedback if migrations passed or failed.

I hope this gives basic idea on how to automate your process - but feel free to reach out to me with follow up questions.

2
  • The issue I have with flyway and similar toolls like dbroll is that it does not show you how data looks, but your data is sum of migrations. This makes it really hard to understand what's inside that DB. I'd like to have a tool which can take desired state as input and adjust database to match that state.
    – Shadow
    Jun 15, 2023 at 10:55
  • The only tool of this sort that I know of is DACPAC - and that is built for SQL Server specifically and has its own set of issues - making it usually not recommended for complex use-cases. So likely what you are looking for doesn't exist here.
    – taleodor
    Jun 15, 2023 at 11:02
0

Unfortunately "diffing" logical contents of relational databases seems to be a largely unexplored problem. I'd assume because SQL itself is a strictly imperative language and that makes it difficult to act on a difference even if calculating it is technically feasible.

Based on your comment which implies that you require a full view of all the data at the single source of truth at all times, I'd approach the issue using complete "rewrite" migrations instead of incremental ones, which is what tools like Flyway usually expect you to do.

  1. Have a pure-text representation of your complete desired configuration in a git repository. Maybe YAML, maybe TOML, maybe some other format that's more appropriate for your domain, wouldn't really matter as long as it can be parsed programatically.
  2. Write "glue code", for example in Python, which takes the above config and converts it to SQL INSERT statements, then executes them against a target DB server.
  3. On every script run, the script drops all existing configuration tables and completely recreates them. Naturally, all of these operations need to be done in one transaction to avoid downtime.

All this can then be automated with Ansible.

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.