PostgreSQL, biasa di singkat dengan kata postgres merupakan sistem manajemen basis data relasional yang open-source (databases) yang dapat menekankan ekstensibilitas dan kepatuhan SQL. Awalnya bernama POSTGRES, merujuk pada asalnya sebagai penerus basis data Ingres yang dikembangkan di University of California. Selengkapnya dapat merujuk pada link berikut: Wiki PostgreSQL
Di CentOS 8 postgresql secara default sudah ada di repository default dan ada beberapa versi posgresql yang dapat Anda gunakna mulai dari versi 9.6, 10 dan 12, sebagai berikut
1
2
3
4
5
6
7
[root@pg1 ~]#
[root@pg1 ~]# dnf module list postgresql
Last metadata expiration check: 2:37:44 ago on Fri 04 Sep 2020 04:00:54 PM UTC.
CentOS-8 - AppStream
Name Stream Profiles Summary postgresql 9.6 client, server [d] PostgreSQL server and client module postgresql 10 [d] client, server [d] PostgreSQL server and client module postgresql 12 [e] client, server [d] PostgreSQL server and client module
Hint: [d]efault, [e]nabled, [x]disabled, [i]nstalled
[root@pg1 ~]#
Secara default postgres di CentOS 8 yaitu versi 10, jika Anda ingin menggunakan versi 9.6 atau 12 Anda hanya perlu enable saja.
Disini kami akan menggunakan versi 12 oleh karena itu Anda hanya perlu menjalankan perintah berikut untuk enable nya:
1
2
3
4
5
6
7
8
9
10
11
12
13
[root@pg1 ~]#
[root@pg1 ~]# dnf module enable postgresql:12
Last metadata expiration check: 0:25:48 ago on Fri 04 Sep 2020 06:24:50 AM UTC.
Dependencies resolved.
============================================================================================================================================================================================== Package Architecture Version Repository Size
==============================================================================================================================================================================================Enabling module streams:
postgresql 12
Transaction Summary
==============================================================================================================================================================================================
Is this ok [y/N]: y
Complete!
[root@pg1 ~]#
Untuk instalasi postgre SQL server nya Anda jalankan perintah berikut
1
2
[root@pg1 ~]#
[root@pg1 ~]# dnf install postgresql-server -y
Inisiasi postgresql
1
2
3
4
5
[root@pg1 ~]#
[root@pg1 ~]# postgresql-setup --initdb
* Initializing database in '/var/lib/pgsql/data'
* Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log
[root@pg1 ~]#
Start dan enable postgreSQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
[root@pg1 ~]#
[root@pg1 ~]# systemctl start postgresql
[root@pg1 ~]# systemctl enable postgresql
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /usr/lib/systemd/system/postgresql.service.
[root@pg1 ~]# systemctl status postgresql
● postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
Active: active (running) since Fri 2020-09-04 06:52:04 UTC; 18s ago
Main PID: 5672 (postmaster)
Tasks: 8 (limit: 11328)
Memory: 17.1M
CGroup: /system.slice/postgresql.service
├─5672 /usr/bin/postmaster -D /var/lib/pgsql/data
├─5674 postgres: logger
├─5676 postgres: checkpointer
├─5677 postgres: background writer
├─5678 postgres: walwriter
├─5679 postgres: autovacuum launcher
├─5680 postgres: stats collector
└─5681 postgres: logical replication launcher
Sep 04 06:52:04 pg1.nurhamim.my.id systemd[1]: Starting PostgreSQL database server...
Sep 04 06:52:04 pg1.nurhamim.my.id postmaster[5672]: 2020-09-04 06:52:04.503 UTC [5672] LOG: starting PostgreSQL 12.1 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 8.3.1 20190507 (Red >Sep 04 06:52:04 pg1.nurhamim.my.id postmaster[5672]: 2020-09-04 06:52:04.503 UTC [5672] LOG: listening on IPv6 address "::1", port 5432
Sep 04 06:52:04 pg1.nurhamim.my.id postmaster[5672]: 2020-09-04 06:52:04.504 UTC [5672] LOG: listening on IPv4 address "127.0.0.1", port 5432
Sep 04 06:52:04 pg1.nurhamim.my.id postmaster[5672]: 2020-09-04 06:52:04.518 UTC [5672] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
Sep 04 06:52:04 pg1.nurhamim.my.id postmaster[5672]: 2020-09-04 06:52:04.650 UTC [5672] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
Sep 04 06:52:04 pg1.nurhamim.my.id postmaster[5672]: 2020-09-04 06:52:04.669 UTC [5672] LOG: redirecting log output to logging collector process
Sep 04 06:52:04 pg1.nurhamim.my.id postmaster[5672]: 2020-09-04 06:52:04.669 UTC [5672] HINT: Future log output will appear in directory "log".
Sep 04 06:52:04 pg1.nurhamim.my.id systemd[1]: Started PostgreSQL database server.
[root@pg1 ~]#
Cek versi posgreSQL
1
2
3
[root@pg1 ~]# postgres --version
postgres (PostgreSQL) 12.1
[root@pg1 ~]#
Selanjutnya mengamankan akun postgres dengan cara memberikan password
1
2
3
4
5
6
7
[root@pg1 ~]#
[root@pg1 ~]# passwd postgres
Changing password for user postgres.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.
[root@pg1 ~]#
Login ke postgres dan membuat user role postgres
1
2
3
4
5
6
7
8
[root@pg1 ~]# su - postgres
Last login: Fri Sep 4 18:45:05 UTC 2020 on pts/0
[postgres@pg1 ~]$
[postgres@pg1 ~]$ psql -c "ALTER USER postgres WITH PASSWORD 'secret2020';"
ALTER ROLE
[postgres@pg1 ~]$ exit
logout
[root@pg1 ~]#
Selanjutnya konfigurasi postgres untuk dapat melakukan authentikasi client seperti pgAdmin.
Metode otentikasi yang didukung mencakup otentikasi berbasis kata sandi yang menggunakan salah satu metode enkripsi berikut: md5, crypt, atau password.
Disini kami akan menggunakan md5.
1
2
[root@pg1 ~]#
[root@pg1 ~]# vim /var/lib/pgsql/data/pg_hba.conf
Ubah menjadi md5
Selanjutnya restart postgresql
1
2
[root@pg1 ~]# systemctl restart postgresql
[root@pg1 ~]#
Selamat saat ini postgresql Anda sudah berhasil terinstall.
Selamat mencoba 😁