Installing and Setting Up PostgreSQL on Ubuntu Linux

This tutorial guides you through installing and configuring PostgreSQL on an Ubuntu Linux server and creating your first multi-table database.

Part 1: Installing PostgreSQL

Step 1: Update the System

Update the package list to ensure the latest versions are installed:

sudo apt update && sudo apt upgrade -y

Step 2: Install PostgreSQL

Install PostgreSQL using the default package manager:

sudo apt install postgresql postgresql-contrib -y

Step 3: Verify Installation

Check if PostgreSQL is running:

sudo systemctl status postgresql

If it’s not active, start and enable it to run on boot:

sudo systemctl start postgresql
sudo systemctl enable postgresql

Part 2: Configuring PostgreSQL

Step 1: Access PostgreSQL Shell

Switch to the postgres user and access the PostgreSQL interactive terminal (psql):

sudo -i -u postgres
psql

Step 2: Set Up a New User and Database

  1. Create a new database: CREATE DATABASE my_database;
  2. Create a new user with a password: CREATE USER my_user WITH PASSWORD 'strong_password';
  3. Grant privileges to the user on the database: GRANT ALL PRIVILEGES ON DATABASE my_database TO my_user;
  4. Exit the psql shell: \q
  5. Return to your normal user shell: exit

Part 3: Creating Your First Multi-Table Database

Step 1: Log In as the New User

Log in to PostgreSQL using the newly created user:

psql -U my_user -d my_database -h localhost

Enter the password for the user when prompted.

Step 2: Create Tables

  1. Create a users table: CREATE TABLE users ( user_id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(100) UNIQUE NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
  2. Create an orders table: CREATE TABLE orders ( order_id SERIAL PRIMARY KEY, user_id INT NOT NULL, order_date DATE NOT NULL, amount NUMERIC(10, 2) NOT NULL, FOREIGN KEY (user_id) REFERENCES users(user_id) );

Step 3: Insert Sample Data

  1. Insert data into users: INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com'), ('Bob', 'bob@example.com');
  2. Insert data into orders: INSERT INTO orders (user_id, order_date, amount) VALUES (1, '2025-01-01', 100.00), (2, '2025-01-02', 200.50);

Step 4: Query Your Data

  1. Join the tables to view data: SELECT users.name, orders.order_date, orders.amount FROM users JOIN orders ON users.user_id = orders.user_id;
  2. Exit the PostgreSQL shell: \q

Part 4: Administering PostgreSQL

Step 1: Back Up Your Database

Use the pg_dump tool to back up your database:

pg_dump -U my_user -d my_database > my_database_backup.sql

Step 2: Restore from Backup

Restore your database from a backup file:

psql -U my_user -d my_database -f my_database_backup.sql

Step 3: Check Logs and Status

To monitor PostgreSQL logs for errors:

sudo tail -f /var/log/postgresql/postgresql-*.log

Part 5: Cleaning Up

If needed, drop tables or databases:

  1. Drop a table: DROP TABLE orders;
  2. Drop a database: DROP DATABASE my_database;

This tutorial provides a complete setup and initial use of PostgreSQL on an Ubuntu server. With these steps, you can start creating and managing more complex databases as you grow familiar with PostgreSQL’s features.

Scroll to Top