Introduction
PostgreSQL is the only database officially supported by Odoo because its reliability, features, and architecture perfectly match Odoo's requirements for building robust, scalable business applications. The combination is so tight that Odoo's ORM (Object Relational Mapping) is specifically optimized for PostgreSQL's capabilities. The relationship between Odoo and PostgreSQL extends into advanced functionality through specialized database features.
PostgreSQL's support for JSONB data types allows Odoo to store flexible, semi-structured data alongside traditional relational information—perfect for custom fields or temporary data that doesn't fit rigid table structures. Geographic information system (GIS) extensions enable location-based features in Odoo modules, while full-text search capabilities power Odoo's intelligent search functionality across millions of records. These aren't just add-ons but integrated features that Odoo leverages to deliver a richer user experience.
Why you should update PostgreSQL?
Updating PostgreSQL is essential for Odoo deployments because each Odoo version enforces a minimum supported PostgreSQL version. Running Odoo on an unsupported PostgreSQL release can result in startup failures, missing ORM features, degraded performance, and potential security risks.
For example, Odoo 13 works with PostgreSQL 9.6 or 10, Odoo 14 requires PostgreSQL 10 or higher, Odoo 16 requires at least PostgreSQL 12, and Odoo 17 mandates PostgreSQL 14 or newer, with PostgreSQL 15 or 16 being the recommended choice for optimal performance and long-term support.
Newer PostgreSQL versions introduce major improvements that directly benefit Odoo, including faster query planning for complex joins across business documents, enhanced JSONB storage for custom fields, improved indexing strategies for large datasets, and better concurrency handling that allows Odoo to support more simultaneous users efficiently.
Step 1: Check Installed Version
Before upgrading PostgreSQL, make sure you have:
- Ubuntu 20.04 / 22.04 / 24.04
- sudo access
- Basic Linux command-line knowledge
Check your current PostgreSQL version:
psql --version
Step 2 : Backup Database
Never upgrade PostgreSQL without a backup.
sudo -u postgres pg_dumpall > postgres_backup_$(date +%F).sql
Or backup per database:
sudo -u postgres pg_dump dbname > dbname_backup.sql
Step 3 : Install the New PostgreSQL Version
PostgreSQL maintains its own APT repository. Add it with:
echo "deb http://apt.postgresql.org/pub/repos/apt/ jammy-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
wget -qO - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
Update package lists:
sudo apt update
Install the new PostgreSQL version (example: upgrading to PostgreSQL 16):
sudo apt install postgresql-16 postgresql-client-16
Step 4 : PostgreSQL Services
Stop both old and new clusters:
sudo systemctl stop postgresql
Ubuntu provides a safe built-in tool: pg_upgradecluster.
Example: Upgrade PostgreSQL 14 → 16
sudo pg_upgradecluster 14 main
This process will:
- Migrate data
- Update system catalogs
- Preserve users, roles, and permissions
Verify the new version installed
Expected output:
psql (PostgreSQL) 16.0x
Conclusion
Upgrading PostgreSQL on Ubuntu doesn’t have to be risky if done correctly. By following this step-by-step guide, you can safely upgrade PostgreSQL while protecting your data and minimizing downtime.