postgres backup questions #581

New Issue

felixx9 · 2022-09-06T09:42:15Z

felixx9 commented

2022-09-06 09:42:15 +00:00

What I'm trying to do and why

Backup a postres database cluster (pg_dumpall) because having backups is livesaver. Different server, completely different system and service, than discussed in #580

Normal file backups are working since days, "native" dumps, created by a cron, included. So I'm safe.
Now I want to add postgres dump functionality by borgmagic, but I struggle with password / superuser - not sure how to do it.

Steps to reproduce (if a bug)

use b3vis/borgmatic
superuser is postgres

added in config:

hooks:
    postgresql_databases:
        - name: all
          username: postgres
          password: ${MY_DATABASE_PASSWORD}
          format: tar
          options: "--role=postgres"

password is provided in .env and docker-compose.yml - even have it directly in config does not work.

Actual behavior (if a bug)

Output:

wmsuser@wmsserver:~/docker-borgmatic/base$ sudo docker exec borgmatic sh -c "cd && borgmatic --stats -v 1 --files 2>&1"
ssh://DOMAIN/./REPO: Pruning archives
------------------------------------------------------------------------------
                       Original size      Compressed size    Deduplicated size
Deleted data:                    0 B                  0 B                  0 B
All archives:              199.38 GB            108.07 GB             10.34 GB
                       Unique chunks         Total chunks
Chunk index:                   80302               856891
------------------------------------------------------------------------------
ssh://DOMAIN/./REPO: Compacting segments
/etc/borgmatic.d/config.yaml: Running command for pre-backup hook
Starting a backup job.
ssh://DOMAIN/./REPO: Creating archive
ssh://DOMAIN/./REPO: Removing PostgreSQL database dumps
ssh://DOMAIN/./REPO: Dumping PostgreSQL databases
Creating archive at "ssh://DOMAIN/./REPO::backup-2022-09-06T09:05:11"
A /mnt/source/etc/systemd/system/accounts-daemon.service
M /mnt/source/etc/borgmatic.d/config.yaml

[...] # reducted, files getting backuped

A /root/.borgmatic/postgresql_databases/localhost/all
------------------------------------------------------------------------------
Repository: ssh://DOMAIN/./REPO
Archive name: backup-2022-09-06T09:05:11
Archive fingerprint: 7c76e8b6db1b3c9c2aab01ad0158287157c0eedfe71ff16cd8d54d4e88785de2
Time (start): Tue, 2022-09-06 09:05:14
Time (end):   Tue, 2022-09-06 09:05:30
Duration: 16.31 seconds
Number of files: 61089
Utilization of max. archive size: 0%
------------------------------------------------------------------------------
                       Original size      Compressed size    Deduplicated size
This archive:               15.22 GB              8.10 GB             34.49 MB
All archives:              214.60 GB            116.17 GB             10.37 GB
ssh://DOMAIN/./REPO: Error running actions for repository
Command 'pg_dumpall --no-password --clean --if-exists --username postgres --role=postgres > /root/.borgmatic/postgresql_databases/localhost/all' returned non-zero exit status 1.
/etc/borgmatic.d/config.yaml: Running command for on-error hook
Error while creating a backup.
/etc/borgmatic.d/config.yaml: Error running configuration file

summary:
/etc/borgmatic.d/config.yaml: Error running configuration file
ssh://DOMAIN/./REPO: Error running actions for repository
Command 'pg_dumpall --no-password --clean --if-exists --username postgres --role=postgres > /root/.borgmatic/postgresql_databases/localhost/all' returned non-zero exit status 1.

some v2-Details:

ssh://DOMAIN/./REPO: Dumping PostgreSQL database all to /root/.borgmatic/postgresql_databases/localhost/all
pg_dumpall --no-password --clean --if-exists --username postgres --role=postgres > /root/.borgmatic/postgresql_databases/localhost/all
borg create --exclude-from /tmp/tmp2g41ckia --compression lz4 --one-file-system --read-special --list --filter AME- --stats --debug --show-rc ssh://DOMAIN/./REPO::backup-{now} /mnt/source/etc /mnt/source/home /mnt/source/srv /mnt/source/var /root/.borgmatic
using builtin fallback logging configuration

[...]

A /root/.borgmatic/postgresql_databases/localhost/all
Remote: check_free_space: few segments, not requiring a full free segment
Remote: check_free_space: calculated working space for compact as 545259520 bytes
Remote: check_free_space: required bytes 566240208, free bytes 339680153600
security: saving state for 4338e3f9c66233f79b638480295b8f7c5e452cb6eded199b806b1cb9dff759ad to /root/.config/borg/security/4338e3f9c66233f79b638480295b8f7c5e452cb6eded199b806b1cb9dff759ad
security: current location   ssh://DOMAIN/./REPO
security: key type           5
security: manifest timestamp 2022-09-06T09:29:45.553809
Remote: Verified integrity of /home/borg/ssc_servers/wmsserver/index.276
------------------------------------------------------------------------------
Repository: ssh://DOMAIN/./REPO
Archive name: backup-2022-09-06T09:29:28
Archive fingerprint: 9e5ad8697ebd817cd4bcae71da042310b82d8dde530df087b4503fbd375ff82d
Time (start): Tue, 2022-09-06 09:29:31
Time (end):   Tue, 2022-09-06 09:29:45
Duration: 14.45 seconds
Number of files: 61251
Utilization of max. archive size: 0%
------------------------------------------------------------------------------
                       Original size      Compressed size    Deduplicated size
This archive:               15.23 GB              8.10 GB              6.02 MB
All archives:              214.63 GB            115.82 GB             10.37 GB
ssh://DOMAIN/./REPO: Error running actions for repository
Command 'pg_dumpall --no-password --clean --if-exists --username postgres --role=postgres > /root/.borgmatic/postgresql_databases/localhost/all' returned non-zero exit status 1.
/etc/borgmatic.d/config.yaml: Running command for on-error hook
echo "Error while creating a backup."
Error while creating a backup.
/etc/borgmatic.d/config.yaml: Error running configuration file
/etc/borgmatic.d/config.yaml: No commands to run for post-everything hook

summary:
/etc/borgmatic.d/config.yaml: Error running configuration file
ssh://DOMAIN/./REPO: Error running actions for repository
Command 'pg_dumpall --no-password --clean --if-exists --username postgres --role=postgres > /root/.borgmatic/postgresql_databases/localhost/all' returned non-zero exit status 1.

Expected behavior (if a bug)

successful dump

Other notes / implementation ideas

not sure, what to do different, to have the dump run as superuser postgres
For creating manual dump I run sudo -u postgres pg_dumpall...

Environment

borgmatic version: 1.7.1

borgmatic installation method: Docker b3vis/borgmatic

Borg version: borg 1.2.2

Python version: Python 3.10.6

Database version (if applicable): psql (PostgreSQL) 10.21 (Ubuntu 10.21-0ubuntu0.18.04.1)

operating system and version: see above

#### What I'm trying to do and why Backup a postres database cluster (pg_dumpall) because having backups is livesaver. Different server, completely different system and service, than discussed in #580 Normal file backups are working since days, "native" dumps, created by a cron, included. So I'm safe. Now I want to add postgres dump functionality by borgmagic, but I struggle with password / superuser - not sure how to do it. #### Steps to reproduce (if a bug) - use b3vis/borgmatic - superuser is `postgres` added in config: ``` hooks: postgresql_databases: - name: all username: postgres password: ${MY_DATABASE_PASSWORD} format: tar options: "--role=postgres" ``` password is provided in .env and docker-compose.yml - even have it directly in config does not work. #### Actual behavior (if a bug) Output: ``` wmsuser@wmsserver:~/docker-borgmatic/base$ sudo docker exec borgmatic sh -c "cd && borgmatic --stats -v 1 --files 2>&1" ssh://DOMAIN/./REPO: Pruning archives ------------------------------------------------------------------------------ Original size Compressed size Deduplicated size Deleted data: 0 B 0 B 0 B All archives: 199.38 GB 108.07 GB 10.34 GB Unique chunks Total chunks Chunk index: 80302 856891 ------------------------------------------------------------------------------ ssh://DOMAIN/./REPO: Compacting segments /etc/borgmatic.d/config.yaml: Running command for pre-backup hook Starting a backup job. ssh://DOMAIN/./REPO: Creating archive ssh://DOMAIN/./REPO: Removing PostgreSQL database dumps ssh://DOMAIN/./REPO: Dumping PostgreSQL databases Creating archive at "ssh://DOMAIN/./REPO::backup-2022-09-06T09:05:11" A /mnt/source/etc/systemd/system/accounts-daemon.service M /mnt/source/etc/borgmatic.d/config.yaml [...] # reducted, files getting backuped A /root/.borgmatic/postgresql_databases/localhost/all ------------------------------------------------------------------------------ Repository: ssh://DOMAIN/./REPO Archive name: backup-2022-09-06T09:05:11 Archive fingerprint: 7c76e8b6db1b3c9c2aab01ad0158287157c0eedfe71ff16cd8d54d4e88785de2 Time (start): Tue, 2022-09-06 09:05:14 Time (end): Tue, 2022-09-06 09:05:30 Duration: 16.31 seconds Number of files: 61089 Utilization of max. archive size: 0% ------------------------------------------------------------------------------ Original size Compressed size Deduplicated size This archive: 15.22 GB 8.10 GB 34.49 MB All archives: 214.60 GB 116.17 GB 10.37 GB ssh://DOMAIN/./REPO: Error running actions for repository Command 'pg_dumpall --no-password --clean --if-exists --username postgres --role=postgres > /root/.borgmatic/postgresql_databases/localhost/all' returned non-zero exit status 1. /etc/borgmatic.d/config.yaml: Running command for on-error hook Error while creating a backup. /etc/borgmatic.d/config.yaml: Error running configuration file summary: /etc/borgmatic.d/config.yaml: Error running configuration file ssh://DOMAIN/./REPO: Error running actions for repository Command 'pg_dumpall --no-password --clean --if-exists --username postgres --role=postgres > /root/.borgmatic/postgresql_databases/localhost/all' returned non-zero exit status 1. ``` some v2-Details: ``` ssh://DOMAIN/./REPO: Dumping PostgreSQL database all to /root/.borgmatic/postgresql_databases/localhost/all pg_dumpall --no-password --clean --if-exists --username postgres --role=postgres > /root/.borgmatic/postgresql_databases/localhost/all borg create --exclude-from /tmp/tmp2g41ckia --compression lz4 --one-file-system --read-special --list --filter AME- --stats --debug --show-rc ssh://DOMAIN/./REPO::backup-{now} /mnt/source/etc /mnt/source/home /mnt/source/srv /mnt/source/var /root/.borgmatic using builtin fallback logging configuration [...] A /root/.borgmatic/postgresql_databases/localhost/all Remote: check_free_space: few segments, not requiring a full free segment Remote: check_free_space: calculated working space for compact as 545259520 bytes Remote: check_free_space: required bytes 566240208, free bytes 339680153600 security: saving state for 4338e3f9c66233f79b638480295b8f7c5e452cb6eded199b806b1cb9dff759ad to /root/.config/borg/security/4338e3f9c66233f79b638480295b8f7c5e452cb6eded199b806b1cb9dff759ad security: current location ssh://DOMAIN/./REPO security: key type 5 security: manifest timestamp 2022-09-06T09:29:45.553809 Remote: Verified integrity of /home/borg/ssc_servers/wmsserver/index.276 ------------------------------------------------------------------------------ Repository: ssh://DOMAIN/./REPO Archive name: backup-2022-09-06T09:29:28 Archive fingerprint: 9e5ad8697ebd817cd4bcae71da042310b82d8dde530df087b4503fbd375ff82d Time (start): Tue, 2022-09-06 09:29:31 Time (end): Tue, 2022-09-06 09:29:45 Duration: 14.45 seconds Number of files: 61251 Utilization of max. archive size: 0% ------------------------------------------------------------------------------ Original size Compressed size Deduplicated size This archive: 15.23 GB 8.10 GB 6.02 MB All archives: 214.63 GB 115.82 GB 10.37 GB ssh://DOMAIN/./REPO: Error running actions for repository Command 'pg_dumpall --no-password --clean --if-exists --username postgres --role=postgres > /root/.borgmatic/postgresql_databases/localhost/all' returned non-zero exit status 1. /etc/borgmatic.d/config.yaml: Running command for on-error hook echo "Error while creating a backup." Error while creating a backup. /etc/borgmatic.d/config.yaml: Error running configuration file /etc/borgmatic.d/config.yaml: No commands to run for post-everything hook summary: /etc/borgmatic.d/config.yaml: Error running configuration file ssh://DOMAIN/./REPO: Error running actions for repository Command 'pg_dumpall --no-password --clean --if-exists --username postgres --role=postgres > /root/.borgmatic/postgresql_databases/localhost/all' returned non-zero exit status 1. ``` #### Expected behavior (if a bug) successful dump #### Other notes / implementation ideas not sure, what to do different, to have the dump run as superuser `postgres` For creating manual dump I run `sudo -u postgres pg_dumpall...` #### Environment **borgmatic version:** 1.7.1 **borgmatic installation method:** Docker b3vis/borgmatic **Borg version:** borg 1.2.2 **Python version:** Python 3.10.6 **Database version (if applicable):** psql (PostgreSQL) 10.21 (Ubuntu 10.21-0ubuntu0.18.04.1) **operating system and version:** see above

witten commented

2022-09-06 16:13:35 +00:00

Postgres has multiple ways of authenticating connections, and I'm not sure which ones you have enabled. Can I get a peek at your Postgres pg_hba.conf file where that's configured?

My guess based on the behavior you're seeing is that Postgres is configured to accept postgres superuser connections only from processes running as the postgres Unix user. But the borgmatic container you're using isn't running as that user (which is fine and expected). Instead (or in addition), Postgres needs to be configured to accept connections over TCP. This is controlled by the method in pg_hba.conf.

Postgres has multiple ways of authenticating connections, and I'm not sure which ones you have enabled. Can I get a peek at your Postgres `pg_hba.conf` file where that's configured? My guess based on the behavior you're seeing is that Postgres is configured to accept `postgres` superuser connections only from processes running as the `postgres` Unix user. But the borgmatic container you're using isn't running as that user (which is fine and expected). Instead (or in addition), Postgres needs to be configured to accept connections over TCP. This is controlled by the `method` in `pg_hba.conf`.

witten added the

question / support

label 2022-09-06 16:13:42 +00:00

felixx9 commented

2022-09-08 20:58:46 +00:00

got it.
was a tiny almost invisible mistake in hostname config.
Sorry about that an thx for your help.

got it. was a tiny almost invisible mistake in hostname config. Sorry about that an thx for your help.

felixx9 closed this issue

2022-09-08 20:58:46 +00:00

witten commented

2022-09-10 00:57:22 +00:00

Glad to hear that did it!