Tecnativa/doodba

Backups not working in 15.0 with postgresql 14

rquevedo opened this issue · 18 comments

rquevedo commented

When use 15.0 image and postgresql 14.0 backups fails with error:

Command 'set -euo pipefail; psql -0Atd postgres -c "SELECT datname FROM pg_database WHERE NOT datistemplate AND datname != 'postgres'" | grep --null-data --invert-match -E "$^" | xargs -0tI DB pg_dump --dbname DB --no-owner --no-privileges --file "/mnt/backup/src/DB.sql"' returned non-zero exit status 123. pg_dump --dbname prod --no-owner --no-privileges --file /mnt/backup/src/prod.sql pg_dump: error: server version: 14.5; pg_dump version: 13.1 pg_dump: error: aborting because of server version mismatch
Seems that latest backup image need an pg_dump update to manage postgresql 14

Tardo commented

Use the "db" service instead of odoo

rquevedo commented

@Tardo could you elaborate your answer please? I don't understand you. Thanks in advance.

Tardo commented

Sorry, I thought this was another repo. ** You can use an external pg_dump.

pedrobaeza commented

I think it's relevant. In a scaffolding with doodba-copier-template, the backup should be done on the db container, not on odoo, but there's not enough information in the issue to judge.

rquevedo commented

@pedrobaeza This is he content of .copier-answers.yml file inside scaffolding:

# Changes here will be overwritten by Copier; NEVER EDIT MANUALLY
_commit: v5.1.2
_src_path: gh:Tecnativa/doodba-copier-template
backup_deletion: true
backup_dst: mega://backups@mega.nz/BACKUPS/www.client1.mykubux.com
backup_email_from: null
backup_email_to: null
backup_image_version: latest
backup_tz: UTC
cidr_whitelist: null
domains_prod:
- hosts:
  - www.client1.mykubux.com
- hosts:
  - client1.mykubux.com
  redirect_to: www.client1.mykubux.com
domains_test: null
gitlab_url: null
odoo_dbfilter: ^prod
odoo_initial_lang: es_ES
odoo_listdb: false
odoo_oci_image: null
odoo_proxy: traefik
odoo_version: 15.0
paths_without_crawlers: []
postgres_cidr_whitelist: null
postgres_dbname: prod
postgres_exposed: false
postgres_exposed_port: 5432
postgres_username: odoo
postgres_version: 14
project_author: Ferialike SRL
project_license: OPL-1.0
project_name: client1
smtp_canonical_default: null
smtp_canonical_domains: null
smtp_default_from: null
smtp_relay_host: null
smtp_relay_port: 587
smtp_relay_user: null
smtp_relay_version: '10'

In my opinion, the relevant part is postgres_version: 14. While postgresql server version can be selected, the postgresql-client installed inside backup image(i think that pg_dump command happens in this image) is fixed in 13.1 version.

@Tardo meanwhile how can i back up instance using external pg_dump?

ap-wtioit commented

@Tardo this line https://github.com/Tecnativa/doodba/blob/master/15.0.Dockerfile#L230 was added to be able to use a postgres client in the odoo image that matches the server version (and avoid this exact problem) for pg_dump. So setting the postgres version to the one the server uses (14) should ideally make the pg_dump version in odoo to be able to dump the database. #365

@rquevedo you would need to install pg_dump with version 14 (postgres) and access the db container with it's docker IP and the corrrect credentials.

Tardo commented

@ap-wtioit Yes, from what I have seen the problem is that in doodba-copier-template is not using that environment variable. So, it is always "latest".

  • 👍1
pedrobaeza commented

Any of you can contribute to change that variable when PG version selected is higher than the current package one?

ap-wtioit commented

Not sure how to do this in copier templates (i never succeeded in testing them locally otherwise i would have proposed a few fixes already). But here is the related code we are using in our scaffolding.
common.yaml:

services:
  odoo:
    ...
    build:
      ...
      args:
        ...
        DB_VERSION: ${DB_VERSION}
        ...
    db:
      image: .../postgres:${DB_VERSION}
      ...
...

.env:

...
# Database
DB_VERSION=...
DB_USER=...
...

This makes sure that the DB_VERSION (postgres client) in the odoo service matches the DB_VERSION for the db service (for us only in dev) and by setting the DB_VERSION in .env to the same (major) our server has we got rid of the.

But we are also not using the backup service in prod / common. (We have our own scripts running with the odoo image)

So to solve this maybe someone also would need to fix the backup service + image:

...
  backup:
    image: ghcr.io/tecnativa/docker-duplicity-postgres...
...
Tardo commented

If no one has done it, I'll do the PR later

Tardo commented

@ap-wtioit How do you have the definition of the Dockerfile?

ap-wtioit commented

@Tardo appart from modifications not relevant to this issue (some with open PRs to Tecnativa/doodba) we are using the odoo Dockerfiles from this project. e.g. https://github.com/Tecnativa/doodba/blob/master/15.0.Dockerfile with the build arg as described in the above common.yaml (DB_VERSION: ${DB_VERSION} and then set in .env with DB_VERSION=...). This triggers the install (onbuild in scaffolding) with apt-get install -yqq postgresql-client-$DB_VERSION in build.d/250-postgres-client .

Tardo commented

@ap-wtioit I understand, thank you. What I was afraid of... the way doodba-copier-template works... doodba would need to create images for each version of postgres-client :/

https://github.com/Tecnativa/doodba-copier-template/blob/main/odoo/Dockerfile

** EDIT: Need check some stuff... maybe I'm wrong.

pedrobaeza commented

I still stand in my question: if we put the latest PG tools versions, it will serve for all the PG DBs? If so, we don't need multiple images, but one with the latest PG tools.

ap-wtioit commented

@pedrobaeza if you do this you also need to test if a restore is possible with the postgres server version in use. i think that's why i decided to implement it this way. as far a i remember you need a version >= server to be able to create a backup/dump but for restore you need the pg_dump version top be <= server.

see:

so using the newest version would allow to create a "backup" but it would not be restorable in the server version (if that is not the pg_dump version)

rquevedo commented

Hi folks, i need at least a temporary solution to this bug. I have two production instances without backup at this time. Double reading the full issue i understand that backup is made in odoo container but in my tests the backup seems to be created in backup container, this has more sense. For example, the error (when execute daily scripts) says:

Command 'set -euo pipefail; psql -0Atd postgres -c "SELECT datname FROM pg_database WHERE NOT datistemplate AND datname != 'postgres'" | grep --null-data --invert-match -E "$^" | xargs -0tI DB pg_dump --dbname DB --no-owner --no-privileges --file "/mnt/backup/src/DB.sql"' returned non-zero exit status 123.
pg_dump --dbname prod --no-owner --no-privileges --file /mnt/backup/src/prod.sql
pg_dump: error: server version: 14.5; pg_dump version: 13.1
pg_dump: error: aborting because of server version mismatch

pg_dump version is 13.1 like in backup container:

cloud@srv:~/$ docker exec -it partner_backup_1 /bin/bash
bash-5.0# pg_dump --version
pg_dump (PostgreSQL) 13.1
bash-5.0#

If i do the same in odoo container the result is 15.2:

cloud@srv:~/$ docker exec -it partner_odoo_1 /bin/bash
odoo@ff374170a4a9:/opt/odoo$ pg_dump --version
pg_dump (PostgreSQL) 15.2 (Debian 15.2-1.pgdg110+1)
odoo@ff374170a4a9:/opt/odoo$

So, i think that backup is made in backup container.
Starting from this point, the possible solution is install the correct 'postgresql-client' in alpine backup image.
This brings other problem, the alpine version used is 3.12 and 'postgresql-client' package in its repository has a 13.1 version. Besides as i know 'postgresql14-client', the version that is needed when postgresql 14 is used, is not available for 3.12, only from 3.13 or later.

Means the solution implies make an upgrade in docker file to backup image.

pedrobaeza commented

This should be solved since Tecnativa/doodba-copier-template#369, so closing.