Connect with us

LINUX

Backup dan Restore dengan Barman (Tipe rsync) – Linux.or.id :: Linux.or.id


January 27th, 2018

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 [email protected]
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 [email protected]:/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 [email protected]"   main-db-server   backup-id   directory_data

$ barman recover --target-time "2017-08-02 13:35:47.881731+07:00" --remote-ssh-command "ssh [email protected]" 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

 

erick_s,








Link Sumber

Continue Reading
Click to comment

Leave a Reply

?>