In this tutorial, we'll learn how to install and configure pgAdmin 4 ubuntu server.
pgAdmin 4 is a comprehensive, web-based administration and management tool for PostgreSQL databases. It provides an intuitive interface for users to manage databases, create SQL queries, and perform various database management tasks. Available in both desktop and server modes, pgAdmin 4 is especially useful in server mode as it allows multiple users to access and manage PostgreSQL instances remotely via a web browser.
We will install and configure pgAdmin 4 in server mode on Ubuntu 24.04. By the end, you’ll have a running instance of pgAdmin 4 accessible from any browser on the network. We’ll also discuss key topics like configuring user authentication, connecting to PostgreSQL databases, and enabling SSL for secure access.
Prerequisites
Before we begin, ensure you have the following:
- An Ubuntu 24.04 dedicated server or KVM VPS.
- A root user or normal user with sudo rights.
- Basic knowledge of Linux command-line operations.
Install and Configure pgAdmin 4 Ubuntu Server
Step 1: Update the System
First, update your system packages to the latest versions to ensure compatibility and security.
sudo apt update
sudo apt upgrade -y
Step 2: Install Required Dependencies
Install the necessary dependencies for pgAdmin 4, including Python 3 and related packages.
sudo apt install -y wget curl ca-certificates gnupg python3-pip virtualenv
Step 3: Install PostgreSQL
If you haven't installed PostgreSQL and wish to manage a local PostgreSQL server, install it using:
sudo apt install -y postgresql postgresql-contrib
Step 4: Install pgAdmin 4
To install pgAdmin4, execute following set of commands:
# Install the public key for the repository (if not done previously):
curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg
# Create the repository configuration file:
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 && apt update'
#
# Install pgAdmin
#
# Install for web mode only:
sudo apt install pgadmin4-web
Step 5: Configure pgAdmin 4 in Server Mode
Configure pgAdmin 4 to run in server mode.
Run the configuration script:
sudo /usr/pgadmin4/bin/setup-web.sh
You will be prompted to create an initial user (an email and password). This user will be used to log in to the pgAdmin 4 web interface.
Setting up pgAdmin 4 in web mode on a Debian based platform...
Creating configuration database...
NOTE: Configuring authentication for SERVER mode.
Enter the email address and password to use for the initial pgAdmin user account:
Email address: admin@hnxcode.dev
Password:
Retype password:
pgAdmin 4 - Application Initialisation
======================================
Creating storage and log directories...
We can now configure the Apache Web server for you. This involves enabling the wsgi module and configuring the pgAdmin 4 application to mount at /pgadmin4. Do you wish to continue (y/n)? y
The Apache web server is running and must be restarted for the pgAdmin 4 installation to complete. Continue (y/n)? y
Apache successfully restarted. You can now start using pgAdmin 4 in web mode at http://127.0.0.1/pgadmin4
After completing the prompts, the script will set up the necessary configurations.
Step 6: Configuring Firewall
If you have a firewall enabled (e.g., UFW), allow HTTP traffic:
sudo ufw allow http
sudo ufw allow https
Step 7: Install SSL
For secure communication, it's recommended to enable HTTPS. You can obtain a free SSL certificate from Let's Encrypt.
Install Certbot:
sudo apt install -y certbot python3-certbot-apache
Obtain and install the certificate:
sudo certbot --apache -d your_domain -d www.your_domain
Follow the prompts to complete the SSL setup.
Step 8: Access pgAdmin 4 Web Interface
Open a web browser and navigate to:
https://your_domain/pgadmin4
Replace your_domain with your domain name.
You should see the pgAdmin 4 login page. Log in using the email and password you set during the configuration in Step 5.
Managing Users and Permissions
pgAdmin 4 allows you to manage multiple users with different roles and permissions. To add users:
Log in to pgAdmin 4.
Navigate to File > Preferences > User Management.
Add new users and assign appropriate roles.
Step 9: Configure PostgreSQL Server Connection in pgAdmin
Once logged into the pgAdmin interface, you can configure it to manage your PostgreSQL instances. Here’s how:
Click on "Add New Server" in the dashboard.
In the General tab, provide a name for the connection (e.g., “PostgreSQL Local”).
Switch to the Connection tab:
- Enter localhost as the hostname (or the server IP if connecting to a remote PostgreSQL instance).
- Provide the PostgreSQL username (usually postgres).
- Provide the password for the PostgreSQL user.
Once done, click Save, and the server will appear in the left-hand pane, allowing you to interact with your PostgreSQL database through pgAdmin.
Conclusion
We've seen how to install and configure pgAdmin 4 ubuntu server. You can now manage your PostgreSQL databases through a robust web interface accessible from anywhere. Remember to secure your installation by enabling HTTPS and configuring your firewall appropriately.
