step by step barman bartender?
709 views
Skip to first unread message
Saulo Neiva
Nov 14, 2017, 11:07:02 AM11/14/17
to Barman, Backup and Recovery Manager for PostgreSQL
Is there a manual for who will install a backup system for the first time?
I do not have much knowledge of the backup system and would like a help from you to get a step by step to implement a backup system. Then I will be fine-tuning as I acquire the knowledge.
Amarnath Gopalan
Nov 17, 2017, 4:04:34 AM11/17/17
to Barman, Backup and Recovery Manager for PostgreSQL
Step 1 - Prerequisites
Build VM Barman Server : it-barman-sing-01v
Build VM PostgreSQL Server : it-postgresql-sing-01v
Install PostgreSQL 9.6 from source and configure HBA to accept connection from barman server.
Step 2 - Install Barman 2.1 from source or using yum install
Create a separate volume group and mount /barman this is to prevent root and barman sharing same volume group
[root@it-barman-sing-01v barman]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LV_ROOT 37G 4.8G 30G 14% /
devtmpfs 909M 0 909M 0% /dev
tmpfs 920M 0 920M 0% /dev/shm
tmpfs 920M 17M 904M 2% /run
tmpfs 920M 0 920M 0% /sys/fs/cgroup
/dev/sda1 976M 155M 754M 18% /boot
tmpfs 184M 0 184M 0% /run/user/12057
172.28.67.41:/las/home/agopalan 4.0G 176M 3.9G 5% /lucas/ilm/home/agopalan
sww.jedi:/ifs/lucas/ilm/sww/rhel7 15T 77G 15T 1% /lucas/ilm/sww/rhel7
tmpfs 184M 0 184M 0% /run/user/0
dept.jedi:/ifs/lucas/ilm/dept/sio 17T 7.9T 8.7T 48% /lucas/ilm/dept/sio
/dev/mapper/VolGroupBkp00-LV_BARMAN 300G 33M 300G 1% /barman
Folder Structure
Base Folder : /barman
Software Repository : /barman/software/version-no
Binaries : /barman/barman-2.1
Barman Home : /barman/backup
Download the barman(https://sourceforge.net/projects/pgbarman/files/) and extract in the required folder.
cp /dept/sio/tmp/barman/barman-2.1.tar.gz /barman/software/2.1/
chown barman. /barman/software/2.1/barman-2.1.tar.gz
cp /dept/sio/tmp/barman/barman-2.1.tar.gz /barman/
chown barman. barman-2.1.tar.gz
tar tzvfp barman-2.1.tar.gz
tar xzvfp barman-2.1.tar.gz
rm -rf /barman/barman-2.1.tar.gz
chown -R barman. barman-2.1
Build(as barman user) using setup.py
bash-4.2$ uname -a
Linux it-barman-sing-01v 3.10.0-514.10.2.el7.x86_64 #1 SMP Fri Mar 3 00:04:05 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
bash-4.2$ id
uid=1000(barman) gid=1000(barman) groups=1000(barman)
bash-4.2$ pwd
/barman/barman-2.1
-bash-4.2$ ./setup.py build
running build
running build_py
creating build
creating build/lib
creating build/lib/barman
copying barman/exceptions.py -> build/lib/barman
...
...
running build_scripts
creating build/scripts-2.7
copying and adjusting bin/barman -> build/scripts-2.7
changing mode of build/scripts-2.7/barman from 664 to 775
As root run the install using setup.py
[root@it-barman-sing-01v barman-2.1]# ./setup.py install
running install
running bdist_egg
running egg_info
...
...
...
Using /usr/lib64/python2.7/site-packages
Finished processing dependencies for barman==2.1
bash-4.2$ barman --version
2.1
Barman by 2ndQuadrant (www.2ndQuadrant.com)
Step 3 - Setup SSH trust between postgresql and barman
#barman user should able to connect remotely to it-barman-sing-01v from it-postgresql-sing-01v.
#postgres user should able to connect remotely to it-postgresql-sing-01v from it-barman-sing-01v.
i.e. [from:postgres@it-postgresql-sing-01v] SSH barman@it-barman-sing-01v
#step 1
[root@it-postgresql-sing-01v base]# su - postgres
Last login: Fri Apr 7 18:11:53 +08 2017 on pts/1
-bash-4.2$ pwd
/var/lib/pgsql
-bash-4.2$ ssh
ssh ssh-add ssh-agent ssh-copy-id sshd sshd-keygen sshfs ssh-keygen ssh-keyscan
-bash-4.2$ ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/var/lib/pgsql/.ssh/id_rsa):
Created directory '/var/lib/pgsql/.ssh'.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /var/lib/pgsql/.ssh/id_rsa.
Your public key has been saved in /var/lib/pgsql/.ssh/id_rsa.pub.
The key fingerprint is:
8c:eb:d4:0f:0b:4a:57:ac:55:63:99:6d:22:79:a5:3c postgres@it-postgresql-sing-01v
The key's randomart image is:
+--[ RSA 2048]----+
| . |
| o * |
| o E o |
| + = = |
| . S |
| * |
| . * o |
| . = . + |
| . . . . |
+-----------------+
-bash-4.2$ ls -ltr /var/lib/pgsql/.ssh/
total 8
-rw-r--r-- 1 postgres postgres 413 Apr 7 18:14 id_rsa.pub
-rw------- 1 postgres postgres 1675 Apr 7 18:14 id_rsa
#step 2
-bash-4.2$ ssh-copy-id -i /var/lib/pgsql/.ssh/id_rsa.pub barman@it-barman-sing-01v
The authenticity of host 'it-barman-sing-01v (172.28.83.25)' can't be established.
ECDSA key fingerprint is 8f:db:62:ea:8c:29:44:c2:ab:bf:59:ed:f8:e0:ad:5e.
Are you sure you want to continue connecting (yes/no)? yes
/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
barman@it-barman-sing-01v's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh 'barman@it-barman-sing-01v'"
and check to make sure that only the key(s) you wanted were added.
#Testing
From it-postgresql-sing-01v
-bash-4.2$ uname -a
Linux it-postgresql-sing-01v 3.10.0-514.10.2.el7.x86_64 #1 SMP Fri Mar 3 00:04:05 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
-bash-4.2$ ssh barman@it-barman-sing-01v
Last login: Mon Apr 10 19:08:12 2017
-bash-4.2$ uname -a
Linux it-barman-sing-01v 3.10.0-514.10.2.el7.x86_64 #1 SMP Fri Mar 3 00:04:05 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
#Next
ssh postgres@it-postgresql-sing-01v from it-barman-sing-01v
#Follow the above steps.
Step 4 - Configure barman.conf file.
# copy the template from /barman/barman-2.1/doc to /etc/barman.conf .Below parameter's are for global level configuration
[barman]
; System user
barman_user = barman
; Main directory
barman_home = /barman/backup
; 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 = true
basebackup_retry_times = 3
last_backup_maximum_age = 1 DAYS
Step 5 - Add server level configuration
#If needed can create a seperate configuration file for each postgres server else append /etc/barman.conf.Here we are taking backup in jedi under /dept/sio
[it-postgresql-sing-01v]
description = "it-postgresql-sing-01v"
ssh_command = ssh postgres@it-postgresql-sing-01v
conninfo = host=it-postgresql-sing-01v user=postgres
backup_directory = /dept/sio/barman_backup
retention_policy_mode = auto
retention_policy = RECOVERY WINDOW OF 7 days
wal_retention_policy = main
archiver = on
Step 6 - Configure $PGDATA/postgresql.conf
#check the incomming wals directory in barman server
bash-4.2$ barman show-server it-postgresql-sing-01v | grep incoming_wals
incoming_wals_directory: /dept/sio/barman_backup/it-postgresql-sing-01v/incoming
#Use the wal incoming directory to set the archive_command in postgresql.conf
listen_addresses = '*'
port = 5432
wal_level = 'archive'
archive_mode = on # enables archiving; off, on, or always
# (change requires restart)
archive_command = 'rsync -a %p barman@it-barman-sing-01v:/dept/sio/barman_backup/it-postgresql-sing-01v/incoming/%f' # command to use to archive a logfile segment
# placeholders: %p = path of file to archive
# %f = file name only
#Restart the postgres server
Step 7 - Testing ,Troubleshooting and Fixing error
#Issue 1 : After restarting postgresql wal files were not shipping to incoming directory.
FATAL: archive command failed with exit code 255
DETAIL: The failed archive command was: rsync -a pg_xlog/000000010000000600000025 barman@it-barman-sing-01v:/opt/barman/backup/it-postgresql-sing-01v/incoming/000000010000000600000025
LOG: archiver process (PID 1174) exited with exit code 1
#Fix : Had to manually create incoming directory , if we specify backup_directory in barman.conf ( else for default location the incoming directory is created automatically)
#Validate the setup configuration
-bash-4.2$ barman check it-postgresql-sing-01v
2017-04-10 20:38:44,184 [20990] barman.utils WARNING: Failed opening the requested log file. Using standard error instead.
2017-04-10 20:38:44,185 [20990] barman.config WARNING: Ignoring the "configuration_files_directory" option as "/etc/barman.d" is not a directory
Server it-postgresql-sing-01v:
2017-04-10 20:38:44,200 [20990] barman.server ERROR: Check 'WAL archive' failed for server 'it-postgresql-sing-01v'
WAL archive: FAILED (please make sure WAL shipping is setup)
PostgreSQL: OK
superuser: OK
wal_level: OK
directories: OK
retention policy settings: OK
2017-04-10 20:38:44,524 [20990] barman.server ERROR: Check 'backup maximum age' failed for server 'it-postgresql-sing-01v'
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
#Issue 2 :Error message is little confusing but issue is missing path
2017-04-10 20:38:44,184 [20990] barman.utils WARNING: Failed opening the requested log file. Using standard error instead.
#Fix : Log file directory is not present ,manually create the path for /var/log/barman/barman.log
#Issue 3 :WAL archive: FAILED (please make sure WAL shipping is setup)
#Fix : run barman cron and later need to put this in crontab
Starting WAL archiving for server it-postgresql-sing-01v
#Issue 4 :backup maximum age: FAILED (interval provided: 1 day, latest backup age: No available backups)
#Fix : No Fix is required this can be ignored , we getting this error as we dont have any current backup for this postgres server
#Recheck Again - Everything should be fine
-bash-4.2$ barman check it-postgresql-sing-01v
Server it-postgresql-sing-01v:
PostgreSQL: OK
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
#Validate backup directories
-bash-4.2$ cd /dept/sio/barman_backup/it-postgresql-sing-01v/
-bash-4.2$ ls -ltr
total 13
drwxrwxr-x 2 barman barman 0 Apr 10 20:38 base
drwxrwxr-x 2 barman barman 0 Apr 10 20:38 errors
drwxrwxr-x 2 barman barman 0 Apr 10 20:38 streaming
drwxrwxr-x 3 barman barman 59 Apr 10 20:50 wals
drwxrwxr-x 2 barman barman 0 Apr 10 20:50 incoming
#Test if wal files are getting shipped from postgresql server to barman server
#From it-postgresql-sing-01v ( perform swith log file )
#The current log ending with *0028 needs to be shipped to it-barman-sing-01v
/usr/local/pgsql/data/pg_xlog
-bash-4.2$ ls -ltr
total 147460
-rw------- 1 postgres postgres 16777216 Apr 9 15:47 000000010000000600000029
-rw------- 1 postgres postgres 16777216 Apr 9 15:47 00000001000000060000002E
-rw------- 1 postgres postgres 16777216 Apr 9 15:47 00000001000000060000002A
-rw------- 1 postgres postgres 16777216 Apr 9 15:47 00000001000000060000002B
-rw------- 1 postgres postgres 16777216 Apr 9 15:47 00000001000000060000002C
-rw------- 1 postgres postgres 16777216 Apr 9 15:47 000000010000000600000030
-rw------- 1 postgres postgres 16777216 Apr 9 15:47 00000001000000060000002F
-rw------- 1 postgres postgres 16777216 Apr 9 15:47 00000001000000060000002D
-rw------- 1 postgres postgres 339 Apr 9 16:32 000000010000000600000025.00000028.backup
drwx------ 2 postgres postgres 59 Apr 10 20:40 archive_status
-rw------- 1 postgres postgres 16777216 Apr 11 13:21 000000010000000600000028 <------------- Current wal file
do switch log file
pocdb=# select pg_switch_xlog() ;
pg_switch_xlog
----------------
6/2800B300
(1 row)
bash-4.2$ ls -ltr
/usr/local/pgsql/data/pg_xlog
total 147460
-rw------- 1 postgres postgres 16777216 Apr 9 15:47 00000001000000060000002E
-rw------- 1 postgres postgres 16777216 Apr 9 15:47 00000001000000060000002A
-rw------- 1 postgres postgres 16777216 Apr 9 15:47 00000001000000060000002B
-rw------- 1 postgres postgres 16777216 Apr 9 15:47 00000001000000060000002C
-rw------- 1 postgres postgres 16777216 Apr 9 15:47 000000010000000600000030
-rw------- 1 postgres postgres 16777216 Apr 9 15:47 00000001000000060000002F
-rw------- 1 postgres postgres 16777216 Apr 9 15:47 00000001000000060000002D
-rw------- 1 postgres postgres 339 Apr 9 16:32 000000010000000600000025.00000028.backup
-rw------- 1 postgres postgres 16777216 Apr 11 13:34 000000010000000600000028
-rw------- 1 postgres postgres 16777216 Apr 11 13:34 000000010000000600000029 <---------New current wal file
drwx------ 2 postgres postgres 96 Apr 11 13:34 archive_status
#Check if the log file is shipped to it-barman-sing-01v
-bash-4.2$ uname -a
Linux it-barman-sing-01v 3.10.0-514.10.2.el7.x86_64 #1 SMP Fri Mar 3 00:04:05 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
-bash-4.2$ pwd
/dept/sio/barman_backup/it-postgresql-sing-01v/incoming
-bash-4.2$ ls -ltr
total 19906
-rw------- 1 barman barman 16777216 Apr 11 13:34 000000010000000600000028 <-------------WAL file shipped
Step 8 - Performing Backup and querying the catalog
#Base Backup
-bash-4.2$ barman backup it-postgresql-sing-01v
Starting backup using rsync-exclusive method for server it-postgresql-sing-01v in /dept/sio/barman_backup/it-postgresql-sing-01v/base/20170411T133949
Backup start at xlog location: 6/2A000028 (00000001000000060000002A, 00000028)
This is the first backup for server it-postgresql-sing-01v
WAL segments preceding the current backup have been found:
000000010000000600000025 from server it-postgresql-sing-01v has been removed
000000010000000600000026 from server it-postgresql-sing-01v has been removed
000000010000000600000027 from server it-postgresql-sing-01v has been removed
Copying files.
Copy done.
This is the first backup for server it-postgresql-sing-01v
Asking PostgreSQL server to finalize the backup.
Backup size: 13.8 GiB
Backup end at xlog location: 6/2A000130 (00000001000000060000002A, 00000130)
Backup completed
Processing xlog segments from file archival for it-postgresql-sing-01v
000000010000000600000028
000000010000000600000029
00000001000000060000002A
00000001000000060000002A.00000028.backup
#List the backup
-bash-4.2$ barman list-backup it-postgresql-sing-01v
it-postgresql-sing-01v 20170411T133949 - Tue Apr 11 13:41:46 2017 - Size: 13.8 GiB - WAL Size: 0 B
Step 9 - Perform db operation and take incremental backup
#From it-postgresql-sing-01v
pocdb=# create table fillup4( counter int) ;
CREATE TABLE
pocdb-# \dt
List of relations
Schema | Name | Type | Owner
--------+------------------+-------+----------
public | afterfirstbackup | table | postgres
public | fillup | table | postgres
public | fillup2 | table | postgres
public | fillup4 | table | postgres
public | testtable1 | table | postgres
public | testtable2 | table | postgres
(6 rows)
#From it-barman-sing-01v
-bash-4.2$ barman backup --reuse=link it-postgresql-sing-01v
Starting backup using rsync-exclusive method for server it-postgresql-sing-01v in /dept/sio/barman_backup/it-postgresql-sing-01v/base/20170411T135306
Backup start at xlog location: 6/2C000028 (00000001000000060000002C, 00000028)
Copying files.
Copy done.
Asking PostgreSQL server to finalize the backup.
Backup size: 31.5 MiB. Actual size on disk: 10.2 MiB (-67.72% deduplication ratio).
Backup end at xlog location: 6/2C0000F8 (00000001000000060000002C, 000000F8)
Backup completed
Processing xlog segments from file archival for it-postgresql-sing-01v
00000001000000060000002B
00000001000000060000002C
00000001000000060000002C.00000028.backup
#Notice the backup size
-bash-4.2$ barman list-backup it-postgresql-sing-01v
it-postgresql-sing-01v 20170411T135306 - Tue Apr 11 13:53:12 2017 - Size: 31.5 MiB - WAL Size: 0 B
it-postgresql-sing-01v 20170411T133949 - Tue Apr 11 13:41:46 2017 - Size: 13.8 GiB - WAL Size: 1.6 MiB
step 10 - Simulate Disaster (user error)
pocdb-# \dt
List of relations
Schema | Name | Type | Owner
--------+------------------+-------+----------
public | afterfirstbackup | table | postgres
public | fillup | table | postgres
public | fillup2 | table | postgres
public | fillup4 | table | postgres
public | testtable1 | table | postgres
public | testtable2 | table | postgres
(6 rows)
pocdb=# select count(*) from fillup2 ;
count
--------
100000
(1 row)
pocdb=# drop table fillup2 ;
DROP TABLE
pocdb=# select count(*) from fillup2 ;
ERROR: relation "fillup2" does not exist
LINE 1: select count(*) from fillup2 ;
#Boom Table is gone :(.No worries you have backup. Lets do point in time recovery
Step 11 - Perform : Point in time recovery/DR
#From it-barman-sing-01v
#Note : For testing purpose backup is restored to same server with different path ,however you can restore the backup to any other server as well .
#List the backup
bash-4.2$ barman list-backup it-postgresql-sing-01v
it-postgresql-sing-01v 20170411T135306 - Tue Apr 11 13:53:12 2017 - Size: 31.5 MiB - WAL Size: 0 B
it-postgresql-sing-01v 20170411T133949 - Tue Apr 11 13:41:46 2017 - Size: 13.8 GiB - WAL Size: 1.6 MiB
#Get More information of latest backup
bash-4.2$ barman show-backup it-postgresql-sing-01v latest
Backup 20170411T135306:
Server Name : it-postgresql-sing-01v
Status : DONE
PostgreSQL Version : 90602
PGDATA directory : /usr/local/pgsql/data
Base backup information:
Disk usage : 31.5 MiB (31.5 MiB with WALs)
Incremental size : 10.2 MiB (-67.72%)
Timeline : 1
Begin WAL : 00000001000000060000002C
End WAL : 00000001000000060000002C
WAL number : 1
WAL compression ratio: 99.84%
Begin time : 2017-04-11 13:53:06.035713+08:00
End time : 2017-04-11 13:53:12.280808+08:00
Begin Offset : 40
End Offset : 248
Begin XLOG : 6/2C000028
End XLOG : 6/2C0000F8
WAL information:
No of files : 0
Disk usage : 0 B
Last available : 00000001000000060000002C
Catalog information:
Retention Policy : VALID
Previous Backup : 20170411T133949
Next Backup : - (this is the latest base backup)
#Perform recovery
#Example : barman recover --target-time "providethetime" --remote-ssh-command "ssh postgres@remote-target-hostname" <servername from catalog> <restore location>
bash-4.2$ barman recover --target-time "2017-04-11 13:53:06.035713+08:00" --remote-ssh-command "ssh postgres@it-postgresql-sing-01v" it-postgresql-sing-01v 20170411T135306 /usr/local/pgsql/data-restore
Starting remote restore for server it-postgresql-sing-01v using backup 20170411T135306
Destination directory: /usr/local/pgsql/data-restore
Doing PITR. Recovery target time: '2017-04-11 13:53:06.035713+08: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 215: archive_command = false <------------- Take note of this ,you need to change this before starting the new restored database.
Your PostgreSQL server has been successfully prepared for recovery!
#From it-postgresql-sing-01v check the restored path
-bash-4.2$ pwd
/usr/local/pgsql/data-restore
-bash-4.2$ ls -ltr
total 152
-rw------- 1 postgres postgres 88 Apr 7 15:36 postgresql.auto.conf.origin
-rw------- 1 postgres postgres 4 Apr 7 15:36 PG_VERSION
drwx------ 2 postgres postgres 4096 Apr 7 15:36 pg_twophase
drwx------ 2 postgres postgres 4096 Apr 7 15:36 pg_tblspc
...
...
-rw------- 1 postgres postgres 22370 Apr 11 20:47 postgresql.conf
-rw------- 1 postgres postgres 88 Apr 11 20:47 postgresql.auto.conf
drwx------ 3 postgres postgres 4096 Apr 11 20:47 pg_xlog
#Stop and restart the postgres with restored data
-bash-4.2$ /opt/postgres/bin/9.6/bin/pg_ctl -D /usr/local/pgsql/data -l logfile stop
waiting for server to shut down.... done
server stopped
#Restart
/opt/postgres/bin/9.6/bin/pg_ctl -D /usr/local/pgsql/data-restore -l logfile start
#After restart got this error(Damn barman warned about it)
LOG: archive command failed with exit code 1
DETAIL: The failed archive command was: false
WARNING: archiving transaction log file "00000002.history" failed too many times, will try again later
#FIX:Barman commented the arhive_command after restore.
#BARMAN# archive_command = 'rsync -a %p barman@it-barman-sing-01v:/dept/sio/barman_backup/it-postgresql-sing-01v/incoming/%f' # command to use to archive a logfile segment
archive_command = false
#Uncomment the first one and comment the archive_command = false ,restart the database.
Step 12 - Verify the restored database
-bash-4.2$ ./psql
psql (9.6.2)
Type "help" for help.
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
pocdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 9993 kB | pg_default |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7343 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7233 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7233 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(4 rows)
postgres=# \c pocdb
You are now connected to database "pocdb" as user "postgres".
pocdb=# \dt
List of relations
Schema | Name | Type | Owner
--------+------------------+-------+----------
public | afterfirstbackup | table | postgres
public | fillup | table | postgres
public | fillup2 | table | postgres
public | fillup4 | table | postgres
public | testtable1 | table | postgres
public | testtable2 | table | postgres
(6 rows)
pocdb=# select count(*) from fillup2
pocdb-# ;
count
--------
100000
(1 row)
pocdb=# select setting from pg_settings where name = 'data_directory' ;
setting
-------------------------------
/usr/local/pgsql/data-restore
(1 row)
Saulo Neiva
Nov 18, 2017, 9:40:20 AM11/18/17
to pgba...@googlegroups.com
[root@server barman.d]# barman check server.icmbio.gov.br
Server server.local.br:
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: FAILED (Connection failed using 'ssh ro...@server.local.br -o BatchMode=yes -o StrictHostKeyChecking=no' return code 255)
not in recovery: OK
archiver errors: OK
--
--
You received this message because you are subscribed to the "Barman for PostgreSQL" group.
To post to this group, send email to pgba...@googlegroups.com
To unsubscribe from this group, send email to
pgbarman+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/pgbarman?hl=en?hl=en-GB
---
You received this message because you are subscribed to a topic in the Google Groups "Barman, Backup and Recovery Manager for PostgreSQL" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/pgbarman/FLcNzS6xMLA/unsubscribe.
To unsubscribe from this group and all its topics, send an email to pgbarman+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Saulo da Silva Neiva
Saulo Neiva
Nov 18, 2017, 9:41:16 AM11/18/17
to pgba...@googlegroups.com
[root@server barman.d]# barman check server.local.br
Server server.local.br:
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: FAILED (Connection failed using 'ssh ro...@server.local.br -o BatchMode=yes -o StrictHostKeyChecking=no' return code 255)
not in recovery: OK
archiver errors: OK
Amarnath Gopalan
Nov 20, 2017, 1:22:38 AM11/20/17
to Barman, Backup and Recovery Manager for PostgreSQL
First you need to fix the SSH
ssh: FAILED (Connection failed using 'ssh ro...@server.local.br -o BatchMode=yes -o StrictHostKeyChecking=no' return code 255)
you have to setup SSH connection between barman and postgres server ( Check step 3 )
Setup ssh between barman and postgres server and vise versa
i.e. [from:postgres@it-postgresql-sing-01v] SSH barman@it-barman-sing-01v and
[from:barman@it-barman-sing-01v] ssh postgres@it-postgresql-sing-01v
//step 1
Step 2
-bash-4.2$ ssh-copy-id -i /var/lib/pgsql/.ssh/id_rsa.pub barman@it-barman-sing-01v
The authenticity of host 'it-barman-sing-01v (172.28.83.25)' can't be established.
ECDSA key fingerprint is 01:4b:5e:c8:a9:77:08:e8:0d:a7:bf:9e:bc:7b:29:1f.
Are you sure you want to continue connecting (yes/no)? yes
/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
barman@it-barman-sing-01v's password:barman12345
Number of key(s) added: 1
Now try logging into the machine, with: "ssh 'barman@it-barman-sing-01v'"
and check to make sure that only the key(s) you wanted were added.
Testing
From it-postgresql-sing-01v
-bash-4.2$ uname -a
Linux it-postgresql-sing-01v 3.10.0-514.10.2.el7.x86_64 #1 SMP Fri Mar 3 00:04:05 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
-bash-4.2$ ssh barman@it-barman-sing-01v
Last login: Mon Apr 10 19:08:12 2017
-bash-4.2$ uname -a
Linux it-barman-sing-01v 3.10.0-514.10.2.el7.x86_64 #1 SMP Fri Mar 3 00:04:05 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
Next
ssh postgres@it-postgresql-sing-01v from it-barman-sing-01v
-bash-4.2$ uname -a
Linux it-barman-sing-01v 3.10.0-514.10.2.el7.x86_64 #1 SMP Fri Mar 3 00:04:05 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
-bash-4.2$ pwd
/barman/.ssh
-bash-4.2$ ls -al
total 4
drwx------ 2 barman barman 29 Apr 10 19:16 .
drwxr-xr-x 7 barman barman 102 Apr 10 19:16 ..
-rw------- 1 barman barman 413 Apr 10 19:16 authorized_keys
-bash-4.2$ ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/barman/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /barman/.ssh/id_rsa.
Your public key has been saved in /barman/.ssh/id_rsa.pub.
The key fingerprint is:
46:03:50:bc:c1:7e:78:fc:7f:c1:53:6e:e3:43:8d:ee barman@it-barman-sing-01v
The key's randomart image is:
+--[ RSA 2048]----+
| .=o |
| +. |
| . =o |
| +.+. .|
| oS. . +.|
| . . =o+|
| . .o+.|
| . oo |
| oE .|
+-----------------+
//Copy the id_rsa.pub key from it-barman-sing-01v to authorized_keys in it-postgresql-sing-01v
-bash-4.2$ cat /barman/.ssh/id_rsa.pub
ssh-rsa someAHSKSDJSDLSDJSDJSDJSDJSJDSDJSDjsdksdjksjdksjd barman@it-barman-sing-01v
-bash-4.2$ uname -a
Linux it-postgresql-sing-01v 3.10.0-514.10.2.el7.x86_64 #1 SMP Fri Mar 3 00:04:05 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
-bash-4.2$ pwd
/var/lib/pgsql/.ssh
-bash-4.2$ vi authorized_keys
Testing
From it-barman-sing-01v
-bash-4.2$ uname -a
Linux it-barman-sing-01v 3.10.0-514.10.2.el7.x86_64 #1 SMP Fri Mar 3 00:04:05 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
-bash-4.2$ ssh postgres@it-postgresql-sing-01v
Last login: Mon Apr 10 19:31:07 2017 from it-barman-sing-01v.lucasfilm.com
-bash-4.2$ uname -a
Linux it-postgresql-sing-01v 3.10.0-514.10.2.el7.x86_64 #1 SMP Fri Mar 3 00:04:05 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
PostgreSQL: FAILED -- Try connecting to postgresql database from barman server ( based on the conninfo set in barman.conf ) .
WAL archive: FAILED ( try executing barman cron and check status again ) ,
Also sometimes since there is not much activity in db , the wal files wont be generated much enough to be backed up by barman.Try manually
switching the log files in db server
select * from pg_switch_xlog() ; --9.6 and earlier
select * from pg_switch_wal() ; -- 10
if issue still persists ,Please paste the o/p of
barman show-server server.local.br
On Wednesday, November 15, 2017 at 12:07:02 AM UTC+8, Saulo Neiva wrote:
Saulo Neiva
Nov 20, 2017, 1:12:44 PM11/20/17
to pgba...@googlegroups.com
-bash-4.2$ barman check server.local.br
Server server.local.br:
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
Note:WAL archive: FAILED ( try executing barman cron and check status again )
Accomplished:
The same fault remains.
-bash-4.2$ barman show-server server.local.br
Server server.local.br:
active: True
archive_command: None
archive_mode: None
archiver: True
archiver_batch_size: 0
backup_directory: /teste
backup_method: rsync
backup_options: BackupOptions(['exclusive_backup'])
bandwidth_limit: None
barman_home: /teste
barman_lock_directory: /teste
basebackup_retry_sleep: 30
basebackup_retry_times: 3
basebackups_directory: /teste/base
check_timeout: 30
compression: gzip
conninfo: host=post...@10.0.0.88 user=postgres
current_xlog: None
custom_compression_filter: None
custom_decompression_filter: None
data_directory: None
description: server.local.br
disabled: False
errors_directory: /teste/errors
immediate_checkpoint: True
incoming_wals_directory: /teste/incoming
is_in_recovery: None
is_superuser: None
last_backup_maximum_age: 1 day (WARNING! latest backup is No available backups old)
max_incoming_wals_queue: None
minimum_redundancy: 0
msg_list: []
name: server.local.br
network_compression: False
parallel_jobs: 1
path_prefix: None
pgespresso_installed: None
post_archive_retry_script: None
post_archive_script: None
post_backup_retry_script: None
post_backup_script: None
pre_archive_retry_script: None
pre_archive_script: None
pre_backup_retry_script: None
pre_backup_script: None
recovery_options: RecoveryOptions([])
replication_slot: None
replication_slot_support: None
retention_policy: RECOVERY WINDOW OF 7 DAYS
retention_policy_mode: auto
reuse_backup: None
server_txt_version: None
slot_name: None
ssh_command: ssh post...@10.0.0.88
streaming_archiver: False
streaming_archiver_batch_size: 0
streaming_archiver_name: barman_receive_wal
streaming_backup_name: barman_streaming_backup
streaming_conninfo: host=post...@10.0.0.88 user=postgres
streaming_wals_directory: /teste/streaming
synchronous_standby_names: None
tablespace_bandwidth_limit: None
wal_retention_policy: MAIN
wals_directory: /teste/wals
--
--
You received this message because you are subscribed to the "Barman for PostgreSQL" group.
To post to this group, send email to pgba...@googlegroups.com
To unsubscribe from this group, send email to
pgbarman+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/pgbarman?hl=en?hl=en-GB
---
You received this message because you are subscribed to a topic in the Google Groups "Barman, Backup and Recovery Manager for PostgreSQL" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/pgbarman/FLcNzS6xMLA/unsubscribe.
To unsubscribe from this group and all its topics, send an email to pgbarman+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Amarnath Gopalan
Nov 20, 2017, 9:52:40 PM11/20/17
to pgba...@googlegroups.com
I guess you didnt follow the instruction which I shared with you previously .
Based on the o/p you sent
archive_command: None --- This needs to be set appropriately
archive_mode: None -- This should be on
Also you need to check pg_hba.conf in postgresql server , timebeing for testing put the below entry in pg_hba.conf later you need to change it appropriately .
host all all 0.0.0.0/0 trust
You received this message because you are subscribed to the Google Groups "Barman, Backup and Recovery Manager for PostgreSQL" group.
To unsubscribe from this group and stop receiving emails from it, send an email to pgbarman+unsubscribe@googlegroups.com.
Reply all
Reply to author
Forward
0 new messages