Database auto-backup backup process crashes with a database size > 200 MB and Odoo worker enabled.
The module “Database auto-backup” will help you to backup Odoo database on your local disk or remotely on a FTP server through ftp our sftp protocol, however it doesn’t give us the possibility to secure a bit more the backup file generated, therefore I added 2 additional options (Enable password and password) to be able to protect by a password the compressed file by using directly Linux 7z command line and recompressing a 2nd time the file, we should use dump file instead of zip to avoid redundant compression. This customization will handle only the backup on local disk part, ftp way should work as well but I didn’t make the test.
First of all, we’ll need to install p7zip-full package on our system Linux, I’m using Ubuntu, here the command bellow for its installation.
Install system package
apt-get install p7zip-full
Update model
I created a new module named auto_backup_2 and inherit “db.backup” model and override schedule_backup method from module auto_backup. “schedule_backup” method code is almost similar from the original one, modification i made are between additional import and additional code comments as bellow.
from odoo import models, fields, api, tools, _ from odoo.exceptions import Warning import odoo from odoo.http import content_disposition import logging _logger = logging.getLogger(__name__) from ftplib import FTP import os import datetime try: from xmlrpc import client as xmlrpclib except ImportError: import xmlrpclib import time import base64 import socket #####additional import##### import subprocess ########################### try: import paramiko except ImportError: raise ImportError( 'This module needs paramiko to automatically write backups to the FTP through SFTP. Please install paramiko on your system. (sudo pip3 install paramiko)') class db_backup(models.Model): _inherit = 'db.backup' #####additional code##### file_password_enable = fields.Boolean('Enable file password', help="compress the backup file with a password") file_password = fields.Char('File password', help="backup file password") #####additional code##### @api.model def schedule_backup(self): conf_ids = self.search([]) for rec in conf_ids: db_list = self.get_db_list(rec.host, rec.port) if rec.name in db_list: try: if not os.path.isdir(rec.folder): os.makedirs(rec.folder) except: raise # Create name for dumpfile. bkp_file = '%s_%s.%s' % (time.strftime('%Y_%m_%d_%H_%M_%S'), rec.name, rec.backup_type) file_path = os.path.join(rec.folder, bkp_file) uri = 'http://' + rec.host + ':' + rec.port conn = xmlrpclib.ServerProxy(uri + '/xmlrpc/db') bkp = '' try: # try to backup database and write it away fp = open(file_path, 'wb') odoo.service.db.dump_db(rec.name, fp, rec.backup_type) fp.close() #####additional code##### if rec.file_password_enable: _logger.debug(file_path) new_file = file_path + '.7z' re = subprocess.call(['7z', 'a', '-p' + rec.file_password, '-y', new_file] + [file_path]) _logger.debug(re) if os.path.exists(new_file) and re == 0: os.remove(file_path) ########################### except Exception as error: _logger.debug( "Couldn't backup database %s. Bad database administrator password for server running at http://%s:%s" % ( rec.name, rec.host, rec.port)) _logger.debug("Exact error from the exception: " + str(error)) continue else: _logger.debug("database %s doesn't exist on http://%s:%s" % (rec.name, rec.host, rec.port)) # Check if user wants to write to SFTP or not. if rec.sftp_write is True: try: # Store all values in variables dir = rec.folder pathToWriteTo = rec.sftp_path ipHost = rec.sftp_host portHost = rec.sftp_port usernameLogin = rec.sftp_user passwordLogin = rec.sftp_password _logger.debug('sftp remote path: %s' % pathToWriteTo) try: s = paramiko.SSHClient() s.set_missing_host_key_policy(paramiko.AutoAddPolicy()) s.connect(ipHost, portHost, usernameLogin, passwordLogin, timeout=20) sftp = s.open_sftp() except Exception as error: _logger.critical('Error connecting to remote server! Error: ' + str(error)) try: sftp.chdir(pathToWriteTo) except IOError: # Create directory and subdirs if they do not exist. currentDir = '' for dirElement in pathToWriteTo.split('/'): currentDir += dirElement + '/' try: sftp.chdir(currentDir) except: _logger.info('(Part of the) path didn\'t exist. Creating it now at ' + currentDir) # Make directory and then navigate into it sftp.mkdir(currentDir, 777) sftp.chdir(currentDir) pass sftp.chdir(pathToWriteTo) # Loop over all files in the directory. for f in os.listdir(dir): if rec.name in f: fullpath = os.path.join(dir, f) if os.path.isfile(fullpath): try: sftp.stat(os.path.join(pathToWriteTo, f)) _logger.debug( 'File %s already exists on the remote FTP Server ------ skipped' % fullpath) # This means the file does not exist (remote) yet! except IOError: try: # sftp.put(fullpath, pathToWriteTo) sftp.put(fullpath, os.path.join(pathToWriteTo, f)) _logger.info('Copying File % s------ success' % fullpath) except Exception as err: _logger.critical( 'We couldn\'t write the file to the remote server. Error: ' + str(err)) # Navigate in to the correct folder. sftp.chdir(pathToWriteTo) # Loop over all files in the directory from the back-ups. # We will check the creation date of every back-up. for file in sftp.listdir(pathToWriteTo): if rec.name in file: # Get the full path fullpath = os.path.join(pathToWriteTo, file) # Get the timestamp from the file on the external server timestamp = sftp.stat(fullpath).st_atime createtime = datetime.datetime.fromtimestamp(timestamp) now = datetime.datetime.now() delta = now - createtime # If the file is older than the days_to_keep_sftp (the days to keep that the user filled in on the Odoo form it will be removed. if delta.days >= rec.days_to_keep_sftp: # Only delete files, no directories! if sftp.isfile(fullpath) and (".dump" in file or '.zip' in file): _logger.info("Delete too old file from SFTP servers: " + file) sftp.unlink(file) # Close the SFTP session. sftp.close() except Exception as e: _logger.debug('Exception! We couldn\'t back up to the FTP server..') # At this point the SFTP backup failed. We will now check if the user wants # an e-mail notification about this. if rec.send_mail_sftp_fail: try: ir_mail_server = self.env['ir.mail_server'] message = "Dear,\n\nThe backup for the server " + rec.host + " (IP: " + rec.sftp_host + ") failed.Please check the following details:\n\nIP address SFTP server: " + rec.sftp_host + "\nUsername: " + rec.sftp_user + "\nPassword: " + rec.sftp_password + "\n\nError details: " + tools.ustr( e) + "\n\nWith kind regards" msg = ir_mail_server.build_email("auto_backup@" + rec.name + ".com", [rec.email_to_notify], "Backup from " + rec.host + "(" + rec.sftp_host + ") failed", message) ir_mail_server.send_email(self._cr, self._uid, msg) except Exception: pass """ Remove all old files (on local server) in case this is configured.. """ if rec.autoremove: dir = rec.folder # Loop over all files in the directory. for f in os.listdir(dir): fullpath = os.path.join(dir, f) # Only delete the ones wich are from the current database # (Makes it possible to save different databases in the same folder) if rec.name in fullpath: timestamp = os.stat(fullpath).st_ctime createtime = datetime.datetime.fromtimestamp(timestamp) now = datetime.datetime.now() delta = now - createtime if delta.days >= rec.days_to_keep: # Only delete files (which are .dump and .zip), no directories. if os.path.isfile(fullpath) and (".dump" in f or '.zip' in f): _logger.info("Delete local out-of-date file: " + fullpath) os.remove(fullpath)
Update view
The xml code bellow will display our 2 additional fields: “file_password_enable” and “file_password”
<odoo> <data> <record id="view_backup_config_form" model="ir.ui.view"> <field name="name">db.backup.form</field> <field name="model">db.backup</field> <field name="type">form</field> <field name="inherit_id" ref="auto_backup.view_backup_config_form" /> <field name="arch" type="xml"> <data> <xpath expr="//field[@name='days_to_keep']" position="after"> <field name="file_password_enable" /> <field name="file_password" /> </xpath> </data> </field> </record> </data> </odoo>