Maximum performance at minimal cost. Start your server in seconds now!
    Vijona

    Install and Secure PostgreSQL on Ubuntu 25.04

    PostgreSQL is a powerful, open-source relational database system known for its stability, scalability, and advanced features such as JSON support, custom data types, and complex query handling. It’s an excellent choice for managing large datasets in analytics, GIS, and dynamic web applications. Backed by decades of development and a strong community, PostgreSQL ensures reliability and data integrity. Developers value its flexibility and seamless integration with modern data workflows.

    This guide explains how to install PostgreSQL on an Ubuntu 25.04 server, enable it to start automatically, and secure the database for production environments.

    Prerequisites

    Before starting, ensure the following:

    • Access to an Ubuntu 25.04 server as a non-root user with sudo privileges.

    Install PostgreSQL

    PostgreSQL is included in Ubuntu’s default APT repository, which allows for an easy setup. Follow the steps below to install PostgreSQL, enable the official repository, and ensure it starts automatically when your server boots. Once complete, PostgreSQL will be ready for use.

    Update the APT Package Index

    Copy Code 
    

    $ sudo apt update

    Install PostgreSQL Common Package

    Install the package that provides shared utilities and configuration management for PostgreSQL.

    Copy Code 
    

    $ sudo apt install -y postgresql-common

    Add the PostgreSQL APT Repository

    Run the setup script to include the official PostgreSQL repository in your system.

    Copy Code 
    

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

    When prompted, press Enter to confirm:

    This script will enable the PostgreSQL APT repository on apt.postgresql.org on
your system. The distribution codename used will be plucky-pgdg.

Press Enter to continue, or Ctrl-C to abort.

    Install the PostgreSQL Server Package

    Copy Code 
    

    $ sudo apt install -y postgresql

    Start the PostgreSQL Service

    Copy Code 
    

    $ sudo systemctl start postgresql

    Enable PostgreSQL at Boot

    Ensure PostgreSQL starts automatically when the system reboots.

    Copy Code 
    

    $ sudo systemctl enable postgresql

    Example 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

    Check PostgreSQL Service Status

    Copy Code 
    

    $ sudo systemctl status postgresql

    Expected output:

    ● postgresql.service - PostgreSQL RDBMS
    Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; preset: enabled)
    Active: active (exited) since Thu 2025-09-18 17:50:25 UTC; 1min 32s ago
...

    Secure the PostgreSQL Database Server

    PostgreSQL uses the default postgres superuser account for administrative tasks such as user and database management. By default, Ubuntu enables peer authentication, allowing only local users to connect without a password. To enhance security, follow the steps below to enable password authentication and restrict access to authorized users.

    Check PostgreSQL Version

    Copy Code 
    

    $ psql --version

    Example output:

    psql (PostgreSQL) 17.6 (Ubuntu 17.6-1.pgdg25.04+1)

    Log In as postgres User

    Copy Code 
    

    $ sudo -u postgres psql

    Set a New Password for postgres User

    In the PostgreSQL console, set a strong password:

    Copy Code 
    

    postgres=# ALTER USER postgres WITH ENCRYPTED PASSWORD 'strong_password';

    Replace strong_password with a secure, randomly generated password.

    Create a New User

    Create a user named park_admin and assign a strong password:

    Copy Code 
    

    postgres=# CREATE USER park_admin ENCRYPTED PASSWORD 'strong_password';

    Exit PostgreSQL Console

    Copy Code 
    

    postgres=# EXIT;

    Enable Password Authentication

    Edit the pg_hba.conf configuration file to use password-based authentication.

    Copy Code 
    

    $ sudo sed -i '/^local/s/peer/scram-sha-256/' /etc/postgresql/17/main/pg_hba.conf

    If your PostgreSQL version differs, replace 17 with your installed version.

    Restart PostgreSQL Service

    Restart the PostgreSQL service to apply the authentication changes.

    Copy Code 
    

    $ sudo systemctl restart postgresql

    Check PostgreSQL Status

    Verify that PostgreSQL is running correctly.

    Copy Code 
    

    $ sudo systemctl status postgresql

    Example output:

    ● postgresql.service - PostgreSQL RDBMS
    Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; preset: enabled)
    Active: active (exited) since Thu 2025-09-18 18:25:59 UTC; 948ms ago
...

    Access the PostgreSQL Database Server

    You can access the PostgreSQL database console using the pre-installed psql utility included with the PostgreSQL server package. Alternatively, graphical database tools can connect directly to your PostgreSQL instance. Follow these steps to log in and create a sample database for a non-privileged user.

    Create a Sample Database

    Create a new PostgreSQL database named amusement_park and assign ownership to the park_admin user.

    Copy Code 
    

    $ sudo -u postgres createdb amusement_park -O park_admin

    When prompted, enter the postgres user password that you previously created.

    Log In as park_admin User

    Log in to the PostgreSQL database using the park_admin user credentials.

    Copy Code 
    

    $ sudo -u postgres psql -U park_admin -d amusement_park

    When prompted, enter the park_admin password and press Enter to access the database.

    Create a New Table

    Create a table named visitors in the amusement_park database.

    Copy Code 
    

    amusement_park=> CREATE TABLE visitors (
    visitor_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    entry_date DATE
);

    This SQL command creates a table with the following columns:

    • visitor_id: Primary key that uniquely identifies each record.
    • SERIAL: Automatically generates an incremental ID for new entries.
    • first_name and last_name: Store the visitor’s name.
    • entry_date: Stores the visitor’s date of entry.

    Insert Sample Data

    Add sample visitor data to the visitors table.

    Copy Code 
    

    amusement_park=> INSERT INTO visitors
    (first_name, last_name, entry_date)
    VALUES
    ('Alice', 'Wong', '2024-11-15'),
    ('Brian', 'Taylor', '2024-02-28'),
    ('Clara', 'Nguyen', '2024-04-10');

    Query the Visitors Table

    Retrieve all records from the visitors table.

    Copy Code 
    

    amusement_park=> SELECT * FROM visitors;

    Expected output:

     visitor_id | first_name | last_name | entry_date 
------------+------------+-----------+------------
          1 | Alice      | Wong      | 2024-11-15
          2 | Brian      | Taylor    | 2024-02-28
          3 | Clara      | Nguyen    | 2024-04-10
(3 rows)

    Exit PostgreSQL Console

    Exit the psql shell once finished.

    Copy Code 
    

    amusement_park=> EXIT;

    Conclusion

    You have successfully installed PostgreSQL on your Ubuntu 25.04 server, secured the database, and created a sample database with a table using the psql utility. You can now connect PostgreSQL to your applications for secure data management. For additional configuration details, refer to the official PostgreSQL documentation.

    Source: vultr.com

    Create a Free Account

    Register now and get access to our Cloud Services.

    Try now

    Posts you might be interested in: