Odoo homepage screen

Installing Odoo frontend and backend on different servers

In this tutorial I will teach you how to separate your Odoo frontend and backend. I will install the Odoo codebase/frontend on one server and the backend (your PostgreSQL server and database) on another server. You will learn how to configure both Odoo and PostgreSQL to achieve this.

1. Introduction

In order to achieve and demonstrate this setup to you I will be working with two virtual machines.
I will refer to the Odoo codebase/frontend server as the ‘frontend server’. The PostgreSQL server and database will be named ‘backend server’ throughout this tutorial.
My frontend server has the IP 192.168.118.167 and my backend server has the IP 192.168.118.168.
Tip: Make sure that both your servers have a fixed IP, otherwise your setup will break due to IP changes!

2. Configuring the backend server

Let us start with the configuration of the backend server. This server will contain the PostgreSQL server and the database(s).

2.1 Installing postgreSQL

Open up a terminal on your backend server and install PostgreSQL if you haven’t yet:

2.2 Creating the PostgreSQL user

Now create a new PostgreSQL user. Make sure that the PostgreSQL username matches with the username of the user running Odoo on your frontend server. In my example I will create a new PostgreSQL user with the name “odoo11”:

After you execute this command the system will ask you for a password for this user. Fill in a password and confirm this password again.
Tip: Don’t forget to remember this password, you’ll need it later on.

2.3 Configuring pg_hba.conf

After installing the PostgreSQL server and creating the user we now need to configure the remote connections. As our backend server will be used for the database connections our frontend server needs to be able to access it. Open up your frontend server and get the IP of the server. In my example my frontend server has the IP 192.168.118.167. Now open up your pg_hba.conf file on your backend server:

Scroll in this file and search for the following line of code:

Add a new line after the existing one which contains your frontend server its IP address. As you don’t want to use the exact IP address you should use the /24 subdomain. In my example this results to 192.168.118.0/24 instead of 192.168.118.167. Your configuration file should now look like this:

Finally, save your file and close it. Your PostgreSQL now knows that you want to allow connections from the backend server (IP 192.168.118.167).

2.4 Configuring postgresql.conf

Your PostgreSQL still needs to know the listen address of your frontend server too though. To achieve this we have to edit the postgresql.conf file:

Find the ‘listen_addresses’ line, which looks like this by default:

Now add in the IP of your frontend Odoo server (in my example 192.168.118.192):

Tip: Don’t forget to remove the # in this line because otherwise this line will be skipped!
Save this file and close it.
You’ve now applied all the changes that you need to do on the backend server. Next reload the PostgreSQL service in order to apply all the changes:

3. Configuring the frontend server

Your backend server is done now. Switch to the frontend server and open up your Odoo configuration file.
Tip: If you haven’t installed an Odoo yet you can follow my tutorial here
Typically your Odoo configuration file is under /etc/ and is named your-odoo.conf:

Add or change the following parameters in the configuration file:

Finally, restart your Odoo service to reload your Odoo configuration:

When you now browse to your Odoo instance (on your frontend server) you’ll see that your new Odoo is ready to use.
Odoo homepage screen
That is all! You’ve now setup your own Odoo instance where the frontend and backend are split between two servers.

4. Tips

While this is everything that you need in order to split up the backend and frontend of Odoo there are still some things to consider.
Because of security reasons you’ll most likely want to encrypt all the data and place everything on SSL. This also includes the calls from the frontend server to the backend server.
After you’ve created a new database you should check if the database is in fact created on the backend server instead of on the frontend server. You can do this executing the following commands (one by one) on your backend server:

After running these commands you can see an overview of your databases and to what user the database belongs. In my example you can see a new database named ‘ABC’ which is owned by my Odoo/PostgreSQL user ‘odoo11’:
Database overview result backend

5. Conclusion

Setting up an Odoo instance where the frontend and the backend are split is actually quite easy. Due to the built-in parameters from Odoo it is very easy to configure the frontend side. When you split the frontend and backend up and apply extra security – such as HTTPS connections and VLAN’s – you’ll have a safer and more controlable Odoo instance.
If you’d like to learn more about the deployment of servers and the default Odoo parameters you can have a look at the official documentation.

Has this tutorial helped you, do you have any feedback or questions? Post away!
Tutorial sponsored by Oocademy


PayPal

Connecting PowerBI and Odoo for BI reporting

In this tutorial I will teach you how to connect Odoo and PowerBI to do BI reporting on Odoo data. You will learn how to configure postgreSQL and PowerBI in order to connect them together for real time reporting.
In this tutorial I will work with postgreSQL 9.3, PowerBI Desktop 2.34 and Odoo 10. While this tutorials targets these versions it should work for any Odoo version and postgreSQL 9.3, 9.4, 9.5 and 9.6.

1. Configuring postgreSQL

The first thing that we need to do is to configure postgreSQL. By default postgreSQL does not allow remote connections and if you do configure this it still has to use HTTPS.
As running postgreSQL over SSL is quite advanced and out of scope in this tutorial we’ll configure it without SSL. It is better to run it over SSL as it is more secure so you might want to keep this in the back of your head as a next task.

1.1 Allowing remote connections

Alright, let us start by allowing remote connections to the postgreSQL server. In order to allow this we should edit the ‘pg_hba.conf’ file:

In order to allow remote connections to postgreSQL we should add a line that says postgreSQL that we can connect remote as long as we authenticate. Add the following line at the top of the file:

Your configuration file should now look like this:
pg_hba.conf result
So, what exactly does this do? Let me explain it part by part:

  • Type ‘host’: This tells postgreSQL that it will allow remote connections from a host.
  • Database ‘all’: By saying ‘all’ postgreSQL will allow connecting to any database that exists. If you only want to allow access to one database you can fill in the database name here.
  • User ‘all’: This tells postgreSQL that it’ll allow connections to postgreSQL from any postgreSQL user. If you only want access from one specific user you can fill in the user his name here.
  • CID-ADDRESS: This part allows you to configure from where connections to postgreSQL will be allowed. In this tutorial I’ve set it to ‘0.0.0.0/0’ which means that connections from anywhere around the world will be accepted. In production this should be narrowed down to the range of your network, for example ‘192.168.0.0/24’. Another option is to limit this from a firewall, this is however out of scope for this tutorial.

So to conclude this line of code: we’re allowing connections to postgreSQL from anywhere in the world as long as they authenticate with an username and password.
Now save the file and close it (ctrl+o to save and ctrl+x to close the file if you work with nano).

1.2 Disabling the SSL from postgreSQL

Now that the postgreSQL server allows connections from anywhere there is a second issue. By default postgreSQL will only allow secure connections that run over SSL, this however requires more configuration so I’ve decided to do this tutorial without SSL. If you do want to run postgreSQL over HTTPS you can find more information here.
Disabling the SSL however is very easy, it is just one setting in a configuration file that you need to set to false.
Edit the postgresql.conf file to disable this:

You can find this setting somewhere along line 80, under the ‘Security and Authentication’ block. There is a line that contains the following code:

Change this from true to false. Your result should look like this:
postgresql.conf SSL key
Now save the file (ctrl+o) and close it (ctrl+x).

1.3 Restarting postgreSQL

Congratulations, you’re done configuring postgreSQL. These changes however require a restart before they become active because otherwise postgreSQL does not know about the changes. Restart the postgreSQL service from the command line:

Restarting the postgreSQL server should return an ‘OK’. If this is not the case you’ve probably made a typo, in this case just go over this tutorial again.
If you get the following output you’re ready to connect with PowerBI:
PostgreSQL server restart

2. Configuring PowerBI

After the postgreSQL configuration we can connect with it from PowerBI. If you don’t have PowerBI yet you can download it here.

2.1 Settings up the connection

Open up PowerBI Desktop, click on ‘Get Data’ (1) and then click on ‘More’ (2):
PowerBI data source configuration
A dialog will pop up. Click on ‘Database’ (1), choose for ‘PostgreSQL database’ (2) and then click on ‘Connect’ (3):
PowerBI database
After this a new dialog will show where you can fill in the IP of the postgreSQL server (1) and the name of the database (2), next click on ‘OK’ (3). In my example the postgreSQL runs on the IP ‘192.168.118.137’ and I have a database named ‘odoo-test-2’:
postgreSQL ip and database
Finally a dialog will show up where you can fill in the username and the password. You can choose the credentials from a user that only has access to a specific database or you can use a user who has rights to all databases.
Tip: If you don’t know the credentials you can change the password by running the following commands:

In my example I will connect with the main user ‘postgres’ as this user has access to any database and is the administrator user of postgreSQL:
PowerBi authentication
After you click on ‘Connect’ you’ll see a new dialog show up from which you can finally choose from which tables you’d like to get data and make reports from. You’ll get something like this:
Database table selection
Choose the database tables from which you want to get data and where you want to report on and finally click on ‘Load’. From here on you can start connecting relations and create graphs as you’d like. If you’d like to learn more about creating good and advanced PowerBI reports you can follow the official tutorials here.

2.2 Non-encrypted authentication

If you run the postgreSQL service without SSL (as we’ve configured in this tutorial) PowerBI will give you a warning about it, just click on ‘OK’ to connect:
No encryption warning
Tip: If you want to turn off the encryption warnings you can do this from ‘File’ > ‘Options and settings’ > ‘Data source setting’ by clicking on your connection and then clicking on ‘Edit’. After clicking on edit a dialog will show up where you can uncheck ‘Encrypt connections’.

3. Conclusion

Connecting Odoo and PowerBI isn’t the easiest thing to do but it is really worth it. While configuring it takes quite some time it will absolutely be a plus on the longer term. At this time there is no option to create advanced reports and do BI on the data so PowerBI will fill up this gap. After you’ve configured this once and ‘played’ a few hours with PowerBI you’ll notice that it is a very powerful and good reporting tool.
Has this tutorial helped you, do you have any feedback or questions? Post away!
Tutorial sponsored by Oocademy




PayPal