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 malicious action.

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 in the 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 omnipitr or pitrtools.

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

Cheers,
Steve

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.

On Fri, Mar 2, 2018 at 12:16 PM, Joshua D. Drake <jd@commandprompt.com>
wrote:

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

Thanks everyone !!

I think I have enough alternatives for this project. I'll start testing on
Monday..

have a good weekend
-g

On Fri, Mar 2, 2018 at 12:55 PM, David Steele <david@pgmasters.net> 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.

Vick,

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

On Fri, Mar 2, 2018 at 2:34 PM, Vick Khera <vivek@khera.org> wrote:

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 Mon, Mar 5, 2018 at 6:58 AM <raf@raf.org> wrote:

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.

AFAIK, btrfs is also not supported on RHEL

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.

How long back-in-time would you need to go? If it is few hours (e.g. you
know that you would be able to identify a malicious transaction with-in
2hrs period) then consider using time-delayed/lagging DR. It is a also
faster than restoring a backup and performing PITR.

There are backup tools available but under the hood they too just be
offering PITR.

Filesystem snapshot was suggested by someone else which is also very neat
and good, but needs other tools around the infrastructure.

File system snapshot on a robust system like ZFS. Rollback to any

snapshot

almost trivially, and view any snapshot trivially.

--

--

Best Regards,

*Sameer Kumar | Senior Solution Architect*

*ASHNIK PTE. LTD.*

36 Robinson Road, #14-04 City House, Singapore 068877

T: +65 6438 3504 | www.ashnik.com

Skype: sameer.ashnik | M: +65 8110 0350