pgAdmin is a powerful open source tool for the administration of PostgreSQL databases. It is specifically designed to monitor and manage multiple PostgreSQL databases via a web browser. It has a simple and user-friendly web-based interface for interacting with the PostgreSQL database. It is very useful for beginners who are not familiar with the PostgreSQL command line. It is used by developers to create and manage PostgreSQL databases via a web browser. It is cross-platform and runs on Linux, Unix, Mac OS X and Windows.
In this article, we will show you how to install pgAdmin on Ubuntu 22.04.
Requirements
- A server on which Ubuntu 22.04 is running.
- A root password is set up on the server.
Install and configure PostgreSQL
Before you install pgAdmin, you must install the PostgreSQL database server on your server. By default, the PostgreSQL server package is included in the Ubuntu standard repository. You can install it with the following command:
apt-get install postgresql -y
After successful installation, start and activate the PostgreSQL service with the following command:
systemctl start postgresql
systemctl enable postgresql
You can also check the PostgreSQL status with the following command:
systemctl status postgresql
You should see the following output:
? postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Mon 2022-07-11 09:05:15 UTC; 31s ago
Process: 50593 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 50593 (code=exited, status=0/SUCCESS)
CPU: 2ms
Jul 11 09:05:15 ubuntu2204 systemd[1]: Starting PostgreSQL RDBMS...
Jul 11 09:05:15 ubuntu2204 systemd[1]: Finished PostgreSQL RDBMS.
By default, PostgreSQL only allows connections from the local host. If you want to manage PostgreSQL from the remote host, you need to allow access from the remote host. You can configure this by editing the PostgreSQL main configuration file:
nano /etc/postgresql/14/main/postgresql.conf
Add the following line:
listen_addresses = '*'
Save and close the file. Then you also need to edit the PostgreSQL client authentication file and allow connections from the remote machines.
nano /etc/postgresql/14/main/pg_hba.conf
Add the following line:
# TYPE DATABASE USER ADDRESS METHOD
host all all all md5
Save and close the file and restart the PostgreSQL service to apply the changes:
systemctl restart postgresql
Once you are done, you can proceed to the next step.
Creating a PostgreSQL database and user
Next, you need to create a database and a user that you can connect to via pgAdmin. To do this, log in to the PostgreSQL shell with the following command:
su - postgres
psql
Once you are connected, create a user and a database with the following command:
postgres=# CREATE USER pguser WITH PASSWORD 'password';
postgres=# CREATE DATABASE pgdb;
Next, grant the PostgreSQL database all rights with the following command:
postgres=# GRANT ALL PRIVILEGES ON DATABASE pgdb to pguser;
Exit the PostgreSQL shell with the following command:
postgres=# \q
exit
When you're done, you can proceed to the next step.
Install pgAdmin4 on Ubuntu 22.04
The latest version of pgAdmin is not included in the default repository of Ubuntu 22.04. Therefore, you need to add the pgAdmin repository to the APT.
First, install all required dependencies with the following command:
apt-get install apt-transport-https ca-certificates software-properties-common curl -y
Next, add the Python repository to your server with the following command:
add-apt-repository ppa:deadsnakes/ppa
Next, install Python version 3.8 with the following command:
apt install python3.8 -y
Once all dependencies are installed, download the GPG key and add it with the following command:
curl -fsSL https://www.pgadmin.org/static/packages_pgadmin_org.pub | gpg --dearmor -o /etc/apt/trusted.gpg.d/pgadmin.gpg
Next, add the pgAdmin repository to the APT:
sh -c 'echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list'
Finally, update the repository and install pgAdmin with the following command:
apt-get update -y
apt-get install pgadmin4 -y
Once successfully installed, run the following script to set up the username and password for pgAdmin:
/usr/pgadmin4/bin/setup-web.sh
You will be prompted to set an admin user and password (see below):
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: [email protected]
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
pgAdmin installs and configures the Apache web server during installation. You can check the status of Apache with the following command:
systemctl status apache2
You will get the following output:
? apache2.service - The Apache HTTP Server
Loaded: loaded (/lib/systemd/system/apache2.service; enabled; vendor preset: enabled)
Active: active (running) since Mon 2022-07-11 09:27:27 UTC; 21s ago
Docs: https://httpd.apache.org/docs/2.4/
Process: 57446 ExecStart=/usr/sbin/apachectl start (code=exited, status=0/SUCCESS)
Main PID: 57451 (apache2)
Tasks: 83 (limit: 2242)
Memory: 16.4M
CPU: 158ms
CGroup: /system.slice/apache2.service
??57451 /usr/sbin/apache2 -k start
??57452 /usr/sbin/apache2 -k start
??57453 /usr/sbin/apache2 -k start
??57454 /usr/sbin/apache2 -k start
Jul 11 09:27:27 ubuntu2204 systemd[1]: Starting The Apache HTTP Server...
Jul 11 09:27:27 ubuntu2204 apachectl[57450]: AH00558: apache2: Could not reliably determine the server's fully qualified domain name, using 2>
Jul 11 09:27:27 ubuntu2204 systemd[1]: Started The Apache HTTP Server.
When you are done, you can proceed to the next step.
Accessing pgAdmin Web via the web browser
At this point, pgAdmin4 is installed and configured. Now open your web browser and access the pgAdmin4 web interface via the URL http://your-server-ip/pgadmin4. You should see the pgAdmin login screen:
Enter your admin email address and password and click the Log In button. Once you are connected, you should see the pgAdmin dashboard on the following screen:
Next, click the Add New Server button to connect to your remote PostgreSQL server. You should see the following screen:
On the General tab, enter the server name and then click on the Connection tab. You should see the following screen:
Enter the IP address of the remote PostgreSQL server, the database name, username and password and click the Save button. Once the connection is established, you will see the following screen:
From here you can interact with your PostgreSQL database.
Conclusion
In this article, we have explained how to install and configure pgAdmin4 on Ubuntu 22.04. You can also add more PostgreSQL servers via the pgAdmin web interface and manage them via a web browser. If you have any questions, feel free to ask me.
