Обсуждение: Is there a continuous backup for pg ?
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 ?
b/r
gary
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
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:
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. *****
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:
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
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:
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.
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. > >
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.
> >
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