This guide will walk you through the instructions to install PostgreSQL 12 relational and object-oriented database management systems and pgAdmin4, a commonly-used web-based PostgreSQL database server administration tool. We will show how to install the latest version of pgAdmin4 that is v4.23.
Prerequisites:
Let’s get started…
Installing PostgreSQL in Ubuntu 20.04
Log into your Ubuntu system and update the system software packages using the following apt command.
$ sudo apt update
Now install the latest version of PostgreSQL from the default Ubuntu repositories.
$ sudo apt install postgresql
During the installation, the installer will create a new PostgreSQL cluster (a collection of databases that will be managed by a single server instance), thus initialize the database. The default data directory is /var/lib/postgresql/12/main and the configurations files are stored in the /etc/postgresql/12/main directory.
After PostgreSQL installed, you can confirm that the PostgreSQL service is active, running and is enabled under systemd using the following systemctl commands:
$ sudo systemctl is-active postgresql $ sudo systemctl is-enabled postgresql $ sudo systemctl status postgresql
Also, confirm that the Postgresql server is ready to accept connections from clients as follows:
$ sudo pg_isready
Creating Database in PostgreSQL
To create a new database in PostgreSQL, you need to access the PostgreSQL database shell (psql) program. First, switch to the postgres system user account and run the psql command as follows:
$ sudo su - postgres $ psql postgres=#
Now create a new database and a user using the following commands.
postgres=# CREATE USER tecmint WITH PASSWORD 'securep@wd'; postgres=# CREATE DATABASE tecmintdb; postgres=# GRANT ALL PRIVILEGES ON DATABASE tecmintdb to tecmint; postgres=# \q
Configuring PostgreSQL Client Authentication
PostgreSQL uses client authentication to decide which user accounts can connect to which databases from which hosts and this is controlled by settings in the client authentication configuration file, which on Ubuntu is located at /etc/postgresql/12/main/pg_hba.conf.
Open this file using your favorite text editor as shown.
$ sudo vim /etc/postgresql/12/main/pg_hba.conf
PostgreSQL uses many types of client authentication methods including peer, ident, password, and md5 (read the PostgreSQL 12 documentation for a detailed explanation of each method).
md5 is the most secure and recommended because it requires the client to supply a double-MD5-hashed password for authentication. So, ensure that the entries below have md5 as the under method:
host all all 127.0.0.1/32 md5 # IPv6 local connections: host all all ::1/128 md5
After making changes in the Client Authentication configuration file, you will need to restart the PostgreSQL service.
$ sudo systemctl restart postgresql
Installing pgAdmin4 in Ubuntu
pgAdmin4 is not available in the Ubuntu repositories. We need to install it from the pgAdmin4 APT repository. Start by setting up the repository. Add the public key for the repository and create the repository configuration file.
$ curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add $ sudo sh -c 'echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'
Then install pgAdmin4,
$sudo apt install pgadmin4
The above command will install numerous required packages including Apache2 webserver to serve the pgadmin4-web application in web mode.
Once the installation is complete, run the web setup script which ships with the pgdmin4 binary package, to configure the system to run in web mode. You will be prompted to create a pgAdmin4 login email and password as shown in the screenshot below.
This script will configure Apache2 to serve the pgAdmin4 web application which involves enabling the WSGI module and configuring the pgAdmin application to mount at pgadmin4 on the webserver so you can access it at:
http://SERVER_IP/pgadmin4
It also restarts the Apache2 service to apply the recent changes.
Remember to replace [email protected] with your email address and set a strong secure password as well:
$ sudo /usr/pgadmin4/bin/setup-web.sh
Accessing pgAdmin4 Web Interface
To access the pgAdmin4 web application interface, open a web browser, and use the following address to navigate:
http://SERVER_IP/pgadmin4
Once the login page loads, enter the email address and password you created in the previous section while configuring the pgAdmin4 to run in web mode.
After a successful login, you will be land in the pgAdmin4 web application dashboard. To connect to a server, click on Add New Server as highlighted in the following screenshot.
Next, enter the connection in General settings (Name, Server group, and a comment). Then click Connections as highlighted in the following screenshot.
Next, enter the PostgreSQL database server hostname/address, Port number (leave 5432 to use default), select the Maintenance database (which should be postgres), enter the database username and password.
If the database access credentials are OK and the server-client authentication configuration is too, pgAdmin4 should successfully connect to the database server.
That’s all! For more information, see the PostgreSQL 12 documentation and pgAdmin 4 documentation. Remember to share your thoughts with us via the comment section below.
