How to Install PostgreSQL Ubuntu

PostgreSQL — often called Postgres — is an object-relational database system with full ACID compliance, advanced indexing, and support for both SQL and JSON querying. This guide covers how to install PostgreSQL on Ubuntu, configure it for remote access using scram-sha-256 authentication, set up streaming replication between a primary and standby server, and schedule regular backups.

How to Install PostgreSQL on Ubuntu

Getting Postgres onto your system takes a single command. Open your terminal and run:

sudo apt install postgresql

The service starts automatically once the installation finishes. Ubuntu’s default repositories include PostgreSQL, which is enough for most deployments. For a specific newer version, the PostgreSQL Global Development Group (PGDG) repository provides the latest releases through the same apt package manager workflow.

How to Configure PostgreSQL on Ubuntu After Installation

Configuration files live at /etc/postgresql/<version>/main. For PostgreSQL 14, that path is /etc/postgresql/14/main.

Ubuntu uses peer authentication by default for local connections and scram-sha-256 for host connections. The scram-sha-256 method replaced md5 starting with Ubuntu 21.10. The steps below configure TCP/IP access using scram-sha-256.

Step 1: Allow Remote Connections to PostgreSQL on Ubuntu

By default, Postgres only accepts connections from the local machine. Edit this file to open it up:

/etc/postgresql/*/main/postgresql.conf

Find this line:

#listen_addresses = 'localhost'

Change it to:

listen_addresses = '*'

The * value tells Postgres to accept connections on all available network interfaces, both IPv4 and IPv6. To restrict to IPv4 only, use 0.0.0.0. For IPv6 only, use ::.

Step 2: Set a Password for the postgres User

Connect to the default template database:

sudo -u postgres psql template1

At the SQL prompt, set the password:

ALTER USER postgres with encrypted password 'your_password';

Step 3: Update the pg_hba.conf Authentication File

Edit /etc/postgresql/*/main/pg_hba.conf and add a line like this, replacing the network range with your own:

hostssl template1   postgres   192.168.1.1/24   scram-sha-256

The hostssl keyword rejects any TCP connection not using SSL. Ubuntu’s Postgres build includes SSL by default, so this works without additional setup. For production environments, replace the default certificate from the ssl-cert package with one from a recognized Certificate Authority.

Step 4: Restart PostgreSQL on Ubuntu to Apply Changes

Apply the configuration changes by restarting the service:

sudo systemctl restart postgresql.service

The systemctl command reference documents the full set of options available for service management, including reload, stop, enable, and status.

Step 5: Test the PostgreSQL Connection from Another Machine

Install the client tool and connect from a remote host:

sudo apt install postgresql-client
psql --host your-servers-dns-or-ip --username postgres --password --dbname template1

A successful prompt confirms the configuration is working.

PostgreSQL Streaming Replication Setup on Ubuntu

Postgres has a built-in feature called streaming replication. It continuously ships Write-Ahead Log (WAL) records from a primary server to one or more standby servers to keep them current.

Configure the Primary Server for Replication

Create a dedicated replication user:

sudo -u postgres createuser --replication -P -e replicator

Edit postgresql.conf to enable replication:

listen_addresses = '*'
wal_level = replica

Add an entry to pg_hba.conf allowing the standby to connect:

host  replication   replicator   <standby IP>   scram-sha-256

Restart Postgres on the primary:

sudo systemctl restart postgresql

Configure the Standby Server for PostgreSQL Replication

Stop the service on the standby:

sudo systemctl stop postgresql

Set hot_standby in postgresql.conf:

hot_standby = on

Then copy the primary’s data directory onto the standby using pg_basebackup:

sudo su - postgres
cp -R /var/lib/postgresql/14/main /var/lib/postgresql/14/main_bak
rm -rf /var/lib/postgresql/14/main/*
pg_basebackup -h <main server IP> -D /var/lib/postgresql/14/main -U replicator -P -v -R

Start Postgres on the standby:

sudo systemctl start postgresql

pg_basebackup Flags Explained

Flag	Purpose
-h	Hostname or IP address of the primary server
-D	Target data directory on the standby
-U	Database user for the operation (must have REPLICATION privilege)
-P	Shows progress during the backup transfer
-v	Verbose output, including file-level detail
-R	Creates standby.signal and writes connection settings into postgresql.auto.conf

Verify Replication Is Running

On the primary server, run:

sudo -u postgres psql -c "select * from pg_stat_replication;"

An active row in the output confirms the standby is connected. To verify data flows correctly, create a test database on the primary and check for it on the standby:

sudo -u postgres createdb test        # on the primary
sudo -u postgres psql -c "\l"         # on the standby

Backing Up PostgreSQL Databases on Ubuntu

Run backups on a regular schedule. The PostgreSQL Administrator’s Guide covers the available approaches, including pg_dump, pg_dumpall, and continuous archiving. Install it locally with:

sudo apt install postgresql-doc

Open it with:

xdg-open /usr/share/doc/postgresql-doc-*/html/index.html

The pg_config utility can help locate PostgreSQL’s binary, documentation, and library directories on your system — useful when you need to track down installation paths across multiple Postgres versions on the same machine.

FAQs