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
 

Server

pgprimary 172.16.16.160
pgreplica 172.16.16.161
pgbackup 172.16.16.162
 

Instalasi software di server pgprimary, pgreplica dan pgbackup

# vi /etc/yum.repos.d/CentOS-Base.repo
 

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

[base]
...
exclude=postgresql*

#released updates
[updates]
...
exclude=postgresql*
 

Setting selinux menjadi disabled di /etc/selinux/config

...
SELINUX=disabled
...
 
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
 

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
 

Create pgbackrest repository di server pgbackup

# mkdir -p /var/lib/pgbackrest
# chmod 750 /var/lib/pgbackrest
# chown postgres:postgres /var/lib/pgbackrest
 

Allow user postgres untuk ssh di masing-masing server

# nano /etc/ssh/sshd_config
 

Pastikan user postgres ada di bagian AllowUsers dan PubkeyAuthentication yes

...
AllowUsers postgres
PubkeyAuthentication yes
...
 

Restart system sshd

# systemctl restart sshd
 

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
 

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
 

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
 

Restart service postgresql

# systemctl restart postgresql-12
 

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
 

Create stanza di server pgbackup

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

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)
 

Lakukan backup di server pgbackup

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

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

$ crontab -e
 

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
 

Create user replikasi pada server pgprimary

# su – postgres
$ createuser --replication -P -e replicator
$ exit
 

Izinkan user replicator diakses dari jaringan server pgreplica dan pgbackup

...
host    replication     replicator      172.16.16.0/24          md5
 

Restart postgresql di server pgbackup

# systemctl restart postgresql-12
 

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
 

Buat file .pgpass di home directory postgres yang berisi berikut

172.16.16.160:5432:replication:replicator:yourpassword
 

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
 

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
 

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
 

restore pada server pgreplica

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

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
 

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
 
  • 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
 
  • lakukan backup di server pgbackup
# su - postgres
$ pgbackrest --stanza=clusterku --log-level-console=info backup
$ exit
 
  • buat konfigurasi .pgpass di server pgreplica baru (160)
$ touch .pgpass
$ chmod 600 .pgpass
$ nano .pgpass
 
172.16.16.161:5432:replication:replicator:your_password
 
  • 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
...
 
  • stop service postgresql dan lakukan restore
# systemctl stop postgresql-12
# su - postgres
$ pgbackrest --stanza=clusterku --delta --type=standby restore
$ exit
# systemctl start postgresql-12
 
  • test lakukan perubahan data di server pgprimary baru (161) dan cek apakah data di server pgreplica baru(160) ikut berubah
# postgresql