Thread: Is there a continuous backup for pg ?

On 03/02/2018 11:05 AM, Gary M wrote:
> Hi,
>
> I have an unusual requirement for schema based, live backup of a 24/7 
> database processing 100K inserts/updates per hour. The data store is 
> around 100TB.
>
> The requirement is supporting an incremental backup of 10 minute 
> windows.  Replication is not considered backup from malicious action.
>
> Are there any best practices or solutions that can meet these 
> requirements ?

A cold standby using PITR?

JD

>
> b/r
> gary


-- 
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc

PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
*****     Unless otherwise stated, opinions are my own.   *****

> On Mar 2, 2018, at 11:05 AM, Gary M <garym@oedata.com> wrote:
>
> Hi,
>
> I have an unusual requirement for schema based, live backup of a 24/7 database processing 100K inserts/updates per
hour.The data store is around 100TB. 
>
> The requirement is supporting an incremental backup of 10 minute windows.  Replication is not considered backup from
maliciousaction. 
>
> Are there any best practices or solutions that can meet these requirements ?

Sounds almost like you're looking for point-in-time recovery, which will let you restore an entire cluster to any time
inthe past (if you can afford the storage), using physical replication. 

https://www.postgresql.org/docs/current/static/continuous-archiving.html

There are several sets of third-party tools that'll help with the setup, monitoring and other tooling. Look for
omnipitror pitrtools. 

(I'd guess you could build something similar that would let you do logical recovery by recording changesets from a
logicalreplication connection, but I don't know if anyone has put that together.) 

Cheers,
  Steve

Hi Gary,

On 3/2/18 2:05 PM, Gary M wrote:
> Hi,
> 
> I have an unusual requirement for schema based, live backup of a 24/7
> database processing 100K inserts/updates per hour. The data store is
> around 100TB.
> 
> The requirement is supporting an incremental backup of 10 minute
> windows.  Replication is not considered backup from malicious action.
> 
> Are there any best practices or solutions that can meet these
> requirements ? 
pgBackRest is specifically designed to handle very large clusters and
high WAL rates.  Backup, restore, and archiving can be run in parallel
to speed operations.  The new version 2 has been optimized to make
archive-push even faster than version 1 and we will be releasing an
optimized archive-get soon.

You would be best off achieving your 10-minute windows with daily
incremental backups and then recovery with PITR to the required time.
PITR allows you to specify any time for recovery.


Regards,
-- 
-David
david@pgmasters.net

Gary M wrote:

> Vick,
> 
> I would love to use ZFS, this project requires RHEL/SeLinux MLS. Without
> MLS and RH support, ZFS is a no-go.

btrfs could be an option. it does mostly the same things as zfs.

> On Fri, Mar 2, 2018 at 2:34 PM, Vick Khera <vivek@khera.org> wrote:
> 
> > On Fri, Mar 2, 2018 at 2:34 PM, Gary M <garym@oedata.com> wrote:
> >
> >> Thanks Josh,
> >>
> >> PITR is the option I was recommending. Project management diligence
> >> dictates I need at least one other option from a different recommending
> >> source, two other options optimally.
> >>
> >
> > File system snapshot on a robust system like ZFS. Rollback to any snapshot
> > almost trivially, and view any snapshot trivially.
> >