Penjelasan dan installasi Postgersql

/ Sysadmin / By
  1. Apa itu Database?

Database adalah kumpulan data yang terorganisir secara terstruktur agar dapat dengan mudah diakses, dikelola, dan diperbarui. Ini dapat berupa koleksi informasi yang terkait atau berkaitan dengan topik tertentu.

  • Relational Database (SQL).

Relational database adalah tipe database yang terdiri dari tabel yang terhubung satu sama lain. Struktur data diorganisir dalam bentuk tabel, di mana setiap tabel berisi baris dan kolom. Contoh SQL Database: MySQL, MariaDB, PostgreSQL.

  • No Relational Database (NoSQL).

No relational database adalah jenis database yang tidak mengikuti struktur tabel tradisional seperti pada database relasional. Jenis database ini dirancang untuk menangani data semi-struktural atau tidak terstruktur. Contoh No SQL Database: MongoDB, Redis.

  1. Instalasi Postgresql di Red Hat Enterprise Linux 9.

Jalankan command dibawah ini untuk instalasi ‘postgresql-server’.

srv1
[sysadmin@srv1 ~]$ sudo dnf update -y
[sysadmin@srv1 ~]$ sudo dnf install -y postgresql-server

Setelah itu lakukan inisialisasi setup postgresql dan enable service postgresql dan open port 5432/tcp.

srv1
[sysadmin@srv1 ~]$ sudo postgresql-setup –initdb
 * Initializing database in ‘/var/lib/pgsql/data’
 * Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log
[sysadmin@srv1 ~]$ sudo systemctl enable –now postgresql
[sysadmin@srv1 ~]$ sudo firewall-cmd –add-port=5432/tcp –permanent 
success
[sysadmin@srv1 ~]$ sudo firewall-cmd –reload 
success
[sysadmin@srv1 ~]$ sudo firewall-cmd –list-ports 
5432/tcp

By default postgresql hanya listen di localhost. Edit file postgresql.conf dan ubah listen address jadi ‘*’.

srv1
[sysadmin@srv1 ~]$ sudo nano /var/lib/pgsql/data/postgresql.conf
/var/lib/pgsql/data/postgresql.conf
#——————————————————————–
# 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

Lalu izinkan remote access dari network 192.168.72.0/24. Edit file ‘pg_hba.conf’.

srv1
[sysadmin@srv1 ~]$ sudo nano /var/lib/pgsql/data/pg_hba.conf
/var/lib/pgsql/data/pg_hba.conf
# 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
host    all             all             192.168.72.0/24         md5
# 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

Lalu restart service postgresql.

srv1
[sysadmin@srv1 ~]$ sudo systemctl restart postgresql.service 

Ubah password user ‘postgres’ jadi ‘postgres’.

srv1
[sysadmin@srv1 ~]$ sudo su – postgres 
[postgres@srv1 ~]$ psql 
psql (13.14)
Type “help” for help.
postgres=# alter user postgres password ‘postgres’;
ALTER ROLE
postgres=# quit

Basic postgresql.

srv1
[sysadmin@srv1 ~]$ sudo su – postgres 
# Membuat user database
[postgres@srv1 ~]$ createuser idn
# Membuat database
[postgres@srv1 ~]$ createdb prod -O idn
# Terhubung ke database
[postgres@srv3 ~]$ psql prod 
psql (13.14)
Type “help” for help.

# Menampilkan role user
prod=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
———–+————————————————————+———–
 idn       |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

# Menampilkan list database
prod=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
———–+———-+———-+————-+————-+———————–
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 prod      | idn      | 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)

# Membuat table
prod=# create table dummy_table (no int, nama text);
CREATE TABLE

# Menampilkan table
prod=# \dt
            List of relations
 Schema |    Name     | Type  |  Owner   
——–+————-+——-+———-
 public | dummy_table | table | postgres
(1 row)

# Memasukkan data ke table
prod=# insert into dummy_table (no,nama) values (01,’RHEL 9′);
INSERT 0 1

# Menampilkan data table
prod=# select * from dummy_table;
 no |  nama  
—-+——–
  1 | RHEL 9
(1 row)
# Menghapus table
prod=# drop table dummy_table;
DROP TABLE

prod=# \dt
Did not find any relations.

prod=# \q


# Menghapus database
[postgres@srv3 ~]$ dropdb prod
[postgres@srv3 ~]$ 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
(3 rows)
  1. Instalasi pgAdmin di Red Hat Enterprise Linux 9.

pgAdmin adalah sebuah tool gratis untuk manajemen administrasi database Postgresql melalui interface web. Jalankan perintah dibawah ini untuk instalasi pgAdmin.

srv1
[sysadmin@srv1 ~]$ sudo rpm -i https://ftp.postgresql.org/pub/pgadmin/pgadmin4/yum/pgadmin4-redhat-repo-2-1.noarch.rpm
[sysadmin@srv1 ~]$ sudo dnf install -y pgadmin4-web

Lakukan konfigurasi web mode dan buat user/password untuk login web pgadmin4.

srv1
[sysadmin@srv1 ~]$ sudo /usr/pgadmin4/bin/setup-web.sh
Setting up pgAdmin 4 in web mode on a Redhat based platform…
Creating configuration database…
NOTE: Configuring authentication for SERVER mode.

Enter the email address and password to use for the initial pgAdmin user account:

Email address: root@idn-academy.id
Password: 
Retype password:
pgAdmin 4 – Application Initialisation
======================================

Creating storage and log directories…
Configuring SELinux…
The Apache web server is not running. We can enable and start the web server for you to finish pgAdmin 4 installation. Continue (y/n)? y
Created symlink /etc/systemd/system/multi-user.target.wants/httpd.service → /usr/lib/systemd/system/httpd.service.
Apache successfully enabled.
Apache successfully started.
You can now start using pgAdmin 4 in web mode at http://127.0.0.1/pgadmin4

Lalu open port 80/tcp.

srv1
[sysadmin@srv1 ~]$ sudo firewall-cmd –add-port=80/tcp –permanent 
success
[sysadmin@srv1 ~]$ sudo firewall-cmd –reload 
success
[sysadmin@srv1 ~]$ sudo firewall-cmd –list-ports 
80/tcp 5432/tcp

Uji coba akses pgAdmin4 melalui browser.

Coba hubungkan server postgresql ke pgAdmin4.



  1. Streaming Replication Postgresql di Red Hat Enterprise Linux 9.

Untuk topologi yang akan kita gunakan seperti gambar diatas. Srv1 akan bertindak sebagai master, Srv2 dan Srv3 akan bertindak sebagai slave. Pastikan masing” server sudah terinstall dan terkonfigurasi postgresql seperti pada point nomor 2.

  1. Konfigurasi Srv1 sebagai master.

Edit file ‘postgresql.conf’ di direktori ‘/var/lib/pgsql/data’. Uncomment dan sesuaikan seperti dibawah ini.

srv1
[sysadmin@srv1 ~]$ sudo nano /var/lib/pgsql/data/postgresql.conf
/var/lib/pgsql/data/postgresql.conf
listen_addresses = ‘*’         # what IP address(es) to listen on;
wal_level = replica            # minimal, replica, or logical
max_wal_senders = 10           # max number of walsender processes
wal_keep_size = 256            # in megabytes; 0 disables
hot_standby = on               # “off” disallows queries during recovery

Edit juga file ‘pg_hba.conf’ di direktori ‘/var/lib/pgsql/data’. Tambahkan konfigurasi seperti dibawah ini.

srv1
[sysadmin@srv1 ~]$ sudo nano /var/lib/pgsql/data/pg_hba.conf
/var/lib/pgsql/data/pg_hba.conf
host    all             all             192.168.72.0/24         md5
host    replication     all             192.168.72.0/24         md5

Restart service postgresql.

srv1
[sysadmin@srv1 ~]$ sudo systemctl restart postgresql.service 
  1. Konfigurasi Srv2 sebagai slave.

Stop service postgresql dan hapus file di direktori ‘/var/lib/pgsql/data/*’.

srv2
[sysadmin@srv2 ~]$ sudo systemctl stop postgresql.service
[sysadmin@srv2 ~]$ sudo -i
[root@srv2 ~]# rm -rf /var/lib/pgsql/data/*

Lalu buat salinan dari Srv1/master dan start service postgresql.

srv2
[root@srv2 ~]# su – postgres 
[postgres@srv2 ~]$ pg_basebackup -h 192.168.72.11 -U repli -D /var/lib/pgsql/data/ -P -R
Password: 
24414/24414 kB (100%), 1/1 tablespace
[postgres@srv2 ~]$ sudo systemctl start postgresql.service

Cek di Srv2

srv2
[postgres@srv2 ~]$ psql -x -c “select * from pg_stat_wal_receiver;”
-[ RECORD 1 ]———+————————————————-
pid                   | 1927
status                | streaming
receive_start_lsn     | 0/3000000
receive_start_tli     | 1
written_lsn           | 0/3000A28
flushed_lsn           | 0/3000A28
received_tli          | 1
last_msg_send_time    | 2024-07-18 16:34:36.183604+07
last_msg_receipt_time | 2024-07-18 16:34:36.182974+07
latest_end_lsn        | 0/3000A28
latest_end_time       | 2024-07-18 16:33:36.113806+07
slot_name             | 
sender_host           | 192.168.72.11
sender_port           | 5432
conninfo              | user=repli password=******** channel_binding=prefer dbname=replication host=192.168.72.11 port=5432
fallback_application_name=walreceiver sslmode=prefer sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any
  1. Konfigurasi Srv3 sebagai slave.

Stop service postgresql dan hapus file di direktori ‘/var/lib/pgsql/data/*’.

srv3
[sysadmin@srv3 ~]$ sudo systemctl stop postgresql.service
[sysadmin@srv3 ~]$ sudo -i
[root@srv3 ~]# rm -rf /var/lib/pgsql/data/*

Lalu buat salinan dari Srv1/master dan start service postgresql.

srv3
[root@srv3 ~]# su – postgres 
[postgres@srv3 ~]$ pg_basebackup -h 192.168.72.11 -U repli -D /var/lib/pgsql/data/ -P -R
Password: 
24414/24414 kB (100%), 1/1 tablespace
[postgres@srv3 ~]$ sudo systemctl start postgresql.service

Cek di Srv3

srv3
[postgres@srv3 ~]$ psql -x -c “select * from pg_stat_wal_receiver;”
-[ RECORD 1 ]———+————————————————-
pid                   | 1924
status                | streaming
receive_start_lsn     | 0/5000000
receive_start_tli     | 1
written_lsn           | 0/5000060
flushed_lsn           | 0/5000060
received_tli          | 1
last_msg_send_time    | 2024-07-18 16:37:19.898232+07
last_msg_receipt_time | 2024-07-18 16:37:19.900943+07
latest_end_lsn        | 0/5000060
latest_end_time       | 2024-07-18 16:37:19.898232+07
slot_name             | 
sender_host           | 192.168.72.11
sender_port           | 5432
conninfo              | user=repli password=******** channel_binding=prefer dbname=replication host=192.168.72.11 port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any

Cek di Srv1

srv1
[postgres@srv1 ~]$ psql -x -c “SELECT * FROM pg_stat_replication;”
-[ RECORD 1 ]—-+——————————
pid              | 1998
usesysid         | 16384
usename          | repli
application_name | walreceiver
client_addr      | 192.168.72.12
client_hostname  | 
client_port      | 35558
backend_start    | 2024-07-18 16:31:42.480223+07
backend_xmin     | 
state            | streaming
sent_lsn         | 0/5000060
write_lsn        | 0/5000060
flush_lsn        | 0/5000060
replay_lsn       | 0/5000060
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 0
sync_state       | async
reply_time       | 2024-07-18 16:37:34.60984+07
-[ RECORD 2 ]—-+——————————
pid              | 2073
usesysid         | 16384
usename          | repli
application_name | walreceiver
client_addr      | 192.168.72.13
client_hostname  | 
client_port      | 43426
backend_start    | 2024-07-18 16:37:19.884169+07
backend_xmin     | 
state            | streaming
sent_lsn         | 0/5000060
write_lsn        | 0/5000060
flush_lsn        | 0/5000060
replay_lsn       | 0/5000060
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 0
sync_state       | async
reply_time       | 2024-07-18 16:37:40.122842+07

[postgres@srv1 ~]$ psql -c “select usename, application_name, client_addr, state, sync_priority, sync_state from pg_stat_replication;”
 usename | application_name |  client_addr  |   state   | sync_priority | sync_state 
———+——————+—————+———–+—————+————
 repli   | walreceiver      | 192.168.72.12 | streaming |             0 | async
 repli   | walreceiver      | 192.168.72.13 | streaming |             0 | async
(2 rows)

Uji coba buat database baru di srv1 dengan nama ‘test_dummy’ dan juga buat table.

srv1
[sysadmin@srv1 ~]$ sudo su – postgres 
[postgres@srv1 ~]$ createdb test_dummy
[postgres@srv1 ~]$ 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
 test_dummy | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)

[postgres@srv1 ~]$ psql test_dummy 

test_dummy=# create table anak_academy (no int, name text);
CREATE TABLE

test_dummy=# \dt
            List of relations
 Schema |     Name     | Type  |  Owner   
——–+————–+——-+———-
 public | anak_academy | table | postgres
(1 row)

test_dummy=# insert into anak_academy (no, name) values (1, ‘asrul’);
INSERT 0 1

test_dummy=# \dt
            List of relations
 Schema |     Name     | Type  |  Owner   
——–+————–+——-+———-
 public | anak_academy | table | postgres
(1 row)

test_dummy=# select * from anak_academy;
 no | name  
—-+——-
  1 | asrul
(1 row)

Lalu cek di Srv2 atau Srv3 pastikan database tersebut muncul namun ketika di write dari Srv2 atau Srv3 tidak bisa karena hanya read only.

srv2
[sysadmin@srv2 ~]$ sudo su – postgres 
[postgres@srv2 ~]$ 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
 test_dummy | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)

[postgres@srv2 ~]$ psql test_dummy 
psql (13.14)
Type “help” for help.

test_dummy=# \dt
            List of relations
 Schema |     Name     | Type  |  Owner   
——–+————–+——-+———-
 public | anak_academy | table | postgres
(1 row)

test_dummy=# select * from anak_academy;
 no | name  
—-+——-
  1 | asrul
(1 row)

test_dummy=# insert into anak_academy (no, name) values (2, ‘alif’);
ERROR:  cannot execute INSERT in a read-only transaction

Uji coba promote salah satu slave (Srv2) untuk menjadi master, ketika master (Srv1) down.

Shutdown Srv1.

srv1
[sysadmin@srv1 ~]$ sudo poweroff

Promote Srv2 sebagai master dan coba insert data pada table ‘anak_academy’ di database ‘test_dummy’.

srv2
[postgres@srv2 ~]$ pg_ctl promote
waiting for server to promote…. done
server promoted
[postgres@srv2 ~]$ psql test_dummy

test_dummy=# \dt
            List of relations
 Schema |     Name     | Type  |  Owner   
——–+————–+——-+———-
 public | anak_academy | table | postgres
(1 row)

test_dummy=# select * from anak_academy;
 no | name  
—-+——-
  1 | asrul
(1 row)

test_dummy=# insert into anak_academy (no, name) values (2, ‘alif’);
INSERT 0 1
test_dummy=# select * from anak_academy;
 no | name  
—-+——-
  1 | asrul
  2 | alif
(2 rows)

test_dummy=# quit

Untuk Srv3 harus authorized ulang supaya bisa asinkron dengan master baru (Srv2). 

srv3
[sysadmin@srv3 ~]$ sudo systemctl stop postgresql
[sysadmin@srv3 ~]$ sudo su – postgres 
[postgres@srv3 ~]$ cd data/
[postgres@srv3 data]$ rm -rf *
[postgres@srv3 data]$ pg_basebackup -h 192.168.72.12 -U repli -D /var/lib/pgsql/data/ -P -R
Password: 
32367/32367 kB (100%), 1/1 tablespace
[postgres@srv3 ~]$ su – sysadmin
[sysadmin@srv3 ~]$ sudo systemctl start postgresql

Sekarang cek di database ‘test_dummy’ apakah pada table ‘anak_academy’ ada data no 1 dan 2.

srv3
[sysadmin@srv3 ~]$ sudo su – postgres
[postgres@srv3 ~]$ 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
 test_dummy | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)

[postgres@srv3 ~]$ psql test_dummy 

test_dummy-# \dt;
            List of relations
 Schema |     Name     | Type  |  Owner
——–+————–+——-+———-
 public | anak_academy | table | postgres
(1 row)

test_dummy=# select * from anak_academy;
 no | name  
—-+——-
  1 | asrul
  2 | alif
(2 rows)

Tertarik mengikuti training di ID-Networkers? Kami menyediakan berbagai pilihan training yang bisa kmau ikuti klik disini untuk info lengkapnya.

Penulis : Achmad Alif Nasrulloh