From: Jim Nasby <Jim.Nasby@BlueTreble.com>
To: Stephen Frost <sfrost@snowman.net>
To: julyanto SUTANDANG <julyanto@equnix.co.id>
Cc: Dinesh Chandra 12108 <Dinesh.Chandra@cyient.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Backup taking long time !!!
Date: Mon, 23 Jan 2017 09:28:34 -0600
Message-ID: <e71cdceb-779c-498d-f6ce-66b9002b54cf@BlueTreble.com> (raw)
In-Reply-To: <20170122173245.GH18360@tamriel.snowman.net>
References: <0fb0c8c3f69340a3a7d70b1c39fa2b2c@cyient.com>
<CAGu3fER+B6dFk=7F+DEMPPOSerYZs0HZMOfVAkbKs5Gdi-B-mg@mail.gmail.com>
<20170122145511.GD18360@tamriel.snowman.net>
<CAGu3fESxPSSqbKrV-UHdVnWnLK0vYk2fTmxjNOoEtevW39j1uw@mail.gmail.com>
<20170122163748.GG18360@tamriel.snowman.net>
<CAGu3fEREP2Vg_n6nqOoOg0Rv_XeHYFrHVKQUQXWj6TxVFUNZ=w@mail.gmail.com>
<20170122173245.GH18360@tamriel.snowman.net>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 1/22/17 11:32 AM, Stephen Frost wrote:
> The 1-second window concern is regarding the validity of a subsequent
> incremental backup.
BTW, there's a simpler scenario here:
Postgres touches file.
rsync notices file has different timestamp, starts copying.
Postgres touches file again.
If those 3 steps happen in the same second, you now have an invalid
backup. There's probably other scenarios as well.
In short, if you're using rsync, it's *critical* that you give it the
--checksum option, which tells rsync to ignore file size and timestamp.
>>>> my latter explanation is: use pg_basebackup, it will do it automatically
>>>> for you.
>>> Yes, if you are unsure about how to perform a safe backup properly,
>>> using pg_basebackup or one of the existing backup tools is, by far, the
>>> best approach. Attempting to roll your own backup system based on rsync
>>> is not something I am comfortable recommending any more because it is
>>> *not* simple to do correctly.
>> OK, that is fine, and actually we are using that.
> You must be sure to use one of the methods with pg_basebackup that keeps
> all of the WAL created during the full backup. That would be one of:
> pg_basebackup -x, pg_basebackup -X stream, or pg_basebackup +
> pg_receivexlog.
>
>> the reason why i explain about start_backup and stop_backup is to give a
>> gradual understand, and hoping that people will get the mechanism in the
>> back understandable.
> I'm more than happy to have people explaining about
> pg_start/stop_backup, but I do have an issue when the explanation is
> incorrect and could cause a user to use a backup method which will
> result in an invalid backup.
The other *critical* thing with PITR backups: you must test EVERY backup
that you take. No test == no backup. There's far, far too many things
that can go wrong, especially if you're rolling your own tool.
The complexities around PITR are why I always recommend also using
pg_dump on a periodic (usually weekly) basis as part of your full DR
strategy. You'll probably never use the pg_dump backups, but (in most
cases) they're a really cheap insurance policy.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Jim.Nasby@BlueTreble.com, sfrost@snowman.net, julyanto@equnix.co.id, Dinesh.Chandra@cyient.com
Subject: Re: Backup taking long time !!!
In-Reply-To: <e71cdceb-779c-498d-f6ce-66b9002b54cf@BlueTreble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox