Logic Uses Username as a DB name

[matt-laird]

Summary

When a backup of my Postgres DB occurs, I get an error on Postgres side saying that a specific target DB doesn't exist. However, the DB that is being incorrectly targeted is the given username.

Steps to reproduce

1. Setup a backup using the below provided env var.
2. Allow backup to occur and observe the error message on the Container & Postgres instance respectively. Provided below.

What is the expected correct behavior?

Do not use the given username as a target DB.

Relevant logs and/or screenshots

Container

...
2022-12-21.23:20:57 [NOTICE] ** [timezone] Timezone: Setting to 'Africa/Johannesburg' from 'Etc/GMT'
2022-12-22.01:20:57 [NOTICE] ** [scheduling] Container configured for scheduled tasks with 'cron'
2022-12-22.01:20:59 [STARTING] ** [scheduling] [1] Starting cron
2022-12-22.01:21:03 [INFO] ** [db-backup] Next Backup at 2022-12-22 01:21:03 SAST
2022-12-22.01:21:04 [NOTICE] ** [db-backup] Dumping all PostgreSQL databases: 'nextcloud' and compressing with 'zstd'
psql: error: connection to server at "db" (172.16.28.3), port 5432 failed: FATAL:  database "nextcloud_owner" does not exist
...

Postgres

...
2022-12-21 23:21:04.062 UTC [25527] FATAL:  database "nextcloud_owner" does not exist
...

Environment

Env vars:

  TIMEZONE=Africa/Johannesburg
  CONTAINER_ENABLE_MONITORING=FALSE
  SPLIT_DB=FALSE
  DB_TYPE=pgsql
  DB_HOST=db
  DB_PORT=5432
  DB_NAME=nextcloud
  DB_USER=nextcloud_owner
  DB_PASS=[REDACTED]
  DB_DUMP_FREQ=180
  DB_CLEANUP_TIME=1440
  CHECKSUM=SHA1
  COMPRESSION=ZSTD
  COMPRESSION_LEVEL=6

• Image version / tag: <= 3.7.3
• Host OS: Debian

Possible fixes

I think the problem lies somewhere here?

docker-db-backup/install/assets/functions/10-db-backup

Line 266 in a90e520

authdb=${DB_USER}

[Iceman248]

I just found this and tried getting it going and I have this same issue. What is the resolution?

[tiredofit]

You can try enabling DEBUG_MODE=TRUE and in the wave of output look to see what the pg_dump command is doing - That will help see what the problem is and I can provide a fix.

[Iceman248]

Okay, will try. Thanks. On the subject of pg_dump, our first read in the official docs then unless you specify to backup everything in the database, it isn't a 100% backup. Is this a complete backup of anything I would need?

[tiredofit]

I suppose there could be some cases where something couldn't be backed up - perhaps indexes, or some sort of stored procedures - There is an EXTRA_OPTS variable that lets you append anything onto the dump command for those sort of cases. I have yet to run into it myself, but there are may users of this image, so perhaps they will give some insight..

[Iceman248]

I was able to work around just by naming all the databases specifically. Even just naming any made it work. ALL for some reason has it trying the username as the db_name, but only when ALL is in db_name.

Reading about complete backup, the pg_dumpall, I was referring to the following:

"pg_dump dumps only a single database at a time, and it does not dump information about roles or tablespaces (because those are cluster-wide rather than per-database). To support convenient dumping of the entire contents of a database cluster, the pg_dumpall program is provided. pg_dumpall backs up each database in a given cluster, and also preserves cluster-wide data such as role and tablespace definitions. The basic usage of this command is:"

Is there a way to run a dumpall with this? I don't know how important what would missing would be, but if it was a problem if it wasn't there, then the backup would be incomplete, which of course I don't want.

[dabico]

Just wanted to report that I'm also facing the same issue. I get the error @Iceman248 posted, but upon inspecting the created dump, all the database data is... present? Weird...

[rursache]

Having the same issue, FATAL: database "my actual username" does not exist

@tiredofit can you check this up?

[gregblake]

@rursache Does the workaround @Iceman248 mentioned in this comment work for you? I want to make sure you're unblocked, too. I encountered this bug, and the above workaround successfully backs up multiple databases if $DB_NAME is a comma-separated list.

For example DB_NAME=foo,bar will create backups for the databases named "foo" and "bar.

This section of the documentation explains the different DB_NAME options in a bit more detail: https://github.com/tiredofit/docker-db-backup/tree/a90e52091d480f50a1400ce4104134ebfa567389#database-specific-options

[rursache]

@gregblake Does the workaround @Iceman248 mentioned in this comment work for you?

it does, indeed. not as elegant as using ALL but whatever works :)

example for future readers:

docker run -d \
  --name postgresql-backup \
  --restart always \
  -e DB_TYPE=pgsql \
  -e DB_HOST=IP \
  -e DB_PORT=PORT \
  -e DB_NAME=freshrss,homeassistant,mastodon,nextcloud,postgres \
  -e DB_USER=USER \
  -e DB_PASS=PASS \
  -e DB_DUMP_FREQ=4320 \
  -e DB_CLEANUP_TIME=43200 \
  -e ENABLE_CHECKSUM=FALSE \
  -e COMPRESSION=GZ \
  -e CONTAINER_ENABLE_MONITORING=FALSE \
  -e TZ=Europe/Bucharest \
  -v path/to/backups:/backup \
  tiredofit/db-backup:latest

[matt-laird]

@rursache Does the workaround @Iceman248 mentioned in this comment work for you?

Workaround doesn't work for those that have one DB targeted, as I raised it, I'm selecting one DB only in my setup, and it seems to exhibit two strange behaviours. One is the original issue (using the username as a DB name) and the other is that the backup logic automatically uses ALL instead of the only given name outline, only if there is one DB name given.

2023-02-16.21:17:28 [NOTICE] ** [db-backup] Dumping all PostgreSQL databases: 'outline' and compressing with 'zstd'
psql: error: connection to server at "db" (0.0.0.0), port 5432 failed: FATAL:  database "outline_owner" does not exist
2023-02-16.21:17:35 [INFO] ** [db-backup] DB Backup of 'pgsql_all_db_20230216-211728.sql.zst' completed successfully
2023-02-16.21:17:35 [NOTICE] ** [db-backup] Generating SHA1 for 'pgsql_all_db_20230216-211728.sql.zst'
2023-02-16.21:17:35 [NOTICE] ** [db-backup] Backup of pgsql_all_db_20230216-211728.sql.zst created with the size of 183507 bytes
2023-02-16.21:17:35 [NOTICE] ** [db-backup] DB Backup for 'all' time taken: Hours: 0 Minutes: 00 Seconds: 07
2023-02-16.21:17:35 [INFO] ** [db-backup] Backup routines finish time: 2023-02-16 21:17:35 SAST with overall exit code 0
2023-02-16.21:17:35 [NOTICE] ** [db-backup] Backup routines time taken: Hours: 0 Minutes: 00 Seconds: 07
2023-02-16.21:17:35 [INFO] ** [db-backup] Cleaning up old backups on filesystem
2023-02-16.21:17:35 [NOTICE] ** [db-backup] Sleeping for another 10793 seconds. Waking up at 2023-02-17 00:17:28 SAST 

[wue]

I am having the same issue and I think the underlying problem is the same as pointed out here: #135

• (somewhat obvious but maybe worth pointing out) if DB_NAME and DB_USER are the same, it works (assuming that you don't use ALL as your db name).
• If you don't use ALL as the DB_NAME AND you specify SPLIT_DB=TRUE it should work (still waiting for a backup job to finish in order to verify)
  • Using ALL runs into

    docker-db-backup/install/assets/functions/10-db-backup

    Line 269 in a90e520

    db_names=$(psql -h ${DB_HOST} -U ${DB_USER} -p ${DB_PORT} -d ${authdb} -c 'COPY (SELECT datname FROM pg_database WHERE datistemplate = false) TO STDOUT;' )

    where it uses -d ${authdb}
  • Using SPLIT_DB=FALSE runs into

    docker-db-backup/install/assets/functions/10-db-backup

    Line 343 in c1d2dc3

    tmp_db_names=$(psql -h ${DB_HOST} -U ${DB_USER} -p ${DB_PORT} -d ${authdb} -c 'COPY (SELECT datname FROM pg_database WHERE datistemplate = false) TO STDOUT;' )

    where it uses -d ${authdb}

[tiredofit]

Release 4.0.0 https://github.com/tiredofit/docker-db-backup/releases/tag/4.0.0 has support for different authentication DBs for Postgresql, courtesy of @alwynpan

[matt-laird]

This issue has been resolved with the latest major version 4.x