PostgreSQL is a free, open-source and one of the most advanced object-relational database management system. It is specially designed for mission-critical applications. Managing the PostgreSQL database from command-line is very difficult for any beginner user. PhpPgAdmin is a web-based database management interface for PostgreSQL. It provides an easy way to manage the PostgreSQL through the web interface.
In this tutorial, we will show you how to install PostgreSQL and PhpPgAdmin on Ubuntu 20.04 server.
- A server running Ubuntu 20.04.
- A root password set up on your server.
Update the System
First, it is recommended to update your system's packages repository to the latest version. You can update them with the following command:
apt-get update -y
Once all the packages are updated, you can proceed to install PostgreSQL.
Install PostgreSQL Server
By default, PostgreSQL server is available in the Ubuntu 20.04 default repository. You can install it by running the following command:
apt-get install postgresql -y
After installing PostgreSQL, start the PostgreSQL service and enable it to start after system reboot.
systemctl start postgresql
systemctl enable postgresql
Next, verify the PostgreSQL status with the following command:
systemctl status postgresql
You should get the following output:
● postgresql.service - PostgreSQL RDBMS Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled) Active: active (exited) since Wed 2020-09-16 07:03:14 UTC; 33s ago Main PID: 2287 (code=exited, status=0/SUCCESS) Tasks: 0 (limit: 4691) Memory: 0B CGroup: /system.slice/postgresql.service Sep 16 07:03:14 ubuntu2004 systemd: Starting PostgreSQL RDBMS... Sep 16 07:03:14 ubuntu2004 systemd: Finished PostgreSQL RDBMS.
Create a Database for PhpPgAdmin
Next, you will need to create a database and user for PhpPgAdmin.
First, log into the PostgreSQL with the following command:
su - postgres
Once login, you should see the following output:
psql (12.4 (Ubuntu 12.4-0ubuntu0.20.04.1)) Type "help" for help.
Next, create a database and user with the following command:
postgres=# CREATE USER pgadmin WITH PASSWORD 'password';
postgres=# CREATE DATABASE pgadmindb;
Next, grant all the privileges to the pgadmindb with the following command:
postgres=# GRANT ALL PRIVILEGES ON DATABASE pgadmindb to pgadmin;
Next, exit from the PostgreSQL shell with the following command:
By default, PhpPgAdmin is available in the Ubuntu 20.04 default repository. You can install it with the following command:
apt-get install phppgadmin -y
By default, PhpPgAdmin is accessible only from the localhost. So you will need to configure it for external access. You can do it by editing the /etc/apache2/conf-available/phppgadmin.conf configuration file:
Find the following line:
And, replace it with the following line:
Require all granted
Save and close the file then check Apache for any configuration error with the following command:
You should get the following output:
Next, restart the Apache service to apply the changes:
systemctl restart apache2
Now, open your web browser and access the PhpPgAdmin web interface using the URL http://your-server-ip/phppgadmin/. You should see the following screen:
Now, click on the Server => PostgreSQL. You should see the PhpPgAdmin login screen:
Provide your PhpPgAdmin username, password and click on the Login button. You should see the PhpPgAdmin dashboard in the following screen:
From here, you can run any query for PostgreSQL database server.
Congratulations! you have successfully installed PostgreSQL and PhpPgAdmin on Ubuntu 20.04 server. You can now easily manage your PostgreSQL database from the web-based interface.