How to Configure Database Connection Parameters in Odoo 19

In any Odoo installation, a database connection is perhaps the most important parameter. Every single task of Odoo requires the establishment of a reliable database connection between the Odoo application server and the PostgreSQL database. Inaccurate database connection configuration may be responsible for a range of problems, starting from the application server being unable to start, connection timeout, authentication failure, poor performance, to corrupted database content on production.

Odoo 19 still relies on PostgreSQL as its database, as we already know from the latest release. Database connection settings for Odoo 19 can be set through the configuration file odoo.conf. Knowledge of database configuration is very important for administrators, but for developers and DevOps specialists too.

In this blog, we will go over the database connection parameters for Odoo 19, as well as how they work.

How the Odoo Configuration File works:

The Odoo application is going to look for the server configuration file, which normally has the following name:

odoo.conf

The configuration file can be located in any position on the server and usually opened as follows:

python3 odoo-bin -c /etc/odoo.conf

The parameters that are set within this file affect the way in which Odoo will connect to PostgreSQL.

A basic configuration file:

[options]
db_host = localhost
db_port = 5432
db_user = odoo
db_password = admin
db_name = False

Every parameter has an explanation of how it works:

Main Database Connection Parameters:

• db_host:

db_host = localhost

How it works: If db_host is set to localhost, it means the PostgreSQL server is running on the same server as the Odoo server; otherwise, if it is set to an IP address or a hostname, the PostgreSQL server is running on a different machine.

db_host = 192.168.1.20

or

db_host = db-server.internal

Remote Database Server usage:

A production instance of Odoo will use the PostgreSQL database located in a different server because:

• More scalable
• More secured
• Dedicated resources allocation
• Easy backup management

Troubles:

• Connection Refused

could not connect to server: Connection refused

The most common issues are:

• PostgreSQL server is not running
• Firewall is blocking port 5432
• PostgreSQL does not listen on the required IP addresses

Check PostgreSQL listening IPs in postgresql.conf:

listen_addresses = '*'

The PostgreSQL configuration file (postgresql.conf) will likely be in one of the following locations.

• db_port:

This parameter determines the PostgreSQL server's port. By default:

db_port = 5432

Custom PostgreSQL Port — if the port differs from the default:

db_port = 5433

Check the port used by PostgreSQL with either of the following commands:

sudo netstat -plunt | grep postgres

or

sudo ss -plunt | grep postgres

Recommendation for Production: The PostgreSQL server should never be available for direct public access. Access should always be restricted by a firewall or a VPN.

• db_user:

This parameter defines the database user used by the Odoo application:

db_user = odoo

Creating a new PostgreSQL User:

The Odoo database connection must use an individual user, not the default superuser account of the PostgreSQL server.

To create the user (using the default postgres superuser account):

sudo -u postgres createuser -s odoo

To set a password for the user:

sudo -u postgres psql
ALTER USER odoo WITH PASSWORD 'admin';

Dedicated PostgreSQL user benefits:

1. Increased security
2. Easier management of user rights
3. Clear separation of users
4. Reduced the possibility of accidental administrative access

• db_password:

This parameter sets the password for the PostgreSQL database user:

db_password = admin

Security Considerations:

Use a strong password for production use.

Bad example:

db_password = admin

Good example:

db_password = T7#kL92!xQp@8

File Protection:

Restrict the permissions for the configuration file:

chmod 640 /etc/odoo.conf

Set the owner to the Odoo application user:

chown odoo:odoo /etc/odoo.conf

This will ensure that no unintended users have access to the credentials.

• db_name:

This parameter specifies the database to connect to in an automated fashion. When it is not defined:

db_name = False

Default behavior: If this parameter is set to "False," Odoo will bring up the database selection screen.

Forced Odoo connection to a single database:

db_name = production_db

This might be useful if you are deploying Odoo to a production server and want to automatically connect to a specific database, or if you have a specific application that will use only one database, or if you plan to use the domain filtering.

Multi-Database:

In order to manage a plurality of databases, this parameter must be set to false, or we must utilize filtering.

Database Filtering using dbfilter:

The dbfilter parameter limits the number of databases available for Odoo to connect to:

dbfilter = ^mycompany.*$

Why use dbfilter: This helps prevent irrelevant databases from being displayed to users. Without this setting, an administrator who manages multiple tenants could be faced with a large list of irrelevant databases. Multi instance setup would also not be secured.

Domain-based Filtering:

This is useful if Odoo needs to connect to databases based on the domain name used by the user to access Odoo:

dbfilter = ^%d$

Here '%d' is a variable representing the domain used to connect. If you access Odoo through "erp.company.com", the database named "erp" will be used. This is very commonly used when managing multi-tenant Odoo systems hosted on the web.

Multi-tenancy: The multi-tenant solution uses a domain-based filter so each tenant's access is isolated in its dedicated database.

Database connection pool parameters:

• db_maxconn:

The db_maxconn parameter represents the maximum number of connections made from Odoo to the PostgreSQL database.

db_maxconn = 64

How it works: Each Odoo worker can maintain an unlimited number of database connections, and if it surpasses the total number of allowed database connections, the worker will return the error:

FATAL: remaining connection slots are reserved

The number of connections is also influenced by the max_connections parameter on the PostgreSQL server. If this value is lower on the PostgreSQL server than in the odoo.conf file Odoo may throw an error:

FATAL: remaining connection slots are reserved

If the db_maxconn parameter is set in the odoo.conf file exceeds the value of the max_connections on PostgreSQL, then there will always be a warning when starting the Odoo service. The worker count and the number of workers must also be taken into account for the total maximum number of concurrent connections.

Example:

• Worker count: 4
• Connections per worker: 16
• Total connections: 4 × 16 = 64

If you use external connection pools such as PgBouncer or PgPool-II, the parameters such as conn_max_tries, conn_timeout, max_conn, etc., will be taken from the external connection pool configuration file instead.

Database template parameters:

• db_template:

This parameter sets the PostgreSQL template database used when creating new databases:

db_template = template0

Usage: It is generally a good practice to use template0 to create a new database in order to ensure a clean and predictable setup. Template1 is also a common choice, but it will include any database objects that are already present in the template1 database from previous installations.

Database SSL configuration:

The database connection is unencrypted by default. For a production environment where data security is critical, you will need to configure SSL connections for PostgreSQL. Odoo itself does not provide specific SSL connection options; you can achieve an SSL connection between Odoo and PostgreSQL by configuring the PostgreSQL server and its environment. Typically, this involves setting up an SSL-enabled PostgreSQL server, using private cloud networking, and/or internal VPNs.

Example Production configurations:

A single-server Odoo setup:

[options]
db_host = localhost
db_port = 5432
db_user = odoo
db_password = StrongPassword123
db_name = production_db
db_maxconn = 64

A multi-database Odoo setup:[

[options]
db_host = localhost
db_port = 5432
db_user = odoo
db_password = StrongPassword123
db_name = False
dbfilter = ^%d$
db_maxconn = 128

A remote PostgreSQL server:

[options]
db_host = 10.0.0.15
db_port = 5432
db_user = odoo
db_password = StrongPassword123
db_name = False

Testing the connection:

After you have made the necessary changes, restart the Odoo service:

sudo systemctl restart odoo

And monitor the logs for any errors:

sudo journalctl -u odoo -f

You can also test the connection to the database server manually using the psql command-line client.

For a local PostgreSQL server:

psql -h localhost -U odoo -d postgres

For a remote PostgreSQL server:

psql -h 10.0.0.15 -U odoo -d postgres

Common database connection errors:

Authentication Failed:-

FATAL: password authentication failed

Check:

• The provided password
• The specified username
• pg_hba.conf file

Database Does Not Exist:-

database "test_db" does not exist

Ensure that the database exists. To see a list of all existing databases use:

psql -U postgres -l

Too Many Connections:-

sorry, too many clients already

Check:

• The db_maxconn setting for Odoo
• The PostgreSQL max_connections parameter
• The number of Odoo workers

Connection Timed Out:-

could not connect to server: timeout expired

Check:

• Network connectivity between Odoo and the PostgreSQL server
• Firewall rules between Odoo and the PostgreSQL server
• The PostgreSQL IP address and port

Best practices for database connection configuration in Odoo 19:

• Dedicated PostgreSQL user for Odoo
• Protection of the configuration file of Odoo.conf
• Utilize the dbfilter parameter
• Production instances should not use a single PostgreSQL server if possible
• Continuous monitoring of the database connections using the following command:

SELECT * FROM pg_stat_activity;

Odoo 19 Database Connection Parameters represent a strong foundation for any Odoo setup. By ensuring proper configuration, you will end up having your Odoo setup become more stable, scalable, secure, and performant. In case you are developing either a testing or a production environment, you should be aware of such parameters as db_host, db_user, db_maxconn, and dbfilter. A properly configured Database Connection Parameters setting will help you prevent possible connectivity issues, increase performance, make the administration easier, and increase the security of your Odoo environment.

To read more about Overview of Database Management in Odoo 19, refer to our blog Overview of Database Management in Odoo 19.