Subject: Re: Backup taking long time !!!
To: Stephen Frost <sfrost@snowman.net>, julyanto SUTANDANG
	<julyanto@equnix.co.id>
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>
CC: Dinesh Chandra 12108 <Dinesh.Chandra@cyient.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
From: Jim Nasby <Jim.Nasby@BlueTreble.com>
Message-ID: <e71cdceb-779c-498d-f6ce-66b9002b54cf@BlueTreble.com>
Date: Mon, 23 Jan 2017 09:28:34 -0600
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.11; rv:45.0)
 Gecko/20100101 Thunderbird/45.6.0
MIME-Version: 1.0
In-Reply-To: <20170122173245.GH18360@tamriel.snowman.net>
Content-Type: text/plain; charset="windows-1252"; format=flowed
Content-Transfer-Encoding: 7bit
Received-SPF: None (protection.outlook.com: bluetreble.com does not designate
 permitted sender hosts)
SpamDiagnosticOutput: 1:99
SpamDiagnosticMetadata: NSPM
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 23 Jan 2017 15:28:47.8019 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-Transport-CrossTenantHeadersStamped: BN6PR11MB1636
X-Pg-Spam-Score: -1.9 (-)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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