Database names configurable with wildcards

powerriegel commented

2024-12-09 09:42:53 +00:00

What I'd like to do and why

I'd like to be able to configure database names with wildcards, e.g. koha_* oder wp_%. The reason is I run a very large DB server and the backups took long (currently 10min, but still growing). I need to have different backup sets to avoid conflicts. Currently, I need to configure each single DB name in the borgmatic config. There are over 100 DBs.

It would be straightforward to just configure their prefixes.

Other notes / implementation ideas

No response

### What I'd like to do and why I'd like to be able to configure database names with wildcards, e.g. `koha_*` oder `wp_%`. The reason is I run a very large DB server and the backups took long (currently 10min, but still growing). I need to have different backup sets to avoid conflicts. Currently, I need to configure each single DB name in the borgmatic config. There are over 100 DBs. It would be straightforward to just configure their prefixes. ### Other notes / implementation ideas _No response_

witten commented

2024-12-09 15:24:40 +00:00

Owner

Could you say a little more about this request? For instance, which database system are you using? How would you expect the wildcards to work? What would fill them in? What would each of * and % indicate? Etc.

Thanks!

Could you say a little more about this request? For instance, which database system are you using? How would you expect the wildcards to work? What would fill them in? What would each of `*` and `%` indicate? Etc. Thanks!

powerriegel commented

2024-12-09 15:39:40 +00:00

Author

I'm using MariaDB. It's not important which placeholder character you are using, % (more SQL specific) or *(more commonly used). I use * here.wp_* as database name should match all databases beginning with wp_. * means an arbitrary number ob characters, in RegEx [0-9a-z_-]* .

mariadb_databases:
    - name: wp_*

is much simpler than


mariadb_databases:
    - name: wp_customer1
    - name: wp_customer2
    - name: wp_customer3
    - name: wp_customer4
...
    - name: wp_customerX

if you have hundreds of DB names.

Additionally you could also implement postfix matching but I don't know if this is practically relevant. Then, *_wp should match all databases ending with _wp.

I'm using `MariaDB`. It's not important which placeholder character you are using, `%` (more SQL specific) or `*`(more commonly used). I use `*` here.`wp_*` as database name should match all databases beginning with `wp_`. `*` means an arbitrary number ob characters, in RegEx `[0-9a-z_-]* `. ``` mariadb_databases: - name: wp_* ``` is much simpler than ``` mariadb_databases: - name: wp_customer1 - name: wp_customer2 - name: wp_customer3 - name: wp_customer4 ... - name: wp_customerX ``` if you have hundreds of DB names. Additionally you could also implement postfix matching but I don't know if this is practically relevant. Then, `*_wp` should match all databases ending with `_wp`.

witten commented

2024-12-09 15:50:53 +00:00

Owner

Got it, thanks for the explanation! I can definitely see the utility of that. There is the existing support for a all databases, so I could see the wildcard syntax you're proposing being the general case. For instance, maybe all could be replaced with just * once wildcards are implemented.

I happen to be working on #418 now, which means I'm currently in the guts of borgmatic's database name logic. So there might be a good opportunity to follow that work with this wildcard support.

Got it, thanks for the explanation! I can definitely see the utility of that. There is the existing support for a `all` databases, so I could see the wildcard syntax you're proposing being the general case. For instance, maybe `all` could be replaced with just `*` once wildcards are implemented. I happen to be working on #418 now, which means I'm currently in the guts of borgmatic's database name logic. So there might be a good opportunity to follow that work with this wildcard support.

👍 1

witten added the

new feature area

label

2024-12-27 04:24:31 +00:00

powerriegel commented

2025-07-08 12:35:44 +00:00

Author

Any news about this?

witten commented

2025-07-08 15:48:32 +00:00

Owner

No news. PRs welcome though. 😄

ntninja commented

2026-07-03 19:31:13 +00:00

A workaround for this is to set psql_command to this Python script:

#!/usr/bin/env python3
PSQL = "psql"

import sys
if sys.argv[1] == "--list":
	import csv
	import locale
	import subprocess

	# Retrieve database list from original command
	result = subprocess.run(
		(PSQL, *sys.argv[1:]),
		encoding = locale.getpreferredencoding(),
		stdout = subprocess.PIPE,
	)
	if result.returncode != 0:
		sys.exit(result.returncode)

	# Filter returned CSV list of tables
	writer = csv.writer(sys.stdout)
	for row in csv.reader(result.stdout.split("\n")):
		if len(row) < 1 or row[0] == "magnetico":  # ← Database to exclude, negate second term to include instead
			continue
		writer.writerow(row)
else:
	# Not a database list request, defer to original command
	import os
	os.execlp(PSQL, PSQL, *sys.argv[1:])

A workaround for this is to set `psql_command` to this Python script: ```py #!/usr/bin/env python3 PSQL = "psql" import sys if sys.argv[1] == "--list": import csv import locale import subprocess # Retrieve database list from original command result = subprocess.run( (PSQL, *sys.argv[1:]), encoding = locale.getpreferredencoding(), stdout = subprocess.PIPE, ) if result.returncode != 0: sys.exit(result.returncode) # Filter returned CSV list of tables writer = csv.writer(sys.stdout) for row in csv.reader(result.stdout.split("\n")): if len(row) < 1 or row[0] == "magnetico": # ← Database to exclude, negate second term to include instead continue writer.writerow(row) else: # Not a database list request, defer to original command import os os.execlp(PSQL, PSQL, *sys.argv[1:]) ```

ntninja commented

2026-07-03 19:42:50 +00:00

The database matching logic isn’t that complicated though, I might make a PR for this at some point (and use this e-mail as reminder)

Rows
Columns

Database names configurable with wildcards #951

What I'd like to do and why

Other notes / implementation ideas