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

This guide will demonstrate how to install PostgreSQL 16 on Ubuntu 24.04, 22.04 or 20.04. We’ll utilize PostgreSQL’s APT repository to install the latest version using the command-line terminal, ensuring easy future updates with the apt update and upgrade commands.

PostgreSQL 16 marks a significant update in the world of database management systems. Officially released on September 14, 2023, PostgreSQL 16 introduces a host of new features and enhancements, fortifying its position as a robust and versatile database solution for users, administrators, and developers alike. This release is not just about the expansion of functionality but also about enhancing performance and user experience.

Key Features of PostgreSQL 16:

• Parallelization Enhancements: PostgreSQL 16 now 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: The addition of the pg_stat_io view enables more in-depth monitoring of I/O statistics.
• SQL/JSON Support: This release sees the introduction of 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: Enhanced security and flexibility are achieved through regular expression matching for user and database names in pg_hba.conf, and user names in pg_ident.conf.

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

Import PostgreSQL APT Repository on Ubuntu

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 things 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 a development or testing phase. Just remember, 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 on Ubuntu via APT

Refresh APT Package Index Sources List

After adding either the stable or testing PostgreSQL repository, it’s important to update your repository sources list. This action ensures your system recognizes the newly added PostgreSQL repository:

sudo apt update

Install PostgreSQL 16 on Ubuntu 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 on Ubuntu

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 made changes to 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 on Ubuntu

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 on Ubuntu

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 look further long-term in locking down any public-facing database as much as possible.

Configure PostgreSQL 16 on Ubuntu

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:

• For a single IP, replace ‘localhost’ with the desired IP address.
• 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 amount of 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

Alright, 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 up 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. Keep those configurations snug, monitor your logs, and stay on top of updates to keep things smooth and secure.