How to Install PostgreSQL on Ubuntu 22.04 LTS

PostgreSQL, often called Postgres, is an open-source Relational Database Management System (RDBMS). It is popularly known for its robust, flexible, and advanced features. In recent times, it has become a go-to choice for handling structured data efficiently for developers building applications and businesses managing their large-scale data.

This guide will walk you through the step-by-step approach of installing and managing PostgreSQL on Ubuntu 22.04 LTS, which is a popular Linux distribution and provides a stable and secure environment, becoming ideal for deploying PostgreSQL. Let's dive into the article to get your PostgreSQL database server up and running!

Prerequisites

• A Virtual Machine (such as the ones provided by NodeShift) with:

  • 2 vCPUs
  • 4GB RAM
  • 10GB SSD

• Ubuntu 22.04 VM

Note: The above prerequisites are highly variable across use cases. One could use a high-end configuration for a large-scale deployment.

Step-by-step process to install PostgreSQL on Ubuntu 22.04 LTS

For this tutorial, we'll use a CPU-powered Virtual Machine by NodeShift, which provides high-compute Virtual Machines at a very affordable cost on a scale that meets GDPR, SOC2, and ISO27001 requirements. Also, it offers an intuitive and user-friendly interface, making it easier for beginners to get started with Cloud deployments. However, feel free to use any cloud provider you choose and follow the same steps for the rest of the tutorial.

Step 1: Setting up a NodeShift Account

Visit app.nodeshift.com and create an account by filling in basic details, or continue signing up with your Google/GitHub account.

If you already have an account, login straight to your dashboard.

Step 2: Create a Compute Node (CPU Virtual Machine)

After accessing your account, you should see a dashboard (see image), now:

1. Navigate to the menu on the left side.

2. Click on the Compute Nodes option.

   

3. Click on Start to start creating your very first compute node.

   

These Compute nodes are CPU powered virtual machines by NodeShift. These nodes are highly customizable, and let you control different environmental configurations, such as CPUs, RAM and storage, according to your needs.

Step 3: Select configuration for VM

1. The first option you see is the Reliability dropdown, which lets you choose the type of uptime guarantee level you're seeking for your VM (e.g., 99%).

   

2. Next, select a geographical region from the Region dropdown, where you want to launch your VM (e.g. United States).

   

3. Now, most importantly, select the right specifications for your VM according to your use-case by sliding the bars for each option.
   

Step 4: Choose VM Configuration and Image

1. After you select your required configuration options, you'll see the available VMs in your region and as per (or very close to) your configuration. In our case, we'll choose a '4 vCPUs/4GB/80GB SSD' Compute node as the best possible match.

2. Next, you'll need to choose an image for your Virtual Machine. For the scope of this tutorial, we'll select Ubuntu, as we are going to deploy PostgreSQL on Ubuntu 22.04 LTS.

   

Step 5: Choose the Billing cycle and Authentication Method

1. For the billing cycle, two options are available: Hourly being ideal for short-term usage, offering pay-as-you-go flexibility, and Monthly being best for long-term projects with consistent usage rate and potentially lower cost.

   

2. Next, you'll need to select an authentication method. There are two methods available: Password and SSH Key. We recommend using SSH keys as they are more secure option. In order to create one, head over to our official documentation.

   

Step 6: Finalize Details and Create Deployment

Finally, you can also add a VPC (Virtual Private Cloud), which provides an isolated section to launch your cloud resources (Virtual machine, storage, etc.) in a secure, private environment. We're keeping this option as default for now, but feel free to create a VPC as per your needs.

Also, you can deploy multiple nodes at once by clicking + in the Quantity tab.

That's it! Now, you are ready to deploy the node. Finalize the configuration summary, if it looks good, go ahead and click Create to deploy the node.

Step 7: Connect to active Compute Node using SSH

As soon as you create the node, it will be deployed in seconds or minutes. Once deployed, you will see a status Running in green, meaning that our Compute node is ready to use!

Once your node shows this status, follow the below steps to connect to the VM via SSH:
1) Open your terminal and run the below SSH command:
(replace root with your username and paste the IP of your VM in place of ip after copying it from the NodeShift dashboard)

ssh root@ip

2) The terminal will authenticate automatically if SSH keys are set up.
3) Sometimes, your terminal may take your consent before connecting, enter 'yes', and you should be connected.

Step 8: Add repository and install dependencies

1) Let's start by installing curl, which will help us download PostgreSQL's repository key and save it to our Ubuntu 22.04 LTS host.

sudo apt-get install curl

2) Create a directory and save PostgreSQL's repository key

sudo install -d /usr/share/postgresql-common/pgdg

3) Use curl to retrieve the key

curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc -fail  https://www.postgresql.org/media/keys/ACCC4CF8.asc

Output:

4) Determine the codename of the Ubuntu release type

lsb_release -cs

Output:

Note down your distribution's codename (e.g., jammy); we'll use it later while creating the source list for the PostgreSQL package.

Step 9: Create Source List for PostgreSQL's package

1) Open the pdpg.list with the help of nano editor.

sudo nano /etc/apt/sources.list.d/pgdg.list

2) Add the below source code to the file

(replace jammy with your own distribution's codename that you noted down in the last step)

deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt  jammy-pgdg main

After editing, the file should look something like this:

Save the edited file (Ctrl + O > Enter) and exit the editor (Ctrl + X).

3) Use ls to verify if the pdpg.list has been added

Output:

Step 10: Install PostgreSQL

Once you're done with the above prerequisite steps, proceed to install PostgreSQL.

1) Update the Ubuntu package list

sudo apt-get update

Output:

2) Install PostgreSQL

sudo apt-get install postgresql -y

Output:

3) Verify if PostgreSQL is running on Ubuntu 22.04 LTS

systemctl status postgresql

Output:

Step 11: Configure PostgreSQL

Before we can start using the PostgreSQL server, we will first need to configure some crucial settings in the configuration files.

To view the configuration files for PostgreSQL, type the below command:

ls /etc/postgresql/16/main

Output:

Here, we have two essential files in this directory: postgresql.conf and pg_hba.conf. Let's configure them one by one:

1) Configure postgresql.conf:

postgresql.conf is the main configuration file used to define the settings needed for the maintenance of DBMS, such as the port of the server to listen to, IP addresses to listen to, and so on.

a). Open this file in the nano editor

(replace your_version with the version you have of PostgreSQL)

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

b). Locate the the line " #listem_addresses = 'localhost' "and change it as shown below:

This line contains the IP addresses to which our PostgreSQL database server can listen. Changing it to * tell the server to listen for all the network addresses.

If you want it to listen to only specific IP addresses, you can do so by mentioning those addresses separated by a comma, as shown below:

listen_addresses = ‘xx.xx.xx.xx, yy.yy.yy.yy, zz.zz.zz.zz, localhost’

2) Configure pg_hba.conf:

The next file to configure is pg_hba.conf. This file specifies some crucial settings for the server such as IP addresses which are allowed to connect to the server, authentication mechanism for user login, etc.

a). Open the file using the Nano editor

(replace your_version with the version you have of PostgreSQL, e.g., 17)

sudo nano /etc/postgresql/your_version/main_pg_hba.conf

b). Locate the following line

# “local” is for Unix domain socket connections only
local all all peer

and replace peer with md5 , which will change the authentication mechanism to md5

Output:

c). Add IP Configuration

Now, add this line at the end of the file to allow all types of IP addresses.

(Caution: Don't use this in a production environment!)

host all all 0.0.0.0/0 scram-sha-256

After configuring this file as per the above edits, that section of our final file looks like this:

3) Restart the PostgreSQL server for changes to take effect

sudo systemctl restart postgresql

4) Verify if the server is listening for the connections

netstat -ano | grep 5432

Output:

In some cases, connections might get blocked by firewalls. To fix that, use the below command:

sudo ufw allow 5432/tcp

Output:

5) Check if the server is accessible remotely using telnet

(replace ip with your server's IP address)

telnet ip 5432

Output:

Step 12: Create a New User in PostgreSQL

Now, let's see how we can create and manage users in PostgreSQL.

1) Log in to PostgreSQL as user "postgres" (default user in PostgreSQL)

sudo -u postgres psql

You should see the postgres console opening up like this:

2) Use \du in the console to see the list of users and roles

Output:

As of now, we only have one user i.e. "postgres" that comes as a default user in PostgreSQL, let's create a new user in the next step.

3) Create a new user

For the demo purpose, we'll create a user named "nodeshift" with the password "nodeshift_password" using the following query

CREATE USER nodeshift WITH PASSWORD 'nodeshift_password';

Output:

Let's use the \du command again to verify if user "nodeshift" is created:

Output:

Step 13: Create a Database in PostgreSQL

Let's now see how we can create a database in PostgreSQL. In the postgres console:

1) Create a database named "nodeshiftdb"

CREATE DATABASE nodeshiftdb;

Output:

2) Grant privileges to the user "nodeshift" for this database

GRANT ALL PRIVILEGES ON DATABASE nodeshiftdb TO nodeshift;

Output:

3) Grant CREATE permission to the user

We need to grant the user CREATE permission on the public schema so that they can create tables inside the database.

Before that, first, connect with the **nodeshiftdb **database as the user **postgres **using the following query:

\c nodeshiftdb postgres;

Output:

Now, grant the CREATE permission to the user "nodeshift"

GRANT ALL ON SCHEMA public to nodeshift;

Output:

Step 14: Create Tables in PostgreSQL Database

1) Create a new table in the database

CREATE TABLE gpus (name varchar(50), region varchar(50));

Output:

2) Insert a new row in the table

INSERT INTO gpus (name, region) VALUES ('A100X', 'USA');

Output:

3) Log the table to verify the inserted data

select * from gpus;

Output:

As you can see, our data has been successfully inserted into the table. You can add more rows to the table in the same way!

Conclusion

Installing PostgreSQL on Ubuntu 22.04 LTS is straightforward. It involves setting up your Ubuntu virtual machine and using the official PostgreSQL repository for installation. We have also covered steps to create users, databases, and tables in PostgreSQL using queries. By leveraging NodeShift’s reliable compute nodes, you can ensure a seamless deployment environment optimized for performance and scalability. Whether setting up PostgreSQL for development or production, following these steps will provide a robust and secure foundation for your database needs.