spec

OS : Debian 8.x
Postgresql-version : 9.5.6
Barman-version : 2.2

/etc/hosts

192.168.34.187 master
192.168.34.182 slave
192.168.34.186 barman

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 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 backup untuk Postgresql-master

Type backup yang digunakan adalah melalui rsync protocol, dibutuhkan koneksi ssh untuk proses backupnya

untuk metode ini diperlukan beberapa langkah yang harus dilakukan di Postgres-master

Di postgres-master

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 tersebut kedalam file konfigurasi pg_hba.conf

host all barman 192.168.34.186/32 md5

Selanjutnya restart service postgresql

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

Pastikan user barman bisa connect ssh tanpa pasword dari server database master ke server barman, nantinya untuk melakukan sync WAL archive ke server barman

Di server 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

Berikut adalah konfigurasi server yang akan di backup

# vi /etc/barman.d/serverdb.conf

[maindb]
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

Setelah mengkonfigurasi selanjutnya jalankan perintah barman show-server nama_server untuk mengetahui posisi incoming backup direktori

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

Di postgres master

Kembali ke server postgres master, edit file postgresql.conf ubah bagian archive_command menjadi seperti ini

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

restart kembali service postgresql

Di server barman

Jalankan barman check untuk mengecek file konfigurasi

$ barman check maindb
Server maindb:
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 maindb
The xlog file 000000010000000000000003 has been closed on server 'maindb'
Waiting for the xlog file 000000010000000000000003 from server 'maindb' (max: 30 seconds)
Processing xlog segments from file archival for maindb
000000010000000000000003

Jalankan kembali barman check

$ barman check maindb
Server maindb:
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

Konfigurasi sudah benar semua dan siap untuk melakukan backup database

Memulai backup

Untuk membackup database jalankan perintah barman backup nama_server

$ barman backup maindb
Starting backup using rsync-exclusive method for server maindb in /var/lib/barman/maindb/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 maindb
000000020000000000000007
000000020000000000000008
000000020000000000000008.00000028.backup

Proses Backup telah dilakukan

untuk scheduling backup bisa menggunakan cron

$ crontab -e

00 01 * * * /usr/bin/barman backup maindb

Simulasi Restore 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)

Select count 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 table test03

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 server 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

memulai recovery

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 postgres master

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=#

Advertisements