pgAdmin Installation and Configuration Guide

pgAdmin is a powerful open-source platform for administering and developing PostgreSQL and related database management systems. Built with Python and jQuery, it fully supports all PostgreSQL features. pgAdmin allows you to perform tasks ranging from writing SQL queries to monitoring databases and configuring advanced architectures.

This tutorial will guide you through installing and configuring the latest version of pgAdmin on an Ubuntu 18.04 server, accessing pgAdmin via a web browser, and connecting it to a PostgreSQL database.

Prerequisites

To follow this tutorial, ensure you have:

• An Ubuntu 18.04 server with a non-root user having sudo privileges and a firewall configured using ufw.
• Nginx installed and set up as a reverse proxy for http://unix:/tmp/pgadmin4.sock.
• PostgreSQL installed with a dedicated role and database for pgAdmin to connect to.
• Python 3 and venv installed on the server.

Step 1 — Installing pgAdmin and Its Dependencies

The latest version, pgAdmin 4, is recommended over the outdated pgAdmin 3. This step covers installing pgAdmin 4 in a virtual environment and setting up its dependencies.

Update your server’s package index:

sudo apt update

Install required dependencies:

sudo apt install libgmp3-dev libpq-dev

Create directories for session data, storage, and logs:

sudo mkdir -p /var/lib/pgadmin4/sessions
sudo mkdir /var/lib/pgadmin4/storage
sudo mkdir /var/log/pgadmin4

Adjust ownership of these directories:

sudo chown -R sammy:sammy /var/lib/pgadmin4
sudo chown -R sammy:sammy /var/log/pgadmin4

Activate your virtual environment:

cd environments/
source my_env/bin/activate

Upgrade pip:

python -m pip install -U pip

Download the latest pgAdmin 4 source code:

wget https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v6.10/pip/pgadmin4-6.10-py3-none-any.whl

Install the wheel package and pgAdmin 4:

python -m pip install wheel
python -m pip install pgadmin4-6.10-py3-none-any.whl

Install Gunicorn:

python -m pip install gunicorn

Step 2 — Configuring pgAdmin 4

Create a local configuration file:

nano my_env/lib/python3.10/site-packages/pgadmin4/config_local.py

Add the following settings:

LOG_FILE = '/var/log/pgadmin4/pgadmin4.log'
SQLITE_PATH = '/var/lib/pgadmin4/pgadmin4.db'
SESSION_DB_PATH = '/var/lib/pgadmin4/sessions'
STORAGE_DIR = '/var/lib/pgadmin4/storage'
SERVER_MODE = True

Run the setup script to create login credentials:

python my_env/lib/python3.10/site-packages/pgadmin4/setup.py

Step 3 — Starting Gunicorn and Configuring Nginx

Start the Gunicorn server:

gunicorn --bind unix:/tmp/pgadmin4.sock --workers=1 --threads=25 --chdir ~/environments/my_env/lib/python3.10/site-packages/pgadmin4 pgAdmin4:app

Configure Nginx as a reverse proxy:

sudo nano /etc/nginx/sites-available/your_domain

Add this configuration:

server {
    listen 80;
    listen [::]:80;

    server_name your_domain www.your_domain;
    
    location / {
        proxy_pass http://unix:/tmp/pgadmin4.sock;
        include proxy_params;
    }
}

Restart Nginx:

sudo systemctl restart nginx

Step 4 — Accessing pgAdmin

Open your browser and visit:
http://your_server_ip

Log in with the credentials you set in Step 2.

Step 5 — Configuring Your PostgreSQL User

Access the PostgreSQL prompt:

sudo -u sammy psql

Set a password for your PostgreSQL user:

ALTER USER sammy PASSWORD 'password';

Exit PostgreSQL:

\q

Connect pgAdmin to your PostgreSQL server by adding a new server under the “Servers” menu in pgAdmin.

Step 6 — Creating a Table in the pgAdmin Dashboard

Expand your server, database, and “Schemas.” Right-click “Tables” → “Create” → “Table…”.

Add columns in the Columns tab:

• Name: Column name
• Data type: Data type (e.g., VARCHAR, INT)
• Primary Key: Enable if needed

Click “Save” to create the table. Use the “INSERT Script” to add data and “View/Edit Data” to view it.

Conclusion

You’ve installed and configured pgAdmin 4 using Gunicorn and Nginx, connected it to PostgreSQL, and learned how to create and manage tables. For more details, visit the pgAdmin documentation.