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!
Atau
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
if succsess you will see file recovery.conf will changed to recovery.done
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=#
================================================
root@BY-PSQL-DB2:~# su - barman
barman@BY-PSQL-DB2:~$ barman check maindb
WARNING: No backup strategy set for server 'maindb' (using default 'concurrent_backup').
Server maindb:
WAL archive: FAILED (please make sure WAL shipping is setup)
PostgreSQL: OK
superuser or standard user with backup privileges: OK
wal_level: OK
directories: OK
retention policy settings: OK
backup maximum age: FAILED (interval provided: 1 day, latest backup age: No available backups)
backup minimum size: OK (0 B)
wal maximum age: OK (no last_wal_maximum_age provided)
wal size: OK (0 B)
compression settings: OK
failed backups: OK (there are 0 failed backups)
minimum redundancy requirements: OK (have 0 backups, expected at least 0)
ssh: FAILED (Connection failed using 'ssh postgres@172.16.30.174 -o BatchMode=yes -o StrictHostKeyChecking=no' return code 255)
systemid coherence: OK (no system Id stored on disk)
archive_mode: OK
archive_command: OK
continuous archiving: OK
archiver errors: OK
barman@BY-PSQL-DB2:~$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/var/lib/barman/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /var/lib/barman/.ssh/id_rsa
Your public key has been saved in /var/lib/barman/.ssh/id_rsa.pub
The key fingerprint is:
SHA256:Tu/wgF44AL8IJF6/nhM68pBAKDDofh3QmmKpicu8Q9I barman@BY-PSQL-DB2
The key's randomart image is:
+---[RSA 3072]----+
|+ . |
|+. . . |
|=oo.+ |
|*=.=.. |
|B+. +.. S |
|*+Eo =.= . |
|=+o o.= = . |
|.=.o.o.o = |
| .=..oo o |
+----[SHA256]-----+
barman@BY-PSQL-DB2:~$ passwd
Changing password for barman.
Current password:
s^C^C
passwd: Authentication token manipulation error
passwd: password unchanged
barman@BY-PSQL-DB2:~$
barman@BY-PSQL-DB2:~$ ssh-copy-id postgres@172.16.30.174
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/var/lib/barman/.ssh/id_rsa.pub"
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
postgres@172.16.30.174's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh 'postgres@172.16.30.174'"
and check to make sure that only the key(s) you wanted were added.
barman@BY-PSQL-DB2:~$ barman check maindb
WARNING: No backup strategy set for server 'maindb' (using default 'concurrent_backup').
Server maindb:
WAL archive: FAILED (please make sure WAL shipping is setup)
PostgreSQL: OK
superuser or standard user with backup privileges: OK
wal_level: OK
directories: OK
retention policy settings: OK
backup maximum age: FAILED (interval provided: 1 day, latest backup age: No available backups)
backup minimum size: OK (0 B)
wal maximum age: OK (no last_wal_maximum_age provided)
wal size: OK (0 B)
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)
systemid coherence: OK (no system Id stored on disk)
archive_mode: OK
archive_command: OK
continuous archiving: OK
archiver errors: OK
barman@BY-PSQL-DB2:~$
Tidak ada komentar:
Posting Komentar