Instalasi Postgres 12 di Centos 8

1. Install and start PostgreSQL.

 [root@www ~]# dnf module -y install postgresql:12/server  
 [root@www ~]# postgresql-setup --initdb  
  * Initializing database in '/var/lib/pgsql/data'  
  * Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log  
 [root@www ~]# systemctl enable --now postgresql  

2. Secara default, dimungkinkan untuk terhubung ke Server PostgreSQL hanya dari Localhost dengan otentikasi [peer].

 # listen only localhost by default  
 [root@www ~]# grep listen_addresses /var/lib/pgsql/data/postgresql.conf  
 #listen_addresses = 'localhost' # what IP address(es) to listen on;  
 # authentication methods by default  
 [root@www ~]# grep -v -E "^#|^$" /var/lib/pgsql/data/pg_hba.conf  
 local  all       all                   peer  
 host  all       all       127.0.0.1/32      ident  
 host  all       all       ::1/128         ident  
 local  replication   all                   peer  
 host  replication   all       127.0.0.1/32      ident  
 host  replication   all       ::1/128         ident  

3. Pada otentikasi [peer], diperlukan pengguna OS dan pengguna PostgreSQL yang namanya sama untuk terhubung ke Server PostgreSQL.

 # add an OS user  
 [root@www ~]# useradd cent  
 # add an PostgreSQL user and his Database with PostgreSQL admin user  
 [root@www ~]# su - postgres  
 [postgres@www ~]$ createuser cent  
 [postgres@www ~]$ createdb testdb -O cent  
 # show users and databases  
 [postgres@www ~]$ psql -c "select usename from pg_user;"  
  usename  
 ----------  
  postgres  
  cent  
 (2 rows)  
 [postgres@www ~]$ psql -l  
                  List of databases  
   Name  | Owner  | Encoding |  Collate  |  Ctype  |  Access privileges  
 -----------+----------+----------+-------------+-------------+-----------------------  
  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  
  testdb  | cent   | UTF8   | en_US.UTF-8 | en_US.UTF-8 |  
 (4 rows)  

PostgreSQL 12 : Remote Connection

1. Ada banyak metode otentikasi di PostgreSQL. Pada contoh ini, Konfigurasikan metode sandi MD5.

 [root@www ~]# vi /var/lib/pgsql/data/postgresql.conf  
 # line 59: uncomment and change  
 listen_addresses = '*'  
 [root@www ~]# vi /var/lib/pgsql/data/pg_hba.conf  
 # add to the end  
 # 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  
 # Allow replication connections from localhost, by a user with the  
 # replication privilege.  
 local  replication   all                   peer  
 host  replication   all       127.0.0.1/32      ident  
 host  replication   all       ::1/128         ident  
 # specify network range you allow to connect on [ADDRESS] section  
 # if allow all, specify [0.0.0.0/0]  
 host  all       all       10.0.0.0/24       md5  
 [root@www ~]# systemctl restart postgresql  

2. Jika Firewalld sedang berjalan, izinkan layanan PostgreSQL.

 [root@www ~]# firewall-cmd --add-service=postgresql --permanent  
 success  
 [root@www ~]# firewall-cmd --reload  
 success  

3. Untuk terhubung ke Database PostgreSQL dengan kata sandi MD5, setel kata sandi untuk setiap pengguna PostgreSQL.

 # connect to own database  
 [cent@www ~]$ psql -d testdb  
 psql (12.1)  
 Type "help" for help.  
 # set or change own password  
 testdb=> \password  
 Enter new password:  
 Enter it again:  
 testdb=> \q  
 # also possible to set or change password for any users with PostgreSQL admin user  
 [postgres@www ~]$ psql -c "alter user cent with password 'password';"  
 ALTER ROLE  

4. Verifikasi pengaturan untuk terhubung ke Database PostgreSQL dengan kata sandi MD5 dari host jarak jauh.

 [root@node01 ~]# psql -h www.srv.world -d testdb -U cent  
 Password for user cent:  # password  
 psql (12.1)  
 Type "help" for help.  
 testdb=> # connected  

PROBLEM:

Sewaktu ingin membuat extention CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

muncul error seperti dibawah ini:

ERROR: could not open extension control file "/usr/share/pgsql/extension/uuid-ossp.control": No such file or directory

SOLUTION:

Anda mungkin juga ingin menginstal paket contrib yang menyediakan beberapa fitur tambahan untuk sistem database PostgreSQL.

 $ sudo dnf install postgresql-contrib  

PROBLEM:

Tidak dapat membuat extention CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; karena bukan superuser

SOLUTION:

 [root@www ~]# su - postgres  
 [postgres@www ~]$ ALTER USER namauser WITH SUPERUSER;