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:
- Ubuntu 22.04 VM
Note: The above prerequisites are highly variable across use cases. A high-end configuration should be used 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:
- Navigate to the menu on the left side.
- Click on the Compute Nodes option.
- 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 vCPUs, RAM, and storage, according to your needs.
Step 3: Select configuration for VM
- The first option you see is the Reliability dropdown. This lets you choose the type of uptime guarantee level you’re seeking for your VM (e.g., 99.9%).
- Next, select a geographical region from the Region dropdown where you want to launch your VM (e.g., United States).
- Most importantly, select the correct specifications for your VM by sliding the bars for each option. For this tutorial, we’ll choose the specifications per the “Prerequisites”.
Step 4: Choose VM Configuration and Image
- After selecting 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.
- 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
- Two billing cycle options are available: Hourly, ideal for short-term usage, offering pay-as-you-go flexibility, and Monthly for long-term projects with a consistent usage rate and potentially lower cost.
- Next, you’ll need to select an authentication method. Two methods are available: Password and SSH Key. We recommend using SSH keys, as they are a more secure option. To create one, head over to our official documentation.
Step 6: Finalize Details and Create Deployment
Finally, if you want, you can also add a VPC (Virtual Private Cloud), which provides an isolated section for you 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 according to your needs.
Also, you can deploy multiple nodes at once by clicking + in the Quantity option.
That’s it! You are now 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 take a few seconds or a minute to get deployed. Once it is 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 running VM via SSH:
- 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 dashboard)
ssh root@ip
- If SSH keys are set up, the terminal will authenticate automatically.
- In some cases, your terminal may take your consent before connecting. Enter ‘yes’, and you should be connected.
Output:
Step 8: Add repository and install dependencies
- 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
- Create a directory and save PostgreSQL’s repository key
sudo install -d /usr/share/postgresql-common/pgdg
- 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:
- 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
- Open the pdpg.list with the help of nano editor.
sudo nano /etc/apt/sources.list.d/pgdg.list
- 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).
- 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.
- Update the Ubuntu package list
sudo apt-get update
Output:
- Install PostgreSQL
sudo apt-get install postgresql -y
Output:
- 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:
- 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’
- 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:
- Restart the PostgreSQL server for changes to take effect
sudo systemctl restart postgresql
- 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:
- 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.
- 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:
- 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.
- 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:
- Create a database named “nodeshiftdb”
CREATE DATABASE nodeshiftdb;
Output:
- Grant privileges to the user “nodeshift” for this database
GRANT ALL PRIVILEGES ON DATABASE nodeshiftdb TO nodeshift;
Output:
- Grant CREATE permission to the user
We need to grant the CREATE permission on public schema to the user in order to allow them to 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
- Create a new table in the database
CREATE TABLE gpus (name varchar(50), region varchar(50));
Output:
- Insert a new row in the table
INSERT INTO gpus (name, region) VALUES ('A100X', 'USA');
Output:
- 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.
