How to get last WAL file for PITR

[No One]

I setup barman to do backups of a server and for the most part everything went smoothly. However I made a test by inserting 2 rows into a table 10 minutes apart, dropping the database, and then doing a restore with a restore point between the two queries. This was to test if the PITR is actually working. However the restore got neither of the last 2 inserts, my guess is that the last WAL file was not transferred to the backup server. The postgres documentation indicates that the WAL file is only transferred when it is 16 MB or after a checkpoint. Can anyone tell me what I have to do to make sure that I get that last transaction just before the time I want to recover?

Thanks,

Matt

[Gabriele Bartolini]

Hi Matt,

  that is hard to say. Until we support WAL shipping through streaming protocol, it is just an asynchronous operation and it is not guaranteed that the backup server has the last WAL file.

  What you can do on the server is to force closing the WAL file by issuing as superuser: "SELECT pg_switch_xlog()"

Cheers,

Gabriele

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

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

[No One]

That will work, possibly I can put that in a script.

Thanks for the super fast answer!

Matt

On Tuesday, October 7, 2014 5:08:49 PM UTC-4, Gabriele Bartolini wrote:

Hi Matt,

  that is hard to say. Until we support WAL shipping through streaming protocol, it is just an asynchronous operation and it is not guaranteed that the backup server has the last WAL file.

  What you can do on the server is to force closing the WAL file by issuing as superuser: "SELECT pg_switch_xlog()"

Cheers,

Gabriele

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

 gabriele.bartolini@2ndQuadrant.it | www.2ndQuadrant.it

[MP]

Workaround: backup pg_xlog directory before recovery, unshipped WALs are there.

Dne úterý, 7. října 2014 23:10:21 UTC+2 No One napsal(a):

[Amanda Simpson]

How do you restore those unshipped WALs?

Thank you,

Amanda

[Gabriele Bartolini]

Hi Amanda,

  as I said before, the only way we can reduce the amount of loss data is through streaming replication support. Until the WAL file is closed, it cannot be shipped. In cases of extreme disaster recovery, you can manually ship the last WAL file in the archive before recovery starts.

  Please let us know if you need more information.

Cheers,

Gabriele 

--
 Gabriele Bartolini - 2ndQuadrant Italia - Managing Director

 PostgreSQL Training, Services and Support

 gabriele....@2ndQuadrant.it | www.2ndQuadrant.it

--

[Amanda Simpson]

Hello Gabriele,

Thank you so much for explaining this to me. I did do some experiment on our test servers and found that barman will actually copy/restore the unshipped WAL files during "restoring from remote server." I did it twice so I think it is really doing that, or is this my imagination?

My spec is Ubuntu 14.04, Postgresql 9.1 and Barman 1.3.0, and this is what I did:

I modified database on the primary database server, made sure WAL files were generated but not yet shipped to Barman backup server, stopped postgres on primary server, then performed restore from Barman backup server. Barman restore the database plus the unshipped WAL files.

(I did turn on the immediate_checkpoint to true in barman.conf)

I also notice that Barman will also copy postgres.conf, pg_hba.conf and indent.conf to my /var/lib/postgresql/9.1/main. I do not have those conf files under my /var/lib/postgresql/9.1/main before restoring database. Those conf files are under /etc/postgresl/9.1/main. Would it be fine if I delete those suddenly appeared conf files under /var/lib/postgresql/9.1/main?

Thank you again,

Amanda

[Amanda Simpson]

Hi MP,

Have you ever tested your method? When you copy those unshipped WALs after recover, will they get shipped to Barman? And you can restore them using recover command?

Thanks,

Amanda

[MP]

Thats not Barman job, thats Postgresql.

http://www.postgresql.org/docs/9.4/static/continuous-archiving.html - paragraph 24.3.4, especially:
WAL segments that cannot be found in the archive will be sought in pg_xlog/; this allows use of recent un-archived segments. However, segments that are available from the archive will be used in preference to files in pg_xlog/.

So, you can copy those "barman unbackuped" WALs to "INCOMING" dir on Barman backup server. They will be backuped by Barman and Postgresql can recover from there. But i dont think, its good way to go (depends on archive_command, evading duplicity filenames on backup server). Better is copy those WALs to pg_xlog on recovering Postgresql server. They will be used (because doesnt exist on backup server) by Postgresql and if recovery done (they are anyway last WALs before server ready), Postgresql use those WALs to filling and after checkpoint occurs, they will be normally shipped by archive_command.

Dne čtvrtek 15. ledna 2015 22:43:06 UTC+1 Amanda Simpson napsal(a):