How to Install PostgreSQL on Ubuntu 24.04

Introduction

PostgreSQL is an advanced, open-source relational database management system known for its reliability, data integrity, and extensive feature set. By installing PostgreSQL on your Raff VM, you gain access to a production-grade database server capable of handling everything from simple web application backends to complex analytical workloads.

Originally developed at the University of California, Berkeley, PostgreSQL has over 35 years of active development. It is fully ACID-compliant, supports advanced data types like JSONB and arrays, and offers powerful features including full-text search, table inheritance, and extensibility through custom functions and extensions like PostGIS and TimescaleDB.

In this tutorial, you will install PostgreSQL 16 from Ubuntu's default package repository, secure the default database user with a password, create a new database and role for your application, enable password authentication, and learn the essential commands for managing your PostgreSQL server.

Step 1 — Install PostgreSQL

PostgreSQL 16 is available in Ubuntu 24.04's default package repository. Update the package index and install the server along with the contrib package, which includes additional utilities and extensions:

bashsudo apt update
sudo apt install -y postgresql postgresql-contrib

The installation automatically creates a postgres system user, initializes the database cluster, and starts the PostgreSQL service.

Verify the installed version:

bashpsql --version

Expected output:

psql (PostgreSQL) 16.x (Ubuntu 16.x-0ubuntu0.24.04.1)

Confirm that PostgreSQL is running:

bashsudo systemctl status postgresql

You should see active (exited) in the output. PostgreSQL uses a main process that spawns worker processes, so the exited status for the parent service is normal. The actual database server runs under the postgresql@16-main sub-service.

Step 2 — Secure the Default PostgreSQL User

PostgreSQL creates a default superuser role called postgres during installation. This role uses peer authentication by default, meaning it can only be accessed from the matching postgres Linux system user without a password. For security, you should set a strong password on this role.

Open the PostgreSQL interactive terminal as the postgres user:

bashsudo -u postgres psql

You are now inside the PostgreSQL prompt, indicated by postgres=#.

Set a strong password for the postgres role:

sqlALTER USER postgres WITH PASSWORD 'your_secure_password_here';

Replace your_secure_password_here with a strong, unique password. Store this password securely.

Exit the PostgreSQL prompt:

sql\q

Step 3 — Create a New Database and Role

Running applications with the postgres superuser is a security risk. Instead, create a dedicated role and database for each application.

Open the PostgreSQL prompt:

bashsudo -u postgres psql

Create a new role with a password. Replace appuser and secure_app_password with your desired values:

sqlCREATE ROLE appuser WITH LOGIN PASSWORD 'secure_app_password';

Create a new database owned by the new role:

sqlCREATE DATABASE appdb OWNER appuser;

Grant the role all privileges on the database:

sqlGRANT ALL PRIVILEGES ON DATABASE appdb TO appuser;

Verify the database was created by listing all databases:

sql\l

You should see appdb in the list with appuser as the owner.

Exit the PostgreSQL prompt:

sql\q

Step 4 — Enable Password Authentication

By default, PostgreSQL on Ubuntu uses peer authentication for local connections, which means it matches the database role to the Linux system user. To allow password-based login (which most applications require), you need to modify the pg_hba.conf configuration file.

Open the PostgreSQL client authentication configuration file:

bashsudo nano /etc/postgresql/16/main/pg_hba.conf

Find the following line under the "local" connections section:

local   all             all                                     peer

Change peer to scram-sha-256:

local   all             all                                     scram-sha-256

The scram-sha-256 method is the most secure password authentication mechanism available in PostgreSQL. It hashes passwords using the SCRAM-SHA-256 algorithm.

Save and close the file, then restart PostgreSQL to apply the changes:

bashsudo systemctl restart postgresql

Test the new authentication by logging in as the application user:

bashpsql -U appuser -d appdb -W

The -W flag prompts for a password. Enter the password you set for appuser. If authentication succeeds, you will see the appdb=> prompt.

Step 5 — Test the Database with Basic Operations

With your new role and database ready, run some basic SQL operations to verify everything works.

If you are not already connected, log in:

bashpsql -U appuser -d appdb -W

Create a sample table:

sqlCREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Insert sample data:

sqlINSERT INTO employees (name, email) VALUES
    ('Alice Johnson', 'alice@example.com'),
    ('Bob Smith', 'bob@example.com'),
    ('Carol Williams', 'carol@example.com');

Query the data to confirm it was inserted:

sqlSELECT * FROM employees;

Expected output:

 id |      name       |       email        |         created_at
----+-----------------+--------------------+----------------------------
  1 | Alice Johnson   | alice@example.com  | 2026-03-13 10:30:00.000000
  2 | Bob Smith       | bob@example.com    | 2026-03-13 10:30:00.000000
  3 | Carol Williams  | carol@example.com  | 2026-03-13 10:30:00.000000

Clean up by dropping the test table:

sqlDROP TABLE employees;

Exit the PostgreSQL prompt:

sql\q

Step 6 — Learn Essential PostgreSQL Management Commands

Here are the key commands you will use regularly when managing your PostgreSQL server.

Service management with systemd:

bashsudo systemctl start postgresql      # Start the server
sudo systemctl stop postgresql       # Stop the server
sudo systemctl restart postgresql    # Restart the server
sudo systemctl reload postgresql     # Reload configuration without restart
sudo systemctl status postgresql     # Check server status
sudo systemctl enable postgresql     # Enable start at boot

Useful psql meta-commands (run inside the psql prompt):

• \l — List all databases
• \du — List all roles (users)
• \dt — List tables in the current database
• \c dbname — Connect to a different database
• \conninfo — Display current connection information
• \q — Exit the psql prompt

Key configuration files:

• /etc/postgresql/16/main/postgresql.conf — Main server configuration (memory, connections, performance tuning)
• /etc/postgresql/16/main/pg_hba.conf — Client authentication configuration
• /var/log/postgresql/ — Log files directory
• /var/lib/postgresql/16/main/ — Data directory

Conclusion

You have installed PostgreSQL on your Raff Ubuntu 24.04 VM, secured the default superuser, created an application-specific database and role, enabled password authentication, and verified the setup with basic SQL operations.

From here, you can:

• Configure remote access to connect from separate application servers
• Set up automated backups using pg_dump and cron jobs, complemented by Raff's VM snapshot and automated backup features
• Install extensions like PostGIS for geospatial data or pg_stat_statements for query performance analysis
• Tune performance settings in postgresql.conf based on your workload and VM resources

Raff VMs use NVMe SSD storage, which provides the fast random I/O that PostgreSQL needs for query-intensive workloads. For database servers handling concurrent read and write operations, the 2 vCPU / 4 GB RAM tier ($19.99/month) or higher provides enough headroom for most small to medium production deployments.