Odoo database backup and restore using terminal
In this blog post, we delve into the intricacies of recovering Odoo databases using the command line interface, often referred to as the terminal. While recovering a database from the front end is a common practice, it might prove inadequate when dealing with substantial database sizes that could potentially impact performance and efficiency. Whether you're facing a data corruption issue, a database crash, or simply need to restore a large database backup, utilizing the terminal for recovery can be a lifesaver.
We will guide you through a step-by-step process of recovering an Odoo database via the terminal, providing you with the knowledge and tools necessary to navigate this essential aspect of database management. By the end of this guide, you'll have a solid understanding of the terminal-based recovery process, enabling you to tackle even the most complex database recovery scenarios with confidence.
So, if you're ready to empower yourself with the skills to efficiently recover Odoo databases, let's embark on this journey through the world of terminal-based database recovery.
Step 1: Download the database backup file.
Go to: /web/database/manager and click backup.
Enter Your Master Password and select the backup format (.zip or .dump)
Step 2: Open Terminal and Run this command
sudo -u postgres psql
Step 3: Create a New Database
Create database rest_db;
This will create a new database with user Postgres.
Step 4: Open a Terminal on the location where your backup file is, and run this command.
sudo su postgres
pg_restore -U postgres -d rest_db -1 your_backup_file.dump
Note: If you get Access denied as a result, move the backup file to /var/tmp and perform Step 4 again.
Step 5:Change the owner of the database to what is mentioned in .config file.
sudo -u postgres psql
ALTER DATABASE rest_db OWNER TO new_owner_name;
(In my case, db_user in .config file is hp)
ALTER DATABASE rest_db OWNER TO hp;
(Use \l to list databases and \c to connect to a particular database)
Step 6: Check Database
Go to: /web/database/selector and check if you see the database (rest_db) in available databases.
For server level db script and auto run that script follow below steps
# Perform the backup
pg_dump -U odoo -F c -b -v -f $BACKUP_DIR/test_$TIMESTAMP.dump $DATABASE_NAME
after adding above script run command to make it executable
sudo chmod +x backup_script.sh
Then setup crone job for auto schedule
0 * * * * /path/to/backup_script.sh