Use a mono-bucket S3 Storage for multiple database dumps

[flazzarini]

Hello,

Firstly, I'd like to express my appreciation for this software. The progress achieved with Greenmask is commendable, and its current state is impressive.

I'm exploring a potential use case and seeking clarification on whether Greenmask supports it or if there are alternative approaches available. Here's the scenario:

Use Case Description

I manage a central PostgreSQL database server housing multiple databases. I aim to back up selected databases to a centralized S3 bucket using Greenmask and potentially anonymize certain data within these databases. This backup process should occur regularly, resulting in a collection of database dumps within the S3 bucket. Additionally, I plan to create READ-only access tokens on S3 so that developers from various application databases can restore specific databases to their local machines.

Configuration Files

Backup / Dump Process Configuration for DB-1 (dump-db1.yaml)

common:
  tmp_dir: ./tmp

dump:
  pg_dump_options:
    dbname: "host=localhost port=50022 user=foobar dbname=foobar"

storage:
  s3:
    endpoint: "http://localhost:50020"
    region: "us-east-1"
    bucket: "coss-db-dumps"
    access_key_id: "full-access-token"
    secret_access_key: "xxxx"

Backup / Dump Process Configuration for DB-2 (dump-db2.yaml)

common:
  tmp_dir: ./tmp

dump:
  pg_dump_options:
    dbname: "host=localhost port=50022 user=foobar dbname=foobaz"

storage:
  s3:
    endpoint: "http://localhost:50020"
    region: "us-east-1"
    bucket: "coss-db-dumps"
    access_key_id: "full-access-token"
    secret_access_key: "xxxx"

Developer Configuration (developer.yaml)

storage:
  s3:
    endpoint: "http://localhost:50020"
    region: "us-east-1"
    bucket: "coss-db-dumps"
    access_key_id: "read-access-token"
    secret_access_key: "yyyy"

Dump Process Execution

The following commands are executed regularly to generate new dumps on the S3 storage within the same bucket:

./greenmask --config dump-db1.yaml dump
./greenmask --config dump-db2.yaml dump

User Perspective

To restore the latest dump of a particular database, a user needs to list all available dumps and select the appropriate ID. For example:

./greenmask --config developer.yaml list-dumps

Suppose a developer wishes to restore the latest dump of the 'foobaz' database. In that case, they would initiate a command like this:

./greenmask --config developer.yaml restore -h localhost -p 50023 -U foobar -d foobaz 1712319103281

Question

Is it feasible to introduce a command that simplifies this workflow? For instance, a way to indicate that the user intends to restore the latest dump of a specific database ('foobaz' in this case)?

Thank you in advance for any suggestions on streamlining this process.

[wwoytenko]

Hi @flazzarini ! Thank you for your appreciation, we really value it!

The problem is that transformations are related to the database and the config (including connection params) has to point to the specific database. If we introduce the transformation for a multiple database in the. one config file it may complicate the user experience because we will be forced to somehow match the transformation with the specific database.

But I think we can take into account developers' experience when they just fetching the database dump from mono-bucket. In that case would be fine to be able to get the latest dump for a database name provided.

Assuming we have storage.s3.prefix config parameter we could develop this into a kind of feature. By applying it you can at least isolate the backups for various databases in mono-bucket.

The config for the dump might be

Config for foobar

common:
  tmp_dir: ./tmp

dump:
  pg_dump_options:
    dbname: "host=localhost port=50022 user=foobar dbname=foobaz"

storage:
  s3:
    prefix: "foobar"
    endpoint: "http://localhost:50020"
    region: "us-east-1"
    bucket: "coss-db-dumps"
    access_key_id: "full-access-token"
    secret_access_key: "xxxx"

By applying prefix you can successfully use the latest keyword without fear that there is an unexpected database.

The next obstacle is - how to simplify the config file usage experience. Where we need only one config file and a specific database restoration request.

I suspect we could introduce the parameter --storage-s3-prefix that could be used for setting up the storage.s3.prefix parameter in the config allowing the developer to use the next call restoring dump for both databases using latest keyword.

./greenmask --config developer.yaml restore --storage-s3-prefix='foobaz' -h localhost -p 50023 -U foobar -d foobaz latest

What do you think about it?

Feel free to share your ideas, concerns and doubts about implementation.

[wwoytenko]

Another problem. Currently, if we want to isolate the dump for specific databases we have to take into account storage.s3.prefix parameter. And for directory storage the prefix parameter is not implemented at all.

I think we should do the following:

• Introduce prefix parameter for all storage
• Add one more level in path. For instance {{ prefix }}/{{ databaseName }}/{{ dumpId }}. This will automatically isolate the dumps by specific database name.
• Introduce the restore parameter for a specific database. For instance greenmask restore --original-database=foobar latest
• Parameter --original-database should not be required

I think it should solve your UX problem

[flazzarini]

Hi @wwoytenko, yes this sounds like a valid option.

Related to the use-case I've mentioned in the initial discussion I think a command line flag --original-database could align well with the use-case I've had in mind.

Additionally I saw that there was already a merge request related to this #62 which as far as I understood is a step forward to this feature, which is great to see.

Thanks for your comments.

[joao-zanutto]

Hey @flazzarini and @wwoytenko while evaluating the possible impact for the implementations suggested in this discussion, I came across a concern: what should be the expected behavior of a command like list-dumps that isn't based on a specific database? Currently, only dump and restore commands expect postgres connection arguments (dbname, host, user, and so on) by design.

Considering this concern, imagine we implement the new suggested storage pattern: {{ prefix }}/{{ databaseName }}/{{ dumpId }}, when we execute greenmask list-dumps, what should the command do?

• Crawl through the subfolders in the {{ prefix }}/ folder and print all dumps for all databases
• Return an error saying that {{ databaseName }} is a required argument
• Print all the folders with {{ databaseName }} and ask the user for a specific database
• Any other possible behavior?

Another concern: what if the user wants to restore a dump in the .../{{ databaseName }}/ to a different target database. Let's say the user dumped a database named originalDB and then want to restore it to a brand new created database named targetDB? (is the new flag --original-database proposed by @wwoytenko supposed to solve this issue? I couldn't completely comprehend the use case for this flag)

Having those concerns in mind, can you think of other commands that should be affected by this change? Is there already a behavior you thought for those commands?

[wwoytenko]

The parameter gonna be logical and not required. When using if you wish to determine the source database name, then the operation will be performed with the provided name. It does not somehow affect the dump or restore connection string. If you do not provide the database name, then the last backup will be chosen whether the database name is

[wwoytenko]

The algorithm

apply dbname filter if provided and crawl through the subfolders in the {{ prefix }}/ folder and print all dumps for all databases

[wwoytenko]

The list of affected commands

• Dump
• Restore
• Validate
• Show dump
• List dump

[wwoytenko]

The other way that could simply the experience is to implement the --storage-prefix parameter that will append the prefix in the storage

All the dump config will have to contain the prefix parameter specified in the storage config

[joao-zanutto]

@wwoytenko I've added two issues so we can discuss each of the proposed solutions separately:

• Add db metadata to storage path #68
• Add prefix to storage config #69

Should we add another issue for the --original-database flag or does it fall under the (#68) issue

I've also created a new PR (#67) with one possible way to implement a solution for (#68), but since I was unsure if the path I was taking was the proper one, I decided to first bring up a conversation before continuing any further development effort. I've added a concern I had with this implementation to the issue, if you have any easier way to solve it, please let me know