Managing Odoo server databases

When working with Odoo, all the data in your instance is stored in a PostgreSQL database. All the standard database management tools you are used to are available, but Odoo also proposes a web interface for some common operations.

Getting ready

We are assuming that your work environment is set up and that you have an instance running.

How to do it...

The Odoo database management interface provides tools to create, duplicate, remove, back up, and restore a database. There is also a way to change the master password, which is used to protect access to the database management interface.

Accessing the database management interface

To access the database, perform the following steps:

1. Go to the login screen of your instance (if you are authenticated, log out).
2. Click on Manage Databases. This will navigate you to http://localhost:8069/web/database/manager (you can also point your browser directly to that URL):

Figure 1.3 – Database manager

Setting or changing the master password

If you’ve set up your instance with default values and haven’t modified it yet, as we will explain in the following section, the database management screen will display a warning, telling you that the master password instance hasn’t been set and will advise you to set one with a direct link:

Figure 1.4 – Master password warning

To set the master password, perform the following steps:

1. Click on the Set Master Password button. You will get a dialog box asking you to fill in the New Master Password field:

Figure 1.5 – Setting a new master password

2. Type in a complex new password and click Continue.

If the master password is already set, click on the Set Master Password button at the bottom of the screen to change it. In the dialog box that opens, type the previous master password and the new one, and then click Continue:

Figure 1.6 – Changing the master password

Note

The master password is the server configuration file under the admin_passwd key. If the server is started without a configuration file being specified, a new one will be generated in ~/.odoorc. Refer to the next recipe for more information about the configuration file.

Creating a new database

This dialog box can be used to create a new database instance that will be handled by the current Odoo server. Follow these steps:

1. On the database management screen, click on the Create Database button, which can be found at the bottom of the screen. This will bring up the following dialog:

Figure 1.7 – The Create Database dialog

2. Fill in the form, as follows:
   • Master Password: This is the master password for this instance.
   • Database Name: Input the name of the database you wish to create.
   • Email: Add your email address here; this will be your username later.
   • Password: Type in the password you want to set for the admin user of the new instance.
   • Phone Number: Set your phone number (optional).
   • Language: Select the language that you wish to be installed by default in the new database in the drop-down list. Odoo will automatically load the translations for the selected language.
   • Country: Select the country of the main company in the drop-down list. Selecting this will automatically configure a few things, including the company’s currency.
   • Demo data: Check this box to obtain demonstration data. This is useful for running interactive tests or setting up a demonstration for a customer, but it should not be checked for a database that is designed to contain production data.

Note

If you wish to use the database to run automated tests for the modules (refer to Chapter 7, Debugging Modules), you need to have the demonstration data since the vast majority of the automated tests in Odoo depend on these records to run successfully.

3. Click Continue and wait until the new database is initialized. After, you will be redirected to the instance and connected as the administrator.

Troubleshooting

If you are redirected to a login screen, this is probably because the --db-filter option was passed to Odoo and the new database name didn’t match the filter option. Note that the odoo-bin start command does this silently, making only the current database available. To work around this, simply restart Odoo without the start command, as shown in the Installing Odoo from the source recipe. If you have a configuration file (refer to the Storing the instance configuration in a file recipe later in this chapter), check that the db_filter option is unset or set to a value matching the new database name.

Duplicating a database

Often, you will have an existing database, and you will want to experiment with it to try a procedure or run a test, but without modifying the existing data. The solution here is simple: duplicate the database and run the test on the copy. Repeat this as many times as required:

1. On the database management screen, click on the Duplicate Database link next to the name of the database you wish to clone:

Figure 1.8 – The Duplicate Database dialog

2. Fill in the form, as follows:
   • Master Password: This is the master password of the Odoo server
   • New Name: The name you want to give to the copy
3. Click Continue.
4. You can then click on the name of the newly created database on the database management screen to access the login screen for that database.

Removing a database

When you have finished your tests, you will want to clean up the duplicated databases. To do this, perform the following steps:

1. On the database management screen, you will find the Delete button next to the name of the database. Clicking on it will bring up the following dialog:

Figure 1.9 – The Delete Database dialog

2. Fill in the form, as well as the Master Password field, which is the master password of the Odoo server.
3. Click Delete.

Caution! Potential data loss!

If you selected the wrong database and have no backup, there is no way to recover the lost data.

Backing up a database

To create a backup, perform the following steps:

1. On the database management screen, you will find the Backup button next to the database’s name. Clicking on it will bring up the following dialog:

Figure 1.10 – The Backup Database dialog

2. Fill in the form, as follows:
   • Master Password: This is the master password of the Odoo server.
   • Backup Format: Always use zip for a production database since this is the only real full backup format. Only use the pg_dump format for a development database when you don’t care about the file store.
3. Click Backup. The backup file will be downloaded to your browser.

Restoring a database backup

If you need to restore a backup, this is what you need to do:

1. On the database management screen, you will find a Restore Database button at the bottom of the screen. Clicking on it will bring up the following dialog:

Figure 1.11 – The Restore Database dialog

2. Fill in the form, as follows:
   • Master Password: This is the master password of the Odoo server.
   • File: This is a previously downloaded Odoo backup.
   • Database Name: Provide the name of the database in which the backup will be restored. The database must not exist on the server.
   • This database might have been moved or copied: Choose This database was moved if the original database was on another server or if it has been deleted from the current server. Otherwise, choose This database is a copy, which is the safe default option.
3. Click Continue.

Note

It isn’t possible to restore a database on top of itself. If you try to do this, you will get an error message (Database restore error: Database already exists). You need to remove the database first.

How it works...

These features, apart from the Change master password screen, run PostgreSQL administration commands on the server and report back through the web interface.

The master password is a very important piece of information that only lives in the Odoo server configuration file and is never stored in the database. There used to be a default value of admin, but using this value is a security liability. In Odoo v9 and later, this is identified as an unset master password, and you are urged to change it when accessing the database administration interface. Even if it is stored in the configuration file under the admin_passwd entry, this is not the same as the password of the admin user; these are two independent passwords. The master password is set for an Odoo server process, which itself can handle multiple database instances, each of which has an independent admin user with their own password.

Security considerations

Remember that we are considering a development environment in this chapter. The Odoo database management interface is something that needs to be secured when you are working on a production server as it gives access to a lot of sensitive information, especially if the server hosts Odoo instances for several different clients.

To create a new database, Odoo uses the PostgreSQL createdb utility and calls the internal Odoo function to initialize the new database in the same way as when you start Odoo on an empty database.

To duplicate a database, Odoo uses the --template option of createdb, passing the original database as an argument. This duplicates the structure of the template database in the new database using internal and optimized PostgreSQL routines, which is much faster than creating a backup and restoring it (especially when using the web interface, which requires you to download the backup file and upload it again).

Backup and restore operations use the pg_dump and pg_restore utilities, respectively. When using the zip format, the backup will also include a copy of the file store that contains a copy of the documents when you configure Odoo so that it doesn’t keep these in the database; this is the default option in 14.0. Unless you change it, these files reside in ~/.local/share/Odoo/filestore.

If the backup becomes too large, downloading it may fail. This will be either because the Odoo server itself is unable to handle the large file in memory or because the server is running behind a reverse proxy because there is a limit to the size of HTTP responses that were set in the proxy. Conversely, for the same reasons, you will likely experience issues with the database restore operation. When you start running into these issues, it’s time to invest in a more robust external backup solution.

There’s more...

Experienced Odoo developers generally don’t use the database management interface and perform operations from the command line. To initialize a new database with demo data, for instance, the following single-line command can be used:

$ createdb testdb && odoo-bin -d testdb

The bonus of using this command line is that you can request add-ons to be installed while you are using it – for instance, -i sale,purchase,stock.

To duplicate a database, stop the server and run the following commands:

$ createdb -T dbname newdbname
$ cd ~/.local/share/Odoo/filestore # adapt if you have changed the data_dir
$ cp -r dbname newdbname
$ cd -

Note that, in the context of development, the file store is often omitted.

Note

The use of createdb -T only works if there are no active sessions on the database, which means that you have to shut down your Odoo server before duplicating the database from the command line.

To remove an instance, run the following command:

$ dropdb dbname
$ rm -rf ~/.local/share/Odoo/filestore/dbname

To create a backup (assuming that the PostgreSQL server is running locally), use the following command:

$ pg_dump -Fc -f dbname.dump dbname
$ tar cjf dbname.tgz dbname.dump ~/.local/share/Odoo/filestore/dbname

To restore the backup, run the following command:

$ tar xf dbname.tgz
$ pg_restore -C -d dbname dbname.dump

Caution!

If your Odoo instance uses a different user to connect to the database, you need to pass -U username so that the correct user is the owner of the restored database.