How to barman recover with --target-time ?

[Raphaelk]

Hello !
I'm using barman 2.1 to backup all my postgresql 9.4 instance. It set up the streaming log for each off my postgresql server :

wal_level = 'hot_standby'
max_wal_senders = 5      
checkpoint_segments = 10  
max_replication_slots = 5

On barman side, each postgresql server settings look like that :

conninfo = host=server_fqdn user=barman dbname=postgres
streaming_conninfo = host=server_fqdn user=streaming_barman
backup_method = postgres
streaming_archiver = on
slot_name = barman

The configuration looks fine as barman check server_name command returns this

barman check server_name
Server server_name:
        PostgreSQL: OK
        superuser: OK
        PostgreSQL streaming: OK
        wal_level: OK
        replication slot: OK
        directories: OK
        retention policy settings: OK
        backup maximum age: OK (interval provided: 1 day, latest backup age: 11 hours, 16 minutes, 18 seconds)
        compression settings: OK
        failed backups: OK (there are 0 failed backups)
        minimum redundancy requirements: OK (have 42 backups, expected at least 10)
        pg_basebackup: OK
        pg_basebackup compatible: OK
        pg_basebackup supports tablespaces mapping: OK
        pg_receivexlog: OK
        pg_receivexlog compatible: OK
        receive-wal running: OK
        archiver errors: OK

The last backup of the server looks like :

barman list-backup server_name
server_name 20171120T030204 - Mon Nov 20 01:05:08 2017 - Size: 83.1 MiB - WAL Size: 0 B

On barman server, the streaming replication-status is :

barman replication-status server_name
Status of streaming clients for server 'server_name':                                                
  Current xlog location on master: 2/81452278                                                        
  Number of streaming clients: 1                                                                    
                                                                                                     
  1. Async WAL streamer
     Application name: barman_receive_wal
     Sync stage      : 3/3 Remote write
     Communication   : TCP/IP
     IP Address      : X.X.X.X / Port: 51629 / Host: -
     User name       : streaming_barman
     Current state   : streaming (async)
     WAL sender PID  : 3459
     Started at      : 2017-10-17 08:43:01.732576+02:00
     Sent location   : 2/81452278 (diff: 0 B)
     Write location  : 2/81452278 (diff: 0 B)
     Flush location  : 2/810E3D80 (diff: -3.4 MiB)

On the postgresql database, I've updated some row at 09:30 am

I try to restore the backup on another postgresql server, with this command

barman recover --remote-ssh-command "ssh postgres@IP_OF_TARGET" server_name 20171120T030204 /var/lib/postgresql/9.4/main --target-time '2017-11-21 10:00:00'


After restarting the postgresql remote server, the restaured instance doesn't have the row I've updated few minutes before.

What do I miss ?

Best regards,

Raphael.

[Raphaelk]

I've run some new test and it seems you can use the --target-time option only with full "n-1" backup. With the latest backup, you cant. It is quite strange, I may have missed a configuration.

How to achieve the latest recovery using the latest backup and the latest wal ?



[Amarnath Gopalan]

target-time option can be used with latest backup as well. See below test I did few months back !!

DB server : it-postgresql-sing-01v

Barman Server : it-barman-sing-01v

cluster restored to :  /usr/local/pgsql/data-restore

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

 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

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

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

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)

--
--
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 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.
For more options, visit https://groups.google.com/d/optout.

[Raphaelk]

Hi Amarnath,
Thx for your answer.

What I tried (and that didn t work) was :
1/ Proceed to backup
2/ wait to few minutes
3/ Update some rows // these change are streamed to the barman server
4/ wait to few minutes
5/ Restore database with the target-time "after the update row" command to a new postgresql instance

I was expecting to have the database recovered with all the rows updated, like in point 3/ but the database was just as it was after the backup, on point 1/

If I do this :

1/ Proceed to backup
2/ wait to few minutes
3/ Update some rows // these change are streamed to the barman server
4/ wait to few minutes
5/ Proceed to backup
6/ Restore database with the target-time "after the update row" command to a new postgresql instance

I have the database as it was in point 3, after the updated row.

It seems that the current wals are not "commited" or replayed if you restore from the latest backup. Maybe it is the expected behaviour, but I wonder how to proceed in case of real hardware failure, (without having a hot spare)

In this post https://groups.google.com/forum/#!topic/pgbarman/iKGj-5ACK5w there is a solution of manually copied the current wals before restarting the remote instance. Is this the way to do the latest recovery ?

pgbarman+u...@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 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+u...@googlegroups.com.

[Gabriele Bartolini]

Hi Raphael,

  before other people in this mailing list get confused, I want to state a couple of points.

  First, you need to make sure you always have the latest version of Barman (currently 2.3).

  Second, I suggest you become more familiar with how PostgreSQL WAL files and base backup work. You can certainly restore from the latest available backup, which is actually the most common way to recover. Obviously, the problem you are seeing is that your database has a very very low workload which does not fill the 16MB of transactional data to close the WAL.

   There are some tricks you can use here: one is to define archive_timeout in PostgreSQL (https://www.postgresql.org/docs/current/static/runtime-config-wal.html). The second one is to look at the "barman switch-wal" command.

Cheers,

Gabriele

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 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.

For more options, visit https://groups.google.com/d/optout.

--

 Gabriele Bartolini - 2ndQuadrant Italia
 PostgreSQL Training, Services and Support
 www.2ndQuadrant.it

[Amarnath Gopalan]

Hi Gabriele , 

 Raphaelk is using streaming replication (async) ,in that case the transaction should have been propagated to barman server even if the WAL file segment is not full ? May be the issue is streaming replication / recovery process has some lag ? 

Raphaelk can you check the i/o utilization and also see if tuning shared_buffers helps . Please go through this link  https://dba.stackexchange.com/questions/62831/postgres-streaming-replication-lagging-using-lots-of-cpu-and-little-i-o Thanks, Amar

[Gabriele Bartolini]

Hi Amar,

2017-11-22 14:57 GMT+11:00 Amarnath Gopalan <insidea...@gmail.com>:

 Raphaelk is using streaming replication (async) ,in that case the transaction should have been propagated to barman server even if the WAL file segment is not full ?

Until the WAL file is finalised by the server, it is stored in a partial file which is invisible to Barman's interface currently (you manually need to fetch it from the streaming folder).

What I suggested is still valid: use switch-wal and/or archive_timeout

Cheers,

Gabriele

[Amarnath Gopalan]

Got it , thanks for the clarification. 

Regards,

Amar

--

[Raphaelk]

Hi !
Thx for your answers, I get it now ! I will look at the switch-wal option !
I will keep you updated !

Le mercredi 22 novembre 2017 05:33:00 UTC+1, Amarnath Gopalan a écrit :

Got it , thanks for the clarification. 

Regards,

Amar

On Wed, Nov 22, 2017 at 12:21 PM, Gabriele Bartolini <gabriele....@gmail.com> wrote:

Hi Amar,

2017-11-22 14:57 GMT+11:00 Amarnath Gopalan <insidea...@gmail.com>:

 Raphaelk is using streaming replication (async) ,in that case the transaction should have been propagated to barman server even if the WAL file segment is not full ?

Until the WAL file is finalised by the server, it is stored in a partial file which is invisible to Barman's interface currently (you manually need to fetch it from the streaming folder).

What I suggested is still valid: use switch-wal and/or archive_timeout

Cheers,

Gabriele

--
--
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+u...@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 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+u...@googlegroups.com.

[Yogesh Rathore]

Hi Gabriele, 

If we have to manually use switch-wal always in case of wal streaming also, Then what's the use of streaming replication. We are still not able to recover the content of latest wal file. So its same as wal archive method.  According to the documentation, wal streaming has an edge over wal archiving because we can achieve almost 0 RPO. But according to your comment, we cant achieve 0 RPO.

Please correct me if I am wrong.

Thanks, 

Yogesh Rathore

[Sam Zhao]

Hi Raphaelk,

I encounted similar issues as you did before. It was always not restored to a time point which it was supposed to be. Did you already have a workaround to fix this problem ?

Thanks.

Regards

Sam