PgAdmin and Replication Postgresql On Ubuntu 20.04

Postgresql

Postgresql merupakan salah satu perangkat lunak pengolah data yang open source. Seperti halnya dengan MariaDB, Postgresql juga merupakan pengelola data pada database. Postgresql juga sering dijadikan pilihan karena keandalannya, skalabilitas, dan fleksibilitasnya yang tinggi.

PgAdmin

PgAdmin merupakan antarmuka berbasis web yang digunakan untuk mengelola database Postgresql. PgAdmin sama seperti Phpmyadmin, dimana memiliki antarmuka yang intiutif dan mudah digunakan untuk pengelola dan pengembangan database Postgresql.

Step 1 — Instalasi

Seperti biasa sebelum penginstalan kita akan melakukan update pada kedua server ubuntu.

$sudo apt update -y

Selanjutnya kita akan melakukan penginstalan postgresql pada kedua server ubuntu. Disini saya menggunakan postgresql versi 12.

$sudo apt install postgresql-12 -y

Masuk ke sebagai sudo pada kedua server.

$sudo su

Selanjutnya kita akan melakukan instalasi untuk Pgadmin Postgresqlnya. Pada ubuntu 20.04 Pgadmin tidak bisa langsung di instal melalui perintah APT, jadi kita harus menambahkan public key untuk repository dan membuat konfigurasi repository terlebih dahiulu.

#curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add #sudo sh -c 'echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'

Setelah menambahkan publik key dan melakukan konfigurasi pada repository ubuntu, barulah kita dapat menginstall Pgadmin.

#apt install pgadmin4

Selanjutnya kita akan membuat akun login untuk pgadmin.

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

Nantinya akan diminta untuk memberikan email address dan password. Kalian dapat menentukan sendiri email dan password yang akan digunakan.

Kita akan melakukan reset password pada user admin postgresql.

#su - postgres

Masuk ke database postgres dengan akun postgres.

$psql -U postgres

Lakukan reset password dan masukkan pasword yang diinginkan.

#\password

Step 2 — Pengujian

Setelah melakukan semua konfigurasi jangan lupa untuk restart layanan postgresql.

#systemctl restart postgresql

Lakukan pengujian pada web browser kalian dan ketikkan http://<IP atau Domain server>/pgadmin4. Masuk dengan email dan password yang telah dibuat tadi.

Setelah dapat melakukan login. Kita akan menghubungkan pgadmin dengan Postgresql kita. Klik menu Add New Server.

Nanti akan menampilkan windows untuk di isi. Pada tab general kalian dapat menentukan Name untuk database yang akan di hubungkan. Disini saya hanya memberikan nama local karena database berjalan di local.

Selanjutnya pergi ke tab Connection. Disini kalian dapat mengisikan

• Host name/address: Isikan dengan alamat IP atau domain Postgresql.
• Port: Isikan port spesifik Postgresql, secara default Postgresql berjalan pada port 5432.
• Username: Isikan dengan user Postgresql.
• Password: Berikan password user Postgresql.

Jika sudah berhasil maka nantinya kalian dapat melihat nama Database yang sudah dihubungkan tadi.

Streaming Replication Synchronous Postgresql

Step 1 — Konfigurasi Server Master

Lakukan perubahan pada file konfig Postgresql dengan menggunakan perintah dibawah. Ikuti intruksi setiap baris perintah yang ada.

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

#line 59: uncomment 
listen_address = '*'  
#line 197: uncomment 
synchronous_commit = on  
#line 300: uncomment 
synchronous_standby_names = '*'  
#line 101, 103 dan 105: comment 
#ssl = on 
#ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem' 
#ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'

Penjelasan pada beberapa perintah yang di uncomment

• listen addresses: agar server dapat melisten semua alamat IP.
• synchronous_commit: Agar kedua server saling sinkron.
• synchronous_standby_names: Agar server melisten semua address.

Pada perintah diatas juga dapat dilihat kita mencomment beberapa baris perintah, by default authentikasi replikasi pada Postgresql berjalan menggunakan SSL. Tapi pada kesempatan kali ini kita tidak menggunakan SSL jadi cukup di comment perintah tersebut agar tidak berjalan.

Selanjutnya kita akan melakukan konfigurasi pada file authentikasi klien Postgresql. File authentikasi klien pada Postgresql bernama pg_hba.conf.

#vim /etc/postgresql/12/main/pg_hba.conf  
#Tambahkan pada line terakhir 
#contoh perintah 
#host    replication     nama user    ip kedua server   md5  
host     replication     rep_user     10.23.2.71/32     md5 
host     replication     rep_user     10.23.2.78/32     md5

Pada perintah ini kita memberikan akses pada Server Master dan Standby menggunakan user bernama “rep_user” untuk melakukan akses replikasi.

Masuk ke Postgresql menggunakan akun admin postgres.

#su - postgres

Kemudian buatlah user baru yang akan digunakan sebagain user replika sesuai dengan yang pada file pg_hba.conf.

$createuser --replication -P rep_user 
Enter password for new role: 
Enter it again: 
$exit

Terakhir restart layanan Postgresql yang telah dikonfigurasi, agar semua konfigurasi dapat berjalan.

#systemctl restart postgresql

Step 2 — Konfigurasi Standby

Pertama kita akan menghentikan layanan postgresql.

#systemctl stop postgresql

Hapus semua file postgresql yang ada pada direktori /var/lib/postgresql/12/main.

#rm -rf /var/lib/postgresql/12/main/*

Masuk ke akun admin Postgresql.

#su - postgres

Lakukan backup dari server Master ke server Standby dengan menggunakan perintah dibawah.

$pg_basebackup -R -h <ip atau domain server master> -U rep_user -D /var/lib/postgresql/12/main -P  

example 
$pg_basebackup -R -h 10.23.2.71 -U rep_user -D /var/lib/postgresql/12/main -P
Password:   // password user replikasi 
32540/32540 kB (100%), 1/1 tablespace 
$exit

Lakukan perubahan pada file konfig Postgresql, ikuti intruksi pada perintah dibawah.

#vim /etc/postgresql/12/main/postgresql.conf  
#line 58: uncomment |
listen_address = '*'  

#line 315: uncomment 
hot_standby = on

Penjelasan pada perintah diatas

• listen_address: untuk mendengarkan alamat IP yang ditentukan.
• hot_standby: untuk merekam transaksi yang ter update.

Selanjutnya melakukan konfigurasi pada file postgresql.auto.conf.

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

#tambahkan perintah 
application_name=(nama server Standby) primary_conninfo = 'user=rep_user password=password host=10.23.2.71 port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any application_name=server-slave'

Jalankan kembali service postgresql.

#systemctl start postgresql

Step 3 — Pengujian

1. Masuk ke akun admin pada server Master

#su - postgres

Lalu jalankan perintah dibawah untuk menampilkan informasi dari server Standby (replika) yang terhubung.

$psql -c "select usename, application_name, client_addr, state, sync_priority, sync_state from pg_stat_replication;"

Jika berhasil maka akan menampilkan hasil seperti dibawah.

Dapat kita lihat ada beberapa informasi seperti username dan alamat IP dari server Standby, disini kita juga melihat state dari server Standby sudah sinkron atau belum.

Selanjutnya kita akan melakukan uji coba untuk melihat apakah database server Master dan Standby sudah saling sinkron atau belum. Masuk ke akun admin postgresql pada server Master dan buatlah suatu database.

#su - postgres $createdb test

Kemudian masuk ke akun admin postgresql pada server Standby untuk melihat apakah database test yang dibuat pada server Master sudah muncul atau belum pada server Standby.

#su - postgres $psql -U postgres #\l

Jika berhasil harusnya muncul nama database test.

Kesimpulan

Postgresql merupakan salah satu program perangkat lunak yang digunakan untuk mengolah data pada suatu database. Postgresql sama seperti MariaDB yang dimana sistem manajemen berbasis data yang open source. Postgresql juga dapat dikelola melalui sebuah perangkat lunak berbasis GUI (Graphical User Interface). Dengan menggunakan PgAdmin seorang sistem administrator database dapat dengan mudah mengelola dan mengembangkan database Postgresql. Selain itu, Postgresql juga memiliki fitur Replication. Ketika kita ingin melakukan suatu backup dengan menggunakan server database yang lain, kita dapat melakukan replikasi. Dengan replikasi ini kita dapat dengan mudah melakukan backup data jika terjadi suatu masalah pada Database server utama.