Home / How To / How to install PostgreSQL and phpPgAdmin on Ubuntu 20.04 LTS

How to install PostgreSQL and phpPgAdmin on Ubuntu 20.04 LTS



PostgreSQL or Postgres is a powerful, high performance object relational database management system (ORDBMS) released under a flexible BSD stillness. PostgreSQL is well suited for large databases and has many advanced features.

PostgreSQL is available for many operating systems including Linux, FreeBSD, Solaris and Microsoft Windows. PhpPgAdmin is a PHP-based web application for managing PostgreSQL databases. With Phppgadmin it is easy to create a database, create a role and create tables in Postgres.

This tutorial shows the installation of PostgreSQL and its web-based administration interface phpPgAdmin on Ubuntu 20.04 LTS (Bionic Beaver). I will use the Ubuntu minimal server as the basis for this setting.

Prerequisites

  • Ubuntu 20.04
  • Root privileges

What we will do?

  1. Install PostgreSQL and phpPgAdmin
  2. Create new user PostgreSQL
  3. Configure Apache2
  4. Setup UFW firewall
  5. Test

Step 1 – Install PostgreSQL, phpPgAdmin and some dependencies4 packages on the Ubuntu system, update and upgrade all packages with the apt command below.

  sudo apt update 
sudo apt upgrade

And now we are ready to install PostgreSQL, phpPgAdmin and Apache2 packages.

PostgreSQL and PhpPgAdmin are available in the Ubuntu repository. So you just need to install them with the apt command.

  sudo apt -y install postgresql postgresql-contrib phppgadmin 

When all installation is complete, start the PostgreSQL service and add it to the system startup.

  systemctl start postgresql 
systemctl enable postgresql

Then, check the PostgreSQL service with the following command.

  systemctl status postgresql 

As a result, the PostgreSQL service is running.

  19659017]  Install PostgreSQL on Ubuntu 20.04      

And all package installation of PostgreSQL and phpPgAdmin has been completed.

Step 2 - Create New User PostgreSQL

PostgreSQL uses user authentication and authorization roles, just like Unix-Style permissions. By default, PostgreSQL creates a new user named "postgres" for basic authentication.

In this step, we create a new PostgreSQL user who has privileges as a super user, creates a database, creates a role and logs in. The new user will be used to log in to PostgreSQL via & # 39; phpPgAdmin & # 39 ;, and in order to do so, we must log in to the PostgreSQL shell as the default postgres user.

Log in to the PostgreSQL shell with the command below.

  sudo -i -u postgres psql 

Then create a new role & # 39; hakase & # 39; with the password & # 39; chakaspspsql & # 39; with the following question.

  CREATE ROLE hakase WITH SUPERUSER CREATEDB CREATEROLE LOGIN ENCRYPTED PASSWORD & # 39; hakasepasspgsql & # 39 ;; 

Now check all available users on the PostgreSQL system.

   you 

And you get a new user & # 39; hakase & # 39; on the list, now enter & # 39; q & # 39; to leave the PostgreSQL shell.

 Create new user PostgreSQL

As a result, a new user named & # 39; hakase & # 39; created and it will be able to log into the PostgreSQL server via phpPgAdmin.

Step 3 - Configure Apache Web Server

In this step, we configure Apache configuration for phpPgAdmin, which is automatically generated during the package installation.

Go to directory & # 39; / etc / apache2 / conf-available & # 39; and edit the configuration file & # 39; phppgadmin.conf & # 39; with vim by typing:

  cd / etc / apache2 / conf-available / 
vim phppgadmin.conf

By default, phpPgAdmin is available through the URL: "phppgadmin". For security reasons, we will change the default path address for phppgadmin by changing the "Alias" option.

Change the default path address "phppgadmin" with your own path as below.

  Alias ​​/ pgsqladminlogin / usr / share / phppgadmin 

No comment from line & # 39; #Require local & # 39; by adding a # in front of the line and adding below the line allow from everyone so you can access from your browser.

  Request all granted 

Save and close.

Then test the Apache configuration and make sure there is no error, then restart the Apache service.

  apachectl configtest 
systemctl restart apache2

As a result, the apache configuration for phpPgAdmin has been completed.

Step 4 - Setting up the UFW firewall

For this guide, we will run the PostgreSQL and Apache services under the UFW firewall.

Add the ssh, http, and https ports to the ufw firewall with the following command.

  for svc in ssh http https 
do
ufw allow $ svc
done

Then the UFW firewall starts and activates.

  ufw enable 

And you will be asked to make sure you want to run the UFW firewall service. Type & # 39; y & # 39; and press Enter to start the firewall.

 Add service to UFW firewall

And the UFW firewall is running with ssh, http and https protocol on top.

Check the UFW firewall status with the command below.

  ufw status numbered 

Below is the result you will get.

 View UFW firewall rules

Step 5 - Test

Before proceeding, check the PostgreSQL and Apache port services with the command ss below.

  ss -plnt 

And you get the PostgreSQL port & # 39; 5432 & # 39; and Apache port & # 39; 80 & # 39; is in LISTEN mode, both services are running.

 Check port with ss command

Then open your web browser and type the server IP address with your custom path of phpPgAdmin.

http://10.5.5.32/pgsqladminlogin/

And you get the default phpP gAdmin page as below.

 phpPgAdmin Index Page

Now click on the & # 39; Server tab and you will get the phpPgAdmin login page.

 phpPgAdmin Login Page

Type the PostgreSQL user and password created on top and click the [ Login ].

And you will get this phpPgAdmin dashboard interface.

 phpPgAdmin Dashboard

Installation of the PostgreSQL database with phpPgAdmin on Ubuntu 20.04 LTS has been successfully completed.

Conclusion

PostgreSQL is an advanced object relational database management system (ORDBMS). It is Open Source and has a large and active community. PostgreSQL provides the psql command-line program as the primary front-end, which can be used to directly enter SQL queries or run them from a file. phpPgAdmin is a web-based management tool for PostgreSQL written in PHP that facilitates the administration of Postgres databases.


Source link