You can follow this tutorial if you want your PgAdmin interface to be available on a public server that is accessible to other people as well. Usually I try to make the tutorials as short as possible, so I wrote a shorter version for people who want to do this using Docker (link here).
Now my own experience is that it is more robust to do it old school – for now at least. And if you want to do things old school it will take multiple steps. So please go ahead and make yourself a good cup of coffee for this one 🙂
Step 1: Install PostgreSQL
I can’t explain this one better than the official documentation – don’t worry it is pretty short => Install PostgreSQL
Step 2: Enable Remote Access to PostgreSQL
I personally use vim (despite Richard Stallman’s rather dull comments on VI), but of course feel free to use your own favorite code editor for all that follows.
First edit the PostgreSQL Client authentication configuration file
# Command line $ sudo vim /etc/postgresql/12/main/pg_hba.conf
Update the lines to
# "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 0.0.0.0/0 md5 # IPv6 local connections: host all all ::1/128 md5
Then edit PostgreSQL’s Parameter Configuration File now
# Command line $ sudo vim /etc/postgresql/12/main/postgresql.conf
Update the lines to
listen_addresses = '*' # FYI, this will allow listening for all IPv4 addresses
# Restart postgresql sudo systemctl restart postgresql.service
At this point it is a good idea to test that everything worked as planned. So you can use this command to test the connection (from another machine ideally, and of course the username, server domain name and database name should be your own).
# Command line $ PGPASSWORD=’postgres-password’ psql -h server-domain-or-ip -U postgresql-username -d database-name
Step 3: Install pgAdmin as a Web App
Note I have adapted these steps for Ubuntu-20.04 and for the latest version of pgadmin, but the originals come from this tutorial which works fine for Ubuntu 16.04.
# Command line $ sudo apt update $ sudo apt install build-essential libssl-dev libffi-dev libgmp3-dev virtualenv python3-pip libpq-dev python3-dev $ sudo apt install apache2 apache2-utils libapache2-mod-wsgi libexpat1 ssl-cert python3
Before you proceed with the next steps, please visit this link and replace the pgadmin .whl file with the latest version that is available (or the version that you want to install).
# Command line $ cd $HOME $ virtualenv pgadmin $ cd pgadmin $ . bin/activate $ wget https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v5.7/pip/pgadmin4-5.7-py3-none-any.whl $ pip install pgadmin4-5.7-py3-none-any.whl
Here I am setting the logs to /var/log and /var/lib but of course you can change to what you prefer.
# Command line $ vim lib/python3.8/site-packages/pgadmin4/config_distro.py
Add the following lines or modify if you want the logs to be stored elsewhere
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'
If you have set the paths like I did, the following is a temporary fix to be able to run the setup for PgAdmin. First, we will create these paths because they don’t exist yet. Then, for a short period of time, we will make you *yes you* the owner of these paths.
# Command line cd /var/log/ sudo mkdir pgadmin4/ sudo chown -R $(whoami):$(whoami) /var/log/pgadmin4/ cd /var/lib/ sudo mkdir pgadmin4/ sudo chown -R $(whoami):$(whoami) /var/lib/pgadmin4/
OK now we are ready to run the setup
# Command line $ cd $HOME/pgadmin $ python lib/python3.8/site-packages/pgadmin4/setup.py
At some point you will be asked to type an email address and a password, please store them somewhere, as you will use them to connect to the Web App later.
Now you can leave the Python virtual environment
# Command line deactivate
It is also time to restore the PgAdmin paths to their rightful owner.
# Command line sudo chown -R www-data:www-data /var/lib/pgadmin4/ sudo chown -R www-data:www-data /var/log/pgadmin4/
Step 4: Apache Server Setup
First, let’s install Apache if it is not done yet
# Command line sudo apt update sudo apt install apache2
Also you will need this for Python3
# Command line sudo apt-get install libapache2-mod-wsgi-py3
Now, you have two options (A) Run your PgAdmin using HTTP or (B)Run your PgAdmin using HTTPS. I strongly advise you to use HTTPS, but for that you will need an SSL certificate. So for the sake of completion I will show how to do both methods.
# Command line sudo vim /etc/apache2/sites-available/pgadmin4.conf
Regardless of the option you choose, do not forget to replace the red text so it matches your own configuration.
Option (A): HTTP
| Contents of /etc/apache2/sites-available/pgadmin4.conf |
<VirtualHost *:80> |
Option (B): HTTPS
| Contents of /etc/apache2/sites-available/pgadmin4.conf |
<VirtualHost *:80> |
Option (C): HTTPS, on another port than 443
Let’s now say you want your PgAdmin on port 8989 (or any other port number that strikes your fancy). First, add that port to pgadmin4.conf
| Contents of /etc/apache2/sites-available/pgadmin4.conf |
<VirtualHost *:80> |
Next, you now want to have Apache listen to your exotic port
# Command-line sudo vim /etc/apache2/ports.conf
| Add the row in blue |
Listen 80
|
Reload Apache
Finally, let’s enable the config and reload Apache
# Command line apachectl -t sudo a2enmod ssl sudo a2ensite pgadmin4.conf sudo service apache2 reload
And voila! You can visit your website hostname or ip address, you should find PgAdmin waiting for you there 🙂
Debugging commands
If PgAdmin doen’t show up, here are some commands to help you debug the thing
$ sudo tail -f /var/log/apache2/error.log
$ sudo tail -f /var/log/apache2/pgadmin-error.log
One last thing though: if you are doing all this on a Cloud server, don’t forget to enable the ports 80 (HTTP) or 443 (HTTPS) in your networking options for this particular server.