borgmatic-collective/borgmatic
borgmatic-collective/borgmatic
Watch
9
74
Fork
You've already forked borgmatic
 57
Code Issues 71 Pull requests 3 Releases 162 Packages 1 Activity Actions 1

Database "missing from archive" #985

New issue
Closed
opened 2025-02-03 03:23:40 +00:00 by enieuwy · 8 comments
enieuwy commented 2025-02-03 03:23:40 +00:00
Copy link

What I'm trying to do and why

I'm trying to restore a database to the original containerised postgres).

Steps to reproduce

Config is:

postgresql_databases:
  - name: authentik
    hostname: authentik-postgresql-1
    username: authentik
    password: <redacted>

Actual behavior

# borgmatic restore --repository borgwarehouse --archive latest --database authentik
borgwarehouse: Error running actions for repository
Cannot restore data source dump authentik@localhost missing from archive
/etc/borgmatic.d/config.yaml: An error occurred

summary:
/etc/borgmatic.d/config.yaml: An error occurred
borgwarehouse: Error running actions for repository
Cannot restore data source dump authentik@localhost missing from archive

Need some help? https://torsion.org/borgmatic/#issues

Expected behavior

Database appears successfully backed up to archive:

# borgmatic list --repository borgwarehouse --archive latest --find *borgmatic/*_databases
borgwarehouse: Searching archives
ssh://borgwarehouse@192.168.50.10:2222/./d1916b04: Listing archive backup-2025-02-02T01:01:44
drwxr-xr-x root   root          0 Sun, 2025-02-02 01:01:13 borgmatic/mongodb_databases
drwx------ root   root          0 Sun, 2025-02-02 01:01:13 borgmatic/mongodb_databases/librechat-mongodb:27017
-rw------- root   root    5830906 Sun, 2025-02-02 01:01:13 borgmatic/mongodb_databases/librechat-mongodb:27017/LibreChat
drwxr-xr-x root   root          0 Sun, 2025-02-02 01:01:13 borgmatic/postgresql_databases
drwx------ root   root          0 Sun, 2025-02-02 01:01:13 borgmatic/postgresql_databases/authentik-postgresql-1
-rw------- root   root    2333102 Sun, 2025-02-02 01:01:13 borgmatic/postgresql_databases/authentik-postgresql-1/authentik
drwx------ root   root          0 Sun, 2025-02-02 01:01:13 borgmatic/postgresql_databases/solidtime-database-1
-rw------- root   root      65648 Sun, 2025-02-02 01:01:13 borgmatic/postgresql_databases/solidtime-database-1/solidtime
drwx------ root   root          0 Sun, 2025-02-02 01:01:13 borgmatic/postgresql_databases/librechat-vectordb
-rw------- root   root       3983 Sun, 2025-02-02 01:01:13 borgmatic/postgresql_databases/librechat-vectordb/mydatabase

Other notes / implementation ideas

No response

borgmatic version

# borgmatic --version 1.9.5

borgmatic installation method

Container

Borg version

# borg --version borg 1.4.0

Python version

# python3 --version Python 3.12.8

Database version (if applicable)

# psql --version psql (PostgreSQL) 17.2 (borgmatic container)
# psql --version psql (PostgreSQL) 16.6 (origin db container)

Operating system and version

Debian 12 (host)

### What I'm trying to do and why I'm trying to restore a database to the original containerised postgres). ### Steps to reproduce Config is: ``` postgresql_databases: - name: authentik hostname: authentik-postgresql-1 username: authentik password: <redacted> ``` ### Actual behavior ``` # borgmatic restore --repository borgwarehouse --archive latest --database authentik borgwarehouse: Error running actions for repository Cannot restore data source dump authentik@localhost missing from archive /etc/borgmatic.d/config.yaml: An error occurred summary: /etc/borgmatic.d/config.yaml: An error occurred borgwarehouse: Error running actions for repository Cannot restore data source dump authentik@localhost missing from archive Need some help? https://torsion.org/borgmatic/#issues ``` ### Expected behavior Database appears successfully backed up to archive: ``` # borgmatic list --repository borgwarehouse --archive latest --find *borgmatic/*_databases borgwarehouse: Searching archives ssh://borgwarehouse@192.168.50.10:2222/./d1916b04: Listing archive backup-2025-02-02T01:01:44 drwxr-xr-x root root 0 Sun, 2025-02-02 01:01:13 borgmatic/mongodb_databases drwx------ root root 0 Sun, 2025-02-02 01:01:13 borgmatic/mongodb_databases/librechat-mongodb:27017 -rw------- root root 5830906 Sun, 2025-02-02 01:01:13 borgmatic/mongodb_databases/librechat-mongodb:27017/LibreChat drwxr-xr-x root root 0 Sun, 2025-02-02 01:01:13 borgmatic/postgresql_databases drwx------ root root 0 Sun, 2025-02-02 01:01:13 borgmatic/postgresql_databases/authentik-postgresql-1 -rw------- root root 2333102 Sun, 2025-02-02 01:01:13 borgmatic/postgresql_databases/authentik-postgresql-1/authentik drwx------ root root 0 Sun, 2025-02-02 01:01:13 borgmatic/postgresql_databases/solidtime-database-1 -rw------- root root 65648 Sun, 2025-02-02 01:01:13 borgmatic/postgresql_databases/solidtime-database-1/solidtime drwx------ root root 0 Sun, 2025-02-02 01:01:13 borgmatic/postgresql_databases/librechat-vectordb -rw------- root root 3983 Sun, 2025-02-02 01:01:13 borgmatic/postgresql_databases/librechat-vectordb/mydatabase ``` ### Other notes / implementation ideas _No response_ ### borgmatic version `# borgmatic --version 1.9.5` ### borgmatic installation method Container ### Borg version `# borg --version borg 1.4.0` ### Python version `# python3 --version Python 3.12.8` ### Database version (if applicable) `# psql --version psql (PostgreSQL) 17.2` (borgmatic container) `# psql --version psql (PostgreSQL) 16.6` (origin db container) ### Operating system and version Debian 12 (host)
witten commented 2025-02-03 04:30:07 +00:00
Owner
Copy link

Thanks for filing this again and including all the details! So I think I found the problem for real now. It turns out that you don't need --hostname authentik-postgresql-1.. you need --original-hostname authentik-postgresql-1. The former is for specifying the destination hostname to restore to, while the latter is for disambiguating/specifying which database dump you want to restore. Confusing, I know. It tripped me up too.. and I implemented the damned thing!

Anyway, let me know if --original-hostname works for you.

In regards to the "original" hostname defaulting to localhost, maybe this would be less confusing and more intuitive if instead an unspecified original hostname defaulted to matching dumps of all hostnames. That would align more with your first attempt of just restoring the database by name. I haven't taken the time to think through the implications in terms of how feasible/practical that would be, but I wanted to get your take on such a change since you were directly bitten by this. Thanks!

Thanks for filing this [again](https://github.com/borgmatic-collective/docker-borgmatic/issues/388) and including all the details! So I think I found the problem for real now. It turns out that you don't need `--hostname authentik-postgresql-1`.. you need `--original-hostname authentik-postgresql-1`. The former is for specifying the destination hostname to restore to, while the latter is for disambiguating/specifying which database dump you want to restore. Confusing, I know. It tripped me up too.. and I implemented the damned thing! Anyway, let me know if `--original-hostname` works for you. In regards to the "original" hostname defaulting to `localhost`, maybe this would be less confusing and more intuitive if instead an unspecified original hostname defaulted to matching dumps of *all* hostnames. That would align more with your first attempt of just restoring the database by name. I haven't taken the time to think through the implications in terms of how feasible/practical that would be, but I wanted to get your take on such a change since you were directly bitten by this. Thanks!
enieuwy commented 2025-02-03 05:53:54 +00:00
Author
Copy link

Really appreciate your support! A new error (looks like progress):

# borgmatic restore --repository borgwarehouse --archive latest --database authentik --original-hostname authentik-postgresql-1
borgwarehouse: Error running actions for repository
Command 'pg_restore --no-password --if-exists --exit-on-error --clean --dbname authentik --host authentik-postgresql-1 --username authentik' returned non-zero exit status 1.
/etc/borgmatic.d/config.yaml: An error occurred

summary:
/etc/borgmatic.d/config.yaml: An error occurred
borgwarehouse: Error running actions for repository
pg_restore: error: could not execute query: ERROR:  unrecognized configuration parameter "transaction_timeout"
Command was: SET transaction_timeout = 0;
Command 'pg_restore --no-password --if-exists --exit-on-error --clean --dbname authentik --host authentik-postgresql-1 --username authentik' returned non-zero exit status 1.

Need some help? https://torsion.org/borgmatic/#issues
Really appreciate your support! A new error (looks like progress): ``` # borgmatic restore --repository borgwarehouse --archive latest --database authentik --original-hostname authentik-postgresql-1 borgwarehouse: Error running actions for repository Command 'pg_restore --no-password --if-exists --exit-on-error --clean --dbname authentik --host authentik-postgresql-1 --username authentik' returned non-zero exit status 1. /etc/borgmatic.d/config.yaml: An error occurred summary: /etc/borgmatic.d/config.yaml: An error occurred borgwarehouse: Error running actions for repository pg_restore: error: could not execute query: ERROR: unrecognized configuration parameter "transaction_timeout" Command was: SET transaction_timeout = 0; Command 'pg_restore --no-password --if-exists --exit-on-error --clean --dbname authentik --host authentik-postgresql-1 --username authentik' returned non-zero exit status 1. Need some help? https://torsion.org/borgmatic/#issues ```
witten commented 2025-02-03 06:06:54 +00:00
Owner
Copy link

Okay, I was afraid of this. It sounds like a PostgreSQL 17 client can't restore to a PostgreSQL 16 database either. From the PostgreSQL docs:

Also, it is not guaranteed that pg_dump's output can be loaded into a server of an older major version — not even if the dump was taken from a server of that version. Loading a dump file into an older server may require manual editing of the dump file to remove syntax not understood by the older server.

So, options here:

  • You could potentially upgrade your Authentik PostgreSQL database to the same major version as the borgmatic container.
  • You could build your own borgmatic container (based on this one you're using) with an older version of the PostgreSQL clients installed to match your Authentik PostgreSQL.
  • You could try adding --quote-all-identifiers to the configured dump command and then creating a new backup. I don't know if that would help, but the PostgreSQL docs seem to indicate it might in certain cross-major-version cases.
Okay, I was afraid of this. It sounds like a PostgreSQL 17 client can't restore to a PostgreSQL 16 database either. From [the PostgreSQL docs](https://www.postgresql.org/docs/current/app-pgdump.html#notes): > Also, it is not guaranteed that pg_dump's output can be loaded into a server of an older major version — not even if the dump was taken from a server of that version. Loading a dump file into an older server may require manual editing of the dump file to remove syntax not understood by the older server. So, options here: * You could potentially upgrade your Authentik PostgreSQL database to the same major version as the borgmatic container. * You could build your own borgmatic container (based on this one you're using) with an older version of the PostgreSQL clients installed to match your Authentik PostgreSQL. * You could try adding `--quote-all-identifiers` to the configured dump command and then creating a new backup. I don't know if that would help, but the PostgreSQL docs seem to indicate it might in certain cross-major-version cases.
enieuwy commented 2025-02-03 06:35:03 +00:00
Author
Copy link

I modified Authentik docker compose to use pgsql 17, then recreated the database (docker compose down --volumes and docker compose up -d).

Ran borgmatic restore --repository borgwarehouse --archive latest --database authentik --original-hostname authentik-postgresql-1 again, and IT WORKED! Hallelujah! Thank you very much for your generous support—saved days of work!

I modified Authentik docker compose to use pgsql 17, then recreated the database (`docker compose down --volumes` and `docker compose up -d`). Ran `borgmatic restore --repository borgwarehouse --archive latest --database authentik --original-hostname authentik-postgresql-1` again, and IT WORKED! Hallelujah! Thank you very much for your generous support—saved days of work!
enieuwy commented 2025-02-03 06:39:25 +00:00
Author
Copy link

How can I buy you a coffee?

How can I buy you a coffee?
witten commented 2025-02-03 06:52:29 +00:00
Owner
Copy link

I'm so glad to hear that worked! I'll leave this ticket open to make that tweak to the --original-hostname default as described above. And I appreciate the offer, but no coffee is needed. Really. All I request is that you spread the word about borgmatic! (And if you've got cash burning a hole in your pocket, donate to the excellent Borg project that makes all of this possible.)

I'm so glad to hear that worked! I'll leave this ticket open to make that tweak to the `--original-hostname` default as described above. And I appreciate the offer, but no coffee is needed. Really. All I request is that you spread the word about borgmatic! (And if you've got cash burning a hole in your pocket, [donate to the excellent Borg project](https://www.borgbackup.org/support/fund.html) that makes all of this possible.)
❤️ 1
witten commented 2025-02-03 19:19:17 +00:00
Owner
Copy link

Okay, the --original-hostname tweak is in place and will be part of the next release. Now, you don't have specify an original hostname with restore if there's a single archive dump match without the hostname.

Okay, the `--original-hostname` tweak is in place and will be part of the next release. Now, you don't have specify an original hostname with `restore` if there's a single archive dump match without the hostname.
witten commented 2025-02-03 19:28:58 +00:00
Owner
Copy link

Released in borgmatic 1.9.9!

Released in borgmatic 1.9.9!
Sign in to join this conversation.
No Branch/Tag specified
Branches Tags
main
parallel-borgmatic
command-hook-steps
2.1.7
2.1.6
2.1.5
2.1.4
2.1.3
2.1.2
2.1.1
2.1.0
2.0.13
2.0.12
2.0.11
2.0.10
2.0.9
2.0.8
2.0.7
2.0.6
2.0.5
2.0.4
2.0.3
2.0.2
2.0.1
2.0.0
1.9.14
1.9.13
1.9.12
1.9.11
1.9.10
1.9.9
1.9.8
1.9.7
1.9.6
1.9.5
1.9.4
1.9.3
1.9.2
1.9.1
1.9.0
1.8.14
1.8.13
1.8.12
1.8.11
1.8.10
1.8.9
1.8.8
1.8.7
1.8.6
1.8.5
1.8.4
1.8.3
1.8.2
1.8.1
1.8.0
1.7.15
1.7.14
1.7.13
1.7.12
1.7.11
1.7.10
1.7.9
1.7.8
1.7.7
1.7.6
1.7.5
1.7.4
1.7.3
1.7.2
1.7.1
1.7.0
1.6.6
1.6.5
1.6.4
1.6.3
1.6.2
1.6.1
1.6.0
1.5.24
1.5.23
1.5.22
1.5.21
1.5.20
1.5.19
1.5.18
1.5.17
1.5.16
1.5.15
1.5.14
1.5.13
1.5.12
1.5.11
1.5.10
1.5.9
1.5.8
1.5.7
1.5.7.dev0
1.5.6
1.5.5
1.5.4
1.5.3
1.5.2
1.5.1
1.5.0
1.4.22
1.4.21
1.4.20
1.4.19
1.4.18
1.4.17
1.4.16
1.4.15
1.4.14
1.4.13
1.4.12
1.4.11
1.4.10
1.4.9
1.4.8
1.4.7
1.4.6
1.4.5
1.4.4
1.4.3
1.4.2
1.4.1
1.4.0
1.3.26
1.3.25
1.3.24
1.3.23
1.3.22
1.3.21
1.3.20
1.3.19
1.3.18
1.3.17
1.3.16
1.3.15
1.3.14
1.3.13
1.3.12
1.3.11
1.3.10
1.3.9
1.3.8
1.3.7
1.3.6
1.3.5
1.3.4
1.3.3
1.3.2
1.3.1
1.3.0
1.2.18
1.2.17
1.2.16
1.2.15
1.2.14
1.2.13
1.2.12
1.2.11
1.2.10
1.2.9
1.2.8
1.2.7
1.2.6
1.2.5
1.2.4
1.2.3
1.2.2
1.2.1
1.2.0
1.1.15
1.1.14
1.1.13
1.1.12
1.1.11
1.1.10
1.1.9
1.1.7
1.1.6
1.1.5
1.1.4
1.1.3
1.1.2
1.1.1
1.1.0
1.0.3
1.0.2
1.0.1
1.0.0
0.1.8
0.1.7
0.1.6
0.1.5
0.1.4
0.1.3
0.1.2
0.1.1
0.1.0
0.0.7
0.0.6
0.0.5
0.0.4
0.0.3
0.0.2
Labels
Clear labels   
blocked

   
breaking

   
bug

   
data loss

   
design finalized

   
good first issue

   
new feature area

   
question / support

   
security

   
waiting for response
No labels
blocked
breaking
bug
data loss
design finalized
good first issue
new feature area
question / support
security
waiting for response
Milestone
Clear milestone
No items
No milestone
Assignees
Clear assignees
No assignees
2 participants
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set

Reference
borgmatic-collective/borgmatic#985
No description provided.