PostgreSQL pgAdmin Tool Guide

pgAdmin is the most popular and feature-rich open-source administration and development platform for PostgreSQL. It provides a comprehensive graphical interface for managing PostgreSQL databases, making database administration accessible even to those who are not comfortable with command-line tools.

PostgreSQL pgAdmin Tool Guide

|--------------|------------------|------------|

What is pgAdmin?

pgAdmin Editions

1. Download from https://www.pgadmin.org/download/pgadmin4-windows/
2. Run the installer
3. Follow the installation wizard
4. Launch pgAdmin from Start Menu

Key Features

Installation Methods

Installation Methods

## Installing pgAdmin
### Installation Methods

# Option 2: Using Homebrew
brew install --cask pgadmin4

Linux

# Ubuntu/Debian
# Ubuntu
curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add -
sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
sudo apt update
sudo apt install pgadmin4-desktop

# Fedora/RHEL
sudo dnf install pgadmin4

# Fedora
docker pull dpage/pgadmin4

docker run --name pgadmin4-container \
  -p 80:80 \
  -e PGADMIN_DEFAULT_EMAIL=admin@pgadmin.org \
  -e PGADMIN_DEFAULT_PASSWORD=admin \
  -d dpage/pgadmin4

Method 3: Python Wheel

# Install Python dependencies
pip install flask flask-sqlalchemy flask-login

# Download and install pgAdmin wheel
pip install pgadmin4-*.whl

Installation Verification

Installation Verification

### Installation Verification
pgadmin4 --version

# Or launch pgAdmin
pgadmin4

Getting Started with pgAdmin

Getting Started with pgAdmin

Launching pgAdmin

2. Navigate to http://localhost:80 / http://localhost:80

On first launch, you may be asked to set a master password:

The pgAdmin dashboard shows:

2. Select "Create" > "Server..." / "Create" > "Server..."

Fill in the connection details:

|------------|------------------|--------------|

|------------|------------------|--------------|

Configure SSL settings based on your server configuration:

Configure advanced settings:

Connecting to a Remote Server

Connecting to a Remote Server

### Initial Setup
## Connecting to PostgreSQL Server
listen_addresses = '*'

### Adding a New Server
### Connecting to a Remote Server
host    all             all             0.0.0.0/0            md5

Located on the left side, displays hierarchical view of:

• Servers /
• Databases /
• Schemas /
• Tables /

Main SQL editing area with:

Displays query results and messages:

• Messages /
• Explain /
• History /

4. Menu Bar

Provides access to various functions:

• File /
• Edit /
• View /
• Tools /
• Help /

5. Toolbars

Quick access to common operations:

• Save /
• Execute /
• Format SQL / SQL

2. Select "Create" > "Database..." / "Create" > "Database..."
3. Click "Save" / "Save"

Using SQL

Creating a Database

Creating a Database

CREATE DATABASE myapp
    WITH 
    OWNER = postgres
    TEMPLATE = template0
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8'
    CONNECTION LIMIT = -1;

COMMENT ON DATABASE myapp
    IS 'My application database';

In pgAdmin, databases are organized hierarchically under the server. Click on a database to:

2. Select "Delete/Drop" / "Delete/Drop"

Using SQL

Deleting a Database

Deleting a Database

-- Terminate existing connections first
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = 'mydb' 
AND pid <> pg_backend_pid();

-- Drop the database
DROP DATABASE mydb;

Access database properties by:

2. Select "Properties" / "Properties"

Available settings include:

• Owner /
• Encoding /
• Comments /

pgAdmin Interface Overview

Main Window Components

1. Navigate to Databases > your_db > Schemas > public / Databases > your_db > Schemas > public
2. Select "Create" > "Table..." / "Create" > "Table..."
3. Click "Save" / "Save"

Database Operations

Creating a Table

Creating a Table

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash CHAR(64) NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    birth_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE
);

-- Add comments
COMMENT ON TABLE users IS 'User accounts table';
COMMENT ON COLUMN users.id IS 'Primary key';
COMMENT ON COLUMN users.username IS 'Unique username';

2. Select "Properties" / "Properties"
3. Click "Save" / "Save"

Creating a Database

Deleting a Table

Deleting a Table

-- Drop a single table
DROP TABLE users;

-- Drop multiple tables
DROP TABLE users, products, orders;

-- Drop table if exists
DROP TABLE IF EXISTS users;

-- Drop table with cascade
DROP TABLE users CASCADE;

Multiple ways to open:

• Press Ctrl+E / Ctrl+E

Writing Queries

Writing Queries

-- Select all columns
SELECT * FROM users;

-- Select specific columns
SELECT id, username, email FROM users;

-- With WHERE clause
SELECT * FROM users WHERE is_active = TRUE;

-- With ordering
SELECT * FROM users ORDER BY created_at DESC;

-- With LIMIT
SELECT * FROM users LIMIT 10;

SELECT 
    u.id,
    u.username,
    u.email,
    COUNT(o.id) AS order_count,
    SUM(o.total_amount) AS total_spent,
    MAX(o.created_at) AS last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2023-01-01'
GROUP BY u.id
HAVING COUNT(o.id) > 0
ORDER BY total_spent DESC
LIMIT 20;

• Row estimates /

2. Select "Import/Export" / "Import/Export"

3. Click "OK" / "OK"

4. Select "Import/Export" / "Import/Export"

5. Click "OK" / "OK"

Example Export Options

Example Export Options

-- Export to CSV with headers
COPY (SELECT * FROM users) TO '/tmp/users.csv' WITH (FORMAT CSV, HEADER);

-- Export with delimiter
COPY users TO '/tmp/users.txt' WITH (FORMAT TEXT, DELIMITER '|');

-- Export specific columns
COPY users(id, username, email) TO '/tmp/users_partial.csv' WITH (FORMAT CSV, HEADER);

2. Select "Backup" / "Backup"
3. Click "Backup" / "Backup"

Selecting a Database

pgAdmin uses pg_dump internally:

Backup Database

Backup Database

### Deleting a Database
pg_dump -h localhost -U postgres -Fc -b -v -f /path/to/backup.dump mydatabase

### Database Properties
pg_dump -h localhost -U postgres -c -f /path/to/backup.sql mydatabase

## Table Operations
pg_dump -h localhost -U postgres -Fd -j 4 -f /path/to/backup_dir mydatabase

2. Select "Restore" / "Restore"
3. Click "Restore" / "Restore"

Creating a Table

Restore Database

Restore Database

### Viewing Table Data
pg_restore -h localhost -U postgres -d mydatabase /path/to/backup.dump

### Editing Table Structure
psql -h localhost -U postgres -d mydatabase -f /path/to/backup.sql

|-------------|------------------| | Format: Plain | Plain SQL text / SQL | | Data only | Only data / |

Deleting a Table

Access dashboard by clicking on the server name. Shows:

• Transaction rates /

2. Click on "Server Status" / "Server Status"

SQL Query Tool

Query Performance

Query Performance

-- Basic EXPLAIN
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- With costs
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) 
SELECT * FROM users WHERE email = 'test@example.com';

Opening Query Tool

-- Enable extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Get top 10 queries by total time
SELECT 
    query,
    calls,
    total_time,
    mean_time,
    rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

Access detailed statistics through:

|-----------------|-------------|

Writing Queries

pgAdmin is a powerful tool that simplifies PostgreSQL database management:

Mastering pgAdmin will significantly improve your productivity as a PostgreSQL developer or administrator.

Query Execution

Continue to SQL Syntax to learn about PostgreSQL SQL syntax.

---