Setup PostgreSQL-pgAdmin4 dan Replikasi

/ Bazigan Tsamara Sukamto, Sysadmin / By

PostgreSQL merupakan program aplikasi basis data dengan tipe relasional. Database ini dikembangkan secara open source sehingga gratis untuk digunakan siapa saja. PostgreSQL dapat berjalan di berbagai sistem operasi, seperti Linux, Windows, dan MacOS. Program ini juga dapat diintegrasikan dengan platform Pgadmin4. Dengan Pgadmin4, kita dapat mengelola PostgreSQL dengan mudah melalui sebuah panel. Panel tersebut dapat diinstal juga di berbagai sistem operasi.

Setelah mengetahui tentang PostgreSQL, kita akan belajar bagaimana cara menggunakannya. Dari melakukan instalasi, konfigurasi, hingga menggunakan Pgadmin4 dan replikasi.

Instalasi dan Konfigurasi PostgreSQL

Pertama-tama, lakukan instalasi PostgreSQL terlebih dahulu dengan perintah:

apt install postgresql-12 -y

Setelah terinstal, periksa status layanannya:

Secara default PostgreSQL hanya bisa diakses dari lokal server itu sendiri:

Untuk menggunakan PostgreSQL, diperlukan user sistem dan user PostgreSQL dengan nama yang sama untuk masuk ke dalam program. Maka dari itu, buat user sistem:

adduser nama_user

Setelah itu, login menggunakan user default postgresql dan buat user postgresql yang baru dengan nama yang sama dengan user sistem yang telah dibuat:

#untuk login user default postgreSQL
su - postgres

#buat user baru
createuser nama_user_pg

Kemudian buat database yang dapat digunakan user baru tersebut:

createdb nama_db -O nama_user_pg

Periksa daftar user untuk memastikan apakah sudah berhasil dibuat:

psql -c "select username form pg_user;"

Lihat juga daftar database untuk memastikan database baru berhasil dibuat:

psql -l

Setelah itu, coba keluar dari user default postgresql, kemudian gunakan user baru yang telah dibuat untuk masuk ke dalam program PostgreSQL:

#untuk masuk ke database
psql nama_db

Contoh di atas menggunakan user dengan nama pgadmin dan menggunakan database dengan nama coba. Terdapat beberapa perintah dasar yang dapat digunakan, diantaranya:

  • Untuk menghapus database:dropdb nama_db
  • Untuk melihat daftar user dan melihat daftar database:#masuk ke database terlebih dahulu: psql nama_db #melihat daftar user: \du #melihat daftar database: \l
  • Coba buat tabel:create table nama_tabel(nama_kolom tipe_data, ..);
  • Memasukkan data ke dalam tabel:insert into nama_tabel(nama_kolom1, nama_kolom2) values (isi_kolom1, isi_kolom2);
  • Melihat data yang telah dimasukkan:select * form nama_tabel;
  • Untuk menghapus tabel:drop table nama_tabel;

Instalasi pgAadmin4 – Web UI PostgreSQL

pgAdmin4 merupakan aplikasi yang dapat berguna untuk mempermudah penggunaan PostgreSQL dengan web admin atau aplikasi desktop. Pada langkah ini, kita akan melakukan instalasi pgAdmin4 tipe web admin yang diakses melalui browser.

Langkah 1. Instalasi pgAdmin4

Pertama-tama, pasang repository pgAdmin4 dan update repository:

curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg
sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'

Setelah itu, instal pgAdmin4 dengan perintah:

apt install pgadmin4-web

Langkah 2. Konfigurasi pgAdmin4

Di dalam paket instalasi pgAdmin terdapat file setup web yang bisa dijalankan untuk melakukan konfigurasi web admin:

/usr/pgadmin4/bin/setup-web.sh

Akan ada beberapa hal yang diminta, yaitu email address dan password yang nantinya digunakan untuk login. Kemudian, ada pertanyaan untuk melakukan konfigurasi web server secara otomatis, maka masukkan y untuk melakukan konfigurasi:

Kemudian masukkan y untuk memuat ulang pgAdmin setelah instalasi:

Setelah selesai, akses IP server kemudian ditambahkan path /pgadmin4, maka akan tampil login page pgAdmin. Kemudian login menggunakan email dan password yang diatur ketika menjalankan setup web admin:

Setelah berhasil masuk ke dalam pgAdmin, maka akan masuk ke dalam dashboard pgAdmin:

Saat pertama kali masuk ke dalam pgAdmin, kita belum bisa menggunakannya karena belum terhubung dengan database mana pun. Untuk itu, hubungkan terlebih dahulu pgAdmin dengan database PostgreSQL.

Langkah 3. Menghubungkan PostgreSQL dengan pgAdmin4

Pertama-tama, masuk ke dalam file konfigurasi PostgreSQL:

vim /etc/postgresql/12/main/postgresql.conf

Kemudian konfigurasi seperti di bawah ini:

# * agar dapat diakses dari luar localhost
listen_addresses = '*'

Kemudian buka file konfigurasi autentikasi host yang dapat mengakses PostgreSQL:

# Menambahkan network yang dapat mangakses postgresql
host    all     all    10.23.0.1/22      md5

Setelah itu, muat ulang service PostgreSQL untuk menerapkan konfigurasi:

Kemudian, atur password user sistem postgres menggunakan user root sistem:

passwd postgres

Selanjutnya, atur juga password user PostgreSQL agar bisa digunakan untuk remote connection. Pada contoh ini menggunakan default user PostgreSQL:

#masuk ke dalam user yang hendak digunakan
su - nama_user

#masuk ke dalam database
psql -d nama_db

#atur password
\password

#perbarui password
alter user nama_user with password 'password_user';

#keluar dari program
\q

Buka kembali web admin pgAdmin4 untuk menambahkan koneksi database:

Pada tab General isi field Name dengan nama sesuai keinginan dan biarkan lainnya default:

Kemudian masuk ke tab Connection:

Isikan field dengan data yang digunakan untuk membangun koneksi, diantaranya:

  • Hostname : localhost atau IP server PostgreSQL.
  • Port : 5432 (default)
  • Maintenance database: nama database
  • Username: nama user
  • Password: password user

Kemudian klik save, jika muncul pada Object Explorer maka sudah berhasil terkoneksi:

Streaming Replication

PostgreSQL juga mendukung replikasi database dari server utama ke server replika. Dengan replikasi, kita dapat menjaga data yang dimiliki jika terjadi masalah pada server utama. Untuk menggunakannya, perlu dilakukan beberapa konfigurasi.

Sebelum melakukan konfigurasi, siapkan 2 server yang sudah terinstal PostgreSQL. Pada contoh ini akan menggunakan server sebagai berikut:

  • Server Master: 10.23.2.81
  • Server Replikasi: 10.23.2.92

Langkah 1. Konfigurasi Server Master

Akses server master, kemudian buka file konfigurasi PostgreSQL:

vim /etc/postgresql/12/main/postgresql.conf

Hilangkan tanda pagar pada listen_addresses dan ubah value-nya menjadi bintang (*) agar bisa terhubung dengan server lain:

Hilangkan tanda pagar wal_level dengan value replica. Hilangkan tanda pagar juga pada synchronous_commit dengan valuenya on:

Kemudian, hilangkan tanda pagar max_wal_senders dengan value 10. Hilangkan tanda pagar juga pada wal_keep_senders dengan valuenya 20:

Yang pada synchronous_standby_names dengan value bintang (*).

Setelah itu, buka file konfigurasi autentikasi host:

vim /etc/postgresql/12/main/pg_hba.conf

Tambahkan konfigurasi seperti di bawah ini:

# Menambahkan izin network yang dapat mangakses postgresql
host    replication     rep_user    10.23.0.1/22      md5

Kemudian buat user dengan nama sesuai dengan konfigurasi yang dilakukan pada file autentikasi host:

su - postgres

createuser --replication -P rep_user
#kemudian masukkan passwordnya

Setelah selesai seluruhnya, muat ulang service postgresql untuk menerapkan konfigurasi:

systemctl restart postgresql
systemctl status postgresql

Langkah 2. Konfigurasi Server Replication

Akses server replikasi atau server yang kedua, hentikan service PostgreSQL terlebih dahulu untuk menghapus file-file program PostgreSQL:

#untuk menghentikan service postgresql
systemctl stop postgresql

#untuk menghapus file-file program
rm -rf /var/lib/postgresql/12/main/*

#untuk melihat isi direktori
ls /var/lib/postgresql/12/main/

Kemudian log in menggunakan user postgres untuk melakukan replikasi file-file program PostgreSQL server master ke dalam server replikasi:

pg_basebackup -R -h IP_server_master -U rep_user -D /var/lib/postgresql/12/main -P

Setelah itu, buka file konfigurasi PostgreSQL:

vim /etc/postgresql/12/main/postgresql.conf

Hilangkan tanda pagar pada listen_addresses dan ubah value-nya menjadi bintang (*) agar bisa terhubung dengan server lain:

Hilangkan juga tanda pagar pada hot_standby dengan value on:

Setelah itu, buka file auto konfigurasi yang telah diambil dari server master:

vim /var/lib/postgresql/12/main/postgresql.auto.conf

Tambahkan application_name=repl pada akhir value-nya:

primary_conninfo = 'user=rep_user password=123 host=10.23.2.81 port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any application_name=repl'

Setelah itu, mulai kembali service PostgreSQL dan lihat status service-nya:

systemctl start postgresql
systemctl status postgresql

Sampai tahap ini konfigurasi replikasi PostgreSQL telah selesai. Selanjutnya lakukan pengujian untuk memeriksa apakah fungsi replikasi sudah berjalan.

Langkah 3. Pengujian

Untuk melakukan pengujian, coba buat database baru dari server master:

Kemudian periksa daftar database melalui server replikasi:

Maka akan ada database yang telah dibuat di server master. Jika replikasi berfungsi, maka konfigurasi sudah berhasil. Sebagai catatan, server replikasi hanya bisa digunakan untuk melihat data saja. Karena server replikasi memiliki akses read only yang berarti hanya akses untuk membaca data. Sebagai contoh, ketika memasukkan perintah untuk membuat database maka akan muncul error:

Kesimpulan

PostgreSQL merupakan sistem manajemen database yang dapat diintegrasikan dengan web admin sebagai alat untuk mempermudah kontrol. Dengan PostgreSQL juga, kita dapat membuat mekanisme replikasi database dari server master ke server lain. Dengan replikasi tersebut dapat mencegah terjadinya kehilangan data jika mungkin saja server utama mengalami masalah.

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

Penulis : Bazigan Tsamara Sukamto