Alhaditech

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 

#!/bin/bash
TIMESTAMP=$(date +"%Y-%m-%d_%H_%M_%S")
BACKUP_DIR="/home/odoo/backups"
DATABASE_NAME="test"

# 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

crontab -e
0 * * * * /path/to/backup_script.sh