DEV Community

Sihar Simbolon
Sihar Simbolon

Posted on • Updated on

Backup dan Replikasi PostgreSQL 12 menggunakan pgBackRest di CentOS 7

#postgres #pgbackrest

Berawal dari kesulitan saya mencari tutorial tentang backup dan replikasi postgresql menggunakan pgbackrest, akhir saya menemukan artikel tentang topik yang sama di dev.to yang ditulis oleh Mehdi Pourfar

Tutorial ini ditujukan untuk development saja.

Environment

CentOS 7.9
PostgreSQL 12.8
pgBackRest 2.35
Enter fullscreen mode Exit fullscreen mode

Server

pgprimary 172.16.16.160
pgreplica 172.16.16.161
pgbackup 172.16.16.162
Enter fullscreen mode Exit fullscreen mode

Instalasi software di server pgprimary, pgreplica dan pgbackup

# vi /etc/yum.repos.d/CentOS-Base.repo
Enter fullscreen mode Exit fullscreen mode

Tambahkan exclude=postgresql* di bagian [base] dan [updates]

[base]
...
exclude=postgresql*

#released updates
[updates]
...
exclude=postgresql*
Enter fullscreen mode Exit fullscreen mode

Setting selinux menjadi disabled di /etc/selinux/config

...
SELINUX=disabled
...
Enter fullscreen mode Exit fullscreen mode 
sudo yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install postgresql12-server postgresql12-contrib
sudo yum install epel-release
sudo yum install libzstd
sudo yum install pgbackrest
sudo systemctl enable postgresql-12
sudo /usr/pgsql-12/bin/postgresql-12-setup initdb
sudo systemctl start postgresql-12
Enter fullscreen mode Exit fullscreen mode

Create pgbackrest configuration di server pgprimary, pgreplica dan pgbackup

# mkdir -p -m 770 /var/log/pgbackrest
# chown postgres:postgres /var/log/pgbackrest
# mkdir -p /etc/pgbackrest
# mkdir -p /etc/pgbackrest/conf.d
# touch /etc/pgbackrest/pgbackrest.conf
# chmod 640 /etc/pgbackrest/pgbackrest.conf
# chown postgres:postgres /etc/pgbackrest/pgbackrest.conf
Enter fullscreen mode Exit fullscreen mode

Create pgbackrest repository di server pgbackup

# mkdir -p /var/lib/pgbackrest
# chmod 750 /var/lib/pgbackrest
# chown postgres:postgres /var/lib/pgbackrest
Enter fullscreen mode Exit fullscreen mode

Allow user postgres untuk ssh di masing-masing server

# nano /etc/ssh/sshd_config
Enter fullscreen mode Exit fullscreen mode

Pastikan user postgres ada di bagian AllowUsers dan PubkeyAuthentication yes

...
AllowUsers postgres
PubkeyAuthentication yes
...
Enter fullscreen mode Exit fullscreen mode

Restart system sshd

# systemctl restart sshd
Enter fullscreen mode Exit fullscreen mode

Generate public key dan copy key tersebut ke masing-masing server

di server pgprimary
$ ssh-keygen
$ ssh-copy-id postgres@172.16.16.161
$ ssh-copy-id postgres@172.16.16.162

di server pgreplica
$ ssh-keygen
$ ssh-copy-id postgres@172.16.16.160
$ ssh-copy-id postgres@172.16.16.162

di server pgbackup
$ ssh-keygen
$ ssh-copy-id postgres@172.16.16.160
$ ssh-copy-id postgres@172.16.16.161
Enter fullscreen mode Exit fullscreen mode

Setting postgresql.conf di pgprimary

archive_command = 'pgbackrest --stanza=clusterku archive-push %p'
archive_mode = on
listen_addresses = '*'
max_wal_senders = 3
wal_level = replica
Enter fullscreen mode Exit fullscreen mode

Setting pgbackrest di pgprimary
/etc/pgbackrest/pgbackrest.conf

[clusterku]
pg1-path=/var/lib/pgsql/12/data

[global]
repo1-host=172.16.16.162
repo1-host-user=postgres
Enter fullscreen mode Exit fullscreen mode

Restart service postgresql

# systemctl restart postgresql-12
Enter fullscreen mode Exit fullscreen mode

Setting pgbackrest di server pgbackup
/etc/pgbackrest/pgbackrest.conf

[clusterku]
pg1-host=172.16.16.160
pg1-host-user=postgres
pg1-path=/var/lib/pgsql/12/data                                                                                                               

[global]                                                                                                                                  
process-max=2
repo1-path=/var/lib/pgbackrest
repo1-retention-full=4
repo1-host-user=postgres
start-fast=y
Enter fullscreen mode Exit fullscreen mode

Create stanza di server pgbackup

$ pgbackrest --stanza=clusterku --log-level-console=info stanza-create
Enter fullscreen mode Exit fullscreen mode

Cek stanza yang sudah dicreate di server pgbackup pada server pgprimary dan pgreplica

$ pgbackrest --stanza=clusterku --log-level-console=info check

...
INFO: check command end: completed successfully (910ms)
Enter fullscreen mode Exit fullscreen mode

Lakukan backup di server pgbackup

$ pgbackrest --stanza=clusterku --log-level-console=info backup
...
 P00   INFO: expire command end: completed successfully (                                                                             10ms)
Enter fullscreen mode Exit fullscreen mode

Buat penjadwalan di server pgbackup agar backup stanza dilakukan secara periodik.

$ crontab -e
Enter fullscreen mode Exit fullscreen mode

Pada script berikut ini berisi backup full yang dilakukan di hari minggu dan backup diferensial di hari lainnya

30 06  *   *   0     pgbackrest --type=full --stanza=clusterku backup
30 06  *   *   1-6   pgbackrest --type=diff --stanza=clusterku backup
Enter fullscreen mode Exit fullscreen mode

Create user replikasi pada server pgprimary

# su – postgres
$ createuser --replication -P -e replicator
$ exit
Enter fullscreen mode Exit fullscreen mode

Izinkan user replicator diakses dari jaringan server pgreplica dan pgbackup

...
host    replication     replicator      172.16.16.0/24          md5
Enter fullscreen mode Exit fullscreen mode

Restart postgresql di server pgbackup

# systemctl restart postgresql-12
Enter fullscreen mode Exit fullscreen mode

Lakukan konfigurasi pgbackrest pada server pgreplica
/etc/pgbackrest/pgbackrest.conf

[clusterku]
pg1-path=/var/lib/pgsql/12/data
recovery-option=primary_conninfo=host=172.16.16.160 port=5432 user=replicator application_name=replica1

[global]
repo1-host=172.16.16.162
repo1-host-user=postgres
Enter fullscreen mode Exit fullscreen mode

Buat file .pgpass di home directory postgres yang berisi berikut

172.16.16.160:5432:replication:replicator:yourpassword
Enter fullscreen mode Exit fullscreen mode

Lakukan penarikan data postgresql dari server pgprimary di server pgreplica

# systemctl stop postgresql-12
# su - postgres
$ pgbackrest --stanza=clusterku --delta --type=standby --log-level-console=info restore
$ exit
# systemctl start postgresql-12
Enter fullscreen mode Exit fullscreen mode

Selesai

Tambahan
Restore database menggunakan pgbackrest ke waktu tertentu pada server pgprimary

# systemctl stop postgresql-12
# su - postgres
$ pgbackrest --stanza=clusterku --delta --type=time "--target=2021-10-08 09:28:23.201731+07" --target-action=promote restore
$ exit
# systemctl start postgresql-12
Enter fullscreen mode Exit fullscreen mode

Jika dilakukan restore pada server pgprimary, maka perlu dilakukan backup pada server pgbackup dan restore pada server pgreplica (karena saat di lakukan restore pada server pgprimary, sinkronisasi server pgreplica ke server pgprimary hilang).

backup pada server pgbackup

pgbackrest --stanza=clusterku --log-level-console=info backup
Enter fullscreen mode Exit fullscreen mode

restore pada server pgreplica

# systemctl stop postgresql-12
# su - postgres
$ pgbackrest --stanza=clusterku --delta --type=standby restore
$ exit
# systemctl start postgresql-12
Enter fullscreen mode Exit fullscreen mode

Data di server pgreplica menjadi sesuai dengan data di server pgprimary

Langkah-langkah promote server pgreplica menjadi pgprimary

  • sesuaikan konfigurasi pgbackrest di server pgprimary baru (161). Beri tanda # (comment) pada bagian recovery-option 
[clusterku]
pg1-path=/var/lib/pgsql/12/data
#recovery-option=primary_conninfo=host=172.16.16.160 port=5432 user=replicator application_name=replica1

[global]
repo1-host=172.16.16.162
repo1-host-user=postgres
Enter fullscreen mode Exit fullscreen mode

Restore pgbackrest di server pgprimary yang baru

# systemctl stop postgresql-12
# su - postgres
$ pgbackrest --stanza=clusterku --delta --type=immediate --target-action=promote restore
$ exit
# systemctl start postgresql-12
Enter fullscreen mode Exit fullscreen mode
  • sesuaikan konfigurasi pgbackrest di server pgbackup 
[clusterku]
#rubah ip address di pg1-host dari ip 160 ke 161
pg1-host=172.16.16.161
pg1-host-user=postgres
pg1-path=/var/lib/pgsql/12/data

[global]
process-max=2
repo1-path=/var/lib/pgbackrest
repo1-retention-full=4
repo1-retention-diff=6
repo1-host-user=postgres
start-fast=y
Enter fullscreen mode Exit fullscreen mode
  • lakukan backup di server pgbackup 
# su - postgres
$ pgbackrest --stanza=clusterku --log-level-console=info backup
$ exit
Enter fullscreen mode Exit fullscreen mode
  • buat konfigurasi .pgpass di server pgreplica baru (160) 
$ touch .pgpass
$ chmod 600 .pgpass
$ nano .pgpass
Enter fullscreen mode Exit fullscreen mode 
172.16.16.161:5432:replication:replicator:your_password
Enter fullscreen mode Exit fullscreen mode
  • tambahkan bagian recovery-option di konfigurasi pgbackrest server pgreplica 
[clusterku]
...
recovery-option=primary_conninfo=host=172.16.16.161 port=5432 user=replicator application_name=replica1
...
Enter fullscreen mode Exit fullscreen mode
  • stop service postgresql dan lakukan restore 
# systemctl stop postgresql-12
# su - postgres
$ pgbackrest --stanza=clusterku --delta --type=standby restore
$ exit
# systemctl start postgresql-12
Enter fullscreen mode Exit fullscreen mode
  • test lakukan perubahan data di server pgprimary baru (161) dan cek apakah data di server pgreplica baru(160) ikut berubah

Top comments (0)

Read next

itxshakil profile image

The Definitive Guide to the Constraint Validation API

Shakil Alam -

mkdev_me profile image

The End of Internet Search as We Know It. Bye Google

mkdev.me -

sanchita76 profile image

First Open-Source Contribution Hacktoberfest'24 Experience

Mehuli Biswas -

fady_nabil10 profile image

Amazon Web Services (AWS) has announced today a new edge location in #Qatar

Fady Nabil -