Barman
(backup and recovery manager) is an administration tool for disaster
recovery of PostgreSQL servers written in Python. Barman can perform
remote backups of multiple servers in business critical environments,
and helps DBAs during the recovery phase.
Barman’s
most wanted features include: backup catalogues, incremental backup,
retention policies, remote recovery, archiving and compression of WAL
files and of backups.
Steps
To Setup pgbarman:
Step 1 :
Install PostgreSQL-9.4 or 9.5.
Download
PostgreSQL from below link
https://ftp.postgresql.org/pub/source/v9.5.1/postgresql-9.5.1.tar.bz2
Step 2 :
Download pgbarman from below link.
https://sourceforge.net/projects/pgbarman/files/1.5.1/barman-1.5.1.tar.gz/download
Step3: Install
prerequisite before installing pgbarman below are necessary
prerequisite.
Python 2.6 or
2.7
Python
modules:
PostgreSQL >=
8.3
rsync >=
3.0.4
Step4: untar
the pgbarman file and install it as given below
[root@localhost
~] tar -xvf barman-1.5.1.tar.gz
[root@localhost
~] cd barman-1.5.1
[root@localhost
barman-1.5.1] python2.6 setup.py build
[root@localhost
barman-1.5.1] python2.6 setup.py install
Step5: copy
barman.conf from doc to /etc/
Step6: create
user barman and change the owner of /etc/barman.conf
Step7: Make
password less authentication between barman server and postgres
server .
Repeat above
steps for postgres user.
[root@localhost
barman-1.5.1]# su - postgres
[postgres@localhost
~]$ ssh-keygen
[postgres@localhost
~]$ ssh-copy-id -i .ssh/id_rsa.pub barman@127.0.0.1
Step8: Edit
barman.conf and edit below parameters in config file
[barman]
barman_home
= /home/barman
barman_user
= barman
log_file
= /home/barman/barman.log
compression
= gzip
reuse_backup
= link
minimum_redundancy
= 1
[main-db-server]
description
= "Main DB Server"
ssh_command
= ssh postgres@127.0.0.1
conninfo
= host=127.0.0.1 user=postgres
Step9:
Edit the postgresql.conf and enable the archiving .
There
is one last configuration to be made on the main, to switch on backup
(or archive) mode. First, we need to locate the value of the incoming
backup directory from the barman, switch to the user barman:
su
- barman
Run
below command to locate the incoming backup directory:
barman
show-server main | grep incoming_wals_directory
incoming_wals_directory: /home/barman/main/incoming
Now
switch to the user postgres on postgres server.
Open
the postgresql.conf and
make the following changes to the file:
Uncomment
the wal_level parameter
and set its value to archive.
Uncomment
the archive_mode parameter
and set its value to on.
Uncomment
the archive_command parameter
and set its value to 'rsync
-a %p barman@127.0.0.1:/home/barman/main/incoming/%f'.
Use
the IP address of the Barman server. If you got a different value
for incoming_wals_directory.
Step10:
Restart postgres server or instance .
Step11:
Now loging to the barman (su – barman) and check wether barman can
connect to postgres or not .
Note
: here 'main' is name for postgres instance which is declared in
barman.conf.
[barman@localhost
~]$ barman check main
Server
main:
PostgreSQL:
OK
archive_mode:
OK
wal_level:
OK
archive_command:
OK
continuous
archiving: OK
directories:
OK
retention
policy settings: OK
backup
maximum age: OK (no last_backup_maximum_age provided)
compression
settings: OK
minimum
redundancy requirements: FAILED (have 0 backups, expected at least
1)
ssh:
OK (PostgreSQL server)
not
in recovery: OK
Step12
: Below command gives the information of the postgres server
[barman@localhost
~]$ barman show-server main
Server
main:
active:
True
archive_command:
false
archive_mode:
on
archived_count:
0
backup_directory:
/home/barman/main
backup_options:
BackupOptions(['exclusive_backup'])
bandwidth_limit:
None
basebackup_retry_sleep:
30
basebackup_retry_times:
0
basebackups_directory:
/home/barman/main/base
compression:
None
config_file:
/home/postgres/data/postgresql.conf
conninfo:
host=127.0.0.1 user=postgres port=5432
copy_method:
rsync
current_archived_wals_per_second:
0.0
current_xlog:
000000010000000000000043
custom_compression_filter:
None
custom_decompression_filter:
None
data_directory:
/home/postgres/data
description:
Main PostgreSQL Database
disabled:
False
failed_count:
0
Step13
: Take first full backup of main using barman
[barman@localhost
~]$ barman backup main
Starting
backup for server main in /home/barman/main/base/20160226T134115
Backup
start at xlog location: 0/48000028 (000000010000000000000048,
00000028)
Copying
files.
Copy
done.
Asking
PostgreSQL server to finalize the backup.
Backup
size: 480.8 MiB. Actual size on disk: 480.8 MiB (-0.00%
deduplication ratio).
Backup
end at xlog location: 0/480000C0 (000000010000000000000048,
000000C0)
Backup
completed
Processing
xlog segments for main
Older
than first backup. Trashing file 000000010000000000000047 from
server main
000000010000000000000048
000000010000000000000048.00000028.backup
Step14 : login to postgres and create database and tables, insert data into
tables then take incremental backup of main using barman(login to
barman).
Adding
data to postgres :
[postgres@localhost
~]$ psql
psql
(9.5.1)
Type
"help" for help.
postgres=#
\dt
List
of relations
Schema
| Name | Type | Owner
--------+------------------+-------+----------
public
| pgbench_accounts | table | postgres
public
| pgbench_branches | table | postgres
public
| pgbench_history | table | postgres
public
| pgbench_tellers | table | postgres
public
| test | table | postgres
public
| test1 | table | postgres
public
| test2 | table | postgres
public
| test4 | table | postgres
public
| test5 | table | postgres
public
| test6 | table | postgres
(10
rows)
postgres=#
create database test;
CREATE
DATABASE
postgres=#
\c test
You
are now connected to database "test" as user "postgres".
test=#
create table test1(i int);
CREATE
TABLE
test=#
create table test2(i int);
CREATE
TABLE
test=#
insert into test1 values (generate_series(1,1000));
INSERT
0 1000
test=#
insert into test2 values (generate_series(1,1000));
INSERT
0 1000
test=#
\q
Now
take incremental backup using barman :
[barman@localhost
~]$ barman backup --reuse=link main
Starting
backup for server main in /home/barman/main/base/20160226T134400
Backup
start at xlog location: 0/4A000028 (00000001000000000000004A,
00000028)
Copying
files.
Copy
done.
Asking
PostgreSQL server to finalize the backup.
Backup
size: 488.0 MiB. Actual size on disk: 7.3 MiB (-98.50% deduplication
ratio).
Backup
end at xlog location: 0/4A0000C0 (00000001000000000000004A,
000000C0)
Backup
completed
Processing
xlog segments for main
000000010000000000000049
00000001000000000000004A
00000001000000000000004A.00000028.backup
Note
: --reuse= link is used to link the main full backup . This will
backup only modified or changed files from main(postgres) .
Step15:
To list out the backus follow below command
[barman@localhost
~]$ barman list-backup main
main
20160226T134400 - Fri Feb 26 13:44:07 2016 - Size: 504.0 MiB - WAL
Size: 0 B
main
20160226T134115 - Fri Feb 26 13:41:29 2016 - Size: 496.8 MiB - WAL
Size: 32.0 MiB
There
are two backups listed 20160226T134400 is
incremental backup and 20160226T134115 is
full backup.
Step16:
Now lets recover from backup
[barman@localhost
~]$ barman recover main 20160226T134400 /tmp/data
Starting
local restore for server main using backup 20160226T134400
Destination
directory: /tmp/data
Copying
the base backup.
Copying
required WAL segments.
Generating
archive status files
Identify
dangerous settings in destination directory.
IMPORTANT
These
settings have been modified to prevent data losses
postgresql.conf
line 209: archive_command = false
Your
PostgreSQL server has been successfully prepared for recovery!
Backup
is restored at location /tmp/data and while recovering user have to
give latest backup ID
Step17:
Now change owner of /tmp/data as 'postgres' and start the recovered
instance.
[root@localhost
tmp]# chown -R postgres:postgres data
[root@localhost
data]# ls -lrth
total
156K
drwx------.
2 postgres postgres 4.0K Feb 26 08:11 pg_twophase
drwx------.
2 postgres postgres 4.0K Feb 26 08:11 pg_tblspc
drwx------.
2 postgres postgres 4.0K Feb 26 08:11 pg_snapshots
drwx------.
2 postgres postgres 4.0K Feb 26 08:11 pg_serial
drwx------.
2 postgres postgres 4.0K Feb 26 08:11 pg_replslot
drwx------.
4 postgres postgres 4.0K Feb 26 08:11 pg_multixact
drwx------.
4 postgres postgres 4.0K Feb 26 08:11 pg_logical
drwx------.
2 postgres postgres 4.0K Feb 26 08:11 pg_dynshmem
drwx------.
2 postgres postgres 4.0K Feb 26 08:11 pg_commit_ts
-rw-------.
1 postgres postgres 4 Feb 26 08:11 PG_VERSION
-rw-------.
1 postgres postgres 88 Feb 26 08:11 postgresql.auto.conf.origin
drwx------.
2 postgres postgres 4.0K Feb 26 08:11 pg_subtrans
-rw-------.
1 postgres postgres 1.6K Feb 26 08:11 pg_ident.conf
drwx------.
2 postgres postgres 4.0K Feb 26 08:11 pg_clog
-rw-------.
1 postgres postgres 4.4K Feb 26 08:27 pg_hba.conf
-rw-------.
1 postgres postgres 22K Feb 26 13:40 postgresql.conf.origin
drwx------.
2 postgres postgres 4.0K Feb 26 13:40 pg_stat
drwx------.
7 postgres postgres 4.0K Feb 26 13:42 base
-rw-------.
1 postgres postgres 224 Feb 26 13:44 backup_label.old
-rw-------.
1 postgres postgres 22K Feb 26 13:45 postgresql.conf
-rw-------.
1 postgres postgres 88 Feb 26 13:45 postgresql.auto.conf
-rw-------.
1 postgres postgres 58 Feb 26 13:46 postmaster.pid
drwx------.
2 postgres postgres 4.0K Feb 26 13:46 pg_notify
-rw-------.
1 postgres postgres 40 Feb 26 13:46 postmaster.opts
drwx------.
2 postgres postgres 4.0K Feb 26 13:46 pg_log
drwx------.
3 postgres postgres 4.0K Feb 26 13:46 pg_xlog
drwx------.
2 postgres postgres 4.0K Feb 26 13:46 global
drwx------.
2 postgres postgres 4.0K Feb 26 13:53 pg_stat_tmp
[root@localhost
tmp]# su - postgres
[postgres@localhost
~]$ pg_ctl -D master/ stop
waiting
for server to shut down.... done
server
stopped
[postgres@localhost
~]$ pg_ctl -D /tmp/data/ start
server
starting
[postgres@localhost
~]$ LOG: redirecting log output to logging collector process
HINT:
Future log output will appear in directory "pg_log".
psql
to new instance which is recovered using barman at location
/tmp/data.
[postgres@localhost
~]$ psql
psql
(9.5.1)
Type
"help" for help.
postgres=#
\dt
List
of relations
Schema
| Name | Type | Owner
--------+------------------+-------+----------
public
| pgbench_accounts | table | postgres
public
| pgbench_branches | table | postgres
public
| pgbench_history | table | postgres
public
| pgbench_tellers | table | postgres
public
| test | table | postgres
public
| test1 | table | postgres
public
| test2 | table | postgres
public
| test4 | table | postgres
public
| test5 | table | postgres
public
| test6 | table | postgres
(10
rows)
postgres=#
\l+
List
of databases
Name
| Owner | Encoding | Collate | Ctype | Access privileges | Size |
Tablespace | Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
postgres
| postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 466 MB |
pg_default | default administrative connection database
template0
| postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7129
kB | pg_default | unmodifiable empty database
|
| | | | postgres=CTc/postgres | | |
template1
| postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7129
kB | pg_default | default template for new databases
|
| | | | postgres=CTc/postgres | | |
test
| postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7265 kB |
pg_default |
(4
rows)
postgres=#
\c test
You
are now connected to database "test" as user "postgres".
test=#
\dt
List
of relations
Schema
| Name | Type | Owner
--------+-------+-------+----------
public
| test1 | table | postgres
public
| test2 | table | postgres
(2
rows)
test=#
select count(*) from test1;
count
-------
1000
(1
row)
test=#
show data_directory ;
data_directory
----------------
/tmp/data
(1
row)
test=#
*******
