PostgreSQL is a popular and reliable open-source database used by many important applications, including ERP systems like Odoo. As software keeps improving, upgrading PostgreSQL on Ubuntu becomes important to keep your system fast, stable, and secure. Newer PostgreSQL versions work better with modern applications, especially the latest Odoo versions such as Odoo 17 and 18, and help avoid problems caused by using old software.
Every new PostgreSQL release brings useful improvements like faster queries, better handling of data, improved system monitoring, and stronger security fixes. Using an old version, such as PostgreSQL 12 on Ubuntu 20.04, can slow down your system and increase security risks. This guide explains the upgrade process in a clear and simple way, including how to prepare your Ubuntu system, install the latest PostgreSQL version, create users and databases, and fix common issues after the upgrade.
Step 1: Upgrade Ubuntu
If your system is still running Ubuntu 20.04 (Focal Fossa), it is important to note that the PostgreSQL Global Development Group repository no longer provides the latest PostgreSQL versions for this release. Because of this limitation, you must first upgrade your operating system to Ubuntu 22.04 LTS (Jammy Jellyfish) to ensure access to newer PostgreSQL packages, security updates, and long-term support. Begin the upgrade process by updating all existing packages and then starting the distribution upgrade using the commands below:
sudo apt update && sudo apt upgrade -y
sudo do-release-upgrade
After the upgrade is completed and the system restarts, you should verify that the upgrade was successful by checking the Ubuntu version. Run the following command:
lsb_release -a
If the upgrade was done correctly, the output should show Ubuntu 22.04.5 LTS with the codename jammy, confirming that your system is now ready to install and use the latest PostgreSQL versions.
Distributor ID: Ubuntu
Description: Ubuntu 22.04.5 LTS
Codename: Jammy
Step 2: Add the PostgreSQL Repository
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 -
Then update it,
sudo apt update
Step 3: Install PostgreSQL 16
Now install the latest stable PostgreSQL (currently 16):
sudo apt install postgresql-16 postgresql-client-16 postgresql-contrib-16 -y
Verify installation:
psql --version
Expected output:
psql (PostgreSQL) 16.x
Step 4: Create a New PostgreSQL User
PostgreSQL won’t allow logins unless a role exists. If your application (e.g., Odoo) requires a dedicated user, create one:
sudo -i -u postgres
psql
Inside the psql shell:
CREATE USER odoo19 WITH PASSWORD 'yourpassword';
ALTER USER odoo19 CREATEDB;
If you want full privileges:
ALTER USER odoo19 WITH SUPERUSER;
Exit the shell with \q.
Step 5: Create a Database
PostgreSQL tries to connect to a database with the same name as the user by default. If it doesn’t exist, you’ll get:
FATAL: database "odoo19" does not exist
To fix this:
createdb -O odoo19 odoo19
Now test the connection:
psql -U odoo19 -h 127.0.0.1 -d odoo19 -W
Step 6: Common Errors and Fixes
- password authentication failed for user > Ensure the user exists and password matches.
- database "<user>" does not exist > Create the database with createdb.
- IPv6/IPv4 issues > Set listen_addresses = '127.0.0.1' in postgresql.conf and edit pg_hba.conf for IPv4.
Upgrading PostgreSQL on Ubuntu involves more than just installing packages—it requires planning for compatibility, setting up users, and configuring databases. By following these steps, you’ll have a clean PostgreSQL 16 installation on Ubuntu 22.04, ready for your applications.
Whether you’re working with Odoo or another platform, this setup ensures you’re running on a secure, modern, and high-performance database engine.
To read more about What Makes PostgreSQL Different? In-Depth Feature and Benefit Breakdown, refer to our blog What Makes PostgreSQL Different? In-Depth Feature and Benefit Breakdown