<p>You can use Flyway for database migrations and you could wrap actual migrations as releases.</p>
<p>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.</p>
<p>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 - <a href="https://worklifenotes.com/2023/05/12/how-to-promote-software-to-production-with-reliza-hub/" rel="nofollow noreferrer">https://worklifenotes.com/2023/05/12/how-to-promote-software-to-production-with-reliza-hub/</a></p>
<p>Reliza Hub can also give you Slack / MS Teams notifications of the releases in progress and if there were errors.</p>
<p>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.</p>
<p>I hope this gives basic idea on how to automate your process - but feel free to reach out to me with follow up questions.</p>

CC BY-SA 4.0
2023-06-15T10:55:15.500Z
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
CC BY-SA 4.0
2023-06-15T11:02:02.150Z
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
CC BY-SA 4.0
2023-06-14T21:27:52.807Z
2023-06-14T21:27:52.807Z
taleodor
<p>Unfortunately &quot;diffing&quot; 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.</p>
<p>Based on your <a href="https://devops.stackexchange.com/questions/17976/how-to-implement-simple-distributed-configuration-system#comment17959_17977">comment</a> 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 &quot;rewrite&quot; migrations instead of incremental ones, which is what tools like Flyway usually expect you to do.</p>
<ol>
<li>Have a pure-text representation of your <strong>complete</strong> 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.</li>
<li>Write &quot;glue code&quot;, for example in Python, which takes the above config and converts it to SQL <code>INSERT</code> statements, then executes them against a target DB server.</li>
<li>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.</li>
</ol>
<p>All this can then be automated with Ansible.</p>

CC BY-SA 4.0
2024-03-12T12:22:43.327Z
2024-03-12T12:22:43.327Z
adoring_ellis
<p>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.</p>
<p>I want to automate this system. I have following requirements:</p>
<ol>
<li>Single source of truth (i.e. git repository)</li>
<li>No drift in the system (inserts/updates/deletes are applied to databases as required)</li>
<li>Feedback that everything was executed correctly (i.e. if some db replication of cli commands fails, I want to know that)</li>
</ol>
<p>Please note I would like to store configuration as &quot;Desired state&quot;, not as migrations to database which insert additional records.</p>
<p>Right now process if following:</p>
<pre><code>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.
</code></pre>
<p>I've evaluated some existing tooling, and I cannot find anything good:</p>
<ul>
<li>Infrastructure management tools like terraform are not good for managing contents of databases, and they cannot really call scripts</li>
<li>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).</li>
</ul>
<p>Can you suggest how to automate this system?</p>

<configuration-management><mysql><distributed-systems>
How to implement simple distributed configuration system
Shadow
# How to implement simple distributed configuration system

(Asked by: username_0 on 2023-06-14)

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:

Single source of truth (i.e. git repository)
No drift in the system (inserts/updates/deletes are applied to databases as required)
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?

**Tags:** configuration-management, mysql, distributed-systems


---

## 2 answers

### Answer by username_1 on 2023-06-14. Score: 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.

> **Comments:**
> **username_0**: 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.  
> **username_1**: 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.  

---

### Answer by username_2 on 2024-03-12. Score: 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](https://devops.stackexchange.com/questions/17976/how-to-implement-simple-distributed-configuration-system#comment17959_17977) 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.


---

