[Odoo] How to backup and restore large database in Odoo

Through time, Odoo will have a heavy database and filestore, especially with internal projects. Or, maybe we are enable workers so the CPU and memory limitations will cause broken backup and restore. So, we will make another solution through manually backup.

BACKUP

First, we have to understand how Odoo can serve content and functions. In config file, they have data_dir config to make sure all static contents will be stored in one centralize place, if it’s missing, it will cause logo broken, or css cannot load, etc. Then, Postgres database will store all the rest. We have to backup these objects for later use.

Second, identify correctly the config file and find data_dir folder location, using tar command to compress this folder that named followed by using database. Normally, this folder will be stored under data_dir/filestore/

tar -zcf <path to store the compress file>/<file name>.tar.gz <path to folder need to be compressed>

Third, using pg_dump to backup database to .sql file
pg_dump -U <postgres_user> -E <encode like UTF-8> <filename>.sql <database name>

RESTORE

Step 1: locate your datadir in your new environment. Then extract file tar.gz, copy folder filestore to your datadir (remember rename your folder as same as database name)
Example: /home/ubuntu/datadir/<dbname>/filestore
Step 2: access to your database, create a new database under role that you have created to run Odoo
Step 3: restore file sql through command
psql -U <postgres_user> -<database name> < <filename>.sql

CONFIGURATION

Step 1: turn off Internet connection to prevent any un-predicted actions to send messages or queues
Step 2: access to database through your role to run Odoo
Step 3: in table ir_cron, turn all value in column Active to False except record [SYSTEM] Update expired time
Step 4: in table ir.mail_server, delete all records in this table
Step 5: run your Odoo with new database. Go to Settings, remove all keys from Gitlab Credentials, Zoho Credentials, Story Credentials, Slack Credentials. Then in Event Notification, enable Test mode, fill Channel ID with your test channel. Then press Save
Step 6: Access to Job Queue menu, if they have any record that have pending or starting state. Select them all and press Action > Set job to done

1 Like