PostgreSQL Installation on Ubuntu

PostgreSQL is a powerful open-source relational database management system (RDBMS) known for its reliability, extensibility, and compliance with SQL standards. It is widely used in enterprise applications, data warehousing, web applications, and cloud environments.

This document describes the steps required to install and configure PostgreSQL on an Ubuntu Linux server.

Prerequisites

Before starting the installation, ensure the following:

• Ubuntu Server (20.04, 22.04, or later)
• Root or sudo privileges
• Internet connectivity for package download
• Minimum 2 GB RAM and adequate disk space

Step 1: Update System Packages

Update the package repository to ensure the latest package information is available.

root@Amit-Gupta:~# sudo apt update
root@Amit-Gupta:~# sudo apt upgrade -y
root@Amit-Gupta:~# apt install sysstat* -y
root@Amit-Gupta:~# apt install net-tool* -y
root@Amit-Gupta:~# sudo apt install -y wget gnupg2 lsb-release

Download the PostgreSQL GPG Key
wget -qO - https://www.postgresql.org/media/keys/ACCC4CF8.asc
wget downloads files from the internet.
-q runs wget in quiet mode (suppresses output).
-O - sends the downloaded content to standard output (stdout) instead of saving it to a file.
https://www.postgresql.org/media/keys/ACCC4CF8.asc is the official PostgreSQL public signing key.
The pipe (|) sends the downloaded key directly to the next command.
Convert the Key to Binary Format
sudo gpg --dearmor -o /usr/share/keyrings/postgresql.gpg
gpg is the GNU Privacy Guard utility.
--dearmor converts the ASCII-armored key (.asc) into a binary GPG keyring format.
-o /usr/share/keyrings/postgresql.gpg saves the converted key to the specified location.
sudo is required because writing to /usr/share/keyrings requires root privileges.
Result
After execution, the file:
/usr/share/keyrings/postgresql.gpg
is created and contains the PostgreSQL repository's trusted signing key.
Why This Is Required
When you add the PostgreSQL APT repository, Ubuntu uses this key to:
Verify package authenticity.
Ensure packages have not been tampered with.
Establish trust between your server and the PostgreSQL package repository.
root@Amit-Gupta:~# wget -qO - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /usr/share/keyrings/postgresql.gpg
Next Step
Add the PostgreSQL repository:root@Amit-Gupta:~# echo "deb [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/pgdg.list

Step 2: Install PostgreSQL

Install PostgreSQL and additional contributed modules.

root@Amit-Gupta:~# sudo apt install -y postgresql-17

                                                            OR

root@Amit-Gupta:~#  sudo apt install postgresql postgresql-contrib -y

root@Amit-Gupta:~# sudo apt install -y postgresql-client-17 postgresql-contrib-17

The installation process automatically creates a PostgreSQL service and initializes a default database cluster.

Step 3: Verify Installation

Verify that the PostgreSQL service is running:

root@Amit-Gupta:~# sudo systemctl status postgresql

Check the PostgreSQL version:

root@Amit-Gupta:~# psql --version

psql (PostgreSQL) 17.10 (Ubuntu 17.10-1.pgdg26.04+1)

Step 4: Start and Enable PostgreSQL Service

If the service is not running, start it manually:

root@Amit-Gupta:~# sudo systemctl start postgresql

Enable automatic startup during system boot:

root@Amit-Gupta:~# sudo systemctl enable postgresql

root@Amit-Gupta:~# sudo systemctl status postgresql@17-main.service

Step 5: Connect to PostgreSQL
Switch to the PostgreSQL operating system user:
root@Amit-Gupta:~# sudo -i -u postgres
Access the PostgreSQL command-line interface:
postgres@Amit-Gupta:~$ psql
psql (17.10 (Ubuntu 17.10-1.pgdg26.04+1))
Type "help" for help.

postgres=# \q
Step 6: Create a Database User
Login to PostgreSQL:
root@Amit-Gupta:~# sudo -u postgres psql
Create a new database user:
postgres=# CREATE USER postgres WITH PASSWORD 'postgres';

                        OR

postgres=# ALTER USER postgres PASSWORD 'postgres';
ALTER ROLE
Step 7: Create a Database
Create a new database:
postgres=# CREATE DATABASE appdb OWNER postgres;
Grant privileges:
postgres=# GRANT ALL PRIVILEGES ON DATABASE appdb TO postgres;
Verify database creation:
postgres=# \l
Verify users:
postgres=# \du

postgres=# \q
Step 8: Configure Remote Connections (Optional)
Modify postgresql.conf
Edit the PostgreSQL configuration file:
root@Amit-Gupta:~# sudo vi /etc/postgresql/17/main/postgresql.conf
Locate:
#listen_addresses = 'localhost'
Change to:
listen_addresses = '*'
Modify pg_hba.conf
Edit client authentication settings:
root@Amit-Gupta:~# sudo vi /etc/postgresql/17/main/pg_hba.conf
Add the following entry:
host    all    all    0.0.0.0/0    md5

                    OR

host    all   all     0.0.0.0/0   scram-sha-256
Restart PostgreSQL
root@Amit-Gupta:~# sudo systemctl restart postgresql

Step 9: Configure Firewall
Allow PostgreSQL default port 5432:
root@Amit-Gupta:~# sudo ufw allow 5432/tcp
Verify firewall rules:
root@Amit-Gupta:~# sudo ufw status
root@Amit-Gupta:~# sudo netstat -tulpn | grep 5432
Useful PostgreSQL Commands
Check database list:
postgres=# \l
Connect to a database:
postgres=# \c appdb
Display tables:
postgres=# \dt
Display users:
postgres=# \du
Exit PostgreSQL:
postgres=# \q

PostgreSQL has been successfully installed and configured on Ubuntu. 
The database service is running, users and databases can be created, 
and remote access can be enabled when required. PostgreSQL provides 
a robust platform for enterprise applications, data analytics, and mission-critical 
database workloads.