Installing Postgres and PgAdmin along with Apache2 Server on Ubuntu Server

Gopi Pacha

Gopi Pacha

— 6 min read

System Update

First, update your Ubuntu system to ensure all packages are current:

sudo apt update
sudo apt upgrade -y

PostgreSQL Installation

Install PostgreSQL Server

sudo apt install postgresql -y

Start and Enable PostgreSQL Service

sudo systemctl start postgresql
sudo systemctl enable postgresql

Verify PostgreSQL Installation

sudo systemctl status postgresql

You should see active (running) in the output.

Check PostgreSQL Version

psql --version

PostgreSQL Configuration

Switch to PostgreSQL User

PostgreSQL creates a default user called postgres. Switch to this user:

sudo -i -u postgres

Access PostgreSQL Prompt

psql

You should see the PostgreSQL prompt: postgres=#

Set Password for Postgres User (Optional If needed)

ALTER USER postgres WITH PASSWORD 'StrongPassword123!';

Exit PostgreSQL Prompt

\q

Exit from Postgres User

exit

Configure PostgreSQL for Remote Access (Optional)

If you need to access PostgreSQL from remote machines:

Edit postgresql.conf

sudo vi /etc/postgresql/14/main/postgresql.conf

Find and modify the line:

listen_addresses = 'localhost'

Change it to:

listen_addresses = '*'

Save and exit (Press esc and Type wq! then Enter).

Edit pg_hba.conf

sudo vi /etc/postgresql/14/main/pg_hba.conf

Add this line at the end of the file:

host    all             all             0.0.0.0/0               md5

Save and exit.

Restart PostgreSQL

sudo systemctl restart postgresql

Create Database User and Database

Switch to PostgreSQL User

sudo -i -u postgres

Access PostgreSQL Prompt

psql

Create a New Database User

CREATE USER appuser WITH PASSWORD 'AppUserPassword123!';

Create a New Database

CREATE DATABASE myappdb;

Grant All Privileges to User on Database

GRANT ALL PRIVILEGES ON DATABASE myappdb TO appuser;

Connect to the New Database

\c myappdb

Grant Schema Privileges

GRANT ALL ON SCHEMA public TO appuser;

Exit PostgreSQL

\q
exit

Create Tables

Now let's create a sample table and insert some data.

Connect to Database as New User

psql -U appuser -d myappdb -h localhost

Enter the password when prompted: AppUserPassword123!

Create a Sample Table

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE DEFAULT CURRENT_DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Insert Sample Data

INSERT INTO employees (first_name, last_name, email, department, salary, hire_date)
VALUES
    ('John', 'Doe', 'john.doe@company.com', 'Engineering', 75000.00, '2023-01-15'),
    ('Jane', 'Smith', 'jane.smith@company.com', 'Marketing', 65000.00, '2023-03-20'),
    ('Michael', 'Johnson', 'michael.j@company.com', 'Sales', 70000.00, '2023-02-10'),
    ('Emily', 'Brown', 'emily.brown@company.com', 'HR', 60000.00, '2023-04-05'),
    ('David', 'Wilson', 'david.wilson@company.com', 'Engineering', 80000.00, '2023-01-25');

Verify Data Insertion

SELECT * FROM employees;

Create Another Table (Products)

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Insert Sample Products

INSERT INTO products (product_name, category, price, stock_quantity)
VALUES
    ('Laptop Pro', 'Electronics', 1299.99, 50),
    ('Wireless Mouse', 'Accessories', 29.99, 200),
    ('USB-C Hub', 'Accessories', 49.99, 150),
    ('Monitor 27"', 'Electronics', 399.99, 75),
    ('Keyboard Mechanical', 'Accessories', 89.99, 100);

Exit PostgreSQL

\q

pgAdmin 4 Installation

Refere Official Link: https://www.pgadmin.org/download/pgadmin-4-apt/

Add pgAdmin Repository

curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg

Add Repository to Sources List

sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list'

Update Package List

sudo apt update

Install pgAdmin 4 Web Mode

sudo apt install pgadmin4-web -y

Run pgAdmin Web Setup Script

sudo /usr/pgadmin4/bin/setup-web.sh

During the setup, you'll be prompted for:

  1. Email address: Enter your email (e.g., admin@example.com)
  2. Password: Create a strong password for pgAdmin login
  3. Apache2 configuration: Type y to configure Apache2

The script will automatically:

  • Configure Apache2
  • Set up WSGI
  • Create necessary directories
  • Set proper permissions

Apache2 Configuration

Verify Apache2 is Running

sudo systemctl status apache2

Check pgAdmin Apache Configuration

cat /etc/apache2/conf-available/pgadmin4.conf

This file should contain the pgAdmin configuration created by the setup script.

Enable Apache2 Modules (if not already enabled)

sudo a2enmod wsgi
sudo a2enmod proxy
sudo a2enmod proxy_http

Restart Apache2

sudo systemctl restart apache2

Enable Apache2 to Start on Boot

sudo systemctl enable apache2

Check Apache2 Ports

sudo netstat -tlnp | grep apache2

You should see Apache listening on port 80.

Configure Firewall (if UFW is enabled)

sudo ufw allow 'Apache'
sudo ufw status

Access pgAdmin

Open Your Web Browser

Navigate to:

http://your-server-ip/pgadmin4

Or if accessing locally:

http://localhost/pgadmin4

Login to pgAdmin

Use the credentials you created during the setup:

  • Email: The email you provided
  • Password: The password you set

Connect to PostgreSQL in pgAdmin

Step 1: Register a New Server

  1. In pgAdmin, right-click on Servers in the left sidebar
  2. Select Register → Server

Step 2: General Tab

  • Name: Local PostgreSQL Server (or any name you prefer)

Step 3: Connection Tab

Fill in the following details:

  • Host name/address: localhost or 127.0.0.1
  • Port: 5432 (default PostgreSQL port)
  • Maintenance database: postgres
  • Username: appuser (the user we created)
  • Password: AppUserPassword123!
  • Save password: ✓ (check this box)

Step 4: Save

Click the Save button.

Step 5: Verify Connection

You should now see your server listed under Servers. Expand it to see:

  • Databases → myappdb
  • Schemas → public
  • Tables → employees and products

Visualize Data in pgAdmin

View Table Data

Method 1: Using the GUI

  1. Navigate to: Servers → Local PostgreSQL Server → Databases → myappdb → Schemas → public → Tables
  2. Right-click on employees table
  3. Select View/Edit Data → All Rows

You'll see all the employee records displayed in a spreadsheet-like interface.

Method 2: Using Query Tool

  1. Right-click on myappdb database
  2. Select Query Tool
  3. Enter your SQL query:
SELECT * FROM employees;
  1. Click the Execute button

View Table Structure

  1. Right-click on employees table
  2. Select Properties
  3. Go to the Columns tab to see all column definitions

Create a Visual Graph

  1. In the Query Tool, run:
SELECT department, COUNT(*) as employee_count, AVG(salary) as avg_salary
FROM employees
GROUP BY department;
  1. After running the query, click on the Graph tab
  2. Select:
    • Graph Type: Bar Chart or Pie Chart
    • X-axis: department
    • Y-axis: employee_count or avg_salary

Export Data

  1. Right-click on employees table
  2. Select View/Edit Data → All Rows
  3. Click the Download icon (↓) in the toolbar
  4. Choose format: CSV, JSON, or others
  5. Save the file

Create a Dashboard

  1. Click on Tools → Query Tool
  2. Write multiple queries to analyze data:
-- Total employees by department
SELECT department, COUNT(*) as total
FROM employees
GROUP BY department
ORDER BY total DESC;

-- Average salary by department
SELECT department, ROUND(AVG(salary), 2) as avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

-- Recent hires
SELECT first_name, last_name, department, hire_date
FROM employees
ORDER BY hire_date DESC
LIMIT 5;
  1. Execute each query and view results
  2. Use the Explain tab to see query execution plans

Troubleshooting

pgAdmin Not Loading

Check Apache2 status:

sudo systemctl status apache2

Restart Apache2:

sudo systemctl restart apache2

Check Apache2 error logs:

sudo tail -f /var/log/apache2/error.log

Cannot Connect to PostgreSQL

Check if PostgreSQL is running:

sudo systemctl status postgresql

Check PostgreSQL logs:

sudo tail -f /var/log/postgresql/postgresql-14-main.log

Test connection from command line:

psql -U appuser -d myappdb -h localhost

Permission Denied Errors

Fix pgAdmin permissions:

sudo chown -R www-data:www-data /var/lib/pgadmin
sudo chown -R www-data:www-data /var/log/pgadmin

Restart services:

sudo systemctl restart apache2

Port Already in Use

Check what's using port 80:

sudo netstat -tlnp | grep :80

If another service is using port 80, stop it:

sudo systemctl stop nginx  # if nginx is running

pgAdmin Shows "Too Many Failed Login Attempts" Or For Password Reset

Delete pgAdmin configuration database

This forces pgAdmin to ask for email & password again.

sudo rm -f /var/lib/pgadmin/pgadmin4.db

This removes:

  • pgAdmin users
  • Saved servers in pgAdmin UI
    Does NOT delete PostgreSQL databases

Choose to reconfigure and set a new password.

Now Reset pgAdmin:

sudo /usr/pgadmin4/bin/setup-web.sh

Table Not Visible in pgAdmin

Refresh the browser:

  • Right-click on Tables and select Refresh

Check table ownership:

psql -U postgres -d myappdb

SELECT tablename, tableowner FROM pg_tables WHERE schemaname = 'public';

Grant privileges if needed:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO appuser;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO appuser;

Additional Configuration

Install Certbot

sudo apt install certbot python3-certbot-apache -y

Get SSL Certificate

sudo certbot --apache -d yourdomain.com

Follow the prompts to configure HTTPS.

Configure PostgreSQL Connection Pooling

Install PgBouncer for better performance:

sudo apt install pgbouncer -y

Edit configuration:

sudo nano /etc/pgbouncer/pgbouncer.ini

Backup Database

Create a backup:

pg_dump -U appuser -d myappdb > myappdb_backup.sql

Restore from backup:

psql -U appuser -d myappdb < myappdb_backup.sql

Quick Reference Commands

# Start/Stop/Restart PostgreSQL
sudo systemctl start postgresql
sudo systemctl stop postgresql
sudo systemctl restart postgresql

# Start/Stop/Restart Apache2
sudo systemctl start apache2
sudo systemctl stop apache2
sudo systemctl restart apache2

# Access PostgreSQL
sudo -u postgres psql
psql -U appuser -d myappdb -h localhost

# Check running services
sudo systemctl status postgresql
sudo systemctl status apache2

# View logs
sudo tail -f /var/log/postgresql/postgresql-16-main.log
sudo tail -f /var/log/apache2/error.log
sudo tail -f /var/log/pgadmin/pgadmin4.log

Useful SQL Commands

-- List all databases
\l

-- Connect to database
\c myappdb

-- List all tables
\dt

-- Describe table structure
\d employees

-- Show all users
\du

-- Exit psql
\q

Read more