PostgreSQL is an object-relational database management system (ORDBMS) that is suited for large databases and has many advanced features. Its emphasis is on extensibility and standards-compliance. PostgreSQL can handle workloads ranging from small single-machine applications to large Internet-facing applications with many concurrent users.
It is available for many operating systems few of which are Linux, Solaris, FreeBSD and Microsoft Windows.
What happens if you want to manage the PostgreSQL databases from a graphical interface and not the command line?
You can use phpPgAdmin which is a is a web-based administration tool for PostgreSQL. It is perfect for users that don’t have experience in managing PostgreSQL from the command line. phpPgAdmin is the PostgreSQL equivalent of MySQL’s phpMyAdmin and provides a convenient way for users to create databases, create tables, alter tables and query their own data using industry-standard SQL.
We will be using our SSD 1 Linux VPS hosting plan for this tutorial.
LOG IN TO YOUR SERVER VIA SSH
# ssh [email protected]_ip
You can check whether you have the proper Ubuntu version installed on your server with the following command:
# lsb_release -a
You should get this output:
Distributor ID: Ubuntu Description: Ubuntu 16.04.1 LTS Release: 16.04 Codename: xenial
UPDATE THE SYSTEM
Make sure your server is fully up to date using:
# το apt-get update && το apt-get αναβάθμιση
Now install PostgreSQL and phpPgAdmin using the below command:
# apt-get install postgresql postgresql-contrib phppgadmin
This command will install all the necessary packages needed by PostgreSQL and phpPgAdmin such as Apache, PHP etc… so don’t worry if you don’t have Apache or PHP installed on your server.
Once the installation is completed, log into the ‘postgres‘ system account so you can access PostgreSQL. Type:
# Su - postgres
Now you can access PostgreSQL using:
[email protected]:~$ psql
Change your ‘postgres‘ role password to match your needs:
postgres=# password postgres
Enter the new password when prompted.
Quit the psql command line:
Exit the postgres user:
In order to access phpPgAdmin via your browser, the Apache web server needs to be configured. Therefore open the phppgadmin.conf αρχείο:
# nano /etc/apache2/conf-available/phppgadmin.conf
Comment the ‘Require local‘ line by adding ‘#’ in front of the line. Then under that very line, add ‘Allow From all‘. Save and close the file. After editing the section should look like this:
# Only allow connections from localhost: #Require local Allow From all
Now tweak some settings for phpPgAdmin. Ανοίξτε το config.inc.php αρχείο:
# nano /etc/phppgadmin/config.inc.php
Locate the ‘$conf[‘extra_login_security’] = true;‘ and change the value from true για να false so you can log in to phpPgAdmin as the postgres user.
Save and close the file.
Now restart PostgreSQL and Apache. Then enable them to start on boot. All of this can be done using the below 4 commands:
# systemctl restart postgresql # systemctl restart apache2 # systemctl enable postgresql # systemctl enable apache2
Now open your favorite web browser and access phpPgAdmin by navigating to http://your_server_IP/phppgadmin/. You will be welcomed by a page as shown in the image below:
You can log in using the postgres user and the password you’ve configured previously. Now use this great tool to create and manage databases in PostgreSQL.