Building Postgres-BDR Cluster in Ubuntu 18.04

Untuk membuat postgres-BDR cluster yang merupakan clustering master-to-master, versi postgres yang didukung hanya versi 9.4 dan pada ubuntu 18.04, harus melakukan building postgresnya secara manual. siapkan 2 node untuk database

  • Server DB 1 : 10.200.200.100
  • Server DB 2 : 10.200.200.110

ganti semua repository pada semua server :

sudo nano /etc/apt/sources.list

# See http://help.ubuntu.com/community/UpgradeNotes for how to upgrade to
# newer versions of the distribution.
deb http://id.archive.ubuntu.com/ubuntu/ bionic main restricted
# deb-src http://id.archive.ubuntu.com/ubuntu/ bionic main restricted

## Major bug fix updates produced after the final release of the
## distribution.
deb http://id.archive.ubuntu.com/ubuntu/ bionic-updates main restricted
# deb-src http://id.archive.ubuntu.com/ubuntu/ bionic-updates main restricted

## N.B. software from this repository is ENTIRELY UNSUPPORTED by the Ubuntu
## team. Also, please note that software in universe WILL NOT receive any
## review or updates from the Ubuntu security team.
deb http://id.archive.ubuntu.com/ubuntu/ bionic universe
# deb-src http://id.archive.ubuntu.com/ubuntu/ bionic universe
deb http://id.archive.ubuntu.com/ubuntu/ bionic-updates universe
# deb-src http://id.archive.ubuntu.com/ubuntu/ bionic-updates universe

## N.B. software from this repository is ENTIRELY UNSUPPORTED by the Ubuntu 
## team, and may not be under a free licence. Please satisfy yourself as to 
## your rights to use the software. Also, please note that software in 
## multiverse WILL NOT receive any review or updates from the Ubuntu
## security team.
deb http://id.archive.ubuntu.com/ubuntu/ bionic multiverse
# deb-src http://id.archive.ubuntu.com/ubuntu/ bionic multiverse
deb http://id.archive.ubuntu.com/ubuntu/ bionic-updates multiverse
# deb-src http://id.archive.ubuntu.com/ubuntu/ bionic-updates multiverse

## N.B. software from this repository may not have been tested as
## extensively as that contained in the main release, although it includes
## newer versions of some applications which may provide useful features.
## Also, please note that software in backports WILL NOT receive any review
## or updates from the Ubuntu security team.
deb http://id.archive.ubuntu.com/ubuntu/ bionic-backports main restricted universe multiverse
# deb-src http://id.archive.ubuntu.com/ubuntu/ bionic-backports main restricted universe multiverse

## Uncomment the following two lines to add software from Canonical's
## 'partner' repository.
## This software is not part of Ubuntu, but is offered by Canonical and the
## respective vendors as a service to Ubuntu users.
# deb http://archive.canonical.com/ubuntu bionic partner
# deb-src http://archive.canonical.com/ubuntu bionic partner

deb http://security.ubuntu.com/ubuntu bionic-security main restricted
# deb-src http://security.ubuntu.com/ubuntu bionic-security main restricted
deb http://security.ubuntu.com/ubuntu bionic-security universe
# deb-src http://security.ubuntu.com/ubuntu bionic-security universe
deb http://security.ubuntu.com/ubuntu bionic-security multiverse
# deb-src http://security.ubuntu.com/ubuntu bionic-security multiverse

install depedency untuk postgresql pada semua server :

sudo su
cd ~
sudo sh -c 'echo "deb-src http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main 9.4" > /etc/apt/sources.list.d/pgdg.list'
sudo apt-get install wget ca-certificates unzip
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get build-dep postgresql-9.4

Build Postgresql pada semua server :

wget https://github.com/2ndQuadrant/bdr/archive/bdr-pg/REL9_4_12-1.tar.gz
tar -xzvf REL9_4_12-1.tar.gz
cd ~/bdr-bdr-pg-REL9_4_12-1
./configure --prefix=/usr/lib/postgresql/9.4 --enable-debug --with-openssl
make -j4 -s install-world
cd ..

Build BDR untuk clustering pada semua server :

wget https://github.com/2ndQuadrant/bdr/archive/bdr-plugin/1.0.6.zip
unzip 1.0.6.zip
cd ~/bdr-bdr-plugin-1.0.6
PATH=/usr/lib/postgresql/9.4/bin:"$PATH" ./configure
make -j4 -s all
make -s install
cd ..

Create user postgres pada semua server :

cd ~
useradd postgres
passwd postgres
mkdir -p /var/lib/postgresql
chown postgres:postgres /var/lib/postgresql
sudo usermod -d /var/lib/postgresql postgres

Inisialisasi postgres pada semua server :

su -l postgres
export PATH=/usr/lib/postgresql/9.4/bin:$PATH
mkdir ~/9.4-bdr
initdb -D ~/9.4-bdr -A trust

edit postgres configuration pada semua server :

nano ~/9.4-bdr/postgresql.conf

listen_addresses = '*'
shared_preload_libraries = 'bdr'
wal_level = 'logical'
track_commit_timestamp = on
max_connections = 100
max_wal_senders = 10
max_replication_slots = 10
max_worker_processes = 10

edit akses postgres pada semua server :

nano ~/9.4-bdr/pg_hba.conf

local   replication     postgres                        trust
host    replication     postgres        127.0.0.1/32    trust
host    replication     postgres        ::1/128         trust

host all all 0.0.0.0/0  password

host replication postgres 10.200.200.100/32 trust
host replication postgres 10.200.200.110/32 trust

host replication bdrsync 10.200.200.100/32 password
host replication bdrsync 10.200.200.110/32 password

start postgres pada semua server :

pg_ctl -l ~/log -D ~/9.4-bdr start

buat akun untuk singkronisasi pada semua server :

psql -c "CREATE USER bdrsync superuser;"
psql -c "ALTER USER bdrsync WITH PASSWORD '12345#';"

buat akun dan database dummy untuk testing pada semua server :

createuser test_user
createdb -O test_user test_db
psql
alter user test_user with encrypted password 'test_pass';
grant all privileges on database test_db to test_user;

\c test_db
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO test_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO test_user;
\q

tambahkan BDR extension ke database pada semua server :

psql test_db -c 'CREATE EXTENSION btree_gist;'
psql test_db -c 'CREATE EXTENSION bdr;'

pada server DB 1 :

psql
\c test_db
SELECT bdr.bdr_group_create(
    local_node_name := 'node1',
    node_external_dsn := 'host=10.200.200.100 user=bdrsync dbname=test_db password=12345#'
);

pada server DB 2 :

psql
\c test_db
SELECT bdr.bdr_group_join(
    local_node_name := 'node2',
    node_external_dsn := 'host=10.200.200.110 user=bdrsync dbname=test_db password=12345#',
    join_using_dsn := 'host=10.200.200.100 user=bdrsync dbname=test_db password=12345#'
);

Lihat koneksi cluster :

select * from bdr.bdr_nodes;
select * from bdr.bdr_connections;

Cluster postgresql sudah terbuat. silahkan ditesting.

Comments are closed.