How to Install PostgreSQL on Ubuntu 26

Install PostgreSQL 18

Please run below command to install and setup PostgreSQL on ubuntu 26.04 and above.

# Remove an older repository definition, if present
sudo rm -f /etc/apt/sources.list.d/pgdg.list
sudo rm -f /etc/apt/sources.list.d/pgdg.sources

# Install required packages
sudo apt update
sudo apt install -y curl ca-certificates

# Download the PostgreSQL repository signing key
sudo install -d /usr/share/postgresql-common/pgdg

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

# Configure the PostgreSQL repository
. /etc/os-release

sudo tee /etc/apt/sources.list.d/pgdg.sources >/dev/null <<EOF
Types: deb
URIs: https://apt.postgresql.org/pub/repos/apt
Suites: ${VERSION_CODENAME}-pgdg
Architectures: $(dpkg --print-architecture)
Components: main
Signed-By: /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc
EOF

# Update package metadata
sudo apt update

Install PostgresSQL

sudo apt install -y postgresql-18 postgresql-contrib

Enable PostgresSQL and Service to start on reboot

sudo systemctl enable --now postgresql
psql --version
sudo systemctl status postgresql

Output

Synchronizing state of postgresql.service with SysV service script with /usr/lib/systemd/systemd-sysv-install.
Executing: /usr/lib/systemd/systemd-sysv-install enable postgresql
psql (PostgreSQL) 18.4 (Ubuntu 18.4-1.pgdg26.04+1)
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; preset: enabled)
     Active: active (exited) since Tue 2026-07-21 14:47:23 UTC; 30s ago
 Invocation: 6fff13b3b7604e3395e60a6edb768507
   Main PID: 5210 (code=exited, status=0/SUCCESS)
   Mem peak: 1.7M
        CPU: 15ms

Setting up database and user

sudo -u postgres psql

You should see a prompt similar to this: postgres=#

There is no need to set or use the postgres database password for this administrative connection. The sudo -u postgres command authenticates through the local operating-system account.

Create the application role

Inside the PostgreSQL prompt, run:

SHOW password_encryption;

It should report scram-sha-256

SCRAM-SHA-256 is PostgreSQL’s current secure password-authentication method; MD5 password authentication is deprecated.

Create a restricted application role:

CREATE ROLE demo_app
WITH
    LOGIN
    NOSUPERUSER
    NOCREATEDB
    NOCREATEROLE
    NOREPLICATION;

Set its password securely:

\password demo_app

PostgreSQL will prompt:

Enter new password for user "demo_app":
Enter it again:

The password will not be displayed. Using \password also keeps the clear-text password out of SQL history and logs.

Use a long, unique password and save it in your application’s secret manager or protected environment file.

Create the database

Still inside psql, run:

CREATE DATABASE demo_dev
    OWNER demo_app
    ENCODING 'UTF8';

Creating the database with OWNER smg_app is cleaner than creating it under postgres and then running broad GRANT ALL commands. PostgreSQL supports assigning the owner directly during CREATE DATABASE.

Connect to the database:

\connect demo_dev

Verify the owner:

SELECT
    datname AS database_name,
    pg_get_userbyid(datdba) AS database_owner
FROM pg_database
WHERE datname = current_database();

Expected result:

 database_name | database_owner
---------------+---------------
 demo_dev       | demo_app

Exit:

\q

Test password authentication

Use -h 127.0.0.1 so PostgreSQL performs a TCP/password connection rather than local peer authentication:

psql -h 127.0.0.1 -p 5432 -U demo_app -d demo_dev -W

Enter the password created earlier.

Once connected, verify the session:

\conninfo

You should see something similar to:

You are connected to database "demo_dev" as user "demo_app"
on host "127.0.0.1" at port "5432".

Test that the role can create and use tables:

CREATE TABLE database_healthcheck (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    checked_at timestamptz NOT NULL DEFAULT now(),
    message text NOT NULL
);

INSERT INTO database_healthcheck (message)
VALUES ('PostgreSQL setup successful');

SELECT * FROM database_healthcheck;

Then remove the test table:

DROP TABLE database_healthcheck;

Exit:

\q

Allow a remote application Server Access

First find PostgreSQL’s configuration files:

sudo -u postgres psql -Atc "SHOW config_file;"
sudo -u postgres psql -Atc "SHOW hba_file;"

Find the PostgreSQL server’s private IP:

hostname -I

Set PostgreSQL to listen on its private IP, replacing 10.0.0.10 with the database server’s actual private IP:

sudo -u postgres psql -c \
  "ALTER SYSTEM SET listen_addresses = 'localhost,10.0.0.10';"

Get the authentication file path:

HBA_FILE=$(sudo -u postgres psql -Atc "SHOW hba_file;")
echo "$HBA_FILE"

Edit it:

sudoedit "$HBA_FILE"

Add a narrowly scoped rule, replacing 10.0.0.20 with the application server’s private IP:

host    demo_dev    demo_app    10.0.0.0/32    scram-sha-256

or use below to open access to all

host    all    all    10.0.0.0/32    scram-sha-256

Save “$HBA_FILE”

Restart PostgreSQL:

sudo systemctl restart postgresql
sudo systemctl status postgresql --no-pager

OPTION: Firewall Access

sudo ufw insert 1 allow 22/tcp
sudo ufw allow from 10.116.0.0/20 to any port 5432 proto tcp
sudo ufw enable
sudo ufw status numbered

Connecting from remote machine from internet

Recommended: SSH tunnel

Run this from PowerShell or Terminal on your local computer:

Linux:

ssh -N \
  -L 127.0.0.1:15432:127.0.0.1:5432 \
  <user>@<VPS_PUBLIC_IP>

Windows PowerShell:

ssh -N -L 127.0.0.1:15432:127.0.0.1:5432 <user>@<VPS_PUBLIC_IP>

Using SSH Key:

ssh -i C:\path\to\private-key.pem -N -L 127.0.0.1:15432:127.0.0.1:5432 <user>@<VPS_PUBLIC_IP>

When SSH uses a nonstandard port:

ssh -p 2222 -N -L 127.0.0.1:15432:127.0.0.1:5432 <user>@<VPS_PUBLIC_IP>

The -L option forwards a port on your local computer through SSH to PostgreSQL on the VPS.

Connect through the tunnel

Execute below command from Linux terminal

psql \
  -h 127.0.0.1 \
  -p 15432 \
  -U demo_app \
  -d demo_dev \
  -W

Option 1: Full DBA user for administration

Use this only for a trusted human administrator. A PostgreSQL superuser bypasses normal database permission checks and can access every database and object. PostgreSQL warns that superuser status is dangerous and should be granted only when necessary.

Open PostgreSQL:

sudo -u postgres psql

Create the account:

CREATE ROLE platform_admin
WITH
    LOGIN
    SUPERUSER;

Set the password without placing it in command history:

\password platform_admin

Exit:

\q

Reload:

sudo systemctl reload postgresql