How to configure Barman :
Step 1 :
Remove existing postgres Instllations if you have for safer side.
sudo su root ==> yum remove postgres* & yum remove barman*
step 2 :
Install this on Both Prod & Backup servers.
PostgreSQL 11
You can install the 2ndQuadrant's General Public RPM repository for PostgreSQL 11 by running the following instructions as a user with sudo privileges on the destination Linux server:
curl https://dl.2ndquadrant.com/default/release/get/11/rpm | sudo bash
The above command will run a shell script that will install the repository definition in your server. You can check the content of the script by running:
curl https://dl.2ndquadrant.com/default/release/get/11/rpm | less
Step 3 :
Install EPEL depends on OS version on prod :
CentOS/RHEL - 7
rpm -Uvh https://yum.postgresql.org/11/redhat/rhel-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install postgresql11-server
yum install postgresql11-contrib ===> This rpm required for the functionality of Postgres Extension.
Make sure to remove existing /var/lib/pgsql folder
/usr/pgsql-11/bin/postgresql-11-setup initdb
systemctl enable postgresql-11.service
systemctl start postgresql-11.service
ps -ef|grep -i postgres.
If not started run the below as postgres user,
/usr/pgsql-11/bin/pg_ctl -D /var/lib/pgsql/11/data/ start
Note : add the line
PATH=/usr/pgsql-11/bin:$PATH under /etc/profile as a root user to get commands properly.
eg :
for i in /etc/profile.d/*.sh /etc/profile.d/sh.local ; do
if [ -r "$i" ]; then
if [ "${-#*i}" != "$-" ]; then
. "$i"
else
. "$i" >/dev/null
fi
fi
done
PATH=/usr/pgsql-11/bin:$PATH
unset i
unset -f pathmunge
step 4 :
Install this on barman backup server :
yum install barman.noarch barman-cli.noarch
Step 5 :
Enable SSH between all the nodes from barman to postgress and vice versal.
Step 6 :
Create Barman user for PostgreSQL on PG Database ( source )
note : createuser is an OS utiliity by pg.
createuser -s -P barman
createuser -s -P streaming_barman
Note down the password ..
go to barman (backup server ) :
bash-4.2$ ls -lrt .pgpass
-rw-------. 1 barman barman 87 Jul 24 18:16 .pgpass
bash-4.2$ pwd
/var/lib/barman
bash-4.2$ cat .pgpass
pg1:5432:postgres:barman:barman123
pg1:5432:postgres:streaming_barman:streaming_barman
bash-4.2$
Note : /var/lib/barman/.pgpass should be 0600
Step 7:
Configure barman.conf under /etc/barman.conf
barman_user = barman
configuration_files_directory = /etc/barman.d
barman_home = /var/lib/barman
log_file = /var/log/barman/barman.log
log_level = INFO
compression = gzip
retention_policy = REDUNDANCY 2
[pgserver11]
description = "Main PG DB Server"
ssh_command = ssh postgres@pg1
streaming_conninfo=host=192.168.56.101 user=streaming_barman dbname=postgres port=5432
conninfo=host=192.168.56.101 user=barman dbname=postgres port=5432
backup_method = rsync
streaming_archiver = on
archiver = on
path_prefix=/usr/pgsql-11/bin/
Note : 192.168.56.101 is where the postgress db is running.
Script Execution Testing :
Step 8 :
configure Source Machine (DB Node ) postgresql.conf and pg_hba.conf
postgresql.conf :
*****************
bash-4.2$ grep -E "archive_mode|wal_level|archive_command|listen|port" postgresql.conf
wal_level = 'replica' # minimal, replica, or logical
archive_mode = on # enables archiving; off, on, or always
archive_command = 'barman-wal-archive 192.168.56.103 pgserver11 %p' # command to use to archive a logfile segment
listen_addresses = '192.168.56.101' # what IP address(es) to listen on;
#port = 5432 ===================================> By default , it will use this port.
bash-4.2$ pwd
/var/lib/pgsql/11/data
bash-4.2$ hostname
pg1
bash-4.2$
pg_hba.conf
**********
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 0.0.0.0/0 trust
# IPv6 local connections:
host all all ::1/128 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
#host replication all 127.0.0.1/32 ident
#host replication all ::1/128 ident
host replication all 192.168.56.103/32 trust
host barman all 192.168.56.103/32 trust
host streaming_barman all 192.168.56.103/32 trust
bash-4.2$
Restart the instance : /usr/pgsql-11/bin/pg_ctl -D /var/lib/pgsql/11/data stop
/usr/pgsql-11/bin/pg_ctl -D /var/lib/pgsql/11/data start
verification :
bash-4.2$ hostname
pg1
bash-4.2$ ps -ef|grep -i postgre
root 6294 5010 0 16:47 pts/0 00:00:00 sudo su postgres
root 6295 6294 0 16:47 pts/0 00:00:00 su postgres
postgres 6296 6295 0 16:47 pts/0 00:00:00 bash
postgres 6457 1 0 17:32 pts/0 00:00:00 /usr/pgsql-11/bin/postgres -D /var/lib/pgsql/11/data
postgres 6458 6457 0 17:32 ? 00:00:00 postgres: logger
postgres 6460 6457 0 17:32 ? 00:00:00 postgres: checkpointer
postgres 6461 6457 0 17:32 ? 00:00:00 postgres: background writer
postgres 6462 6457 0 17:32 ? 00:00:00 postgres: walwriter
postgres 6463 6457 0 17:32 ? 00:00:00 postgres: autovacuum launcher
postgres 6464 6457 0 17:32 ? 00:00:00 postgres: archiver last was 000000010000000000000007.00000108.backup
postgres 6465 6457 0 17:32 ? 00:00:00 postgres: stats collector
postgres 6466 6457 0 17:32 ? 00:00:00 postgres: logical replication launcher
postgres 6733 6457 0 18:52 ? 00:00:03 postgres: walsender streaming_barman 192.168.56.103(46650) streaming 0/80001A8
postgres 7145 6296 0 20:14 pts/0 00:00:00 ps -ef
postgres 7146 6296 0 20:14 pts/0 00:00:00 grep -i postgre
netstat -lpn | grep LISTEN
bash-4.2$ netstat -lpn | grep LISTEN
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 0.0.0.0:111 0.0.0.0:* LISTEN -
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN -
tcp 0 0 192.168.56.101:5432 0.0.0.0:* LISTEN 6457/postgres
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN -
tcp6 0 0 :::111 :::* LISTEN -
tcp6 0 0 :::22 :::* LISTEN -
NOTE :
Login as barman user at backup server.
psql -c 'SELECT version()' -U barman -d postgres -h pg1 -p 5432
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 11.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
(1 row)
Now verify , are you able to connect barman user from backup server to source db.
STEP 9 :
Login as barman on backup server :
bash-4.2$ barman check pgserver11
WARNING: No backup strategy set for server 'pgserver11' (using default 'exclusive_backup').
WARNING: The default backup strategy will change to 'concurrent_backup' in the future. Explicitly set 'backup _options' to silence this warning.
Server pgserver11:
WAL archive: FAILED (please make sure WAL shipping is setup) ======================================>
PostgreSQL: OK
is_superuser: OK
PostgreSQL streaming: OK
wal_level: OK
directories: OK
retention policy settings: OK
backup maximum age: OK (no last_backup_maximum_age provided)
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
pg_receivexlog: OK
pg_receivexlog compatible: OK
receive-wal running: OK
archiver errors: OK
Note : For the first one error...Usually it is refreshed after some time automatically..But I tried the below.
on backup node :
bash-4.2$ barman switch-wal --force --archive pgserver1
ERROR: Unknown server 'pgserver1'
on db node :
check pg_wal folder and its archive folder..whether files are creating..If files are creating then, status will change in backup node.
Server pgserver11:
PostgreSQL: OK
is_superuser: OK
PostgreSQL streaming: OK
wal_level: OK
directories: OK
retention policy settings: OK
backup maximum age: OK (no last_backup_maximum_age provided)
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
pg_receivexlog: OK
pg_receivexlog compatible: OK
receive-wal running: OK==============================================> Until barman server get some file, it will display failed.
archiver errors: OK
Note : if you get PostgreSQL: FAILED then some issue especially user , password, or db name in connection properties (barman.conf )
Note : To fix the issue on receive-wal warning, go to primary db node and create one sample table and load some bulk data which will generate so many files in pg_wlog
automatically invokes chekpont too.
CREATE TABLE k1 (
col1 int,
col2 int
);
INSERT INTO k1 SELECT i/12345, i/4000000
FROM generate_series (1,9000000) s(i);
ANALYZE tbl;
bash-4.2$ barman backup pgserver11
WARNING: No backup strategy set for server 'pgserver11' (using default 'exclusive_backup').
WARNING: The default backup strategy will change to 'concurrent_backup' in the future. Explicitly set 'backup _options' to silence this warning.
Starting backup using rsync-exclusive method for server pgserver11 in /var/lib/barman/pgserver11/base/2019072 4T192510
Backup start at LSN: 0/7000108 (000000010000000000000007, 00000108)
This is the first backup for server pgserver11
WAL segments preceding the current backup have been found:
000000010000000000000001 from server pgserver11 has been removed
000000010000000000000002 from server pgserver11 has been removed
000000010000000000000003 from server pgserver11 has been removed
000000010000000000000004 from server pgserver11 has been removed
000000010000000000000005 from server pgserver11 has been removed
000000010000000000000006 from server pgserver11 has been removed
Starting backup copy via rsync/SSH for 20190724T192510
Copy done (time: 6 seconds)
This is the first backup for server pgserver11
Asking PostgreSQL server to finalize the backup.
Backup size: 67.2 MiB
Backup end at LSN: 0/7000210 (000000010000000000000007, 00000210)
Backup completed (start time: 2019-07-24 19:27:14.105664, elapsed time: 8 seconds)
Processing xlog segments from file archival for pgserver11
000000010000000000000007
000000010000000000000007.00000108.backup
bash-4.2$
bash-4.2$ barman list-backup pgserver11
WARNING: No backup strategy set for server 'pgserver11' (using default 'exclusive_backup').
WARNING: The default backup strategy will change to 'concurrent_backup' in the future. Explicitly set 'backup_options' to silence this warning.
pgserver11 20190724T192510 - Wed Jul 24 19:27:22 2019 - Size: 67.2 MiB - WAL Size: 0 B
bash-4.2$
Ref : https://blog.dbi-services.com/postgresql-barman-rsync-method-vs-streaming-method/
bash-4.2$ barman list-server
WARNING: No backup strategy set for server 'pgserver11' (using default 'exclusive_backup').
WARNING: The default backup strategy will change to 'concurrent_backup' in the future. Explicitly set 'backup_options' to silence this warning.
pgserver11 - Main DB Server
bash-4.2$
Note ; If you configured server side files under /etc/barmand.d then those will also be listed along with backup servers.
barman show-backup pgserver11 latest
WARNING: No backup strategy set for server 'pgserver11' (using default 'exclusive_backup').
WARNING: The default backup strategy will change to 'concurrent_backup' in the future. Explicit ly set 'backup_options' to silence this warning.
Backup 20190724T192510:
Server Name : pgserver11
Status : DONE
PostgreSQL Version : 110004
PGDATA directory : /var/lib/pgsql/11/data
Base backup information:
Disk usage : 67.2 MiB (67.2 MiB with WALs)
Incremental size : 67.2 MiB (-0.00%)
Timeline : 1
Begin WAL : 000000010000000000000007
End WAL : 000000010000000000000007
WAL number : 1
WAL compression ratio: 99.90%
Begin time : 2019-07-24 19:25:10.775357+00:00
End time : 2019-07-24 19:27:22.299868+00:00
Copy time : 6 seconds + 1 second startup
Estimated throughput : 9.6 MiB/s
Begin Offset : 264
End Offset : 528
Begin LSN : 0/7000108
End LSN : 0/7000210
WAL information:
No of files : 1
Disk usage : 23.2 KiB
WAL rate : 1.31/hour
Compression ratio : 99.86%
Last available : 000000010000000000000008
Catalog information:
Retention Policy : VALID
Previous Backup : - (this is the oldest base backup)
Next Backup : - (this is the latest base backup)
bash-4.2$
How to do incremental backup ?
bash-4.2$ barman backup --reuse=link pgserver11
WARNING: No backup strategy set for server 'pgserver11' (using default 'exclusive_backup').
WARNING: The default backup strategy will change to 'concurrent_backup' in the future. Explicitly set 'backup_options' to silence this warning.
Starting backup using rsync-exclusive method for server pgserver11 in /var/lib/barman/pgserver11/base/20190725T154912
Backup start at LSN: 0/41000028 (000000010000000000000041, 00000028)
Starting backup copy via rsync/SSH for 20190725T154912
Copy done (time: 27 seconds)
Asking PostgreSQL server to finalize the backup.
Backup size: 343.5 MiB. Actual size on disk: 313.4 MiB (-8.75% deduplication ratio).
Backup end at LSN: 0/41000168 (000000010000000000000041, 00000168)
Backup completed (start time: 2019-07-25 15:51:42.156606, elapsed time: 29 seconds)
Processing xlog segments from file archival for pgserver11
000000010000000000000041
000000010000000000000041.00000028.backup
Processing xlog segments from streaming for pgserver11
000000010000000000000041
How to restore database upto time :
bash-4.2$ barman recover --remote-ssh-command "ssh postgres@pg2" --target-time="2019-07-25 17:57:10.935270+00:00" pgserver11 20190725T175357 /var/lib/pgsql/11/data
WARNING: No backup strategy set for server 'pgserver11' (using default 'exclusive_backup').
WARNING: The default backup strategy will change to 'concurrent_backup' in the future. Explicitly set 'backup_options' to silence this warning.
Starting remote restore for server pgserver11 using backup 20190725T175357
Destination directory: /var/lib/pgsql/11/data
Remote command: ssh postgres@pg2
Doing PITR. Recovery target time: '2019-07-25 17:57:10.935270+00: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 222: archive_command = false
Recovery completed (start time: 2019-07-26 02:34:56.325657, elapsed time: 59 seconds)
Note : On Target Machine, make sure to check postgersql.conf for listener restriction..if any make it '*' and also check other non related parameters.
NOTE : It does not support restore of a single database nor table or tablespace..it supports full or PITR only and managing multiple ..and easy handling.
https://www.youtube.com/watch?v=kH3XKCbwsHU
Comments
Post a Comment