How to install PostgreSQL on Ubuntu 24.04 LTS

PostgreSQL offers an open-source solution to anyone who is looking for a secure, extensible, and transactional database management system. Its open-source license makes this technology highly attractive to organizations and big businesses that need to customize software to their specific requirements.

This tutorial will teach you how to install PostgreSQL on Ubuntu 24.04 LTS.

#What is PostgreSQL?

PostgreSQL is a community-driven database management system with a strong reputation for its highly customizable ability and performance. As a developer, you can write your own custom PostgreSQL data types, plugins, and even functions in one of your favorite programming languages.

With support for relational and non-relational querying PostgreSQL is the developer’s primary choice when storing and analyzing complex data.

The fact that PostgreSQL is ACID compliant ensures the data’s integrity.

PostgreSQL conforms to the SQL standard which means that if you have experience with writing SQL there will be no problems or hardships when implementing and deploying your databases.

Due to its support for major operating systems such as Windows, Linux, Mac OS, and a wide range of programming languages PostgreSQL makes it easy for developers coming from different backgrounds.

#What is PostgreSQL used for?

Web developers use PostgreSQL as a database management system for their applications. Django developers prefer to use PostgreSQL as there are many third-party libraries available that are designed for PostgreSQL integration.

Offering scalability, support for JSON data types, and advanced text search PostgreSQL fits perfectly with content management systems.

Due to its support for various geometrical data types out of the box, PostgreSQL is a cornerstone in the development of geospatial applications. Combined with PostGIS PostgreSQL offers all the features and functionalities to anyone writing apps that deal with location data. Whether you are building a vehicle track mobile app or environmental monitoring systems PostgreSQL is a key in your development technology stack.

PostgreSQL is also a good choice for social media applications because it can handle high traffic and concurrency.

According to stackshare.io popular apps like Instagram, Pinterest, Spotify, and Twitch make use of PostgreSQL as their database management system.

#How to install PostgreSQL on Ubuntu 24.04 LTS

#Step 1: Add the official repository and install the dependencies

The PostgreSQL package is provided by default in Ubuntu. To stay updated with the latest package you can manually configure the repository.

First download curl, ca-certificates, pip, venv, and libpq-dev.

sudo apt-get install curl ca-certificates python3-pip python3-venv libpq-dev -y

Note: The above command downloads curl, the Python package installer, venv, and libpq-dev as a dependency for psycopg2. The psycopg2 is a driver that makes it possible to connect and interact with the PostgreSQL database management system through Python applications.

The curl command line tool helps to download files over different protocols. We will use it to download the PostgreSQL’s repository key and save it in our Ubuntu 24.04 LTS host.

The pip tool helps to download Python packages required to run a project. As for venv, it helps to isolate your Python project inside an environment separate from the system-wide packages to avoid conflict resulting from the usage of different versions of Python packages.

Create the directory for the repository key to be saved.

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

The above command creates all directories and subdirectories as specified in the argument with the -d option. You can do the same thing with mkdir -p.

To use curl to retrieve the key from the PostgreSQL repository type:

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

The -o option specifies the path for the output.

To determine the codename of your Ubuntu release type:

lsb_release -cs

Create the sources list for the PostgreSQL package with the help of the nano editor.

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

Write the source code as shown below.

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

Instead of noble, you should put the codename of your distribution which you found out via the lsb_release command. Write to the file with the help of Control O. Type Enter and then exit the nano text editor with Control X.

You can use ls to verify the pgdg.list has been added.

#Step 2: Install PostgreSQL on Ubuntu 24.04 LTS

Before going any further with the installation you should update the software repository. To update the software repository on Ubuntu 24.04 LTS type:

sudo apt-get update

To install PostgreSQL on Ubuntu 24.04 LTS type:

sudo apt-get install postgresql -y

The option -y automatically answers Yes to the questions asked.

To find out if PostgreSQL is running on Ubuntu 24.04 LTS type:

systemctl status postgresql

The systemctl utility checks for the status of a service. The service we are interested in this case is the postgresql.service. The above figure shows the service postgresql.service as active and it also informs about the uptime.

#Step 3: Log into PostgreSQL

PostgreSQL creates a default user named postgres. The postgres user is a superuser, which means that this role has all the privileges one can have inside the PostgreSQL DBMS.

To log into the database server as the user postgres type:

sudo -u postgres psql

The option -u specifies the user while psql stands for the interactive shell used to interact with the database engine. Upon a successful login, you will have a PostgreSQL shell as below.

The superuser postgres doesn’t have a default password. Before going any further you should create one as without it your PostgreSQL database server may become an easy target for cybercriminals once you expose the DBMS to accept remote connections.

To create a password for the postgres user type the following command on the PostgeSQL’s console.

\password postgres

The password is required when you try to connect and interact with PostgreSQL through a scripting language such as Python, Ruby, Perl, etc. It’s very important to create a strong password for the administrator of the PostgreSQL database management system as a good security practice to avoid brute force attacks from the outside.

To update the password for the postgres user from the PostgreSQL console type:

ALTER USER postgres WITH PASSWORD ‘new_password_here’;

Before deploying applications to make use of your database server you need to configure and tweak certain settings inside the PostgreSQL configuration file.

To exit the PostgreSQL console type:

\q

or

exit

Then hit Enter.

#Step 4: Configure PostgreSQL

The configuration file for PostgreSQL 16 on Ubuntu 24.04 LTS is stored inside the /etc/postgresql/16/main subdirectory. As you may have guessed for another version of PostgreSQL you just need to put the exact version in the path /etc/postgresql/exact-version-here/main to locate the configuration file.

To view the files present inside /etc/postgresql/16/main type:

ls /etc/postgresql/16/main

There are two files of interest in figure above, postgresql.conf, and pg_hba.conf. The postgresql.conf is the main configuration file and it defines settings such as the default port for the database server to listen to, the maximum amount of memory per operation, what IP addresses to listen on, and many other parameters required for the maintenance of a DBMS.

To view the contents of the postgresql.conf type:

cat /etc/postgresql/16/main/postgresql.conf | less

The less command helps to view the content of a large file step by step. As for the | it’s an operator which passes the output of one command as input for another one. It’s called piping.

By default, you can’t connect to your PostgreSQL database server from the remote. Since you will probably work remotely it’s a good idea to enable other machines to be able to connect to your database server.

Open the postgresql.conf configuration file with the help of nano or your favorite text editor.

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

Locate the following line.

#listen_addresses = ‘localhost’

The listen_addresses line tells PostgreSQL the IP addresses for the database server to listen on. The above configuration instructs PostgreSQL to listen on and accept connections only from the local loopback network interface.

When you try to connect to your PostgreSQL database from machines on your local network the connections are refused.

Change the above configuration parameter as shown below.

listen_addresses = '*'

The asterisk in the above line tells the PostgreSQL server to listen on all network interfaces. In case the system administrator wants the database server to listen on specific network interfaces, the listen_addresses parameter can be configured to do so.

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

Make sure to separate the IP addresses for the PostgreSQL database server to listen on with a comma.

Type Control O on your keyboard to write the changes and then hit Enter. To exit the nano text editor type Control X.

The next configuration file you need to edit to connect to your PostgreSQL database server from the outside is /etc/postgresql/16/main/pg_hba.conf.

This configuration file specifies what IP address or IP ranges are allowed to connect to the PostgreSQL database server, what accounts, and what authentication mechanisms are to be used to log users.

The following are the columns we need to update.

• connection type
• database
• user
• IP address or range of IPs
• authentication mechanism

As for the connection type the two main ones are host which stands for TCP/IP connections and hostssl which is a TCP/IP socket that is encrypted. For this tutorial, we will use the option host.

When it comes to the database column you can specify one or just leave it as all. The same thing can be applied to the user column.

The default client authentication mechanism in PostgreSQL 16 is scram-sha-256. You can switch to md5 or leave it as it is by default. Both scram-sha-256 and md5 send the passwords encrypted.

Open /etc/postgresql/16/main/pg_hba.conf with the nano text editor.

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

To allow connections from the remote on your PostgreSQL database server you need to specify the ranges of IPs which are allowed to connect to the server, the user or users that are allowed to log into the database management system, and also the databases that the users can access.

For example, if the IP of the client machine is xx.xx.xx.xx you can leave it as a single address or specify the whole range with the CIDR notation below.

host all all xx.xx.xx.xx/24 scram-sha-256

To limit the connection from outside to a specific user change the above line as below.

host all cherryservers xx.xx.xx.xx/24 scram-sha-256

The above configuration allows connections from the outside to the PostgreSQL database server only for the user cherryservers with the IP in the xx.xx.xx.xx/24 range.

To limit connection to a specific database format the above configuration like shown below.

host learning cherryservers xx.xx.xx.xx/24 scram-sha-256

The above configuration allows connections from the remote to the PostgreSQL database server only on the learning database to the user cherryservers with the IP in the xx.xx.xx.xx/24 range.

If you prefer to authenticate PostgreSQL clients through the md5 mechanism instead of scram-sha-256 just change the last column as shown below.

host all cherryservers xx.xx.xx.xx/24 md5

We do not advise due to security concerns. Still, you can also configure the PostgreSQL database server to accept connections from any IP address on any database by any user. Just keep in mind that this increases the attack surface of your server infrastructure as it exposes a service that can be exploited and/or brute-forced by cyber criminals.

Take the following configuration as an example and don’t use it in a production scenario.

host all all 0.0.0.0/0 scram-sha-256

The above line tells the PostgreSQL server to accept connections from all types of IP addresses, on all databases by any user and to authenticate through the scram-sha-256 mechanism.

To avoid authentication failures for local socket connections it is required to edit the following line.

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

Change the authentication method from peer to md5 like shown below.

In case you forgot to edit the file with nano type Control O to write, hit Enter, and then type Control X to exit.

For a deeper understanding of the above columns and their values refer to the comments inside the /etc/postgresql/16/main/pg_hba.conf file. Although the information shared so far on these PostgreSQL configuration parameters is sufficient for a correct and secure database management it’s always good to expand your knowledge on a specific topic.

Once you have finished updating the configuration file, restart the PostgreSQL server for the changes to take place.

sudo systemctl restart postgresql

To check if PostgreSQL is listening for network connections type:

netstat -ano | grep 5432

The netstat command lists the available connections on your network. Then we pipe the output to the grep utility to look for the specific service running on port 5432.

Note: PostgreSQL listens by default on port 5432, but that can be easily modified in the main configuration file.

There may be cases where the firewall blocks incoming connections. To allow connections on port 5432 type:

sudo ufw allow 5432/tcp

To find out if the PostgreSQL database server is reachable remotely use telnet:

telnet your_server_ip 5432

#Step 5: Basic commands in PostgreSQL

Before learning how to operate your PostgreSQL database you should know the basic commands.

Log into the PostgreSQL database with the following command.

sudo -u postgres psql

The above command asks for a password because you have already created one for the user postgres.

To find out the version of PostgreSQL type:

select version();

To list all databases type:

\l

To list all the users type:

\du

To find the current user type:

SELECT current_user;

You can also find information about the current connection with the following command.

\conninfo

To delete a database type:

DROP DATABASE database_name;

To delete a user type:

DROP  USER username;

#Step 6: Create a new user in PostgreSQL

To create a new PostgreSQL user with a password through the psql interactive shell type:

CREATE USER cherry_servers WITH PASSWORD 'cherry_servers';

Upon success, you should see the following message:

CREATE ROLE

You can check if the user has been created with the help of the \du command.

The above figure displays a list of all users present in PostgreSQL 16 and among them is the one you just created.

#Step 7: Create a new database in PostgreSQL

To verify the current database user type:

select current_user;

To create a new database in PostgreSQL type:

CREATE DATABASE learning;

To grant all the privileges to the database learning for the user cherry_servers type:

GRANT ALL PRIVILEGES ON DATABASE learning TO cherry_servers;

With PostgreSQL 15 it is required to grant permission for CREATE on the public schema or the default one. To do so first connect to the database learning as user postgres.

\c learning postgres;

Then grant the CREATE privilege on the public schema to the user cherry_servers.

GRANT ALL ON SCHEMA public to cherry_servers;

Without setting the above permission we can’t create a table using the user cherry_servers inside the learning database.

To connect to the learning database as user cherry_servers type:

\c learning cherry_servers;

Type the password of the new user when asked.

As you can see from the above figure the user is informed about the successful connection on the specified database. You can verify the information on the current connection with the help of the \conninfo command which you learned in the basic PostgreSQL commands section.

To find the current database type:

SELECT current_database();

To create a new table in the current database type:

CREATE TABLE books (title varchar(50), author varchar(50));

The above SQL statement creates a table with two columns inside the database learning. Both of the columns are of the varchar type.

Those who have some experience with SQL should be fine understanding the database queries executed so far.

To insert your first row in the table type:

INSERT INTO books (title, author) VALUES ('learning python', 'mark');

To check the data has been inserted into the learning.books table type:

select * from books;

Note: The current database name will appear on the left of the console reminding the user where their commands are being executed.

#Step 8: Connect to PostgreSQL from the remote

#Step 8.1 Connect to PostgreSQL via pgAdmin GUI client

pgAdmin is a free tool designed to administer the PostgreSQL database server through a graphical user interface.

Once you have finished installing pgAdmin from the official release you will be presented with the following.

Select Add New Server and continue. The General tab asks for information such as the name of the server. Specify one as you wish. The settings specified inside the Connection tab do matter.

Once you have finished specifying the IP of the PostgreSQL server, username, database name, and the port as shown in the above figure click on Save. The connection happens automatically.

On a successful connection, pgAdmin lists the servers on the left panel. Find your server and click on it. A list of databases appears. You can write SQL queries by doing a right click on the database you want to operate and then selecting the PSQL tool.

The following shows an SQL SELECT statement that was executed successfully.

#Step 8.2 Connect to PostgreSQL via Python

There are many open-source libraries available that can be used to connect and interact with the PostgreSQL database management system. As a system administrator, you can write your scripts to automate tasks while working on a PostgreSQL database server.

Python offers the psycopg2 package. It can be used to connect to the PostgreSQL database and also interact with it. You can read, insert, update, and delete data directly through your Python scripts.

First, we highly recommend you create a virtual environment for your Python scripts. This way you can keep the system-wide Python packages clean and avoid dependency conflicts in case your system has multiple versions of Python installed.

To create a new Python virtual environment type:

python3 -m venv postgres

The above command makes use of the Python package venv to create a fresh virtual environment named postgres. Feel free to name yours as you like. For example, you can create a virtual environment named as below.

python3 -m venv postgres_py

To activate the new virtual environment inside a Unix-based host type:

source postgres/bin/activate

To activate the new virtual environment inside a Windows host type:

postgres\Scripts\activate

To install psycopg2 on Ubuntu 24.04 LTS type:

pip install psycopg2

Note: You can use the above command too if you decide to run your PostgreSQL's client script from a Windows machine.

Create a new script named sqlcon.py with the nano text editor.

nano sqlcon.py

Type the following Python code.

import psycopg2


db_ip = "your_database_server_ip_here"
db_port = "5432"
db_name = "your_database_name_here"
db_user = "your_database_user_here"
db_password = "your_database_password_here"


# define a connection obj to connect with the DBMS
con = psycopg2.connect(host=db_ip, port=db_port, dbname=db_name, user=db_user,
                   	password=db_password)


# execute a SELECT sql statement to read data from a table
cur = con.cursor()
cur.execute("SELECT * FROM books")


# retrieve the rows
records = cur.fetchall()

# display the data on the console
print(records)

To run the above script type:

python sqlcon.py

The above script works, but in case of some errors, it interrupts. Python's try/except statement becomes very useful to handle errors in such scenarios. The following is an updated version of the sqlcon.py script.

from psycopg2 import connect
from psycopg2 import OperationalError, errors


# the db settings for connection
db_ip = "your_database_server_ip_here"
db_port = "5432"
db_name = "your_database_name_here"
db_user = "your_database_user_here"
db_password = "your_dbms_password_here"


# define a connection obj to connect with the DBMS
try:
	con = connect(host=db_ip, port=db_port, dbname=db_name, user=db_user,
                       	password=db_password)
except OperationalError as e:
	con = None
	print(e)

if con is not None:
	try:
    	cur = con.cursor()
    	cur.execute("SELECT * FROM books")
    	# retrieve the rows
    	records = cur.fetchall()

    	# display the data on the console
    	print(records)
	except errors.UndefinedTable:
    	print("Table doesn't exist.")

To run the Python script type:

python sqlcon.py

You can see from the above demonstration that each row of PostgreSQL's database table is presented as a tuple in Python.

#Final thoughts

Through this tutorial, you learned how to install the latest stable version of PostgreSQL on Ubuntu 24.04 LTS, how to configure your PostgreSQL database server to listen and accept connections from other client computers, and also some basic commands on the psql interactive shell.

To automate interaction with PostgreSQL you also learned how to write a basic Python script that reads data from an arbitrary table. Now that you have installed PostgreSQL on Ubuntu 24.04 LTS you can improve your database administration skills by diving deep into the official PostgreSQL documentation.