Install PostgreSQL 9.6 Dan phpPgAdmin di Centos 7

PostgreSQL adalah sistem manajemen basis data open source, sangat skalabel, dan sesuai dengan SQL. PostgreSQL dikembangkan University of California di Berkeley Computer Science Department. Artikel ini akan membantu Anda untuk menginstal PostgreSQL di CentOS,

Menambahkan Repositori Yum PostgreSQL

# yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Setelah Proses penamabahan repositori selesai lanjutkan menginstalasi postgresql 9.6

# yum install postgresql96 postgresql96-server postgresql96-contrib postgresql96-libs -y

Initializing PGDATA

Setelah menginstal server PostgreSQL, diperlukan inisialisasi sebelum digunakan pertama kali. Untuk menginisialisasi database gunakan perintah di bawah ini

# /usr/pgsql-9.6/bin/postgresql96-setup initdb

Jalankan dan aktifkan postgresql96

# systemctl start postgresql-9.6
# systemctl enable postgresql-9.6

Aturan password untuk super user postgres

# su - postgres
-bash-4.2$ psql

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

Lalu ubah konfigurasi pg_hba.conf dengan perintah dibawah ini :

# nano /var/lib/pgsql/9.6/data/pg_hba.conf

Ubah tampilan dibawah ini

# 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

Menjadi :

# 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            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

Kemudian restart postgresql96

# systemctl restart postgresql-9.6.service

Atur koneksi ke dbms Postgresql :

# nano /var/lib/pgsql/9.6/data/postgresql.conf

Cari dan tambahkan listen_addresses = ‘*’ dan port = 5432 pada bagian

# - Connection Settings -

listen_addresses = '*'
port = 5432

Lakukan restart kembali service postgresql

# systemctl restart postgresql-9.6.service

Install phpPgAdmin

phpPgAdmin digunakan mengelola database berbasis web, untuk versi terbaru phpPgAdmin membutuhkan minimal PHP 7.2. Untuk penjelasan instalasi php 7.2 dan Apache kunjungi artikel Instalasi LAMP.

# yum install phpPgAdmin

Setelah proses instalasi phpPgAdmin selesai lakukan perubahan

# nano /etc/httpd/conf.d/phpPgAdmin.conf

Akan tampil seperti dibawah ini :

#
# This configuration file maps the phpPgAdmin directory into the URL space.
# By default this application is only accessible from the local host.
#

Alias /phpPgAdmin /usr/share/phpPgAdmin

<Location /phpPgAdmin>
    <IfModule mod_authz_core.c>
        # Apache 2.4
        Require local
        #Require host example.com
    </IfModule>
    <IfModule !mod_authz_core.c>
        # Apache 2.2
        Order deny,allow
        Deny from all
        Allow from 127.0.0.1
        Allow from ::1
        # Allow from .example.com
    </IfModule>
</Location>

Diubah seperti dibawah ini dengan merunbah Require local menjadi Require all granted dan Deny from all menjadi Allow from all

#
# This configuration file maps the phpPgAdmin directory into the URL space.
# By default this application is only accessible from the local host.
#

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 127.0.0.1
        Allow from ::1
        # Allow from .example.com
    </IfModule>
</Location>

Aturan konfigurasi keamanan hak ases user di phpPgAdmin

# nano /etc/phpPgAdmin/config.inc.php

Ubah bagian seperti dibawah ini :

        // If extra login security is true, then logins via phpPgAdmin with no
        // password or certain usernames (pgsql, postgres, root, administrator)
        // will be denied. Only set this false once you have read the FAQ and
        // understand how to change PostgreSQL's pg_hba.conf to enable
        // passworded local connections.
        $conf['extra_login_security'] = true;

        // Only show owned databases?
        // Note: This will simply hide other databases in the list - this does
        // not in any way prevent your users from seeing other database by
        // other means. (e.g. Run 'SELECT * FROM pg_database' in the SQL area.)
        $conf['owned_only'] = false;

menjadi seperti dibawah ini :

        // If extra login security is true, then logins via phpPgAdmin with no
        // password or certain usernames (pgsql, postgres, root, administrator)
        // will be denied. Only set this false once you have read the FAQ and
        // understand how to change PostgreSQL's pg_hba.conf to enable
        // passworded local connections.
        $conf['extra_login_security'] = false;

        // Only show owned databases?
        // Note: This will simply hide other databases in the list - this does
        // not in any way prevent your users from seeing other database by
        // other means. (e.g. Run 'SELECT * FROM pg_database' in the SQL area.)
        $conf['owned_only'] = true;

Lakukan restart service :

# systemctl restart postgresql-9.6.service
# systemctl restart httpd

Selamat Mencoba