How to Install PostgreSQL 15 on Ubuntu 22 and Expose It to the Internet

PostgreSQL is a popular open-source relational database management system that offers many advanced features and high performance. In this blog post, we will show you how to install PostgreSQL 15 on Ubuntu 22, and how to configure it to allow remote access from the internet. We will also explain the folder structure that gets created by PostgreSQL and what each folder contains.

Prerequisites

Before you begin, you will need the following:

• An Ubuntu 22 server with sudo access
• A domain name or a public IP address for your server
• A firewall that allows incoming traffic on port 5432 (the default port for PostgreSQL)

Step 1: Install PostgreSQL 15

To install PostgreSQL 15 on Ubuntu 22, we will use the official PostgreSQL APT repository, which provides the latest stable versions of the software. First, we need to import the repository’s GPG key to verify the authenticity of the packages:

sudo apt install curl ca-certificates gnupg
curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | sudo tee /etc/apt/trusted.gpg.d/apt.postgresql.org.gpg >/dev/null

Next, we need to add the repository to our sources list. Depending on your Ubuntu version, you can use one of the following commands:

# For Ubuntu 22.04 Jammy Jellyfish
echo deb [arch=amd64,arm64,ppc64el signed-by=/usr/share/keyrings/postgresql.gpg] http://apt.postgresql.org/pub/repos/apt/ jammy-pgdg main | sudo tee -a /etc/apt/sources.list.d/postgresql.list

Then, we need to update our package index and install PostgreSQL 15 along with some additional utilities:

sudo apt update
sudo apt install postgresql-client-15 postgresql-15 postgresql-contrib-15 -y

This will install PostgreSQL 15 and start the service automatically. You can check the status of the service with this command:

sudo systemctl status postgresql@15-main

You should see something like this:

● [email protected] - PostgreSQL Cluster 15-main
     Loaded: loaded (/lib/systemd/system/[email protected]; indirect; vendor preset: enabled)
     Active: active (running) since Sat 2022-01-29 20:29:01 UTC; 5min ago
   Main PID: 1234 (postgres)
      Tasks: 7 (limit: 2286)
     Memory: 23.4M
     CGroup: /system.slice/system-postgresql.slice/[email protected]
             ├─1234 /usr/lib/postgresql/15/bin/postgres -D /var/lib/postgresql/15/main -c config_file=/etc/postgresql/15/main/postgresql.conf
             ├─1235 postgres: 15/main: checkpointer 
             ├─1236 postgres: 15/main: background writer 
             ├─1237 postgres: 15/main: walwriter 
             ├─1238 postgres: 15/main: autovacuum launcher 
             ├─1239 postgres: 15/main: stats collector 
             └─1240 postgres: 15/main: logical replication launcher 

Jan 29 20:29:01 ubuntu systemd[1]: Starting PostgreSQL Cluster 15-main...
Jan 29 20:29:01 ubuntu systemd[1]: Started PostgreSQL Cluster 15-main.

Step 2: Create a Database User and a Database

By default, PostgreSQL creates a system user named postgres that is associated with the default PostgreSQL role. This user can access the PostgreSQL prompt without a password and perform administrative tasks. To switch to this user, run:

sudo -i -u postgres

Then, you can access the PostgreSQL prompt by running:

psql

You should see something like this:

psql (15.0 (Ubuntu 15.0-1.pgdg20.04+1))
Type "help" for help.

postgres=#

From here, you can create a new database user and a new database for your application. For example, to create a user named appuser with a password of appuser123 and a database named appdb, run:

CREATE USER appuser WITH PASSWORD 'appuser123';
CREATE DATABASE appdb OWNER appuser;

You can verify that the user and the database have been created by running:

\du

This will show you a list of all the database users and their roles:

                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 appuser   |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

And by running:

\l

This will show you a list of all the databases and their owners:

                              List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 appdb     | appuser  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

To exit the PostgreSQL prompt, run:

\q

This will bring you back to the postgres Linux command prompt. To return to your regular system user, run the exit command:

exit

Step 3: Configure PostgreSQL for Remote Access

By default, PostgreSQL only allows local connections from the same machine. To enable remote access from the internet, we need to modify two configuration files: pg_hba.conf and postgresql.conf.

The pg_hba.conf file controls the client authentication methods for each database and user. The postgresql.conf file sets various parameters that affect the operation of the database server.

To edit these files, we need to use a text editor such as nano. First, open the pg_hba.conf file with this command:

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

Then, scroll down to the bottom of the file and add a new line like this:

host    all             all             0.0.0.0/0               md5

This line tells PostgreSQL to allow remote connections from any IP address (0.0.0.0/0) using password authentication (md5) for any database (all) and any user (all). You can customize this line according to your needs, for example, by specifying a specific IP address or a range of IP addresses instead of 0.0.0.0/0, or by limiting the access to a specific database or user.

Save and close the file by pressing Ctrl+O followed by Enter, then Ctrl+X.

Next, open the postgresql.conf file with this command:

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

Then, find the line that starts with #listen_addresses = 'localhost' and uncomment it by removing the # sign at the beginning. Then, change the value from 'localhost' to '*'. The line should look like this:

listen_addresses = '*'

This line tells PostgreSQL to listen on all network interfaces (*) instead of only on the local loopback interface (localhost). You can also specify a comma-separated list of IP addresses or hostnames instead of *, if you want to restrict the listening interfaces.

Save and close the file by pressing Ctrl+O followed by Enter, then Ctrl+X.

Finally, restart the PostgreSQL service for the changes to take effect:

sudo systemctl restart postgresql@15-main

Step 4: Configure Your Firewall

If you have a firewall enabled on your server, you need to allow incoming traffic on port 5432, which is the default port for PostgreSQL. For example, if you are using UFW (Uncomplicated Firewall), you can run this command:

sudo ufw allow 5432/tcp

You can verify that the rule has been added by running:

sudo ufw status

You should see something like this:

Status: active

To                         Action      From
--                         ------      ----
22/tcp                     ALLOW       Anywhere                  
5432/tcp