ODOO Database Backup Automatically & Restore Using PSQL

Backup management is one of the essential systems needed for a live ERP. We can discuss about ODOO Database Backup and Restore. If we follow the ODOO conventional method to backup and restore leads to very time consuming and error-prone in case of large databases.

We can find a lot of ODOO backup management modules available in the ODOO market place, but the common thing we can see that they all clubbing dump backup and attachment filestore. Suppose the database size is 25 GB and the filestore is 50 GB, definitely this will break your system while doing restore if you didn’t have a strong ODOO configuration. The restoration process also consumes a lot of time.

Instead of using these error-prone conventional methods, we can do it directly using PSQL queries by compressing data. We can also split the filestore and database backup separately. We can move the backups from a specified path to a local location, remote server location using a secured FTP session, or we can use cloud storage Google Drive / Dropbox.

The next need is to make it an automatic process and also a job needs to do it regularly. We can depend on ODOO CRON jobs to do this repeated process and it gives you the status.

All of these thoughts reached as an ODOO module and you can find it in the ODOO Apps Store.

ODOO Database Backup Configuration
ODOO Database Backup Configuration and status.

You can get the module from this link.

https://apps.odoo.com/apps/modules/13.0/auto_backup_odoo/

Here is the video demonstration of the ODOO module.

How to restore ODOO databases?

The restoring large databases using the ODOO conventional method doesn’t allow to restore backup file formats Plain SQL, or compressed gzip backup. If you got a dump of large database and you are trying to restore using the same method will ends to timeout error.

I can recommend you to restore ODOO database using PSQL queries, which is reliable, fast and efficient. Here I can give you simple methods to restore the databases using PSQL.

Plain SQL Format

The plain text format is useful for very small databases with a minimal number of objects but other than that, it should be avoided.

psql -U username -d database -f backupfile.sql

or

psql database < backupfile.sql

Tar Format

Using this archive format allows reordering and/or exclusion of database objects at the time the database is restored. It is also possible to limit which data is reloaded at restore time. we use tar with gzip

pg_restore -d database backupfile.tar -U username

You can add following parameters

  • -c create a new database before restore
  • -v verbose mode
  • - t particular table only

Compressed Format

This is the most flexible format in that it allows the reordering of loading data as well as to object definitions. This format is also compressed by default.

pg_restore -d database backupfile.sql.gz -U username

or

gunzip -c backupfile.sql.gz | psql -U username -d database

Experienced Python / ODOO Developer with a demonstrated history of working in the information technology and services industry.