Backup dan Restore dengan Barman (Tipe rsync)

Barman merupakan backup tools product dari 2ndquadrant, tools ini digunakan khusus untuk database postgresql. Saat ini Barman telah masuk versi 2.2, kali ini akan sama-sama kita lihat bagaimana mensetup barman untuk menangani proses backup dan restore database postgresql
Pada barman terdapat dua tipe backup, yaitu dengan metode rsync dan streaming protocol. Untuk kali ini saya akan menjelaskan cara konfigurasi barman dengan metode rsync di debian.
Spec
OS : Debian 8.x
Postgresql-version : 9.5.6
Barman-version : 2.2
Konfigurasi /etc/hosts
192.168.10.18 masterdb
192.168.10.19 slavedb
192.168.10.20 barman
Langkah-langkahnya adalah sebagai berikut
Instalasi Barman
Buatlah terlebih dahulu file repo dengan nama pgdg.list

# vi /etc/apt/sources.list.d/pgdg.list
deb http://apt.postgresql.org/pub/repos/apt/ jessie-pgdg main

Kemudian import/sign key repositorynya

# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | \
apt-key add -
# apt-get update

Menginstal barman

# apt-get install -y barman

Setup dan konfigurasi Barman
Karena tipe backup yang digunakan adalah rsync, maka pastikan dari masterdb bisa connect ssh tanpa password ke barman dengan user barman. Dan juga sebaliknya dengan user postgres
Ada beberapa hal yang perlu dilakukan di masterdb terkait dengan barman ini, yaitu
Di masterdb
Buatlah user barman dengan privileges superuser untuk melakukan koneksi dari barman ke Postgres-master

postgres=# create role barman with password 'password123' ; alter role barman login ; alter role barman superuser ;
CREATE ROLE
ALTER ROLE
ALTER ROLE

Tambahkan user barman kedalam file pg_hba.conf

host     all		 barman           192.168.10.20/32	 md5

Selanjutnya restart service postgresql

$ /opt/pgsql-9.5/bin/pg_ctl -D /home/postgres/project/data/ -m fast restart

Di Barman
File konfigurasi barman
File konfigurasi utama barman berada di /etc/barman.conf , sedangkan untuk file server konfigurasinya berada di /etc/barman.d
Berikut adalah konfigurasi barman.conf

# vi /etc/barman.conf
; Main configuration file
[barman]
barman_user = barman
; Directory of configuration files. Place your sections in separate files with .conf extension
configuration_files_directory = /etc/barman.d
; Main directory
barman_home = /var/lib/barman
; Log location
log_file = /var/log/barman/barman.log
; Log level (see https://docs.python.org/3/library/logging.html#levels)
log_level = INFO
; Default compression level: possible values are None (default), bzip2, gzip, pigz, pygzip or pybzip2
compression = gzip
; Immediate checkpoint for backup command - default false
immediate_checkpoint = true
; Number of retries of data copy during base backup after an error - default 0
basebackup_retry_times = 3
; Number of seconds of wait after a failed copy, before retrying - default 30
basebackup_retry_sleep = 30
last_backup_maximum_age = 1 DAYS

File konfigurasi server yang hendak di backup

# vi /etc/barman.d/serverdb.conf
[linuxjakdb]
description = "Main DB Server"
ssh_command = ssh postgres@192.168.34.188
conninfo = host=192.168.34.188 user=barman dbname=postgres password=password123
retention_policy_mode = auto
backup_method = rsync
reuse_backup = link
parallel_jobs = 2
retention_policy = RECOVERY WINDOW OF 7 days
wal_retention_policy = main
archiver = on

Selanjutnya jalankan perintah barman show-server nama_server untuk mengetahui posisi incoming backup direktori

$ barman show-server linuxjakdb | grep incoming_wals_directory
incoming_wals_directory: /var/lib/barman/linuxjakdb/incoming

Di masterdb
Kembali ke masterdb, edit file postgresql.conf ubah bagian archive_command menjadi seperti ini

archive_command = 'rsync -avp %p barman@192.168.34.186:/var/lib/barman/linuxjakdb/incoming/%f'

Kemudian restart kembali service postgresqlnya

$ /opt/pgsql-9.5/bin/pg_ctl -D /home/postgres/project/data/ -m fast restart

Di Barman
Jalankan perintah barman check nama_server untuk mengecek konfigurasi

$ barman check linuxjakdb
Server linuxjakdb:
	WAL archive: FAILED (please make sure WAL shipping is setup)
	PostgreSQL: FAILED
	directories: OK
	retention policy settings: OK
	backup maximum age: FAILED (interval provided: 1 day, latest backup age: No available backups)
	compression settings: OK
	failed backups: OK (there are 0 failed backups)
	minimum redundancy requirements: OK (have 0 backups, expected at least 0)
	ssh: OK (PostgreSQL server)
	not in recovery: OK
	archiver errors: OK

Jika masih muncul error WAL archive: FAILED (please make sure WAL shipping is setup), jalankan perintah berikut untuk memverifikasi WAL archive

$ barman switch-xlog --force --archive linuxjakdb
The xlog file 000000010000000000000003 has been closed on server 'linuxjakdb'
Waiting for the xlog file 000000010000000000000003 from server 'linuxjakdb' (max: 30 seconds)
Processing xlog segments from file archival for linuxjakdb
	000000010000000000000003

Jalankan kembali barman check

$ barman check linuxjakdb
Server linuxjakdb:
	PostgreSQL: OK
	is_superuser: OK
	wal_level: OK
	directories: OK
	retention policy settings: OK
	backup maximum age: FAILED (interval provided: 1 day, latest backup age: No available backups)
	compression settings: OK
	failed backups: OK (there are 0 failed backups)
	minimum redundancy requirements: OK (have 0 backups, expected at least 0)
	ssh: OK (PostgreSQL server)
	not in recovery: OK
	archive_mode: OK
	archive_command: OK
	continuous archiving: OK
	archiver errors: OK

Semua sudah OK, kecuali backup maximum age, itu karena belum ada satu pun backup yang dilakukan
Memulai Backup
Untuk memulai backup, jalankan perintah barman backup nama_server

$ barman backup linuxjakdb
Starting backup using rsync-exclusive method for server linuxjakdb in /var/lib/barman/linuxjakdb/base/20170802T115638
Backup start at xlog location: 0/8000028 (000000020000000000000008, 00000028)
Starting backup copy via rsync/SSH for 20170802T115638 (2 jobs)
Copy done (time: 2 seconds)
Asking PostgreSQL server to finalize the backup.
Backup size: 27.5 MiB. Actual size on disk: 308.9 KiB (-98.90% deduplication ratio).
Backup end at xlog location: 0/8000130 (000000020000000000000008, 00000130)
Backup completed (start time: 2017-08-02 11:56:38.629006, elapsed time: 5 seconds)
Processing xlog segments from file archival for linuxjakdb
	000000020000000000000007
	000000020000000000000008
	000000020000000000000008.00000028.backup

Proses Backup telah selesai dilakukan
Untuk scheduling backup bisa menggunakan cron

$ crontab -e
00 01 * * * /usr/bin/barman backup maindb

Simulasi Recovery database
Misalkan di postgres master ada database asus dengan tiga table seperti berikut

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 asus      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
postgres=# \c asus
You are now connected to database "asus" as user "postgres".
asus=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 asus      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)
asus=# \dt
         List of relations
 Schema |  Name  | Type  |  Owner
--------+--------+-------+----------
 public | test01 | table | postgres
 public | test02 | table | postgres
 public | test03 | table | postgres
(3 rows)

Lakukan select count(*) pada masing-masing table

asus=# select count(*) from test01 ;
 count
-------
    60
(1 row)
asus=# select count(*) from test02 ;
 count
-------
    60
(1 row)
asus=# select count(*) from test03 ;
 count
-------
    60
(1 row)

Selanjutnya drop table03

asus=# drop table test03;
DROP TABLE
asus=# \dt
         List of relations
 Schema |  Name  | Type  |  Owner
--------+--------+-------+----------
 public | test01 | table | postgres
 public | test02 | table | postgres
(2 rows)

Melakukan recovery
Untuk melakukan recovery terlebih dahulu stop service postgresql di postgres-master dan slave

$ /opt/pgsql-9.5/bin/pg_ctl -D /home/postgres/project/data/ stop

Di Barman
Tampilkan list hasil backup database yang telah dilakukan oleh barman

$ barman list-backup maindb
maindb 20170802T133526 - Wed Aug  2 13:35:53 2017 - Size: 27.6 MiB - WAL Size: 30.2 KiB
maindb 20170802T115638 - Wed Aug  2 11:57:05 2017 - Size: 27.6 MiB - WAL Size: 58.2 KiB
maindb 20170802T112932 - Wed Aug  2 11:29:59 2017 - Size: 27.6 MiB - WAL Size: 30.9 KiB

Selanjutnya tampilkan detail informasi dari backup terakhir

$ barman show-backup maindb 20170802T133526
Backup 20170802T133526:
  Server Name            : maindb
  Status                 : DONE
  PostgreSQL Version     : 90506
  PGDATA directory       : /var/lib/postgresql/main
  Base backup information:
    Disk usage           : 27.5 MiB (27.6 MiB with WALs)
    Incremental size     : 40.8 KiB (-99.86%)
    Timeline             : 2
    Begin WAL            : 00000002000000000000000A
    End WAL              : 00000002000000000000000A
    WAL number           : 1
    WAL compression ratio: 99.84%
    Begin time           : 2017-08-02 13:35:47.881731+07:00
    End time             : 2017-08-02 13:35:53.301917+07:00
    Copy time            : 1 second + 3 seconds startup
    Estimated throughput : 21.0 KiB/s (2 jobs)
    Begin Offset         : 40
    End Offset           : 304
    Begin XLOG           : 0/A000028
    End XLOG             : 0/A000130
  WAL information:
    No of files          : 1
    Disk usage           : 30.2 KiB
    WAL rate             : 10.83/hour
    Compression ratio    : 99.82%
    Last available       : 00000002000000000000000B
  Catalog information:
    Retention Policy     : VALID
    Previous Backup      : 20170802T115638
    Next Backup          : - (this is the latest base backup)

Proses recovery siap untuk dilakukan, namun pastikan user postgres bisa connect ssh tanpa password dari server barman ke server database master
Untuk melakukan recovery jalankan perintah berikut

barman recover --target-time "Begin time"  --remote-ssh-command "ssh postgres@db-server-ip"   main-db-server   backup-id   directory_data
$ barman recover --target-time "2017-08-02 13:35:47.881731+07:00" --remote-ssh-command "ssh postgres@192.168.34.188" maindb 20170802T133526 /var/lib/postgresql/main/
Starting remote restore for server maindb using backup 20170802T133526
Destination directory: /var/lib/postgresql/main/
Doing PITR. Recovery target time: '2017-08-02 13:35:47.881731+07:00'
Copying the base backup.
Copying required WAL segments.
Generating recovery.conf
Identify dangerous settings in destination directory.
IMPORTANT
These settings have been modified to prevent data losses
postgresql.conf line 61: archive_command = false
Your PostgreSQL server has been successfully prepared for recovery!

Proses recovery telah selesai dilakukan, selanjutnya cek di postgres master
Di masterdb
Start service postgresql

$ /opt/pgsql-9.5/bin/pg_ctl -D /home/postgres/project/data/ start

Login ke postgresql, connect ke database asus kemudian lakukan select count di setiap table

$ /opt/pgsql-9.5/bin/psql -Upostgres -h127.0.0.1
psql (9.5.6)
Type "help" for help.
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 asus      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)
postgres=# \c asus
You are now connected to database "asus" as user "postgres".
asus=# \dt
         List of relations
 Schema |  Name  | Type  |  Owner
--------+--------+-------+----------
 public | test01 | table | postgres
 public | test02 | table | postgres
 public | test03 | table | postgres
(3 rows)
asus=# select count(*) from test01;
 count
-------
    60
(1 row)
asus=# select count(*) from test02;
 count
-------
    60
(1 row)
asus=# select count(*) from test03;
 count
-------
    60
(1 row)
asus=#

Terlihat semua table sama hasil countnya seperti sebelum di drop dan proses recovery telah berhasil dilakukan
Thank you
Semoga bermanfaat
 

Leave a Reply

Your email address will not be published.