I had to deal with this question, or some version of it, quite a few times. So, decided to write a summary on what one could (or should) do, after data is in database, and application is running. Namely – setup some kind of replication and backups.
What to use, how, and why? This is what this post is all about.
Continue reading I have PostgreSQL, loaded some data, and have app using it. Now what?
Just thought I'll share a “fun" story. Friend reported weird bug – OmniPITR reported that xlogs are sent to archive, but they actually weren't.
After some checking we found out that he was giving custom rsync-path (–rsync-path – path to rsync program) – and the path was broken.
In this case – OmniPITR was not reporting error, and quite happily was working under assumption that it works OK.
Otto Bretz reported bug in OmniPITR.
The bug was that when using -dr (remote destinations for backups) – you couldn't use –skip-xlogs. Obvious overlook on my side.
Fix was trivial, and so 1.3.2 version was born.
On 12th of December, Simon Riggs committed patch:
Allow time delayed standbys and recovery Set min_recovery_apply_delay to force a delay in recovery apply for commit and restore point WAL records. Other records are replayed immediately. Delay is measured between WAL record time and local standby time. Robert Haas, Fabrízio de Royes Mello and Simon Riggs Detailed review by Mitsumasa Kondo
Continue reading Waiting for 9.4 – Allow time delayed standbys and recovery
Right after releasing 1.3.0 I realized that I forgot about one thing.
If you're using ext3 (and possibly other, not sure) file system, removal of large file can cause problems due to heavy IO traffic.
We did hit this problem earlier at one of client sites, and devised a way to remove large files by truncating them, bit after bit, and getting them to small enough size to be removed in one go. I wrote about it earlier, of course.
Unfortunately – I forgot about this when releasing 1.3.0, but as soon as I tried to deploy at the client site, I noticed the missing functionality.
So, today I released 1.3.1, which adds two options to omnipitr-backup-cleanup:
If truncate is specified, and is more than 0, it will cause omnipitr-backup-slave to remove large files (larger than truncate value) in steps.
In pseudocode:
if param('truncate') {
file_size = file_to_be_removed.size()
while ( file_size > param('truncate') ) {
file_size = file_size - param('truncate')
file_to_be_removed.truncate_to( file_size )
sleep( param('sleep') )
}
}
file_to_be_removed.unlink()So, for example, specifying –truncate=1000000, will remove the file truncating it first by 1MB blocks.
–sleep parameter is used to delay removal of next part of the file (it's used only in truncating loop, so has no meaning when truncate-loop is not used). It's value is in milliseconds, and defaults to 500 (0.5 second).
Hope you'll find it useful.
I just now committed new version of OmniPITR.
You can download it from:
The important change about 1.3.0 is that there is new tool – omnipitr-backup-cleanup. What is it for?
Title: OmniPITR v1.2.0 released
It's been a while since last release, but the new one finally arrived, and has some pretty cool goodies 🙂
Finally, after all these years, version 1.0.0 of OmniPITR got Released.
The reason I went to 1.0.0, and not 0.8.0 is very simple – finally, all programs in bin/ actually work 🙂
By that I mean: since beginning there was “omnipitr-monitor" – which simply didn't work, because work on it was always postponed. But now, it does. It's functionality is not all that great now, but it works, checks some basic data about replication, and can be used in production.
Now, there is still a todo but these things are less important.
I have to say that writing, and maintaining OmniPITR taught me a lot about PostgreSQL – how it works, and what really WAL is. It was really cool.
Just released new version of OmniPITR.
This version has one important new feature: when you're calling omnipitr-backup-slave, it will make backups only of required xlog files, and not, as previously, of all in walarchive directory.
This is important, especially in case you have multiple slaves, or you keep shared long-term walarchive. Previously – backups would get all files from walarchive (-s option to omnipitr-backup-slave), but now, it picks just the ones that are needed.
On somehow related note – I will be working now, finally, to get omnipitr-monitor functionality working.