Leveraging Odoo.sh Database Backups for Advanced BI and Data Lakes

Odoo.sh is an enterprise ready platform that provides businesses with the ability to deploy their own cloud platform for Odoo, equipped with development environments, email servers, branch management, monitoring, and more, without the hassle associated with managing their own servers for their production ERP.

Odoo.sh restricts access to the PostgreSQL database for security reasons. However, in the realm of advanced BI and data lakes, data access is often a requirement. The challenge then lies in maintaining Odoo.sh's convenience while ensuring data availability and compatibility with external tools.

As a side note, Odoo offers plenty of internal reporting tools including pivot tables, graphs, dashboards, and most importantly Odoo Sheets. When that is not enough, this article explores additional options. Keep in mind, Odoo does offer on-premise hosting if real time direct database access is required.

When it comes to data extraction there are two main avenues to explore. One involves leveraging Odoo's External API to extract only the pertinent data. This would typically be the preferred method of extraction when very specific data points are needed. The other, which we'll delve into in this article, revolves around accessing your Odoo backups and restoring them on an external server—giving you the ability to connect directly to the entirety of your data using external tools.

This article is meant to be beginner friendly. Readers may only need certain parts of this article that are relevant to their situation. The overall goal is to provide one step-by-step example of accessing your Odoo data via backups.

Here's a technical overview of our journey:

1. Odoo.sh Backup:

Odoo.sh offers daily backups with a retention policy of up to 3 months. We'll explore where these backups are located.

2. Amazon EC2 Server Setup:

We'll configure an Amazon EC2 instance to automate backups, encompassing AMI selection, key pair creation, and security group settings for SSH access.

3. Amazon RDS Postgres Database:

To restore backups, an Amazon RDS Postgres database is created, aligning with Odoo's PostgreSQL version. Proper configurations and connectivity settings are ensured.

4. SSH Connections:

SSH connections are established between the local machine and the EC2 instance, leveraging a private key file and the instance's Public IPv4 DNS.

5. Connecting to Odoo via SSH:

SSH key generation, along with adding the public key to the Odoo SH profile, enables SSH access to the Odoo server.

6. Connecting to Amazon RDS Database:

Installation of an SQL client on the EC2 instance facilitates the testing of connections to the Amazon RDS database using credentials configured during RDS setup.

7. Installing sshpass for Non-Interactive Authentication:

The 'sshpass' tool is installed to enable secure non-interactive password-based authentication for automated SSH connections.

8. Automating Backup Retrieval and Restoration:

Automation scripts are created to copy, unzip, and restore backups from the Odoo server to the Amazon RDS Postgres database. The process includes error handling and verification steps.

9. Cron Job Scheduling:

Cron job scheduling on the EC2 server automates the execution of backup scripts at designated daily intervals.

10. Conclusion:

Completion of this journey empowers users to automate Odoo.sh backup and restoration processes, ensuring data availability for advanced analytics and integration with data lakes or other systems. We'll consider some possible connection points.

Locating Your Odoo.sh Backup

Production builds are backed up every 24 hours. We keep 14 full backups of each Odoo production instance for up to 3 months: 1/day for 7 days, 1/week for 4 weeks, 1/month for 3 months.

You can access your Odoo backups from the user interface on your SH instance, pictured below.

Backups are also accessible through the Odoo provided shell. The shell can be accessed via the SH platform or by using SSH which we will explore later.

Once you are connected to your server you can view your daily backups by navigating to the 'backup.daily' directory, pictured below.

The name of your daily backup will always be consistent, assuming you don't change the build. This will make it easier for us to copy the backup later on. We will use the compressed database file with the '.sql.gz' extension.

Setting Up an Amazon EC2 Server

In order to automate this process we will need access to a server than can run a cron job. You can elect to use your local machine or any other server. I am simply using the EC2 server because it is quick and easy to configure. If you already have a server you can skip this step.

You will need an AWS account. I created an account using the free tier.

Begin by searching for EC2 in the search console and navigate to the EC2 service. Click on the "Launch an Instance" button to build your server. You can follow the images below to get started.

Once you have completed the above steps, launch your instance. The server will initialize and be available shortly. Next, let's create a database.

Setting Up an Amazon RDS Postgres Database

A Postgres database is needed to restore our backup to. This is the database we will use to connect to our data lake or analytic tools. If you already have a database on amazon, your local machine, or on another server you can skip this step.

Begin by searching for RDS in the search console and navigate to the RDS service. Click on the "Create Database" button to build your database. You can follow the images below to get started.

It is safe to assume that any settings I didn't cover above were left as the default configurations.

Once you have completed the above steps click the "Create database" button.

In later steps we will need several data points including the database host or endpoint, port, user, password, and database name.

The database host and port can be found by navigating to your newly created database and viewing the connectivity & security settings. The database name, user, and password we entered during the creation steps above.

Here are the values for my database:

• Database name: postgres
• Endpoint: odoo-back-up-db.cg0dua4tsuvv.us-east-2.rds.amazonaws.com
• Port: 5432
• User: kevinzaki
• Password: Odootest123!

Connecting to EC2 via SSH

Now that we have our server and database configured we can begin the real work.

Let's begin by connecting to our newly created EC2 via SSH from our local machine. I am using Terminal on my Mac. You can utilize any SSH client.

Earlier a ".pem" file was downloaded to your local machine during the server setup. We will need the path to this file in to connect to our server. You will also need the Public IPv4 DNS which is available in your server details.

Here are my values:

• Private Key File: ~/Downloads/odoo-backup-test.pem
• DNS: ec2-18-119-160-34.us-east-2.compute.amazonaws.com

Before we can connect we need to ensure that our private key file is secured. Run the following command.

# Command
chmod 600 /path/key-pair-name.pem

# Example
chmod 600 ~/Downloads/odoo-backup-test.pem        

Now we will connect to the server.

# Command
ssh -i /path/key-pair-name.pem instance-user-name@instance-public-dns-name

# Example
ssh -i ~/Downloads/odoo-backup-test.pem [email protected]        

In the above example we connect as the default ec2 user "ec2-user". You may be prompted to confirm the connection during your first attempt.

You should now be connected.

Connecting to Odoo via SSH

In the section we will connect to our Odoo instance using SSH from our EC2 server.

In your shell, run the following command to generate an SSH key. Note, you need to use the email addresses connected to your github account you used to create your Odoo SH instance.

# Command
ssh-keygen -t ed25519 -C [email protected]

# Example
ssh-keygen -t ed25519 -C [email protected]

# result
Generating public/private ed25519 key pair.
Enter file in which to save the key (/home/ec2-user/.ssh/id_ed25519): 
Enter passphrase (empty for no passphrase): odootest
Enter same passphrase again: odootest
Your identification has been saved in /home/ec2-user/.ssh/id_ed25519
Your public key has been saved in /home/ec2-user/.ssh/id_ed25519.pub
The key fingerprint is:
SHA256:hAOQ8lDrleqbUmzcbWkgedZBvxKUCW5NbAUHcG6m+R4 [email protected]

# note: press enter on all prompts for default / blank values        

The key pair was generated. We now need to access the public key and add it to Odoo SH in order to connect.

Note, we entered in a passphrase that we will use later. If you choose not to enter a password you can skip the sshpass section and edit the final script to use scp directly.

Run the following commands and copy the result.

# Command
cd .ssh
cat id_ed25519.pub

# result
ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAIM+OMzWwR5MjGBwGvCYWbPRpItH+MnXXyq2c2SKYIR7V [email protected]        

Next, log into Odoo.sh and navigate to your profile from the top right corner of the screen. Under SSH Keys paste in the result from the previous step and click add.

Now that we have added our key we can connect to our Odoo server via SSH. To connect, all we need to do is paste in the Odoo provided command into our shell which is connected to our EC2 server. The command is available at the top of your Odoo SH environment. It is unique per build.

Once you have pasted in the command you should be connected. You can log out by using the exit command.

Connecting to your Amazon RDS Postgres DB from EC2

Before we continue, let's test the connection from our Amazon EC2 sever to our Amazon RDS hosted database. To do that we will use the credentials we saved earlier in this guide.

Before we can connect we need to install an SQL client. From your shell run the following command.

# Command
sudo dnf install postgresql15        

Now we can connect to our database. Note, I am setting the password as a parameter for simplicity. A safer way to do this is by storing the password in a protected file and referencing the file.

Run the following command.

# Command
PGPASSWORD=your-password psql -h db-endpoint -p port-number -U username -d initial-db-name

# Example
PGPASSWORD=Odootest123! psql -h odoo-back-up-db.cg0dua4tsuvv.us-east-2.rds.amazonaws.com -p 5432 -U kevinzaki -d postgres        

You should now be connected. To exit use the \q command.

Installing sshpass for Non-Interactive Password Authentication

We're almost there. The last thing we need to do is install sshpass. Typically, an SSH connection requires a password to be keyed in on each connection. In order to automate this process we can utilize sshpass which will allow us to provide the password as a parameter.

There are many easier ways to install sshpass on other servers. However, at the time of writing this article I needed to follow the steps below. If you need additional assistance with this step you can reference this post .

# Commands

wget https://sourceforge.net/projects/sshpass/files/sshpass/1.10/sshpass-1.10.tar.gz/download
mv download sshpass-1.10.tar.gz
gunzip sshpass-1.10.tar.gz
tar xvf sshpass-1.10.tar
sudo yum groupinstall "Development Tools" # in case configure & make (next 2 commands) does not work, else dont run this command
cd sshpass-1.10
./configure
sudo make install        

Automating Backup Retrieval and Restorations with a Cron Job

We are finally at the fun part. We will know take a look at an example script that will allow you to automate your backup process.

Let's first test a few of the commands that we will use.

Copy the backup from our Odoo server to our Amazon server using the command below.

# Command structure
sshpass -p "your-key-password" -P "passphrase" scp -O ssh-connection-url:/home/odoo/backup.daily/back-up-file-name.sql.gz .

# Example
sshpass -p "odootest" -P "passphrase" scp -O [email protected]:/home/odoo/backup.daily/2023-09-07_010937-kez-odoo-kezdb-prod-9592724_manual.sql.gz .        

Next, unzip your backup.

# Command structure
gunzip file-name.sql.gz

# Example
kez-odoo-datalayer-prod-9326632_daily.sql.gz        

Now, let's upload our backup to our Amazon db.

# Command structure
PGPASSWORD=amzn-db-pass psql -h db-url -p port-number -U db-username -d db-name < back-up-file-name

# Example
PGPASSWORD=Odootest123! psql -h odoo-back-up-db.cg0dua4tsuvv.us-east-2.rds.amazonaws.com -p 5432 -U kevinzaki -d postgres < kez-odoo-kezdb-prod-9592724_daily.sql        

Your backup should now be restored to your Amazon database. You can connect to it and run a few queries to ensure everything is working. Here are my results:

It is now time to create our script to automated this process!

From your shell create a new file:

nano backupcron.sh        

Copy and paste in the script below. Make sure you change all of the parameters to your values. Read the comments in the script to follow along. Save the file once you have edited and reviewed the script.

Here is a screenshot:

Here is the code:

#!/bin/bash

# Postgres connection details for Amazon RDS Postgres database
AMZN_DB_HOST="odoo-back-up-db.cg0dua4tsuvv.us-east-2.rds.amazonaws.com"
AMZN_DB_PORT="5432"
AMZN_DB_USER="kevinzaki"
AMZN_DB_PASSWORD="Odootest123!"
AMZN_DB_NAME="postgres"

# Odoo SH backup file name
BACK_UP_FILE="kez-odoo-kezdb-prod-9592724_daily.sql.gz"

# Odoo SH unziped file name
BACK_UP_FILE_SQL="kez-odoo-kezdb-prod-9592724_daily.sql"

# Odoo SH connection URL
ODOO_SSH="[email protected]:/home/odoo/backup.daily/${BACK_UP_FILE}"

# Use sshpass and scp to securely copy the backup file from the Odoo server to current EC2 Server
# -P option tells sshpass what prompt to look for when inserting our password
sshpass -p "odootest" -P "passphrase" scp -O $ODOO_SSH .

# Verify if the file was copied successfully
if [ $? -eq 0 ]; then
    echo "Backup file copied successfully."

    # Unzip the backup and overwrite existing file
    yes | gunzip $BACK_UP_FILE
    
    # Restore the backup to the Amazon RDS database
    PGPASSWORD=$AMZN_DB_PASSWORD psql -h $AMZN_DB_HOST -p $AMZN_DB_PORT -U $AMZN_DB_USER -d $AMZN_DB_NAME < $BACK_UP_FILE_SQL

    # Check if the restore was successful
    if [ $? -eq 0 ]; then
        echo "Database restore completed successfully."
    else
        echo "Database restore failed."
    fi
else
    echo "Failed to copy backup file."
fi        

As noted earlier, I am using passwords directly in the command. Alternatively, you can store them in a secure file on the server and create an environment variable that references the values. You can then use those environment variables inside of your script.

Now make the file executable.

chmod +x backupcron.sh        

Test you script!

./backupcron.sh        

If everything is successful you can now schedule your cron job. On our EC2 server we need to run a few commands to install crontab in order to schedule our crons.

# Install the "cronie" package
sudo yum install cronie -y

# Enable the "cronie" service
sudo systemctl enable crond.service

# Start the "cronie" service
sudo systemctl start crond.service

# Verify is is active
sudo systemctl status crond | grep Active        

Type the follow command to create a cron.

crontab -e        

Type in the following and save.

30 7 * * * ~/backupcron.sh        

The numbers and asterisks represent the schedule for the job (minutes, hours, days of the month, months, and days of the week). Here's a brief explanation of the fields:

• 30 is for the minutes (0-59).
• 14 is for the hour in 24-hour format (0-23).
• * for the days of the month (1-31).
• * for the months (1-12) or * for all months.
• * for the days of the week (0-7, where both 0 and 7 represent Sunday) or * for all days.

In the example above we are scheduling the cron job at 07:30 each day.

We have now successfully automated our database replication using the Odoo provided daily backups. Each day you will have the previous days backup database at your disposal to run advanced analytics or connect to your data lake.

In the next section we will discuss some possibilities to explore from here!

Conclusion

As mentioned in the beginning of this article - I presume many of you have skipped around to the parts that are relevant to you. After all, each company will have different data needs. Whether you need access to your data each day, month, or year, you now have the tools to do so.

So what's next?

If your reading this article you are either exploring the utilization of data lakes or advanced business intelligence tools for your business or you have already done so and are exploring the compatibility of Odoo to your existing tools. In either case, the tools listed below might be of interest to you.

These tools are ones I've tested and connected to as I wrote this article. Keep in mind, this is not an endorsement for any of these products. I simply tested connection and explored their offerings for my own knowledge.

• Tableau : Tableau is a data visualization and business intelligence platform that empowers users to create interactive, shareable dashboards and reports from various data sources, enabling data-driven decision-making.
• Snowflake : Snowflake is a cloud-based data warehousing platform that provides a scalable and flexible environment for storing and analyzing large volumes of data, with features like automatic scaling and data sharing.
• Databricks : Databricks is a unified analytics platform that simplifies big data processing and machine learning tasks, enabling data engineers and data scientists to collaborate on data pipelines and advanced analytics in a scalable manner.
• BigQuery : BigQuery is a serverless, fully managed data warehouse by Google Cloud that allows users to perform super-fast, SQL-like queries on large datasets and gain real-time insights, making it ideal for data analysis and exploration.

To help simplify the connection to some of the above platforms I utilized trials of the following software.

1. Hevo : Hevo is a data integration platform that enables users to easily extract, transform, and load (ETL) data from various sources into a data warehouse or other destinations, providing a streamlined process for data ingestion and consolidation.
2. Rivery : Rivery is a cloud-native data integration platform that simplifies data pipeline orchestration, allowing users to collect, transform, and move data between different systems and databases, facilitating data-driven insights and decision-making.

I hope this article has been helpful! If you are exploring Odoo as your ERP solution or plan to revamp your tech stack in the future let's connect. I am available on LinkedIn or via email [email protected] .

In the meantime, if you want to learn more about Odoo visit the website and checkout my YouTube channel for functional insights and tutorials.