Backup dan Replikasi PostgreSQL 12 menggunakan pgBackRest di CentOS 7
Tags: postgres, pgbackrest
Published: 2021-10-08 04:57:15

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](https://dev.to/mehdipourfar/backup-and-replicate-postgresql-12-using-pgbackrest-in-ubuntu-20-04-5c4e)  
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