PostgreSQL is a free and open source relational database management system. It is the most popular database server used in a production environment.
It is capable of handling workloads from small applications to large applications. The main function of PostgreSQL is to store data more securely.
You can execute stored procedures in various programming languages such as Python, Java, C/C++, Perl and Ruby.
PostgreSQL runs on most of all operating systems such as Windows, Linux, HP-UX, SGI IRIX, Mac OS X, Solaris, Unix and many more.
PhppgAdmin is a web based database management tool for PostgreSQL. It is very useful for users that do not have any experience to manage PostgreSQL
database from command line interface. It is very similar to PhpmyAdmin that provides an easy way to create a database, create a role and create
tables in PostgreSQL. So if you want to manage the PostgreSQL databases from a web based interface, then PhppgAdmin is best choice for you.
In this tutorial, we will explain how to install PostgreSQL and PhppgAdmin in Ubuntu 16.04 server.
Requirements
- A server runing Ubuntu-16.04 on your system.
- A non-root user with sudo privileges setup on your server.
Update the System
Before starting, you will need to update the system’s package repository database with the latest version. You can do this with the following command:
sudo apt-get update -ysudo apt-get upgrade -y
You your system is up to date you can proceed to the next step.
Install PostgreSQL
By default PostgreSQL is available in the Ubuntu 16.04 repository. You can easily install it by running the following command:
sudo apt-get install postgresql postgresql-contrib
Once installation is completed, it will create the user postgres with the role postgres and also create a system account with same name postgres.
Next start postgresql service and enable it to start on boot with the following command:
sudo systemctl start postgresqlsudo systemctl enable postgresql
You can connect to the postgres server with the following command:
sudo -u postgres psql
The above command will log you directly into Postgres.
To check the login information, run the following command:
postgres-# \conninfo
You should see the following output:
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
By default, PostgreSQL creates a new user called “postgres” for basic authentication. So you will need to set a postgres user password to login to the “postgres” account.
You can do this by running the following command:
postgres=# \password postgres
If you want to disconnect from PostgreSQL database command prompt, run the following command:
postgres-# \q
The above command will return you back to the Ubuntu command prompt.
Install and Configure PhppgAdmin
Next, you will need to install Apache and PhppgAdmin on your server.
You can install it by just running the following command:
sudo apt-get install apache2 phppgadmin
The above command will install all the necessary packages needed by PostgreSQL.
Once installation is complete, you will need to configure apache in order to access phpPgAdmin via web browser.
You can do this by editing phppgadmin.conf located at /etc/apache2/conf-available/ directory:
sudo nano /etc/apache2/conf-available/phppgadmin.conf
Comment the “Require local” and add “Allow From all” line after it. After editing the file looks like this:
Alias /phppgadmin /usr/share/phppgadmin
<Directory /usr/share/phppgadmin>
<IfModule mod_dir.c>
DirectoryIndex index.php
</IfModule>
AllowOverride None
# Only allow connections from localhost:
#Require local
Allow From all
<IfModule mod_php.c>
php_flag magic_quotes_gpc Off
php_flag track_vars On
#php_value include_path .
</IfModule>
<IfModule !mod_php.c>
<IfModule mod_actions.c>
<IfModule mod_cgi.c>
AddType application/x-httpd-php .php
Action application/x-httpd-php /cgi-bin/php
</IfModule>
<IfModule mod_cgid.c>
AddType application/x-httpd-php .php
Action application/x-httpd-php /cgi-bin/php
</IfModule>
</IfModule>
</IfModule>
</Directory>
Save and close the file when you are finished.
Next, you will also need to edit config.inc.php file in order to tweak some settings for phpPgAdmin.
To do so, open the config.inc.php file:
sudo nano /etc/phppgadmin/config.inc.php
Change the file as shown below:
<?php
/**
// Display name for the server on the login screen
$conf['servers'][0]['desc'] = 'PostgreSQL';
// Hostname or IP address for server. Use '' for UNIX domain socket.
// use 'localhost' for TCP/IP connection on this computer
$conf['servers'][0]['host'] = 'localhost';
// Database port on server (5432 is the PostgreSQL default)
$conf['servers'][0]['port'] = 5432;
// Database SSL mode
// Possible options: disable, allow, prefer, require
// To require SSL on older servers use option: legacy
// To ignore the SSL mode, use option: unspecified
$conf['servers'][0]['sslmode'] = 'allow';
// For a PostgreSQL 8.1+ server, you can set this to 'postgres'.
$conf['servers'][0]['defaultdb'] = 'template1';
// You can set these to '' if no dumper is available.
$conf['servers'][0]['pg_dump_path'] = '/usr/bin/pg_dump';
$conf['servers'][0]['pg_dumpall_path'] = '/usr/bin/pg_dumpall';
// your browser preference.
$conf['default_lang'] = 'auto';
$conf['autocomplete'] = 'default on';
$conf['extra_login_security'] = false;
$conf['owned_only'] = false;
// Display comments on objects? Comments are a good way of documenting
// a database, but they do take up space in the interface.
$conf['show_comments'] = true;
// Display "advanced" objects? Setting this to true will show
// aggregates, types, operators, operator classes, conversions,
// languages and casts in phpPgAdmin. These objects are rarely
// administered and can clutter the interface.
$conf['show_advanced'] = false;
// Display "system" objects?
$conf['show_system'] = false;
// Minimum length users can set their password to.
$conf['min_password_length'] = 1;
// Width of the left frame in pixels (object browser)
$conf['left_width'] = 200;
// Which look & feel theme to use
$conf['theme'] = 'default';
// Show OIDs when browsing tables?
$conf['show_oids'] = false;
// Max rows to show on a page when browsing record sets
$conf['max_rows'] = 30;
// Max chars of each field to display by default in browse mode
$conf['max_chars'] = 50;
// Send XHTML strict headers?
$conf['use_xhtml_strict'] = false;
// Base URL for PostgreSQL documentation.
// '%s', if present, will be replaced with the PostgreSQL version
// (e.g. 8.4 )
$conf['help_base'] = 'http://www.postgresql.org/docs/%s/interactive/';
// Configuration for ajax scripts
// Time in seconds. If set to 0, refreshing data using ajax will be disabled (locks and activity pages)
$conf['ajax_refresh'] = 3;
$conf['plugins'] = array();
/*****************************************
* Don't modify anything below this line *
*****************************************/
$conf['version'] = 19;
?>
The above settings will allow us to log in to PhppgAdmin as the postgres user.
Save and close the file when you are finished, then restart PostgreSQL and Apache service with the following command:
sudo systemctl restart apache2sudo systemctl restart postgresql
Once you are done, you can proceed to the next step.
Access PhppgAdmin Web Interface
Once everything is ok, it’s time to access PhppgAdmin web interface.
Open your favourite web browser and type the URL http://your-server-ip/phppgadmin, you will be asked for username and password, enter postgres as user and password which you have created earliar to log in PhppgAdmin.
Conclusion
Congratulations! you have successfully installed PostgreSQL and PhppgAdmin on your Ubuntu 16.04 server. You can now easily create database, user, role and manage postgresql database using web based interface.
Share this Article!
