Install and Configure PostgreSQL on Ubuntu : A Complete 2025 Guide

✅ Introduction

PostgreSQL is one of the most powerful and reliable open-source relational database systems available today. Whether you're setting up a development environment or preparing a production server, PostgreSQL on Ubuntu is a rock-solid choice. This guide covers everything from installation to user management and database operations.

📥 1. Installing PostgreSQL on Ubuntu Using APT

PostgreSQL 14–17

Ubuntu 20.04, 22.04, and 24.04 LTS

Ubuntu’s package manager makes it easy to install PostgreSQL.

Step 1: Update system packages

sudo apt update && sudo apt upgrade -y

Step 2: Install PostgreSQL

sudo apt install postgresql postgresql-contrib -y

Step 3: Verify PostgreSQL status

sudo systemctl status postgresql

The PostgreSQL server should now be running.

🔐 2. Set PostgreSQL Superuser (postgres) Password

By default, PostgreSQL uses a role-based authentication and creates a user called postgres.

Switch to the postgres user:

sudo -i -u postgres

Enter PostgreSQL prompt:

psql

Set the password:

\password postgres

Exit psql and return to your system user:

\q exit

👥 3. Create a New PostgreSQL User and Assign Password

To add your own user:

sudo -i -u postgres createuser --interactive

Follow the prompt to name your user and choose whether to grant them superuser privileges.

OR create user with password directly:

sudo -i -u postgres psql

CREATE USER myuser WITH PASSWORD 'strongpassword';

🎛️ 4. Grant Roles and Permissions

Grant access to databases or privileges:

ALTER USER myuser CREATEDB; -- Or to grant all privileges: GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

🧪 5. Create Database and Tables (Test Setup)

Create a database:

CREATE DATABASE mydb;

Connect to the database:

\c mydb

Create a table:

CREATE TABLE users ( id SERIAL PRIMARY KEY, name VARCHAR(100), email VARCHAR(100) UNIQUE );

Insert sample data:

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

View table contents:

SELECT * FROM users;

🧼 6. Exit PostgreSQL and Test as New User

1. Exit PostgreSQL:

\q exit

2. Try logging in with your new user:

psql -U myuser -d mydb -h 127.0.0.1 -W

✅ Conclusion

You now have PostgreSQL installed and configured securely on Ubuntu. From setting passwords to creating users and running queries, this setup gives you a strong foundation for development or production use.