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