Database Auto-Backup fails on pg_dump error

[claudiobgit]

Module

Database Auto-Backup

Loaded on a containerized Odoo 14.0-20231106 (CE) official image running with Docker Compose, along with PostgreSQL version 13.13 (Debian 13.13-1.pgdg120+1),
pg_dump (PostgreSQL) is version 16.0 (Debian 16.0-1.pgdg100+1)

Describe the bug

The error shows up if you go to the database management from the login page and start a full db+data backup in ZIP format.
Related to this, the Database Automatic-Backup module fails, even if you try to start the backup manually as a dev user from the db.backup model web page.

What is strange is that the automatic backup worked until around the end of September 2023, after which the files saved are empty.

-rw-r--r-- 1 odoo odoo  49M Sep 25 23:15 2023_09_25_23_15_43.dump.zip
-rw-r--r-- 1 odoo odoo    0 Sep 26 20:22 2023_09_30_20_22_39.dump.zip
-rw-r--r-- 1 odoo odoo    0 Sep 27 23:15 2023_09_30_23_15_38.dump.zip
...

To Reproduce

Affected versions: Odoo 14.0-2023xxxx (CE)

Steps to reproduce the behavior:

1. Go to Odoo login screen
2. Select Database Management and pick a db
3. Click Backup (full data+files in .zip format)

Expected behavior
A backup of the database should be saved in the configured directory without error.

Additional context
Logs from inside the docker db container show:

INFO my.db odoo.service.db: DUMP DB: my.db format zip 
2023-11-26 00:35:49,562 17 ERROR my.db odoo.addons.auto_backup.models.db_backup: Database backup failed: /var/lib/odoo/backups/my.db @ localhost 
Traceback (most recent call last):
  File "/mnt/server-tools/auto_backup/models/db_backup.py", line 213, in backup_log
    yield
  File "/mnt/server-tools/auto_backup/models/db_backup.py", line 169, in action_backup
    self.env.cr.dbname, destiny, backup_format=rec.backup_format
  File "<decorator-gen-21>", line 2, in dump_db
  File "/usr/lib/python3/dist-packages/odoo/service/db.py", line 41, in if_db_mgt_enabled
    return method(self, *args, **kwargs)
  File "/usr/lib/python3/dist-packages/odoo/service/db.py", line 241, in dump_db
    odoo.tools.exec_pg_command(*cmd)
  File "/usr/lib/python3/dist-packages/odoo/tools/misc.py", line 130, in exec_pg_command
    raise Exception('Postgres subprocess %s error %s' % (args2, rc))
Exception: Postgres subprocess ('/usr/bin/pg_dump', '--no-owner', '--file=/tmp/tmpbfshujp6/dump.sql', 'my.db') error 1

A case for pg_dump mismatch of version between the web container running Odoo and the db container running Postgres has been investigated but we have a higher pg_dump version on the web container (v16) than the db container (v13).

What is strange is that by entering the container with:
$ docker exec -it odoo-docker-deployment-web-1 /bin/bash

and executing manually (the shell user is 'odoo') a dump of the db with:
$ pg_dump -h odoo-docker-deployment-db-1 --no-owner --file=/var/lib/odoo/backups/my.db/2023_11_26_00_30_00.dump.zip my.db

then the dump is saved with no errors!

[sysadminmatmoz]

I think this is PostgreSQL >= 16 related, since I encounter this issue on all the servers where PostgreSQL 15 -> 16 db upgrade was performed.
It concerns this module since pg_dump --no-owner -d dbname > date_in_yyyy-mm-dd.dump.sql works fine from command line, but not directly since the problem starts with dump_db method from base odoo db.py service file.

It seems that my case confirms [this issue](TRESCLOUD/odoo@977fe3b effects also raw OS and is related to the pg_dump versions > 16.1.

[badbole]

same happens without this module, resolve it by installing postgres specific client version in odoo container:
instead of postgresql-client use: postgresql-client-13 and all should be good

[badbole]

@claudiobgit did my answer resolve the issue? can you mark it resovled and close or do you still face the same problem?

[claudiobgit]

@claudiobgit did my answer resolve the issue? can you mark it resovled and close or do you still face the same problem?

I didn't want to mess with the db client on the Odoo server and properly install postgresql-client-13 in the odoo container (Debian buster) as it could be critical for other services. So I ended up with a cron job that executes the pg_dump command from the Odoo container and that works perfectly. We can close this issue but not really solved.

[badbole]

FYI.... for odoo 14 suggested postgresql version is 12... and that one works without prolem..
offcourse, newver versions of psql will work with respectful clients installed :)

[claudiobgit]

FYI.... for odoo 14 suggested postgresql version is 12...

This sounds new because we were getting Odoo official images from the hub and the Docker compose yaml was taking in Postgres 13 not 12.