Managing Odoo server databases

When working with Odoo, all the data of 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 assume that your work environment is set up and you have an instance running. Do not start it using the odoo.py start command shown in the previous recipe, as it configures the server with some options which interfere with multi-database management.

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.

Access the Database Management interface

To access the database, the following steps need to be performed:

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

Set or change the master password

If you've set up your instance with default values, and not yet modified it as explained in the following section, the database management screen will display a warning telling you that the master password is not set, and advising you to set one, with a direct link:

1. To set the Master Password, you can click on that link. You will get a dialog box asking you to provide the new password:
   
2. Type in a non-trivial new password and click on Continue.
3. When the master password is already set, click the Set Master Password button at the bottom of the screen to change it
4. In the displayed dialog box, type the previous master password and the new one, and then click on Continue.

Note

The master password is in the server configuration file under the admin_password key. If the server was started without specifying a configuration file, a new one will be generated in ~/.openerp_serverrc. See 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 which will be handled by the current Odoo server:

1. In the database management screen, click on the Create Database button at the bottom of the screen.
   
2. Fill the form in as follows:
   • Master password: The master password for this instance.
   • Database name: Input the name of the database you wish to create.
   • Language: Select the language you wish to be installed by default in the new database.
   • Password of admin user: Type the password you want to set for the admin user of the new instance.
   • Load demonstration data: Check this box to have demonstration data. This is useful to run tests or set up a demonstration for a customer, but should not be checked for a database meant to contain production data.
3. Click on the Continue button, and wait a little until the new database is initialized. You will then be redirected to the instance, connected as the Administrator.

Tip

Troubleshooting

If you are redirected to a login screen, this is probably because the option --db-filter was passed to Odoo and the new database name did not match the new database name. Note that the odoo.py 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 first recipe of this chapter. If you have a configuration file (see the Storing the instance configuration in a file recipe later in this chapter), then check that the db_filter option is unset or set to a value matching the new database name.

Duplicating a database

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

1. In the database management screen, click on the Duplicate link next to the name of the database you wish to clone.
   
2. Fill in the form:
   • Master Password: the master password of the Odoo server
   • New Name: the name you want to give to the copy
3. Click on the Continue button.
4. You can then click on the name of the newly created database in 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. In the database management screen, click on the Delete link next to the name of the database you want to remove.
   
2. Fill in the form; enter the Master Password, which is the master password of the Odoo server.
3. Click the Delete button.

Note

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

For creating a backup, the following steps need to be performed:

1. In the database management screen, click the Backup link next to the database you want to back up.
   
2. Fill in the form:
   • Master Password: the master password of the Odoo server.
   • Backup Format: always use zip for a production database, as it is the only real full backup format. Only use the pg_dump format for a development database where you don't really care about the file store (admin by default).
3. Click the Backup button. 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. In the database management screen, click the Restore Database button at the bottom of the screen.
   
2. Fill in the form:
   • Master Password: the master password of the Odoo server.
   • File: 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.
   • Generate a new database uuid: leave unchecked if you are installing a database which has been deleted from the server; otherwise check the box. There is little difference between them, and if in doubt, leaving it unchecked is a safe choice.
3. Click the Continue button.

Note

Note: It is not 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 which 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 as it is well known. In Odoo v9, this is identified as an "unset" master password and you get 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 his own password.

Note

Security considerations: Remember that we are considering a development environment in this chapter. The Odoo database management interface is something which 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. This will be covered in Chapter 16, Server Deployment.

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 essentially 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 downloading the backup file and uploading 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 which contains a copy of the documents when you configure Odoo to not keep these in the database, which is the default in 9.0. Unless you configure it otherwise, these files live in ~/.local/share/Odoo/filestore.

Note

If the backup gets large, downloading it may fail, either because the Odoo server itself is not able to handle the large file in memory or if the server runs behind a reverse proxy (see Chapter 16, Server Deployment) because there is a limit to the size of HTTP responses 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 is time to invest in a more robust external backup solution.

There is more...

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

$ createdb testdb && odoo.py -d testdb

The additional bonus of this command line is that you can request installation of addons while you are at it using for instance -i sale,purchase,stock (more on this in Chapter 2, Managing Odoo Server Instances).

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

$ 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.

Tip

The use of createdb -T only works if there are no active sessions on the database, which means 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 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

Note

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.