VoIP: (05.02.02) Hướng dẫn cài đặt PostgreSQL 9.6 trên CentOS 7 (64bit) trước khi cài đặt FusionPBX cho FreeSwitch v1.6

Mục tiêu thực hành:

- Add PostgreSQL 9.6 Repository.

- Install PostgreSQL 9.6.

- Initialize PostgreSQL 9.6.

- Start/Enable PostgreSQL.

- Kiểm tra PostgreSQL Database. 

- Create Databases & User.

- Hiệu chỉnh PostgreSQL Port cho phiên kết nối Remote Access.

- Cài đặt PostgreSQL Client.

- Kiểm tra danh sách Database hiện có.

Các bước triển khai:

Add PostgreSQL 9.6 Repository.

yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm -y

Install PostgreSQL 9.6.

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

Initialize PostgreSQL 9.6.

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

Start/Enable PostgreSQL.

systemctl enable postgresql-9.6.service

systemctl start postgresql-9.6.service

systemctl restart postgresql-9.6.service

systemctl status postgresql-9.6.service

- Cấu hình PostgreSQL không cần xác thực đối với localhost.

nano +82 /var/lib/pgsql/9.6/data/pg_hba.conf

host  all all 0.0.0.0/0    trust

host  all all ::1/128      trust

- Restart PostgreSQL 9.6.

systemctl restart postgresql-9.6
systemctl status postgresql-9.6

Kiểm tra PostgreSQL Database. 

[root@localhost ~]# cd /tmp

[root@localhost tmp]# sudo -u postgres psql

psql (9.6.21)

Type "help" for help.

postgres=# postgres=#\q

[root@localhost tmp]# 

Create Databases & User.

[root@localhost tmp]# cd /tmp

[root@localhost tmp]# sudo -u postgres createuser fusionpbx

[root@localhost tmp]# sudo -u postgres psql

psql (9.6.21)

Type "help" for help.

postgres=# alter user fusionpbx with encrypted password 'password';

ALTER ROLE

postgres=# postgres=#\q

[root@localhost tmp]# 

sudo -u postgres psql -c "CREATE DATABASE fusionpbx;"

sudo -u postgres psql -c "CREATE DATABASE freeswitch;"

sudo -u postgres psql -c "CREATE ROLE fusionpbx WITH SUPERUSER LOGIN PASSWORD 'admin@2021';"
sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE fusionpbx to fusionpbx;"

sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE freeswitch to fusionpbx;"

or

sudo -u postgres createuser fusionpbx

sudo -u postgres createdb fusionpbx

[root@localhost tmp]# sudo -u postgres psql

psql (9.6.21)

Type "help" for help.

postgres=# alter user fusionpbx with encrypted password 'admin@2021';

ALTER ROLE

postgres=# grant all privileges on database fusionpbx to fusionpbx;

GRANT

postgres=# postgres=#\q

[root@localhost tmp]# adduser fusionpbx

You have mail in /var/spool/mail/root

[root@localhost tmp]# sudo passwd fusionpbx

Changing password for user fusionpbx.

New password: admin@2021

Retype new password: admin@2021

passwd: all authentication tokens updated successfully.

[root@localhost tmp]# usermod -aG postgres fusionpbx

[root@localhost tmp]# cat /etc/group | grep postgres

postgres:x:26:fusionpbx

[root@localhost tmp]#

 

[root@localhost ~]# sudo passwd postgres

Changing password for user postgres.

New password: 

Retype new password: 

passwd: all authentication tokens updated successfully.

[root@localhost ~]# 

Hiệu chỉnh PostgreSQL Port cho phiên kết nối Remote Access.

- The results above (including "127.0.0.1:5432") shows that PostgreSQL is listening only for connects originating from the local computer, so we will have to edit the "postgresql.conf" configuration file. A result including "0.0.0.0:5432" indicates that PostgreSQL is already listening for remote connections.

[root@localhost tmp]# netstat -nlp | grep 5432

tcp        0      0 127.0.0.1:5432          0.0.0.0:*             LISTEN      984/postmaster      

tcp6       0      0 ::1:5432                :::*                    LISTEN      984/postmaster      

unix  2      [ ACC ]     STREAM     LISTENING     19190    984/postmaster       /tmp/.s.PGSQL.5432

unix  2      [ ACC ]     STREAM     LISTENING     19187    984/postmaster       /var/run/postgresql/.s.PGSQL.5432

[root@localhost tmp]#

- Edit postgresql.conf

[root@localhost data]# nano /var/lib/pgsql/9.6/data/postgresql.conf

- Restart PostgreSQL.

systemctl restart postgresql-9.6.service

[root@localhost data]# netstat -nlp | grep 5432

tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      21986/postmaster    

unix  2      [ ACC ]     STREAM     LISTENING     52886    21986/postmaster     /tmp/.s.PGSQL.5432

unix  2      [ ACC ]     STREAM     LISTENING     52884    21986/postmaster     /var/run/postgresql/.s.PGSQL.5432

[root@localhost data]# 

Cài đặt PostgreSQL Client.

- Kết nối tới PostgreSQL Database.

[root@localhost tmp]# su - postgres

Last login: Wed May  5 17:12:18 +07 2021 on pts/2

-bash-4.2$ psql

psql (9.6.21)

Type "help" for help.

postgres=# \q

-bash-4.2$ exit

logout

[root@localhost tmp]# 

- Kiểm tra kết nối tới PostgreSQL Database bằng User được khởi tạo trước đó.

[root@localhost tmp]# su - postgres

Last login: Wed May  5 17:12:18 +07 2021 on pts/2

-bash-4.2$ psql postgres -U postgres -W admin@2021

psql: warning: extra command-line argument "admin@2021" ignored

Password for user postgres: admin@2021

psql (9.6.21)

Type "help" for help.

postgres=# \q

-bash-4.2$ exit

logout

[root@localhost tmp]# 

[root@localhost tmp]# su - postgres

Last login: Wed May  5 17:12:18 +07 2021 on pts/2

-bash-4.2$ psql fusionpbx -U fusionpbx -W admin@2021

psql: warning: extra command-line argument "admin@2021" ignored

Password for user postgres: admin@2021

psql (9.6.21)

Type "help" for help.

postgres=# \q

-bash-4.2$ exit

logout

[root@localhost tmp]# 

[root@localhost ~]# sudo -u fusionpbx psql

could not change directory to "/root": Permission denied

psql (9.6.21)

Type "help" for help.

fusionpbx=> psql fusionpbx

fusionpbx-> psql fusionpbx -U fusionpbx

fusionpbx-> 

Kiểm tra danh sách Database hiện có.

- Để biết bạn đang dùng database nào, bạn gõ lệnh \c. Nếu muốn biết thêm thông tin về socket, port, bạn dùng lệnh \conninfo.

[root@localhost ~]# sudo -u postgres psql

could not change directory to "/root": Permission denied

psql (9.6.21)

Type "help" for help.

postgres=# \list

                                   List of databases

    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges    

------------+----------+----------+-------------+-------------+------------------------

 freeswitch | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres          +

            |          |          |             |             | postgres=CTc/postgres +

            |          |          |             |             | fusionpbx=CTc/postgres

 fusionpbx  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres          +

            |          |          |             |             | postgres=CTc/postgres +

            |          |          |             |             | fusionpbx=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

(5 rows)

postgres=# 

postgres=# \c

You are now connected to database "postgres" as user "postgres".

postgres=#

 

postgres=# \conninfo

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

postgres=#

Mọi thắc mắc, các bạn vui lòng liên hệ với mình thông qua kênh Zalo (Jade Bùi) 076.877.2021.