on the previous article, i was show how do backup with barman. For this section is time to simulate restore with barman.

Assumed i have database backup_barman with 2621440 number of row and then suddenly the server master and slave are down, for recovery need restore latest backup to new server.

Next go to the simulation

Simulation

stop service postgresql on master and slave

$ /opt/pgsql-9.5/bin/pg_ctl -D /home/postgres/project/data/ stop

At New server

Make sure from Barman server user barman can connect ssh passwordless to new server postgresql

Create directory data for postgresql

$ mkdir /home/postgres/project/data

$ chmod -R 700 /home/postgres/project/data

At Barman Server

Show backup from postgres master and choose latest backup

$ barman list-backup master
master 20170731T170253 - Mon Jul 31 17:03:20 2017 - Size: 118.2 MiB - WAL Size: 0 B
master 20170731T144406 - Mon Jul 31 14:44:33 2017 - Size: 27.6 MiB - WAL Size: 31.8 MiB

Show detail information of latest backup

$ barman show-backup master 20170731T170253
Backup 20170731T170253:
  Server Name            : master
  Status                 : DONE
  PostgreSQL Version     : 90506
  PGDATA directory       : /home/postgres/project/data

  Base backup information:
    Disk usage           : 118.2 MiB (118.2 MiB with WALs)
    Incremental size     : 118.2 MiB (-0.00%)
    Timeline             : 1
    Begin WAL            : 000000010000000000000015
    End WAL              : 000000010000000000000015
    WAL number           : 1
    WAL compression ratio: 99.84%
    Begin time           : 2017-07-31 10:03:18+00:00
    End time             : 2017-07-31 17:03:20.340676+07:00
    Copy time            : 2 seconds
    Estimated throughput : 45.7 MiB/s
    Begin Offset         : 40
    End Offset           : 0
    Begin XLOG           : 0/15000028
    End XLOG             : 0/16000000

  WAL information:
    No of files          : 1
    Disk usage           : 26.7 KiB
    WAL rate             : 37.04/hour
    Compression ratio    : 99.84%
    Last available       : 000000010000000000000016

  Catalog information:
    Retention Policy     : not enforced
    Previous Backup      : 20170731T144406
    Next Backup          : - (this is the latest base backup)

After that you can do restore database to new server using the command

$ barman recover --target-time "Begin time" --remote-ssh-command "ssh postgres@new_server" name_server backup_id Destination_dir

$ barman recover --target-time "2017-07-31 10:03:18+00:00" --remote-ssh-command "ssh postgres@192.168.34.188" master 20170731T170253 /home/postgres/project/data/
Starting remote restore for server master using backup 20170731T170253
Destination directory: /home/postgres/project/data/
Doing PITR. Recovery target time: '2017-07-31 10:03:18+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 60: archive_command = false

Your PostgreSQL server has been successfully prepared for recovery!

At New Server

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

$ ls -al
total 152
drwx------ 19 postgres postgres  4096 Aug  1 13:43 .
drwxr-xr-x  4 postgres postgres  4096 Jul 31 17:09 ..
-rw-------  1 postgres postgres   208 Jul 31 17:02 backup_label.old
-rw-r--r--  1 postgres postgres   679 Jul 31 17:02 .barman-recover.info
drwx------  7 postgres postgres  4096 Aug  1 14:27 base
drwx------  2 postgres postgres  4096 Aug  1 13:44 global
drwx------  2 postgres postgres  4096 Jul 31 17:02 pg_clog
drwx------  2 postgres postgres  4096 Jul 31 17:02 pg_commit_ts
drwx------  2 postgres postgres  4096 Jul 31 17:02 pg_dynshmem
-rw-------  1 postgres postgres  4662 Jul 31 17:02 pg_hba.conf
-rw-------  1 postgres postgres  1636 Jul 31 17:02 pg_ident.conf
drwx------  4 postgres postgres  4096 Aug  1 18:27 pg_logical
drwx------  4 postgres postgres  4096 Jul 31 17:02 pg_multixact
drwx------  2 postgres postgres  4096 Aug  1 13:43 pg_notify
drwx------  4 postgres postgres  4096 Aug  1 13:58 pg_replslot
drwx------  2 postgres postgres  4096 Jul 31 17:02 pg_serial
drwx------  2 postgres postgres  4096 Jul 31 17:02 pg_snapshots
drwx------  2 postgres postgres  4096 Aug  1 13:43 pg_stat
drwx------  2 postgres postgres  4096 Aug  1 18:27 pg_stat_tmp
drwx------  2 postgres postgres  4096 Jul 31 17:02 pg_subtrans
drwx------  2 postgres postgres  4096 Jul 31 17:02 pg_tblspc
drwx------  2 postgres postgres  4096 Jul 31 17:02 pg_twophase
-rw-------  1 postgres postgres     4 Jul 31 17:02 PG_VERSION
drwx------  3 postgres postgres  4096 Aug  1 16:34 pg_xlog
-rw-------  1 postgres postgres    88 Jul 31 17:13 postgresql.auto.conf
-rw-------  1 postgres postgres    88 Jul 31 17:02 postgresql.auto.conf.origin
-rw-r--r--  1 postgres postgres  2057 Aug  1 13:43 postgresql.conf
-rw-------  1 postgres postgres 21265 Jul 31 17:02 postgresql.conf-orig
-rw-r--r--  1 postgres postgres  2033 Jul 31 17:02 postgresql.conf.origin
-rw-------  1 postgres postgres    63 Aug  1 13:43 postmaster.opts
-rw-------  1 postgres postgres    83 Aug  1 13:43 postmaster.pid
-rw-r--r--  1 postgres postgres   136 Jul 31 17:12 recovery.done

Login to postgresql and connect to database backup_barman, count the table test01

backup_barman=# select count(*) from test01 ;
  count  
---------
 2621440
(1 row)

Restore finish

CMIIW

Advertisements