Restoring Odoo Database from .sql / .dump File

Encountering data corruption or needing to migrate your Odoo server? Restoring from a backup is your first line of defence. Since Odoo uses PostgreSQL as its backend, the restoration process is a core administrative skill.

This step-by-step guide explains how to restore your Odoo database from both .sql and .dump backup files, ensuring your business data is recovered safely.

Critical Check

Before you begin, always create a fresh backup of your current, live database. This ensures you have a rollback point if anything goes wrong during the restoration process.

Prerequisites

Ensure you have the following before starting:

  • PostgreSQL Installed: The same major version as the one that created the backup to avoid compatibility issues.
  • Command-Line Access: You will need access to psql and pg_restore tools.
  • User Privileges: Appropriate privileges for the postgres user (typically sudo access).
  • Your Backup File: The .sql or .dump file you intend to restore.

Step 1: Stop the Odoo Service

First, halt all operations to prevent any new connections or writes to the database. Run:

sudo systemctl stop odoo

Step 2: Create a New Target Database

It's a best practice to restore into a new database rather than overwriting the live one. Connect to PostgreSQL and create it. Run:

sudo -u postgres psql

Inside the psql prompt, create a database, assigning ownership to your Odoo user (commonly odoo):

CREATE DATABASE odoo_restored WITH OWNER odoo;
\q

If you encounter encoding issues, use this command instead:

CREATE DATABASE odoo_restored WITH OWNER odoo ENCODING 'UTF8' TEMPLATE template0;

Step 3: Restore the Backup File

The command you use depends on the format of your backup file.

Case A: Restoring from a Plain Text (.sql) File

Use the psql command to execute the SQL commands in the file.

sudo -u postgres psql -U odoo -d odoo_restored -f /path/to/your/backup.sql
  • -U odoo: Connects as the odoo user.
  • -d odoo_restored: Targets the new database.
  • -f: Specifies the file to execute.
Case B: Restoring from a .dump File (Custom Format)

Use the pg_restore utility for more efficient and flexible restoration from binary dumps.

sudo -u postgres pg_restore -U odoo -d odoo_restored --clean --if-exists /path/to/your/backup.dump
  • --clean: Instructs the tool to drop database objects (like tables) before recreating them, preventing conflicts.
  • --if-exists: Use this with --clean to avoid errors if the objects don't already exist.

Step 4: Configure and Restart Odoo

Point your Odoo server to the newly restored database by updating the db_name parameter in your configuration file.

sudo nano /etc/odoo/odoo.conf # or the path to your odoo.conf file

Find and modify the line:

db_name = odoo_restored

Save the file and restart the Odoo service to apply the changes.

sudo systemctl start odoo

Step 5: Verification

Log in to your Odoo web interface. Select the restored database and thoroughly check that:

  • All expected apps are installed.
  • Critical client data records are present.
  • Configurations and customisations are intact.
  • Any custom modules are present in your addons path and are functional.

Best Practices & Troubleshooting

Always Back Up First

Before any restore operation, always create a fresh backup of your current, live database.

Test Restores Periodically

A backup is only valid if it can be restored. Regularly test your procedure on a staging server.

Match PostgreSQL Versions

Ensure the major version of PostgreSQL on the restoration server matches the one that created the backup.

Verify User Permissions

The odoo PostgreSQL user must be the owner of the new database or have full read/write privileges.

We work with Odoo every day.

Check out our blog for more guides and how-to articles about Odoo.

View more articles

Share

Sign in to leave a comment
Reserve Stock in Odoo Before Sales Order Confirmation