Installing PostgreSQL 9.4 & phpPgAdmin in CentOS 7

PostgreSQL is a database server. It is an object-relational database management system and it’s main purpose is to store data. PostgreSQL calls itself “The world’s most advanced open-source database”. 

It is a fact that MySQL and PostgreSQL are two most popular open-source relation database management systems (RDMS).


This article will help you for installing PostgreSQL and phpPgAdmin on CentOS 7 

1. Install PostgreSQL 9.4 Repository


Install the latest stable version of PostgreSQL 9.4 for CentOS 7 64bit

rpm -ivh http://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/pgdg-centos94-9.4-3.noarch.rpm


2. Install PostgreSQL 9.4 Server and Dependencies


Install PostgreSQL 9.4 server from YUM repository installed in previous step. YUM will automatically resolve and install dependencies also.

yum install -y postgresql94-server postgresql94-contrib

3. Initialize PostgreSQL 9.4 Database


You need to initialize the PostgreSQL 9.4 database before you can successfully start it. 

/usr/pgsql-9.4/bin/postgresql94-setup initdb


4. Start PostgreSQL 9.4 and Make it Start at Boot


You can find the PostgreSQL 9.4 service with the following command and also see that it is not enabled to start at boot:

[root@server1 ~]# systemctl list-unit-files |grep postgres
postgresql-9.4.service disabled


Enable the PostgreSQL 9.4 service to start at boot:

systemctl enable postgresql-9.4.service


and start the PostgreSQL 9.4 service as follows:

systemctl start postgresql-9.4.service


5. Confirm the Database is Running

You can confirm the database is running by viewing the process list:

ps auxf |grep postgres
postgres 3042 0.0 1.9 337452 15200 ? S 11:40 0:00 /usr/pgsql-9.4/bin/postgres -D /var/lib/pgsql/9.4/data
postgres 3043 0.0 0.1 192600 1368 ? Ss 11:40 0:00 _ postgres: logger process 
postgres 3045 0.0 0.2 337452 1680 ? Ss 11:40 0:00 _ postgres: checkpointer process 
postgres 3046 0.0 0.3 337452 2480 ? Ss 11:40 0:00 _ postgres: writer process 


6. Environmental Setup and Server Configurations 


Set the desired path for postgres user :


Edit passwd file as

vi /etc/passwd

postgres:x:501:501:PostgreSQL:/usr/pgsql-9.4:/bin/bash

Set Permissions :

cd /usr 

chown -R postgres:postgres pgsql-9.4

cd /var/lib/

chmod 755 pgsql


7.  Adjust Iptables/Firewall


Next, open the postgres ports and adjust iptables to access postgresql from remote systems.

firewall-cmd --permanent --add-port=5432/tcp
firewall-cmd --permanent --add-port=80/tcp
firewall-cmd --reload


Set SELINUX to permissive mode 


8.  PostgreSQL basic setup


The default database name and database user are “postgres”. Switch to postgres user to perform postgresql related operations:


To login to postgresql, enter the command:

[root@server1 ~]# su - postgres

-bash-4.2$ psql


Sample Output :

psql (9.4.4)
Type "help" for help.
postgres=#


Change the postgres user’s Linux password:

sudo passwd postgres


Set “postgres” user password

postgres=# \password postgres 
Enter new password: 
Enter it again: 
postgres=# \q


To install PostgreSQL Adminpack, enter the command in postgresql prompt:

postgres=# CREATE EXTENSION adminpack;
CREATE EXTENSION

To see the available commands in the psql shell, run:

help or /h 


Verify the version of PostgreSQL installed:

SELECT version();


Create a schema called test in the default database called postgres

postgres=# CREATE SCHEMA test;


Create a role (user) with password “

postgres=# CREATE USER xxx PASSWORD 'yyy';


Grant privileges (like the ability to create tables) on new schema to new role

postgres=# GRANT ALL ON SCHEMA test TO xxx;


Grant privileges (like the ability to insert) to tables in the new schema to the new role

postgres=# GRANT ALL ON ALL TABLES IN SCHEMA test TO xxx;


Create Database :

$ createdb mydb

OR

postgres=# create database mydb


List Databases :

postgres=# \l
                          List of databases
Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
mytestdb  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
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)

You may also show the current database and user by entering \c from the Postgres shell. Additional info, like socket and port, will be included if you use \conninfo

You are connected to database "mydb" as user "postgres" via socket in "/var/run/postgresql" at port "5432".


Create Tables :

CREATE TABLE employees (employee_id int, first_name varchar, last_name varchar);

INSERT INTO employees VALUES (1, 'John', 'Doe');

SELECT * FROM employees;


List Tables in a Database :

mydb-# \dt
          List of relations
Schema |   Name    | Type  |  Owner
--------+-----------+-------+----------
public | employees | table | postgres


9.  Configuration Changes


The configuration files for PostgreSQL on CentOS 7 are located in:


/var/lib/pgsql/9.4/data/

These files contains helpful comments regarding the configuration options available.


Configuring access is handled by editing a couple files. First we will tell PostgreSQL to start listening on our network interfaces. This is done by making a change in /var/lib/pgsql/9.4/data/postgresql.conf.


Uncomment the listen_addresses line and changing localhost to * 

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - 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)


#data_directory = ‘ConfigDir’  # If we want to change the data directory path, uncomment this line and enter full path to the data directory – this change requires PostgreSQL restart.


#hba_file = ‘ConfigDir/pg_hba.conf’  # If we want to change thehba file directory path, uncomment this line and enter full path to the hba file – this change requires PostgreSQL restart.

#port = 5432  # If we want to change the port PostgreSQL listens on, uncomment this line and enter the desired number – this change requires PostgreSQL restart.


#max_connections = 100  # This is the value of max connection PostgreSQL will accept. Raise this value if needed – this change requires PostgreSQL restart.

You can make following Parameter Changes :

shared_buffers = 150MB

wal_buffers = 64MB

temp_buffers=8MB

checkpoint_timeout = 1h

checkpoint_segments = 30

bgwriter_delay = 100ms

bgwriter_lru_maxpages = 300 

wal_writer_delay = 100ms

log_filename= 'postgresql-%Y-%m-%d.log'

log_destination = ‘stderr’

Logging_collector =on 

log_directory = 'pg_log' 

log_rotation_age = 2d

log_rotation_size =20MB

log_statement = 'all'

log_connections = on

log_disconnections  = on

log_error_verbosity = verbose

log_checkpoints = on

log_temp_files  = 0 

log_statement  =all 

shared_preload_libraries = 'pg_stat_statements'

pg_stat_statements.max = 10000

pg_stat_statements.track = all

client_min_messages = notice 

log_min_messages =warning

log_min_error_statement = error

log_min_duration_statement = 0

log_hostname = on


pg_stat_statements


Set the following variables:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 1000
pg_stat_statements.track = all


Create extension : 

create extension pg_stat_statements;


To have access to the statistics and the helper functions Use :

• Reset statistics

select pg_stat_statements_reset();

• Which query was called the most

select * from pg_stat_statements order by calls desc;

• Which query used the most CPU time

select * from pg_stat_statements order by total_time desc;

• Report time, calls, row and hit percentage

SELECT query,calls,total_time,rows,100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;



Secure Local Access :

By default, PostgreSQL does not allow password authentication. We will change that by editing its host-based authentication (HBA) configuration.


Open the HBA configuration with your favorite text editor.

sudo vi /var/lib/pgsql/data/pg_hba.conf


Find this section in the file:

# 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


and add a new host line with a specific IP address or range:

host    all             all             <Client IP address>/32        md5


Replace peer with md5 on this line to activate password authentication using an MD5 hash.


To enable these changes, you need to restart PostgreSQL.

sudo systemctl restart postgresql-9.4.service
su - postgres


and verify that we are now listening on port 5432:

# ss -l -n |grep 5432
u_str  LISTEN     0      128    /var/run/postgresql/.s.PGSQL.5432 7728992                 * 0
u_str  LISTEN     0      128    /tmp/.s.PGSQL.5432 7728994                 * 0
tcp    LISTEN     0      128                    *:5432                  *:*
tcp    LISTEN     0      128                   :::5432                 :::*


Now we can connect from a remote system using a command-line client, or a GUI such as “pgAdmin3”. Connecting from another system using psql looks like this:

$ psql -h <Server IP Address> -p 5432 -U postgres -W
Password for user postgres:
psql (9.4.4)
Type "help" for help.

postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication | {}

postgres=# \q


Manage PostgreSQL with phpPgAdmin


To install phpPgAdmin, enter the following command:

yum install phpPgAdmin httpd


By default, you can access phppgadmin using http://localhost/phpPgAdmin from your local system only. To access remote systems, do the following.


Edit file /etc/httpd/conf.d/phpPgAdmin.conf:

vi /etc/httpd/conf.d/phpPgAdmin.conf


Make the changes as shown below 

[...]
Alias /phpPgAdmin /usr/share/phpPgAdmin

<Location /phpPgAdmin>
    <IfModule mod_authz_core.c>
        # Apache 2.4
        Require all granted
        #Require host example.com
    </IfModule>
    <IfModule !mod_authz_core.c>
        # Apache 2.2
        Order deny,allow
        Allow from all
        # Allow from .example.com
    </IfModule>
</Location>
systemctl  start httpd

systemctl enable httpd


Configure phpPgAdmin


Edit file /etc/phpPgAdmin/config.inc.php, and do the following changes. Most of these options are self-explanatory.

Find the following line:

$conf['servers'][0]['host'] = '';
Change it as shown below:

$conf['servers'][0]['host'] = 'localhost';
And find the line:

$conf['extra_login_security'] = true;
Change the value to false:

$conf['extra_login_security'] = false;
Find the line:

$conf['owned_only'] = false;
Set the value as true.ru

$conf['owned_only'] = true;
Save and close the file. Restart postgresql service and Apache services.


Restart PostgreSQL Services :

systemctl restart postgresql-9.4
systemctl restart httpd


Now open your browser and navigate to http://ip-address/phpPgAdmin

Connect to database using username and Password 

More Information

PostgreSQL Online Documentation

Checkout the article on how to configure Master-Slave streaming replication  click here

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Powered by WordPress.com.

Up ↑

%d bloggers like this: