PostgreSQL is an advanced, powerful, open source object-relational database system. It supports almost all operating systems. PostgreSQL can store data securely and can handle requests for a small website to a very large enterprise application. It is fully ACID compliant, has full support for foreign keys, joins, views, triggers, and stored procedures. It runs stored procedures in more than a dozen programming languages, including Java, Perl, Python, Ruby, Tcl, C/C++, and has its own PL/pgSQL. PostgreSQL is free and open source, it has been in active development for more than 15 years.
Advantages of PostgreSQL
- It’s free and open source and need less maintenance and tuning compared to other databases servers.
- It’s extremely stable and reliable, also it’s cross platform.
- It uses multiple row data storage strategy called MVCC to make PostgreSQL extremely responsive in high volume environments.
- There are many GUI based tools available for administering PostgreSQL.
In this tutorial we are going to install latest stable version of PostgreSQL which is 9.5.3 along with phpPgAdmin.
Requirements
PostgreSQL does not need any special minimum hardware requirements. You only need a VPS or Dedicated server having CentOS 7.x installed. In this tutorial we are going to use a non-root account to execute the commands. If you are logged in as root user, omit sudo command from all the commands.
Installing PostgreSQL
First of all update your system and repositories using the following command.
sudo yum -y update
To install PostgreSQL on your server, first you will need to add the latest repository of PostgreSQL as the default repository contains PostgreSQL 9.2. Run the following command to do so.
sudo rpm -Uvh https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-7-x86_64/pgdg-centos95-9.5-2.noarch.rpm
Now as we have enabled the PostgreSQL repository in our system, we can install PostgreSQL 9.5 using the following command.
sudo yum -y install postgresql95-server postgresql95-contrib
The above command will install PostgreSQL in your system, before using the software, we will need to run the following command to initialize the database.
sudo /usr/pgsql-9.5/bin/postgresql95-setup initdb
Now start PostgreSQL and enable it to automatically start at boot time using the following commands:
sudo systemctl start postgresql-9.5
sudo systemctl enable postgresql-9.5
Next, you will need to adjust your firewall to allow access to port number 5432 on which PostgreSQL runs, as well as port number 80 on which http runs, so that we can access phpPgAdmin through a web browser. Run the following commands for same.
sudo firewall-cmd --permanent --add-port=5432/tcp
sudo firewall-cmd --permanent --add-service=http
sudo firewall-cmd --reload
Now your can access PostgreSQL command line interface directly from your terminal, use the following command for same.
sudo su - postgres
postgres is the default database and database user, the above command will log you into your system as user postgres. To switch to PostgreSQL SQL command line, run the following command.
psql
You will see following output.
[centos@liptan ~]$ sudo su - postgres
Last login: Thu Jul 21 05:26:08 UTC 2016 on pts/1
-bash-4.2$ psql
psql (9.5.3)
Type "help" for help.
postgres=#
From this interface you can run PostgreSQL queries. It is very important that you set a password to postgres user, to add a password run the following command in postgres SQL command line.
password postgres
You will see following output.
postgres=# password postgres
Enter new password:
Enter it again:
postgres=#
To view the version of PostgreSQL you are running run the following query.
SELECT version();
You will see following output.
postgres=# SELECT version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 9.5.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit
(1 row)
You can logout from this interface by executing q or quitcommand. To exit from postgres user’s terminal back to your default user, execute exit command.
Working with PostgreSQL via Command Line
To create a database, run the following query in postgres user’s command line interface by logging in as user postgres using command sudo su - postgres.
createdb mydata
In the above example we have used database mydata, but you can choose any name for your database, as long as it is not similar to another database in your server. To create a user, run the following command.
createuser datauser
In the above example we have created a user datauser but you can create any user as long as it is not similar to any PostgreSQL user as well as any system user. To provide full access to user datauser on database mydata you will need to goto SQL prompt mode by executing psql command. Now run the following query to protect your new user with an encrypted password.
ALTER USER datauser WITH ENCRYPTED password 'StrongPassword';
Now after creating a strong password, you can grant all privileges to this user on database by data, by running the following query.
GRANT ALL PRIVILEGES ON DATABASE mydata TO datauser;
To see the list of database run l command, you should see following output.
postgres=# l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
mydata | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres+
| | | | | datauser=CTc/postgres
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
To select a database, run the following command.
c mydata
You will see following output.
postgres=# c mydata
You are now connected to database "mydata" as user "postgres".
In above command, mydata is the name of the database you want to select. To create a table into the database, select the database in which you want to create a table using the command above. Now use the following query syntax to create a database.
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( one or more columns )
);
For example you can consider the following table.
CREATE TABLE STUDENT(
S_ID INT PRIMARY KEY NOT NULL,
S_NAME TEXT NOT NULL,
F_NAME TEXT NOT NULL,
S_AGE INT NOT NULL,
S_ADDRESS CHAR(50),
MARKS INT
);
The above query will create a table in your database with 6 tuples or columns in it. The primary key of the table will be S_ID. To view a list of all the tables in a database, run d command. You will see following output.
mydata=# d
List of relations
Schema | Name | Type | Owner
--------+---------+-------+----------
public | student | table | postgres
(1 row)
To see the schema of a table you can run the following command.
d student
In above example student is the name of table. The command should print schema of the table in tabular format.
mydata=# d student
Table "public.student"
Column | Type | Modifiers
-----------+---------------+-----------
s_id | integer | not null
s_name | text | not null
f_name | text | not null
s_age | integer | not null
s_address | character(50) |
marks | integer |
Indexes:
"student_pkey" PRIMARY KEY, btree (s_id)
To delete a table from selected database you can run the following query.
DROP TABLE student;
To delete a database you can use following command in postgres user command prompt.
dropdb mydata
Or you can also execute the SQL query in SQL mode which is
DROP DATABASE mydata
To delete a database user, run the following command in terminal mode.
dropuser datauser
Configuring MD5 Authentication
MD5 authentication enables users to supply MD5 encrypted passwords only, this increases the security of the passwords in open world. Login to your system as a normal user and edit the /var/lib/pgsql/9.4/data/pg_hba.conf file with your favorite editor. In this example we are using nano, if you don’t have nano installed you can run sudo yum -y install nano to install it.
sudo nano /var/lib/pgsql/9.5/data/pg_hba.conf
Scroll down to find the following lines.
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 ident
# IPv6 local connections:
host all all ::1/128 ident
Change the methods from peer and ident to md5 also change 127.0.0.1/32 to 0.0.0.0/0, so that MD5 authentication is enabled for request from any IP address. Your configuration should look like as shown below.
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 0.0.0.0/0 md5
# IPv6 local connections:
host all all ::1/128 md5
Now restart PostgreSQL using the following command.
sudo systemctl restart postgresql-9.5
Configuring TCP/IP Connections
By default PostgreSQL connections to our system is blocked for outside users, so that outside users can not access our SQL server. To enable access to all the users of any IP address, you will need to edit the default PostgreSQL configuration file. Run the following command to edit the file using nano.
sudo nano /var/lib/pgsql/9.5/data/postgresql.conf
Find the following lines.
# - Connection Settings -
#listen_addresses = 'localhost' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*'
# for all
# (change requires restart)
#port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
Uncomment the line #listen_addresses = 'localhost' and change it to listen_addresses = '*' also uncomment the line #port = 5432. You can also change the value of maximum concurrent connections at a time by changing the value of max_connections. It should look like as shown below.
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*'
# for all
# (change requires restart)
port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
Now restart PostgreSQL using the following command.
sudo systemctl restart postgresql-9.5
Installing phpPgAdmin
phpPgAdmin is a GUI application which is used for graphical administration of PostgreSQL database and users.
Features of phpPgAdmin
- phpPgAdmin gives you ability to maintain multiple servers from single installation.
- It Supports multiple version of PostgreSQL.
- It supports all types of operations on database, table and users.
- Easily import and export databases in multiple formats.
- Supports multiple languages with no encoding conflicts.
The phpPgAdmin package is available in PostgreSQL yum repository, which we have already added to our system while installing PostgreSQL. You can add it again by running the following command.
sudo rpm -Uvh https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-7-x86_64/pgdg-centos95-9.5-2.noarch.rpm
Now install phpPgAdmin using the following command.
sudo yum -y install phpPgAdmin httpd
The above command will install the latest version of phpPgAdmin along with Apache web server. By default phpPgAdmin is not accessible from outside. You will need to edit /etc/httpd/conf.d/phpPgAdmin.conf the file to enable phpPgAdmin to be accessible from outside.
sudo nano /etc/httpd/conf.d/phpPgAdmin.conf
You will see following lines.
Alias /phpPgAdmin /usr/share/phpPgAdmin
# Apache 2.4
Require local
#Require host example.com
# Apache 2.2
Order deny,allow
Deny from all
Allow from 127.0.0.1
Allow from ::1
# Allow from .example.com
Change Require local to Require All Granted and comment out Deny from all and change Allow from 127.0.0.1 to Allow from all. After the changes your configuration should look like this.
Alias /phpPgAdmin /usr/share/phpPgAdmin
# Apache 2.4
Require all granted
#Require host example.com
# Apache 2.2
Order deny,allow
#Deny from all
Allow from all
Allow from ::1
# Allow from .example.com
If you have SELinux enabled in your system, then you will need to run the following command otherwise you will not be able to login to phpPgAdmin.
setsebool -P httpd_can_network_connect_db 1
Now you will need to configure phpPgAdmin settings so that it can communicate with the PostgreSQL server. Edit /etc/phpPgAdmin/config.inc.php file with your favorite editor.
sudo nano /etc/phpPgAdmin/config.inc.php
Now find these lines.
// Hostname or IP address for server. Use '' for UNIX domain socket.
// use 'localhost' for TCP/IP connection on this computer
$conf['servers'][0]['host'] = '';
Change $conf['servers'][0]['host'] = ''; to $conf['servers'][0]['host'] = 'localhost';
By default phpPgAdmin will not allow the default database user to login which is postgres. To allow this user to login scroll down further to find this configuration.
$conf['extra_login_security'] = true;
Change it to
$conf['extra_login_security'] = false;
Now save the file and restart PostgreSQL and also start or restart Apache web server using the following commands.
sudo systemctl restart postgresql-9.5
sudo systemctl restart httpd
sudo systemctl enable httpd
Now browse to the following link via your favorite web browser.
http://your-server-ip/phpPgAdmin
You will see following interface.
HP_NO_IMG/data/uploads/users/2a78e75d-343a-47ce-9d84-14a6ba54abbc/1467266354.png” alt=”” />
Click on PostgreSQL link from left sidebar, you will see interface to provide username and password. Login using any database user you have created, if you did not created any database user yet, you can login with the default PostgreSQL account which is postgres.
HP_NO_IMG/data/uploads/users/2a78e75d-343a-47ce-9d84-14a6ba54abbc/1633583947.png” alt=”” />
Once you are logged in you will see following interface.
HP_NO_IMG/data/uploads/users/2a78e75d-343a-47ce-9d84-14a6ba54abbc/1444152693.png” alt=”” />
With the help of this interface you can create, modify and delete the databases and users. You can also modify the data inside a row using this interface.
Conclusion
In this tutorial we have learnt how to install latest version of PostgreSQL which is known for its stability and performance. You can now easily install PostgreSQL 9.5 along with phpPgAdmin on CentOS 7. You can also configure phpPgAdmin to administrator you databases.
Share this Article!
