Re: Suggestion on Backup

Thank you Gabriele & all for your suggestions. 
I'm going to review them.

-----Original Message-----
From: Gabriele Bartolini [mailto:Gabriele.Bartolini@2ndQuadrant.it] 
Sent: Wednesday, October 16, 2013 2:51 AM
To: Jeni Fifrick
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Suggestion on Backup

 Hi Jen,

 On Tue, 15 Oct 2013 21:09:50 +0000, Jeni Fifrick <jfifrick@incomm.com>
 wrote:
> We're having Master & Slave Postgres 9.1.3 on CentOS - using Streaming 
> Replication, which serve as a read-only database, and as 'backup' for 
> the production database.

 Sure.

> I'm trying to setup a process that can be used to have the continuous 
> backup of the production, to make sure if some problems happen on the 
> production server, we won't loss the data.

 It is important that you understand though that such an architecture  (with just replication) won't completely protect
youfrom disasters  happening on the master. I am referring to data loss caused by  undeliberate DELETE or DROP
operations,for example, or SQL injections. 
 
 Especially if you have a streaming replicated standby, data that  disappears on the master will disappear from the
standbyimmediately  after.
 

 Physical backup and continuous archiving will add this kind of  protection to your system. Using 9.1 unfortunately you
can'tbenefit  from cascading replication using the streaming protocol (introduced in  9.2), however you can look into
thepg_basebackup utility for this  purpose.
 

 Otherwise, there are tools out there that help you achieving these  goals, such as OmniPITR
(https://github.com/omniti-labs/omnipitr),
 pg-rman (http://code.google.com/p/pg-rman/), WAL-E  (https://github.com/heroku/WAL-E, mainly for Amazon AWS users) and
barman (http://www.pgbarman.org/) - to name a few.
 

 Being one of the developers, I can definitely recommend Barman. It does  not yet support streaming replication, but
usuallywith WAL file  shipping you can still achieve very good results in terms of Recovery  Point Objective (which
measuresdata loss). It is very easy to setup,  and already has packages for CentOS.
 

 I hope this helps.

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