Re: Decreasing the data loss after failover - Mailing list pgsql-admin

I'm not sure how you set Barman up, but it should have been backing up your WAL files as well as performing base backups of your data. Part of the Barman setup is configuring your archive_command. Wherever this points to is where your missing WAL files should have gone. If you don't have those WAL files, or know where Barman was saving them, then you may be out of luck on that missing data. If the built in restore commands did not bring things back up to the way you expected them, I'd take a closer look and make sure Barman was actually doing proper backups.

What version of PostgreSQL are you running? If you're on 9.0+, you should be using streaming replication instead of WAL shipping. Otherwise your slave will only ever be as caught up as fast as you can ship WAL files over. This was a severe limitation in older versions of Postgres that has been overcome for nearly 5 years now.

Also, if you're not running 9.0+, you're out of support and no longer receiving security and bug fix updates, which should be of great concern if you're handling financial data.

I would also encourage you to read up on how to manually perform backup & recovery with the tools that PostgreSQL comes with. Be sure you understand everything in this section of the PostgreSQL documentation (note this is for 9.4): http://www.postgresql.org/docs/9.4/interactive/backup.html. Pay particular attention to basebackups & wal archiving. Barman is a nice tool, but it really hides a lot of important steps that you need to know to understand how to perform recovery in a disaster situation. If you don't understand what it's doing under the hood, you'll very likely be in this predicament again because if its built in recovery script doesn't work, then you're left not knowing what to do.

--
Keith Fiske
Database Administrator
OmniTI Computer Consulting, Inc.
http://www.keithf4.com