Installing PostgreSQL on Ubuntu 26.04

PostgreSQL is a powerful open-source relational database known for extensibility, standards compliance, and support for advanced data types including JSON and arrays. The PostgreSQL Global Development Group maintains a dedicated APT repository with the latest releases. This guide installs PostgreSQL from the PGDG repository, secures the installation, and creates a database with a dedicated user. By the end, you'll have a hardened PostgreSQL instance with a working database ready for application use.

Install PostgreSQL

The PostgreSQL Global Development Group provides an official APT repository with the latest PostgreSQL releases.

1. Update the APT package index:

$ sudo apt update

2. Install the PostgreSQL common package:

$ sudo apt install postgresql-common -y

3. Run the repository setup script:

$ sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

4. Install PostgreSQL:

$ sudo apt install postgresql -y

5. Verify the installed version:

$ psql --version

Manage the PostgreSQL Service

Enable PostgreSQL to start automatically when the server boots.

1. Enable and start the service:

$ sudo systemctl enable postgresql
$ sudo systemctl start postgresql

2. Check the service status:

$ sudo systemctl status postgresql

3. Stop or restart the service when needed:

$ sudo systemctl stop postgresql
$ sudo systemctl restart postgresql

Secure PostgreSQL

1. Log in as the postgres superuser:

$ sudo -u postgres psql

2. Set a password for the postgres account:

ALTER USER postgres WITH PASSWORD 'your_strong_password';

3. Create a new application user:

CREATE USER example_admin WITH PASSWORD 'secure_password';
\q

4. Update the authentication method in pg_hba.conf:

$ sudo nano /etc/postgresql/18/main/pg_hba.conf

Find the lines for local connections and change peer to scram-sha-256:

local   all             postgres                                scram-sha-256
local   all             all                                     scram-sha-256

5. Restart PostgreSQL to apply changes:

$ sudo systemctl restart postgresql

Create a Database and User

Log in as the postgres superuser and create a database owned by the application user:

$ sudo -u postgres psql

CREATE DATABASE example_store OWNER example_admin;
GRANT ALL PRIVILEGES ON DATABASE example_store TO example_admin;
\q

Create a Sample Table

Log in as the application user to verify access:

$ psql -U example_admin -d example_store -h localhost

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(100),
    price NUMERIC(10, 2)
);

INSERT INTO products (product_name, category, price) VALUES
    ('Widget A', 'Hardware', 9.99),
    ('Widget B', 'Software', 29.99),
    ('Widget C', 'Services', 49.99);

SELECT * FROM products;
\q

All three rows in the query output confirm the database, user, and table are working correctly.

Next Steps

PostgreSQL is now installed and accepting connections. From here you can:

• Use PostgreSQL as the database layer in a web application with Django or Rails
• Set up streaming replication for high availability
• Automate backups with pg_dump and a cron job

For the full guide with additional tips, visit the original article on Vultr Docs.