pgAdmin Installation and Configuration Guide

pgAdmin is an open-source administration and development platform for PostgreSQL and its related database management systems. Written in Python and jQuery, it supports all the features found in PostgreSQL. You can use pgAdmin to do everything from writing basic SQL queries to monitoring your databases and configuring advanced database architectures.

In this tutorial, we’ll walk through the process of installing and configuring the latest version of pgAdmin onto an Ubuntu 18.04 server, accessing pgAdmin through a web browser, and connecting it to a PostgreSQL database on your server.

Prerequisites

To complete this tutorial, you will need:

• A server running Ubuntu 18.04. This server should have a non-root user with sudo privileges, as well as a firewall configured with ufw.
• Nginx installed and configured as a reverse proxy for http://unix:/tmp/pgadmin4.sock.
• PostgreSQL installed on your server. Be sure to create a new role and database to connect pgAdmin to your PostgreSQL instance.
• Python 3 and venv installed on your server.

Step 1 — Installing pgAdmin and its Dependencies

As of this writing, the most recent version of pgAdmin is pgAdmin 4, while the most recent version available through the official Ubuntu repositories is pgAdmin 3. pgAdmin 3 is no longer supported though, and the project maintainers recommend installing pgAdmin 4. In this step, we will go over the process of installing the latest version of pgAdmin 4 within a virtual environment and installing its dependencies using apt.

To begin, update your server’s package index if you haven’t done so recently:

sudo apt update

Next, install the following dependencies. These include libgmp3-dev, a multiprecision arithmetic library, and libpq-dev, which includes header files and a static library that helps communication with a PostgreSQL backend:

sudo apt install libgmp3-dev libpq-dev

Following this, create a few directories where pgAdmin will store its sessions data, storage data, and logs:

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

Change ownership of these directories to your non-root user and group:

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

Next, open your virtual environment. Navigate to the directory where your programming environment is located and activate it:

cd environments/
source my_env/bin/activate

After activating the virtual environment, ensure you have the latest version of pip installed on your system. If you haven’t upgraded pip to the latest version, you may encounter issues when configuring pgAdmin. Upgrade pip with the following command:

python -m pip install -U pip

Next, download the pgAdmin 4 source code. To find the latest version, navigate to the pgAdmin 4 (Python Wheel) Download page. Copy the link for the latest .whl file and run the following wget command, replacing the link with the copied URL:

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

Install the wheel package, which is required to work with .whl files:

python -m pip install wheel

Then install pgAdmin 4 using the downloaded .whl file:

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

Next, install Gunicorn, a Python WSGI server that will be used with Nginx to serve the pgAdmin web interface:

python -m pip install gunicorn

With that, pgAdmin and its dependencies are installed. Before connecting it to your database, you need to configure pgAdmin further.

Step 2 — Configuring pgAdmin 4

Although pgAdmin is installed, additional configuration is necessary for it to correctly serve the web interface. Avoid modifying config.py directly; instead, create a new configuration file named config_local.py:

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

Add the following content to the file:

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

Save and close the file. Next, run the setup script to set your login credentials:

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

You’ll be prompted to enter an email address and password. These credentials will be used to log in to pgAdmin later.

Step 3 — Starting Gunicorn and Configuring Nginx

Use Gunicorn to serve pgAdmin as a web application. First, 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

Next, configure Nginx as a reverse proxy. Open your Nginx configuration file:

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

Add the following content:

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 to apply the changes:

sudo systemctl restart nginx

Step 4 — Accessing pgAdmin

On your local machine, open a web browser and navigate to your server’s IP address:

http://your_server_ip

Log in using the credentials you set in Step 2 to access the pgAdmin Welcome screen.

Step 5 — Configuring Your PostgreSQL User

To allow pgAdmin to connect to your PostgreSQL database, you need to configure your PostgreSQL user. By default, PostgreSQL uses the “ident” authentication method, which matches your Ubuntu username to the database username. This may cause issues for external applications like pgAdmin. To resolve this, set a password for your PostgreSQL user.

Log in to the PostgreSQL prompt as the superuser:

sudo -u sammy psql

Set a password for the user:

ALTER USER sammy PASSWORD 'password';

Exit the PostgreSQL prompt:

\q

Go back to the pgAdmin interface in your browser. In the Browser menu, right-click on “Servers,” hover over “Create,” and click “Server…”.

In the General tab, enter a name for your server (e.g., “Sammy-server-1”).

Switch to the Connection tab and enter the following details:

• Host name/address: localhost
• Port: 5432 (default)
• Maintenance database: The database name you created earlier
• Username: Your PostgreSQL username (e.g., sammy)
• Password: The password you set earlier

Click “Save” to connect pgAdmin to your database. The database will appear under “Servers” in the Browser menu.

Step 6 — Creating a Table in the pgAdmin Dashboard

In the Browser menu, expand your server, database, and “Schemas.” Right-click on “Tables,” hover over “Create,” and click “Table…”.

In the General tab, enter a name for your table (e.g., “table-01”).

Navigate to the Columns tab and add columns by clicking the “+” icon. For each column, provide the following details:

• Name: Column name
• Data type: Data type for the column (e.g., VARCHAR, INT)
• Primary Key: Toggle to “Yes” if this column should be the primary key

Click “Save” to create the table. To insert data, right-click the table name, hover over “Scripts,” and click “INSERT Script.” Replace placeholders in the script with your data and execute it by clicking the play icon (▶).

To view the data, right-click the table name, hover over “View/Edit Data,” and select “All Rows.”

Conclusion

In this guide, you installed pgAdmin 4 in a Python virtual environment, configured it, served it using Gunicorn and Nginx, and connected it to a PostgreSQL database. You also learned how to create a table and populate it with data through the pgAdmin web interface.

For more information on pgAdmin’s features, consult the pgAdmin documentation.