Tuesday, 10 October 2017

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

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

PostgreSQL Online Documentation



0 comments:

Post a Comment