How to Install PostgreSQL 16 on Ubuntu 24.04, 22.04, or 20.04

PostgreSQL 16 marks a significant update in the world of database management systems. Officially released on September 14, 2023, PostgreSQL 16 introduces many new features and enhancements, fortifying its position as a robust and versatile database solution for users, administrators, and developers.

Below are some of the key feature highlights of PostgreSQL 16:

• Parallelization Enhancements: PostgreSQL 16 allows the parallelization of FULL and internal right OUTER hash joins, significantly improving query performance.
• Replication Improvements: It introduces the capability for logical replication from standby servers and allows subscribers to apply large transactions in parallel.
• Monitoring Capabilities: Adding the pg_stat_io view enables more in-depth monitoring of I/O statistics.
• SQL/JSON Support: This release introduces SQL/JSON constructors and identity functions, broadening its data handling capabilities.
• Vacuum Freezing Performance: PostgreSQL 16 improves the performance of vacuum freezing, aiding in efficient database maintenance.
• Regular Expression Matching: Regular expression matching for user and database names in pg_hba.conf and user names in pg_ident.conf enhances security and flexibility.

Now, let’s proceed to the technical how-to and delve into the installation process of PostgreSQL 16.

Prerequisites for Installing PostgreSQL 16

To successfully install PostgreSQL 16 on Ubuntu, ensure you meet the following requirements:

Recommended Hardware and Supported Ubuntu Versions

Component	Requirement
Processor	1 gigahertz (GHz) or faster compatible processor
RAM	2 GB or more
Disk Space	512 MB of HDD (additional disk space required for data or supporting components)
Supported Versions	Ubuntu 24.04 (Noble), Ubuntu 22.04 (Jammy), Ubuntu 20.04 (Focal)

Additional Requirements

Requirement	Description
Internet Connection	Required to download PostgreSQL 16 and future updates.
Terminal Conventions	All terminal commands should be run as a regular user with sudo privileges.
CLI Commands	Use the Command Line Interface (CLI) for installation and setup.

Import PostgreSQL APT Repository

Update Ubuntu Before PostgreSQL 16 Installation

Start by ensuring your Ubuntu system is up-to-date. This step is crucial for maintaining system stability and compatibility:

sudo apt update

Next, upgrade any packages that are out of date:

sudo apt upgrade

Install Initial Packages for PostgreSQL 16

To assist in installing the database software, install the following packages:

sudo apt install dirmngr ca-certificates software-properties-common apt-transport-https lsb-release curl -y

Add PostgreSQL 16 APT Repository

First, import the PostgreSQL GPG key. This is about ensuring the packages you’re about to install are genuine and untampered with:

curl -fSsL https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | sudo tee /usr/share/keyrings/postgresql.gpg > /dev/null

Choosing the Right PostgreSQL 16 Repository

Now, it’s time to select the appropriate PostgreSQL 16 repository for your needs.

Stable Repository for Production / General Use

The stable repository is your go-to for most users, especially in a production environment. It’s regularly updated with stable and secure versions:

echo deb [arch=amd64,arm64,ppc64el signed-by=/usr/share/keyrings/postgresql.gpg] http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main | sudo tee /etc/apt/sources.list.d/postgresql.list

Snapshot or Testing Repositories for Developers

Consider the snapshot or testing repositories if you’re in the development or testing phase. Just remember, however, that these are not for production use due to their less stable nature.

Snapshot Repository for Cutting-Edge Testing

Grab the latest snapshots here:

echo deb [arch=amd64,arm64,ppc64el signed-by=/usr/share/keyrings/postgresql.gpg] http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg-snapshot main | sudo tee /etc/apt/sources.list.d/postgresql.list

Test Repository for the Newest Features

To test the newest features, this command will set you up:

echo deb [arch=amd64,arm64,ppc64el signed-by=/usr/share/keyrings/postgresql.gpg] http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg-testing main | sudo tee /etc/apt/sources.list.d/postgresql.list

Install PostgreSQL 16 via Terminal Commands

Refresh APT Package Index Sources List

After adding either the stable or testing PostgreSQL repository, updating your repository sources list is important. This action ensures your system recognizes the newly added PostgreSQL repository:

sudo apt update

Install PostgreSQL 16 via APT Command

Now, let’s move on to the installation of PostgreSQL 16. This command installs both the PostgreSQL client and server:

sudo apt install postgresql-client-16 postgresql-16

Verifying PostgreSQL Installation

Once the installation is complete, confirming that PostgreSQL is correctly installed and running is crucial. This command checks the status of the PostgreSQL service:

systemctl status postgresql

Activating PostgreSQL 16

If PostgreSQL isn’t active, use this command to start the PostgreSQL service. This also ensures that PostgreSQL starts automatically with your system:

sudo systemctl enable postgresql --now

Managing PostgreSQL 16 Service

Stopping the PostgreSQL 16 Server

To halt the PostgreSQL service temporarily, perhaps for maintenance or configuration changes, use this command:

sudo systemctl stop postgresql

Starting the PostgreSQL 16 Server

When you’re ready to start the PostgreSQL service, especially after a stop or initial installation, this command comes in handy:

sudo systemctl start postgresql

Restarting the PostgreSQL 16 Server

In scenarios where you’ve changed configurations or updates, a restart is often necessary. This command restarts the PostgreSQL service, applying any new changes:

sudo systemctl restart postgresql

Reloading the PostgreSQL 16 Server

To reload the PostgreSQL service without disrupting its operation, use this command. It’s useful when you need to apply minor configuration changes:

sudo systemctl reload postgresql

Checking PostgreSQL 16 Status

To verify the operational status of the PostgreSQL service, including whether it’s active and running without issues, use:

systemctl status postgresql

Enabling PostgreSQL 16 on System Boot

If PostgreSQL isn’t set to start automatically, this command will enable it, ensuring it’s active immediately and on subsequent system boots:

sudo systemctl enable postgresql --now

Basic Example Commands with PostgreSQL 16

Accessing the Postgres User Account

In PostgreSQL, the ‘postgres’ user is a default superuser role. This account is pivotal for executing administrative commands. To switch to this account, use:

sudo -i -u postgres

Once in the account, enter the PostgreSQL command-line interface:

psql

The prompt postgres=# confirms your connection to the PostgreSQL environment. To exit, simply type exit.

Quick Access to PostgreSQL Without User Switch

For swift operations without changing the user account, directly access PostgreSQL:

sudo -u postgres psql

This command is a time-saver for routine tasks. To leave this interface, type exit.

Creating a New PostgreSQL User Role

PostgreSQL’s flexibility allows superusers to create user roles. To establish a new user role, issue this command:

sudo su - postgres -c "createuser <username>"

Replace <username> with your desired username.

Initiating a New PostgreSQL Database

To set up a new database for your user:

sudo su - postgres -c "createdb <database_name>"

Replace <database_name> with the intended name for your database.

Assigning User Privileges to a Database

Securely assign database access:

sudo -u postgres psql
GRANT ALL PRIVILEGES ON DATABASE <database_name> TO <username>;

Substitute <database_name> and <username> as required. Conclude by typing exit.

Configure UFW Firewall for PostgreSQL 16

Installing and Activating the UFW Firewall

Ensure the UFW (Uncomplicated Firewall) is installed and activated on your system. UFW simplifies firewall management and is essential for securing network traffic:

sudo apt install ufw
sudo ufw enable

Setting Up UFW Rules for PostgreSQL 16

PostgreSQL communicates on port 5432 by default. It’s necessary to configure UFW to allow traffic on this port, especially when connecting from remote locations.

Allowing Subnet Access

For allowing access to a range of IP addresses within a subnet, use this command:

sudo ufw allow proto tcp from 192.168.1.0/24 to any port 5432

Replace 192.168.1.0/24 with your specific subnet range.

Allowing Individual IP Access

To grant access to PostgreSQL from a single IP address, use:

sudo ufw allow proto tcp from 192.168.1.0 to any port 5432

Modify 192.168.1.0 to the IP address you want to allow.

These are just basic examples. You should consider locking down any public-facing database as much as possible long-term.

Configure PostgreSQL 16

Enable Remote Access in PostgreSQL 16

Configuring PostgreSQL to accept remote connections involves adjusting the listening address from the default local interface.

Modifying the Configuration for Remote Access

Access the PostgreSQL configuration file:

sudo nano /etc/postgresql/16/main/postgresql.conf

In the “Connection Settings” section, change listen_addresses = 'localhost' to your specific needs:

• Replace ‘localhost’ with the desired IP address for a single IP.
• To allow connections on all interfaces, use listen_addresses = '*'.

After making changes, save with CTRL+O and exit with CTRL+X. Then, restart PostgreSQL:

sudo systemctl restart postgresql

Verify the changes with:

ss -nlt | grep 5432

In the output, you should see port 5432 active.

Configuring pg_hba.conf for Enhanced Security

Edit the pg_hba.conf file for fine-tuned access control:

sudo nano /etc/postgresql/16/main/pg_hba.conf

Customizing User and Database Access

In pg_hba.conf, you can specify which users and databases can connect, and from which addresses. Here are some common configurations:

• User-Specific Access: Replace ‘all’ with a specific username to restrict access to that user only.
• Database-Specific Access: Specify a particular database instead of allowing access to all databases.
• Address Restrictions: Define specific IP addresses or ranges (e.g., 192.168.1.0/24) to limit where connections can originate.

Choosing Authentication Methods

PostgreSQL supports various authentication methods:

• MD5: Requires a password and encrypts it with MD5.
• Password: Uses plain-text passwords (less secure, not recommended for production).
• Peer: Relies on OS-level user identity (common for local connections).
• Ident: Similar to ‘peer,’ typically used for network connections.
• SCRAM-SHA-256: A more secure method, recommended over MD5 or password.

Tuning Performance in postgresql.conf

For performance optimization, consider these settings in postgresql.conf:

• shared_buffers: Adjusts the amount of memory PostgreSQL uses for shared memory buffers.
• work_mem: Sets the memory used for internal sorting and hashing operations.
• max_connections: Controls the maximum number of concurrent connections to the database.

Monitoring and Diagnostics

Enable detailed logging for insights into database performance and issues:

• log_statement: Set to ‘all’ to log every SQL statement executed.
• log_duration: When enabled, logs the duration of each completed SQL statement.

Conclusion

We’ve just walked through a comprehensive guide to setting up PostgreSQL 16 on Ubuntu, covering everything from installation and configuring remote access to tightening security with firewall rules. Remember, while it’s great to have PostgreSQL up and running, the real key lies in regular maintenance and security checks.

Useful Links

Here are some helpful links related to installing PostgreSQL 16 on Ubuntu Linux:

• PostgreSQL 16 Release Notes: Read the release notes for PostgreSQL 16 to learn about new features, enhancements, and bug fixes.
• PostgreSQL GitHub Repository: Visit the official PostgreSQL GitHub repository to access the source code, report issues, and contribute to the development.
• PostgreSQL Documentation: Access the comprehensive PostgreSQL documentation for detailed installation, configuration, and usage guides.
• PostgreSQL Community: Join the PostgreSQL community to connect with other users, participate in discussions, and get support.